Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Part 3: Postgres
Tags: api, webdev, dart, sql
Published: 2018-05-22 23:01:16

---
title: Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Part 3: Postgres
published: true
description: Learn how to build your own RESTful Web APIs with Dart, Aqueduct, and PostgreSQL
tags: api, webdev, dart, sql
series: Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL
---

![Featured image for Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL](https://thepracticaldev.s3.amazonaws.com/i/dv19znif0x9wpvdcdt13.jpg)

---

_**PLEASE NOTE: As of Dart 2 the API for Aqueduct has changed, leading to breaking changes. This article was based on Aqueduct 2.5.0 for Dart v1.**_

_**I have updated this as a new video series: [http://bit.ly/aqueduct-tutorial](http://bit.ly/aqueduct-tutorial)**_

---

In Part 2, we implemented a "/books" route with CRUD capabilities as part of our FaveReads reading list app, having looked at Aqueduct's _Routers_ and _HTTPControllers_. The final solution left us with completed routes (\*\0/\*) and a `List` type acting as our datastore.

This article is part of a series, covering these topics:

* [Part 1: Setting up and running the example](https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresql-55k)
* [Part 2: Implementing routing with CRUD operations](https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresqlpart-2-routing-53ip)
* **Part 3: Connecting Web APIs to PostgreSQL database** (we're here)
* [Part 4: Writing automated tests](https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresqlpart-4-testing-3974)
* [**\*Bonus content\*** DB Migration and Model Relationships](https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresqlbonus-content-551g) 😄

In this part we'll replace the list with a PostgreSQL database, allowing us to persist the information sent to our APIs. We will be using the inbuilt ORM to help with this.

---

## 1. Setup a PostgreSQL server

I'd recommend installing [Postgres.app](https://postgresapp.com) on Mac as it's the easiest way to get going or use the [official download page](https://www.postgresql.org/download/) 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:

```sql
CREATE DATABASE fave_reads;
CREATE USER dartuser;
ALTER USER dartuser WITH password 'dbpass123';
GRANT all ON database fave_reads TO dart;
```
_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:

```dart
import '../fave_reads.dart';

class Book extends ManagedObject<_Book> implements _Book {}

class _Book {
  @managedPrimaryKey
  int id;

  String title;
  String author;
  int year;
}
```
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](https://aqueduct.io/docs/db/modeling_data/)_

## 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:

```dart
class FaveReadsSink extends RequestSink {
  FaveReadsSink(ApplicationConfiguration appConfig) : super(appConfig) {
    logger.onRecord.listen(
        (rec) => print("$rec ${rec.error ?? ""} ${rec.stackTrace ?? ""}"));

    var managedDataModel = new ManagedDataModel.fromCurrentMirrorSystem(); // load our models
    var persistentStore = new PostgreSQLPersistentStore.fromConnectionInfo(
      "dartuser", "dbpass123", "localhost", 5432, "fave_reads"); // configure the db connection

    ManagedContext.defaultContext = new ManagedContext(managedDataModel, persistentStore);
  }
  // ...
  // ...remaining logic
}
```
We load our models, configure our DB connection, and pass these as dependencies, forming 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:

```dart
class FaveReadsSink extends RequestSink {
  //...
  //...
  //...
  Future createDatabaseSchema(ManagedContext context) async {
    var builder = new SchemaBuilder.toSchema(
      context.persistentStore,
      new Schema.fromDataModel(context.dataModel),
      isTemporary: false); // Set to false to persist our data

    for (var cmd in builder.commands) {
      await context.persistentStore.execute(cmd);
    }
  }
}
```
A bunch of SQL commands are generated, which are executed in the `for` loop. Within the `willOpen` method, lets trigger this operation:

```dart
@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:

```dart
// 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:

```dart
@httpGet
Future<Response> getAllBooks() async {
  var query = new Query<Book>();
  return new Response.ok(await query.fetch());
}
```
And modify `getBook` responder method as follows:

```dart
@httpGet
Future<Response> getBook(@HTTPPath("index") int idx) async {
  var query = new Query<Book>()..where.id = whereEqualTo(idx);
  var book = await query.fetchOne();

  if (book == null) {
    return new Response.notFound(body: 'Book does not exist')
      ..contentType = ContentType.TEXT;
  }
  return new Response.ok(book);
}
```
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:

```sql
SELECT (id, name, author, year) FROM _book WHERE id = "<idx>"
```
And here is the fully updated `BooksController` with the various query methods:

```dart
class BooksController extends HTTPController {
  @httpGet
  Future<Response> getAllBooks() async {
    var query = new Query<Book>();
    return new Response.ok(await query.fetch());
  }

  @httpGet
  Future<Response> getBook(@HTTPPath("index") int idx) async {
    var query = new Query<Book>()..where.id = whereEqualTo(idx);
    var book = await query.fetchOne();

    if (book == null) {
      return new Response.notFound(body: 'Book does not exist')
        ..contentType = ContentType.TEXT;
    }
    return new Response.ok(book);
  }

  @httpPost
  Future<Response> addBook(@HTTPBody() Book book) async {
    var query = new Query<Book>()..values = book;
    return new Response.ok(await query.insert());
  }

  @httpPut
  Future<Response> updateBook(@HTTPPath("index") int idx, @HTTPBody() Book book) async {
    var query = new Query<Book>()
      ..values = book
      ..where.id = whereEqualTo(idx);
    var updatedBook = await query.updateOne();

    if (updatedBook == null) {
      return new Response.notFound(body: 'Book does not exist');
    }
    return new Response.ok(updatedBook);
  }

  @httpDelete
  Future<Response> deleteBook(@HTTPPath("index") int idx) async {
    var query = new Query<Book>()..where.id = whereEqualTo(idx);
    var deletedBookId = await query.delete();

    if (deletedBookId == 0) {
      return new Response.notFound(body: 'Book does not exist');
    }
    return new Response.ok('Successfully deleted book.');
  }
}
```
Restart the application and test with [Postman](https://www.getpostman.com/apps).

## 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](https://github.com/graphicbeacon/favereads) and will be updated as we go through the series. Stay tuned for more.

---

## Further reading

1. [Aqueduct Database Overview](https://aqueduct.io/docs/db/overview/)
2. [Aqueduct ORM Snippets](https://aqueduct.io/docs/snippets/orm/)
3. [ManagedObject\<T> Class Documentation](https://www.dartdocs.org/documentation/aqueduct/latest/aqueduct/ManagedObject-class.html)
4. [ManagedContext Class Documentation](https://www.dartdocs.org/documentation/aqueduct/latest/aqueduct/ManagedContext-class.html)

---

{% link https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresqlpart-4-testing-3974 %}

---

_Originally posted on [Medium](https://medium.com/@98d967ac6f3a/90ff70742675)_