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

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

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:

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:

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:

building our _author table deleting the _author column in our _book table adding a book_id column in the author table

In order to apply these changes, create a database.yaml file in the project root with the details below:

username: "dartuser"

password: "dbpass123"

host: "localhost"

port: 5432

databaseName: "fave_reads"

Then running the command below will upgrade the database to the updated schema:

aqueduct db upgrade

database upgrade example

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:

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:

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

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:

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

}

}]

}

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

This expects a payload in the format:

{

"title": "Dart: Scalable Application Development",

"year": 2016,

"authors": [{

"name": "Dave Mitchells"

}]

}

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