DEV Community

Jermaine
Jermaine

Posted on • Updated on

Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Bonus content

Featured image goes here


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


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:

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:

aqueduct db generate
Enter fullscreen mode Exit fullscreen mode

This creates a migration file at migrations/00000001_Initial.migration.dart.

Initial migration file

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:

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;
}
Enter fullscreen mode Exit fullscreen mode

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:

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
}
Enter fullscreen mode Exit fullscreen mode

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:

aqueduct db upgrade --connect postgres://<user>:<password>@localhost:5432/<database-name>
Enter fullscreen mode Exit fullscreen mode

Updated database schema

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:

// 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
];
Enter fullscreen mode Exit fullscreen mode

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:

// 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;
});
Enter fullscreen mode Exit fullscreen mode

This allows us to amend the assertion of our first unit test as such:

// ...
// ...
test("GET /books returns list of books", () async {
  // ..
  // ..
  expectResponse(response, 200, body: allOf([
      hasLength(greaterThan(0)),
      everyElement(partial(
        {
          "title": isString,
          "year": isInteger,
          "authors": isList
        }))
    ]));
});
Enter fullscreen mode Exit fullscreen mode

The full changes to the tests are here.

4. Make the tests pass

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

// ..
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());
  }
  // ..
  // ..
}
Enter fullscreen mode Exit fullscreen mode

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:

{
  "id": 1,
  "title": "Dart: Scalable Application Development",
  "year": 2016,
  "authors": [{
    "id": 1,
    "name": "Dave Mitchells"
    "book": {
      "id": 1
    }
  }]
}
Enter fullscreen mode Exit fullscreen mode

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

// ..
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());
  }
  //..
}
Enter fullscreen mode Exit fullscreen mode

This expects a payload in the format:

{
  "title": "Dart: Scalable Application Development",
  "year": 2016,
  "authors": [{
    "name": "Dave Mitchells"
  }]
}
Enter fullscreen mode Exit fullscreen mode

The full changes can be seen here.

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.

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

Further reading

  1. Database Migration and Tooling
  2. Modelling Data
  3. Advanced Database Queries

Top comments (0)