Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Bonus content
Tags: webdev, dart, database, testing
Published: 2018-06-16 06:46:05

---
title: Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Bonus content
published: true
description: Learn how to build your own RESTful Web APIs with Dart, Aqueduct, and PostgreSQL
tags: webdev, dart, databases, testing
series: Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL
---

![Featured image goes here](https://thepracticaldev.s3.amazonaws.com/i/bckufxk26wr197vhvrvd.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)**_

---

Picking up from where we left off, we now have some written tests for our working APIs, having scaffolded our project, setup some routes and integrated a relational database.

This article has been part of a series, covering:

* [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](https://dev.to/graphicbeacon/building-restful-web-apis-with-dart-aqueduct-and-postgresqlpart-3-postgres-9c7)
* [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 _(we're here 😄)_

In this bonus part, we'll be covering Aqueduct's database migration tool and how that can help while making modifications to our current application model. We've only worked with the `Book` model so far, which was intentional in order to simplify things.

---

## 1. Create a migration file

Aqueduct's migration tool is useful for creating and executing migration files. These files contain SQL commands to create and modify database tables based on our current application model.

In the root of our project, let's generate our migration file by running the command below:

```bash
aqueduct db generate
```
This creates a migration file at `migrations/00000001_Initial.migration.dart`.

![Initial migration file](https://thepracticaldev.s3.amazonaws.com/i/8ut7mmydcl2fmu44y7fy.png)

Opening that file details the steps taken to build our database table based on the model definitions in `lib/model`, so `book.dart` in this case.

## 2. Create an Author model

The author of our current `Book` model is set as an instance property of type `String`, which translates to a column in the **_book** table in our database.

It works in this simple case, but what if we needed to specify multiple authors? Sure we could and use commas as a separator, splitting them when we process each author, but what if we wanted each author to have a short description? It certainly wouldn't be great to store all that information in a single database column!

Ideally we want to capture this `author` property as its own entity so that we could separate that concern as the application grows.

Let's create `lib/model/author.dart` with our `Author` model:

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

class Author extends ManagedObject<_Author> implements _Author {}

class _Author {
  @managedPrimaryKey
  int id;

  String name;

  @ManagedRelationship(#authors) // <--- Sets our column as a foreign key pointing to its associated book id
  Book book;
}
```
This is similar to `lib/model/book.dart`, except we now have established a one-many relationship where a book has many authors. We have a `book` property and using the `@ManagedRelationship()` annotation we set this relationship.

The first argument(#authors) is a symbol representing the property on our `Book` model that marks this relationship. Let's amend our `Book` model to have this property:

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

class Book extends ManagedObject<_Book> implements _Book {}

class _Book {
  @managedPrimaryKey
  int id;

  String title;
  int year;

  ManagedSet<Author> authors; // <-- over here
}
```
Our `authors` property is a `ManagedSet<T>` type, implying a book containing a collection of authors. This means that when we run our application a new table named **_author** will be created containing the columns `id`, `name` and `book_id`. The latter is a foreign key that points to the associated row id on the **_book** table.

Let's update our migrations directory by running `aqueduct db generate`. This will generate another `migration/*.migration.dart` file alongside the earlier one. It builds upon the initial migration file by adding SQL commands for:

1. building our **_author** table
2. deleting the `_author` column in our **_book** table
3. adding a `book_id` column in the author table

Running the command below will upgrade the database to the updated schema:

```bash
aqueduct db upgrade --connect postgres://<user>:<password>@localhost:5432/<database-name>
```
![Updated database schema](https://thepracticaldev.s3.amazonaws.com/i/qzvuicjzg40yywd4nhwu.png)

_This assumes that your database you're migrating to is empty of any pre-existing tables, i.e., the **_book** and **_author** tables do not exist, else an exception will be thrown._

## 3. Amend the tests

The setup of our tests need to be amended to contain our managed set of authors:

```dart
// Populate DB
var books = [
  new Book()
    ..title = "Head First Design Patterns"
    ..authors = (new ManagedSet()
      ..add(new Author()..name = "Bert Bates")
      ..add(new Author()..name = "Kathy Sierra")
      ..add(new Author()..name = "Eric Freeman"))
    ..year = 2004,
  new Book()
    ..title = "Clean Code: A handbook of Agile Software Craftsmanship"
    ..authors =
        (new ManagedSet()..add(new Author()..name = "Robert C. Martin"))
    ..year = 2008,
  new Book()
    ..title = "Code Complete: A Practical Handbook of Software Construction"
    ..authors =
        (new ManagedSet()..add(new Author()..name = "Steve McConnell"))
    ..year = 2004
];
```
And in the loop afterwards, we create two queries to insert the books and the authors, two because the model data goes into separate tables:

```dart
// Query for books
await Future.forEach(books, (Book b) async {
  var query = new Query<Book>()..values = b;
  var insertedBook = await query.insert();
  
  // Query for authors
  await Future.forEach(b.authors, (Author a) async {
    var query = new Query<Author>()
      ..values = a
      ..values.book = insertedBook; // Reference to its associated book, which becomes a foreign key column `book_id`
    return (await query.insert());
  });
  return insertedBook;
});
```
This allows us to amend the assertion of our first unit test as such:

```dart
// ...
// ...
test("GET /books returns list of books", () async {
  // ..
  // ..
  expectResponse(response, 200, body: allOf([
      hasLength(greaterThan(0)),
      everyElement(partial(
        {
          "title": isString,
          "year": isInteger,
          "authors": isList
        }))
    ]));
});
```
The full changes to the tests are [here](https://github.com/graphicbeacon/favereads/blob/master/test/books_controller_test.dart).

## 4. Make the tests pass

In `lib/controller/books_controller.dart` amend the `getAllBooks()` method as such:

```dart
// ..
class BooksController extends HTTPController {
  @httpGet
  Future<Response> getAllBooks() async {
    var query = new Query<Book>()..join(set: (book) => book.authors);
    return new Response.ok(await query.fetch());
  }
  // ..
  // ..
}
```
Here, we're using the `join` method to pull the row data from the **_author** table associated with each book.

Sending a GET request to `http://localhost:8000/books` will now return the JSON response below:

```json
{
  "id": 1,
  "title": "Dart: Scalable Application Development",
  "year": 2016,
  "authors": [{
    "id": 1,
    "name": "Dave Mitchells"
    "book": {
      "id": 1
    }
  }]
}
```

Our `addBook()` responder method is now amended as follows:

```dart
// ..
class BooksController extends HTTPController {
  //..
  @httpPost
  Future<Response> addBook(@HTTPBody() Book book) async {
    var query = new Query<Book>()..values = book;
    var insertedBook = await query.insert();

    // Insert authors from payload
    await Future.forEach(book.authors, (Author a) async {
      var author = new Query<Author>()
        ..values = a
        ..values.book = insertedBook; // set foreign key to inserted book

      return (await author.insert());
    });

    var insertedBookQuery = new Query<Book>()
      ..where.id = whereEqualTo(insertedBook.id);

    insertedBookQuery.join(set: (book) => book.authors)
      ..returningProperties((Author author) => [author.name]); // <-- Specify the columns returned for each author in the response

    return new Response.ok(await insertedBookQuery.fetchOne());
  }
  //..
}
```
This expects a payload in the format:

```json
{
  "title": "Dart: Scalable Application Development",
  "year": 2016,
  "authors": [{
    "name": "Dave Mitchells"
  }]
}
```
The full changes can be seen [here](https://github.com/graphicbeacon/favereads/blob/master/lib/controller/books_controller.dart).

## Conclusion

Check out the further reading materials below to understand database migration and model relationships in further detail. As always, feedback is welcome. Let me know what you liked, disliked and what you'd like to see next.

And this concludes the series. The source code is [available on github](https://github.com/graphicbeacon/favereads).

Thanks for sticking with me through the series and follow me for more Dart and Aqueduct tutorials.

## Further reading

1. [Database Migration and Tooling](http://aqueduct.io/docs/db/db_tools)
2. [Modelling Data](http://aqueduct.io/docs/db/modeling_data)
3. [Advanced Database Queries](http://aqueduct.io/docs/tut/model-relationships-and-joins/#more-on-joins-and-relationships)