trestle 0.5.8

Database gateway and ORM

Trestle

Database gateway and ORM for Dart

---

• Abstract
• Getting started
• Gateway
  • Creating a table
  • Altering a table
  • Deleting a table
  • Accessing a table
• Migrations
• ORM
  • Extending the repository

---

Abstract

Trestle is the database package used in Bridge. It was created with extensibility and clean API in mind. Providing a unified interface to work with different databases across multiple setups for maximum reusability and agility.

The package is divided into two parts – the Gateway and the ORM. The Gateway is the common abstraction that the different database drivers implement, and the ORM uses the Gateway to talk to the database.

The Gateway has both a Schema Builder and a Query Builder, accessible from the common Gateway class.

One of the more controversial features of Trestle are the so called Predicate Expressions. They are callback-style lambda functions that are translated into SQL constraints. So we can say where((user) => user.age > 20), which then gets parsed into something like WHERE "age" > 20. An it works with pretty complex functions! As soon as you create a predicate that's too complex, the runtime will tell you in time, so that you can straighten things out.

Just know that Trestle doesn't get all rows and then run the constraint, even though that's what it looks like.

---

Getting started

To get started, choose what database implementation you want to use (you can easily change your mind later). In this example, we use the InMemoryDriver. It doesn't need schema and it doesn't need any configuration.

import 'package:trestle/gateway.dart';

main() async {
  // The database implementation
  Driver driver = new InMemoryDriver();

  // The gateway takes the driver as a constructor argument
  Gateway gateway = new Gateway(driver);

  // Next, connect!
  await gateway.connect();

  // ... Do some work

  // Disconnect when you're done
  await gateway.disconnect();
}

Later, if we want, we can just swap out the driver and call it a day.

// Driver driver = new InMemoryDriver();
// Driver driver = new SqliteDriver('storage/production.db');
// Driver driver = new MySqlDriver(username: 'myuser', password: '123', database: 'mydatabase');
Driver driver = new PostgresqlDriver(username: 'myuser', password: '123', database: 'mydatabase');

---

Gateway

Think of the gateway as the actual database in SQL. It contains the tables, which can be accessed and modified using a few simple methods.

Creating a table

To create a new table we use the create method on the Gateway class. This method takes two parameters: the name of the table to be created, and a callback containing the Schema Builder. It looks like this:

await gateway.create('users', (Schema schema) {
  schema.id(); // shortcut for an auto incrementing integer primary key
  schema.string('email').unique().nullable(false);
  schema.string('username').unique().nullable(false);
  schema.string('password', 60);
  schema.timestamps(); // adds created_at and updated_at timestamps (used by the ORM)
});

This method returns a Future (much like everything else in Trestle), and should probably be await-ed.

Altering a table

Altering a table is almost identical to creating one, except we use the alter method instead:

await gateway.alter('users', (Schema schema) {
  schema.drop('username');
  schema.string('first_name');
  schema.string('last_name');
});

Deleting a table

Deleting (or dropping) a table could not be simpler:

await gateway.drop('users');

Accessing a table

When we're satisfied with the columns of our table, we can start a query by calling the table method. This starts up the Query Builder, providing a fluent API to construct queries. The builder is stateless, so we can save intermediate queries in variables and fork them later:

// Full query
Stream allUsersOfDrinkingAge = gateway.table('users')
  .where((user) => user.age > 18).get(); // At least in Sweden...

// Intermediate query
Query uniqueAddresses = gateway.table('addresses').distinct();

// Continued query
Stream allUniqueAddressesInSweden = uniqueAddresses
  .where((address) => address.country == 'SWE').get();

// A function extending an intermediate query
Query allUniqueAddressesIn(String country) {
  return uniqueAddresses
    .where((address) => address.country == country);
}

// An aggregate query
int count = await allUniqueAddressesIn('USA').count();

There's a bunch of stuff you can do. Experiment with the query builder and report any bugs! 🐛

---

Migrations

You can think of migrations as version control for your database. It's an automated way to ensure that everyone on your team is using the same table schema. Each migration extends the Migration abstract class, enforcing the implementation of a run method, as well as a rollback method.

The run method makes a change to the database schema (using the familiar syntax). The rollback method reverses that change. For example, creating a table in run, and dropping it in rollback.

By storing a Set<Type> (where the types are subtypes of Migration), we can ensure that each migration is run in order. And if we need to change something, we can roll back and re-migrate.

class CreateUsersTable extends Migration {
  Future run(Gateway gateway) {
    gateway.create('users', (Schema schema) {
      schema.id();
      schema.string('email');
      // ...
    });
  }

  Future rollback(Gateway gateway) {
    gateway.drop('users');
  }
}

final migrations = [
  CreateUsersTable,
  // more migrations
  CreateAddressesTable,
  DropUsernameColumnInUsersTable,
].toSet();

// Somewhere in a command line utility or something
gateway.migrate(migrations);

// Somewhere else – remember to import the same migrations set
gateway.rollback(migrations);

---

ORM

Trestle's primary feature is to provide an ORM for the Bridge Framework. One of the key features of Bridge is the WebSocket transport system Tether. So it was important that Trestle would be able to map rows to plain Dart objects, that could be shared with the client.

So instead of embracing the full Active Record style, we had to move the database interaction from the data structures to a Repository class. However, using a plain object without any intrusive annotations is kind of brittle. So we can optionally extend a Model class and use annotations if we don't care that we're coupling ourselves to Trestle. It works like this:

// Create a data structure
class User {
  int id;
  String email;
  String first_name;
  String last_name;
  String password;
  int age;
}

// Or create a full model
class User extends Model {
  @field String email;
  @Field('first_name') String firstName;
  @Field('last_name') String lastName;
  @field String password;
  @field int age;
}

// Instantiate the repository with the model as a type argument.
final users = new Repository<User>();

// Connect the gateway (we do this in a separate step to prevent extended
// repositories from having to pass a constructor argument along)
users.connect(gateway);

// You're done! The repository works like `gateway.table('users')` would,
// but it returns `User` objects instead of maps.
User firstUser = await users.find(1);

Extending the repository

The Trestle ORM has the mindset convention over configuration, so it infers a lot of things. For instance, the Repository<User> automatically works with a users table. Repository<UpperCamelCase> would look for upper_camel_cases.

To override these options, we can extend the Repository like so:

class UsersRepository extends Repository<User> {
  String get table => 'my_users_table';
}

We can use this class to implement some query scopes or filters:

class UsersRepository extends Repository<User> {
  Query get ofDrinkingAge => where((user) => user.age > 20);
}

// And use it like so:
users.ofDrinkingAge.count();

---

In Bridge

As (soon to be) mentioned in the Bridge docs, Trestle is automatically set up for you, so we can use dependency injection to get immediate access to a repository:

// An example in the context of the HTTP router – not a part of Trestle
router.get('/users/count', (Repository<User> users) async {
  return 'There are ${await users.count()} users registered';
});