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
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
- Part 2: Implementing routing with CRUD operations
- Part 3: Connecting Web APIs to PostgreSQL database (we're here)
- Part 4: Writing automated tests
- *Bonus content* DB Migration and Model Relationships 😄
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 on Mac as it's the easiest way to get going or use the official download page 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:
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:
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
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:
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:
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:
@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:
// 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:
@httpGet
Future<Response> getAllBooks() async {
var query = new Query<Book>();
return new Response.ok(await query.fetch());
}
And modify getBook responder method as follows:
@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:
SELECT (id, name, author, year) FROM _book WHERE id = "<idx>"
And here is the fully updated BooksController with the various query methods:
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.
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 and will be updated as we go through the series. Stay tuned for more.
Further reading
- Aqueduct Database Overview
- Aqueduct ORM Snippets
- ManagedObject<T> Class Documentation
- ManagedContext Class Documentation
Article No Longer Available
Originally posted on Medium
Top comments (0)