Schema definition topic

For package:typed_sql to offer a convenient type-safe API for writing SQL queries we need to define the database schema and generate code from it.

Database Schema

The schema must be defined in a single Dart library, this is typically called model.dart, the generated part will be written to model.g.dart using build_runner. Thus, we must always include this part-file as follows:

import 'package:typed_sql/typed_sql.dart';

part 'model.g.dart';

We must then define a schema class. This is an abstract final class extending Schema, specifying what tables the database has. If we were working on a bookstore we might define a schema as follows:

abstract final class Bookstore extends Schema {
  Table<Author> get authors;
  Table<Book> get books;
}

The schema class is only allowed to have abstract getters that returns Table objects. Each such getter defines a table in the database. The T in Table<T> must be a row class specifying the table layout.

Note

Each table must have its own row class. It's not possible to reuse the same row class for multiple tables.

Row class for `Author`

For each table in our database we must define a row class. This is an abstract final class extending the Row class, specifying what fields the database table has. Continuing with the bookstore example we can define a row class for the authors table as follows:

@PrimaryKey(['authorId'])
abstract final class Author extends Row {
  @AutoIncrement()
  int get authorId;

  @Unique.field()
  String get name;
}

The Author row class specifies a table with two fields:

authorId, and,
name.

The authorId will be a 64 bit integer, auto-incremented by default and used as primary key. The name field will be TEXT and have two constraints NOT NULL (because the Dart getter isn't nullable) and UNIQUE because of the @Unique.field() annotation.

The equivalent SQL depends on the database, but it looks something like:

CREATE TABLE authors (
  authorId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  UNIQUE (name)
);

The following data types are allowed for fields:

bool,
int,
double,
String,
DateTime,
Uint8List,
JsonValue, and,
Custom subclasses of CustomDataType<T>.

These types are allowed to be nullable and non-nullable. When fields are non-nullable in the Dart row class the SQL table will have a NOT NULL constraint. For details on CustomDataType see Custom data types documentation.

Note

These row classes are intended to be data classes, they may not have constructors or members other than abstract public getters. Nor can they subclass or implement other classes.

If you want to add custom helper methods, you may write extension methods for your row classes.

`Book` row class with foreign key

Returning to our bookstore example, we still need to define a Book row class for the books table in the Bookstore schema. If we want the books table to have a foreign key referencing the authors table we can define the Book row class as follows:

@PrimaryKey(['bookId'])
abstract final class Book extends Row {
  @AutoIncrement()
  int get bookId;

  @Unique.field()
  String? get title;

  @References(
    // This fields references "authorId" from "authors" table
    table: 'authors',
    field: 'authorId',

    // The reference is _named_ "author", this gives rise to a
    // Expr<Book>.author property when building queries.
    name: 'author', // optional
    // This is referenced _as_ "books", this gives rise to a
    // Expr<Author>.books property when building queries.
    as: 'books', // optional
  )
  int get authorId;

  @DefaultValue(0)
  int get stock;
}

Note

The name and as properties in the @References annotation are optional. These gives rise to convinient subquery properties we can use when writing queries later.

The equivalent SQL depends on the database, but it looks something like:

CREATE TABLE books (
  bookId BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT,
  authorId BIGINT NOT NULL,
  stock BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (authorId) REFERENCES authors (authorId),
  UNIQUE (title)
);

Notice that because the title field is nullable, it does not have a NOT NULL constraint in the database.

Adding a default value

In our bookstore example above, the row class Book has a stock field annotated with @DefaultValue(0). This gives the stock field a default value of 0, and thus, causes the stock field to be optional when inserting rows.

@PrimaryKey(['bookId'])
abstract final class Book extends Row {
  @AutoIncrement()
  int get bookId;

  @Unique.field()
  String? get title;

  @References(table: 'authors', field: 'authorId', name: 'author', as: 'books')
  int get authorId;

  @DefaultValue(0) // Gives the `stock` field to have a default value!
  int get stock;
}

The equivalent SQL depends on the database, but it looks something like:

CREATE TABLE books (
  bookId BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT,
  authorId BIGINT NOT NULL,
  stock BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (authorId) REFERENCES authors (authorId),
  UNIQUE (title)
);

Notice the DEFAULT 0 clause in the declaration of the stock field.

It's possible to use the @DefaultValue(...) annotation to define a default value for fields with type:

bool,
int,
double,
String, and,
JsonValue.

However, since DateTime does not have a const constructor one of the following annoations must be used to create a DateTime field with a default value:

@DefaultValue.dateTime(year, [month, day, ...]),
@DefaultValue.epoch, or,
@DefaultValue.now.

With DefaultValue.now being a special constructor that uses CURRENT_TIMESTAMP in the database to ensure the field has a default value of DateTime.now().toUtc() when row is inserted.

See DefaultValue for further documentation on default values.

Generating code

Whenever the definition of the database schema is changed, it's important to run code generation. This is done using build_runner. If you don't have a dev-dependency on build_runner you may add this as follows:

dart pub add dev:build_runnner

You can run code-generation by invoking build_runner as follows:

dart run build_runner build

This should create or update the model.g.dart part-file for your model.dart library.

Using the database

When we've defined a schema and generated code we can start using the database. We must first create a Database instance using a DatabaseAdapter and SqlDialect as follows:

final db = Database<Bookstore>(adapter, dialect);

How you obtain a DatabaseAdapter and which SqlDialect to use depends on your database, as well as how you are connecting.

Once you have Database<Bookstore> instance you can create empty tables for your schema as follows:

// Create tables
await db.createTables();

Creating empty tables from scratch is mostly useful for testing, it's rarely needed in production. Instead you can use the generated create<Schema>Tables, which outputs the DDL for creating the tables.

// Get the database schema
final ddl = createBookstoreTables(SqlDialect.postgres());

This can then be used with external migration management tools to managing database migrations. See Migrations.

Note

The astute reader might notice that we never actually create an instance of Bookstore schema class. Instead the class is used to define a type we can parameterize the Database instance with. This is intentional to avoid juggling constructors, but it might be revised in the future.

With a Database<Bookstore> and tables created through migrations or db.createTables() you can insert data into the database as follows:

// Insert a row into the "authors" table
final author = await db.authors
    .insert(
      name: toExpr('Easter Bunny'),
    )
    .returnInserted()
    .executeAndFetch(); // returns Future<Author?>

// Insert a row into the "books" table
await db.books
    .insert(
      title: toExpr('How to hide eggs'),
      authorId: toExpr(author.authorId),
    )
    .execute();

Now we can also write queries against the database. The following demonstrates how to write a query that filters on the book title and only returns title and author.name.

// Query for books where the title contains 'eggs'
// select the title and author name
final titleAndAuthor = await db.books
    .where(
      (book) => book.title
          .orElseValue('') // because title can be null
          .toLowerCase()
          .containsValue('eggs'),
    )
    .select(
      (book) => (
        book.title,
        book.author.name, // use the 'author' subquery property
      ),
    )
    .fetch();

// Compare the results
check(titleAndAuthor).unorderedEquals([
  ('How to hide eggs', 'Easter Bunny'),
]);

The astute reader might notice that author.name is not actually a property on the books table. However, because we defined a foreign key on Book row class with the @References annotation, and gave it the name author, the Expr<Book> expression gets a subquery property book.author that allows us to access properties on the referenced authors row.

See References documentation for details.

Customize SQL generation

When generating SQL package:typed_sql will derive table names, column names, data-types and constraints from the Dart identifiers, types and annotations used in schema class and row classes. We can customize the generated SQL using the SqlOverride annotation, which can be used to override:

columnType, raw SQL data type to be used for a column,
defaultValue, raw SQL expression for a default value,
collation, raw SQL definition of collation,
name, column name, and,
naming convention, used for deriving the name from Dart identifier.

The overrides are hierarchical, and can be applied at different levels using the appropriate named constructor.

SqlOverride.schema, can be applied to a schema class to override schema-wide naming convention.
SqlOverride.tableName, can be applied to a table declaration inside a schema class to override the table name.
SqlOverride.table, can be applied to a row class to override table-wide naming convention.
SqlOverride.field, can be applied to a field declaration inside a row class to overide column-specific details.

To use snake_case for all table column names in the database, we can put an SqlOverride.schema annotation on the schema class, and still override the specific name for a table with a SqlOverride.tableName annotation later, as illustrated below:

@SqlOverride.schema(naming: .snake_case)
abstract final class Bookstore extends Schema {
  @SqlOverride.tableName(name: 'tbl_authors')
  Table<Author> get authors; // 'tbl_authors' in SQL

  Table<Book> get booksInStock; // 'books_in_stock' in SQL
}

The naming override on the schema class will be default for all tables, unless name or naming-convention is overriden further down. An override will be dialect-specific if a dialect parameter is given, this is often useful for things like collation or columnType which are rarely portable across databases. The example below shows how to use SqlOverride.field to specify collation when generating SQL for sqlite.

@PrimaryKey(['authorId'])
abstract final class Author extends Row {
  @AutoIncrement()
  int get authorId; // 'author_id' in SQL

  @Unique.field()
  @SqlOverride.field(name: 'author_name')
  @SqlOverride.field(dialect: 'sqlite', collation: 'NOCASE')
  String get name; // 'author_name' in SQL
}

The equivalent SQL depends on the database, but for sqlite it looks something like:

CREATE TABLE tbl_authors (
  author_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  author_name TEXT NOCASE NOT NULL,
  UNIQUE(author_name)
);

> [!WARNING]
> It is not possible to make a _dialect-specific_ override of `name` or
> `naming` convention, attempting to do so will cause a code-generation error.


<!-- GENERATED DOCUMENTATION LINKS -->
[Custom data types]: ../topics/custom_data_types-topic.html
[CustomDataType]: ../typed_sql/CustomDataType-class.html
[Database]: ../typed_sql/Database-class.html
[DatabaseAdapter]: ../typed_sql/DatabaseAdapter-class.html
[DefaultValue]: ../typed_sql/DefaultValue-class.html
[Migrations]: ../topics/migrations-topic.html
[References]: ../typed_sql/References-class.html
[Schema]: ../typed_sql/Schema-class.html
[SqlDialect]: ../typed_sql/SqlDialect-class.html
[SqlOverride]: ../typed_sql/SqlOverride-class.html
[SqlOverride.field]: ../typed_sql/SqlOverride/SqlOverride.field.html
[SqlOverride.schema]: ../typed_sql/SqlOverride/SqlOverride.schema.html
[SqlOverride.table]: ../typed_sql/SqlOverride/SqlOverride.table.html
[SqlOverride.tableName]: ../typed_sql/SqlOverride/SqlOverride.tableName.html
[Table]: ../typed_sql/Table-class.html

Schema definition topic

Database Schema

Row class for `Author`

`Book` row class with foreign key

Adding a default value

Generating code

Using the database

Customize SQL generation

Classes

Enums

typed_sql package

Schema definition topic

Schema definition topic

Database Schema

Row class for Author

Book row class with foreign key

Adding a default value

Generating code

Using the database

Customize SQL generation

Classes

Enums

typed_sql package

Schema definition topic

Row class for `Author`

`Book` row class with foreign key