1. Setup a PostgreSQL server

I’d recommend installing Postgres.app on Mac as it’s the easiest way to get going or use the official download page for other options/platforms.

Once the server is running, use its command-line tool( psql ) to create your database and a user that can access it with the SQL commands below:

CREATE DATABASE fave_reads;

CREATE USER dartuser;

ALTER USER dartuser WITH password 'dbpass123';

GRANT all ON database fave_reads TO dartuser;

If you’re using Postgresapp, you can open this command-line tool by clicking any of the database icons displayed in the window.

2. Refactor the Book class

To provide the correct model for our database, we need to extend the ManagedObject<T> class. A managed object handles the translation of database rows to application objects and vice-versa.

Amend the Book model in lib/model/book.dart as follows:

Our database columns will be based on the properties defined in _Book . The id property is marked as our primary key for each column, denoted by @managedPrimaryKey . It will auto-increment for every new book we add.

Learn more about how data is modelled

3. Create a ManagedContext

In order to tie our application to the database, it needs to:

Maintain a database connection

Execute database queries, and

Translate the results from the database row to application objects

This is where a ManagedContext comes in! It does exactly that.

Modify the FaveReadsSink constructor as follows:

We load our models, configure our DB connection, and pass these as dependencies that creates our managed context. This managed context is set under the static property defaultContext which will be required by Query<T> objects during interaction with our database (more on that later).

4. Build our database schema

We will use a schema builder for this. It will take our data model and Postgres connection and from that creates our database tables. To use this, let’s define a method called createDatabaseSchema in our FaveReadsSink class:

A bunch of SQL commands are generated, which are executed in the for loop. Within the willOpen method, lets trigger this operation:

@override

Future willOpen() async {

try {

await createDatabaseSchema(ManagedContext.defaultContext);

} catch (e) {}

}

The schema will be created before the application is ready to receive requests. The try/catch block is use to prevent the exception that throws the second time due to the schema already existing, consequently, exiting the process. There’s likely a better way to do this, so I’ll leave you to play with and own this😉

We should now be able to restart our application and should still run. Just ensure your PostgreSQL server is active.

Lastly, let’s refactor our BooksController to communicate with this database.

5. Refactor our BooksController class

Talking to our database involves creating query objects and invoking methods to create, read, update, and delete data. These query objects come from instances of the Query<T> class, where T represents our model:

// Example:

// Build your query

var query = new Query<Book>()

..values.name = ‘Book name’

..values.author = ‘John Smith’

..values.year = 2016; // Execute query

var result = await query.insert();

The Query<Book> object has a values property set to the instance of a managed object, in this case, being Book . The CRUD methods available include insert (create), fetch (read), update , and delete .

Let’s modify the getAllBooks responder method to read from our database:

@httpGet

Future<Response> getAllBooks() async {

var query = new Query<Book>();

return new Response.ok(await query.fetch());

}

And modify getBook responder method as follows:

The query object uses the where property which also is an instance of Book . This allows us to add filtering to our data. The whereEqualTo function on the same line is one of the matcher functions that come with Aqueduct. The above will execute the following SQL query:

SELECT (id, name, author, year) FROM _book WHERE id = "<idx>"

And here is the fully updated BooksController with the various query methods:

Restart the application and test with Postman.

Conclusion

We now have a fully working API as far as the backend is concerned. In Part 4, we will be writing some tests.

I’d encourage you to go through the further reading materials below to fully grasp some of the concepts we covered. As always, I’d love feedback. Let me know what you liked, disliked and what you’d want to see next. I’d really be grateful for that.

And this concludes Part 3 of the series. The source code is available on github and Part 4 is now available. Like and follow me if you enjoyed this article for more content on Dart.

Further reading