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:
- Part 1: Setting up and running the example
- Part 2: Implementing routing with CRUD operations
- Part 3: Connecting Web APIs to PostgreSQL database
- Part 4: Writing automated tests
- 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:
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:
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:
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:
- building our _author table
- deleting the
_authorcolumn in our _book table - adding a
book_idcolumn 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>
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
];
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;
});
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
}))
]));
});
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());
}
// ..
// ..
}
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:
// ..
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:
{
"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.
Top comments (0)