DEV Community

Jermaine
Jermaine

Posted on • Updated on

Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL — Part 3: Postgres

Featured image for Building RESTful Web APIs with Dart, Aqueduct, and PostgreSQL


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:

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

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

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

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

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

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

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

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

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

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

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

  1. Aqueduct Database Overview
  2. Aqueduct ORM Snippets
  3. ManagedObject<T> Class Documentation
  4. ManagedContext Class Documentation


Originally posted on Medium

Top comments (0)