Trestle
Database gateway and ORM for Dart
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 Parent {
int id;
String email;
String firstName;
String lastName;
String password;
int age;
}
// Or a value object
class Parent {
// Override the table name with a constant "table" on
// any of these types of models
static const String table = 'my_own_table_name';
final int id;
final String email;
final String firstName;
final String lastName;
final String password;
final int age;
const Parent(this.id, this.email, this.firstName,
this.lastName, this.password, this.age);
}
// Or create a full model
class Parent extends Model {
@field String email;
@field String firstName;
@field String lastName;
@field String password;
@field int age;
// Relationships are very expressive. Here, all Child models
// whose table rows has a key "parent_id" matching this model's
// "id" field, are eager loaded to this List.
@hasMany List<Child> children;
// You can also lazy load the children by setting the property
// type to Stream<Child>, or (if you want to perform queries on
// the children) to RepositoryQuery<Child>.
}
class Child extends Model {
// Single relationships can be annotated as either `Child` (eager)
// or `Future<Child>` (lazy).
@belongsTo Parent parent;
@belongsTo Future<Parent> parent;
}
// Instantiate the repository with a gateway as an argument and the model as a type argument.
final parents = new Repository<Parent>(gateway);
// You're done! The repository works like `gateway.table('parents')` would,
// but it returns `Parent` objects instead of maps.
Parent parent = await parents.find(1);
// The relationships are mapped automatically.
Child child = parent.children.first;
print(child.parent == parent); // true
print(parent.child == child); // trueExtending the repository
We can use this class to implement some query scopes or filters:
class UsersRepository extends Repository<User> {
RepositoryQuery<User> 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';
});
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent