Define Tables

There are two ways to define a table in Drift - using Dart code or using SQL code. Dart code is easier, but SQL code is more powerful and decouples the table definition from the Dart code. Generally, SQL code is preferred.

See how to define tables using Dart code and SQL code below.

SQL

SQL code is defined in .drift files. The following example defines the same table as above:

CREATE TABLE todos(
  -- The identifier for this todo.
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  -- The title of this todo.
  title TEXT NOT NULL,
  -- The content of this todo.
  body TEXT NOT NULL,
  -- Category of this todo.
  category INTEGER,
  -- Created at timestamp.
  created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
  -- Updated at timestamp.
  updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

Dart

/// Todos table definition
final class TodosTable extends Table {
  /// The identifier for this todo.
  IntColumn get id => integer().autoIncrement()();

  /// The title of this todo.
  TextColumn get title => text().withLength(min: 6, max: 32)();

  /// The content of this todo.
  TextColumn get content => text().named('body')();

  /// Category of this todo.
  IntColumn get category => integer().nullable()();

  /// Created at timestamp.
  DateTimeColumn get createdAt => dateTime().clientDefault(() => DateTime.now())();

  /// Updated at timestamp.
  DateTimeColumn get updatedAt => dateTime().nullable()();
}

To make code generation work, you must point to the .drift file or dart Table in the @DriftDatabase annotation:

@DriftDatabase(
  // For dart tables
  tables: [TodosTable],
  // For SQL files
  include: {'todos.drift'}
);

Column types

Drift supports the following column types:

• INTEGER - INTEGER
• double - REAL
• bool - INTEGER (0 or 1)
• String - TEXT
• DateTime - INTEGER (Unix timestamp) or TEXT
• Uint8List - BLOB
• Enum - INTEGER

Keys

Keys are used to enforce uniqueness of rows and to reference rows from other tables. Drift supports the following keys:

Primary Key

Primary key is a column or a set of columns that uniquely identify a row in a table. It is used to enforce uniqueness of rows and to reference rows from other tables.

Drift automatically defines a primary key if you use autoIncrement():

IntColumn get id => integer().autoIncrement()();

If you want to define a primary key manually:

SQL

CREATE TABLE todos(
  -- The identifier for this todo.
  id INTEGER AUTOINCREMENT,
  PRIMARY KEY(id, ...)
);

Dart

final class TodosTable extends Table {
  /// The identifier for this todo.
  IntColumn get id => integer().autoIncrement()();

  @override
  Set<Column> get primaryKey => {id};
}

Foreign Key

Foreign key is a column (or collection of columns) in one table that uniquely identifies a row of another table. The role of a foreign key is to enforce referential integrity within the database.

To reference a column from another table, use references:

SQL

CREATE TABLE todos(
  -- The category of this todo that references the id of the category.
  category INTEGER NOT NULL,
  FOREIGN KEY(category) REFERENCES categories(id)
);

Dart

final class TodosTable extends Table {
  /// The category of this todo that references the id of the category.
  IntColumn get categoryId => integer().nullable().references(categories, #id)();
}

Modifiers

Modifiers are used to define constraints on columns. Drift supports the following modifiers:

Default values

If no value is specified for a column when inserting a row, the database automatically uses the column’s default value.

SQL

CREATE TABLE todos(
  -- The title of this todo (default value is 'Untitled').
  title TEXT NOT NULL DEFAULT 'Untitled'
);

Dart

final class TodosTable extends Table {
  /// The title of this todo (default value is 'Untitled').
  TextColumn get title => text().withDefault(const Constant('Untitled'))();
}

Nullable columns

Some columns may not have a value, so they should be marked as nullable.

SQL

CREATE TABLE todos(
  -- Columns are null by default, so this is not necessary.
  -- category INTEGER is the same as category INTEGER NULL
  category INTEGER NULL
);

Dart

final class TodosTable extends Table {
  /// The category of this todo (nullable).
  IntColumn get category => integer().nullable()();
}

Auto-incrementing columns

Auto-incrementing columns are used to automatically generate a unique value for each new row. To define an auto-incrementing column, use autoIncrement:

SQL

CREATE TABLE todos(
  -- The identifier for this todo with auto-incrementing.
  id INTEGER PRIMARY KEY AUTOINCREMENT,
);

Dart

final class TodosTable extends Table {
  /// The identifier for this todo with auto-incrementing.
  IntColumn get id => integer().autoIncrement()();
}

Length constraints

To define a length constraint, use withLength:

SQL

CREATE TABLE todos(
  -- The title of this todo with length constraints.
  title TEXT NOT NULL CHECK(LENGTH(title) >= 6 AND LENGTH(title) <= 32)
);

Dart

final class TodosTable extends Table {
  /// The title of this todo with length constraints.
  TextColumn get title => text().withLength(min: 6, max: 32)();
}

Check constraints

Check constraints are used to enforce domain integrity. For example, to ensure that the price of a product is greater than 0 you may use:

SQL

CREATE TABLE todos(
  -- The price of this todo (greater than 0).
  price INTEGER NOT NULL CHECK(price > 0)
);

Dart

final class TodosTable extends Table {
  /// The price of this todo (greater than 0).
  IntColumn get price => integer().check(price.isBiggerThan(const Constant(0)))();
}

Indexes

Indexes improve read performance by making it faster to look up values in a column.

To create an index, you must use SQL code:

CREATE INDEX index_name ON table_name(column_name, ...);

If you want to maintain tables in Dart code, you need to import dart code into the .drift file:

import 'tables.dart';

CREATE INDEX index_name ON table_name(column_name, ...);

Triggers

Triggers are used to execute custom code when a certain event occurs in the database. To create a trigger, you must use SQL code.

Let’s create a trigger that will automatically update the updated_at column when a row is updated:

CREATE TRIGGER update_timestamp
AFTER UPDATE ON todos
FOR EACH ROW
BEGIN
  UPDATE todos SET updated_at = strftime('%s', 'now') WHERE id = OLD.id;
END;

Relationships

SQLite is a relational database, which means that it supports relationships between tables. There are three types of relationships:

• One-to-one (One table has a row that references another table’s row)
• One-to-many (One table has many rows that reference another table’s row)
• Many-to-many (One table has many rows that reference another table’s row and vice versa)

Since Drift is not an ORM, it does not automatically fetch relationships between tables.

To see how to fetch relationships, see Accessing Data.

One-to-one

In a one-to-one relationship between two tables, there can exist either zero or one record on each side of the relationship for a given primary key value. This means each primary key value in one table relates to at most one record in the other table.

Consider the following code that defines a one-to-one relationship between Persons and Passports:

SQL

-- Create the Person table
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    Name VARCHAR(255),
    date_of_birth INTEGER
);

-- Create the Passport table
CREATE TABLE passport (
    passport_id INTEGER PRIMARY KEY,
    issue_date INTEGER,
    expiry_date INTEGER,
    person_id INTEGER UNIQUE,
    FOREIGN KEY (person_id) REFERENCES person(id)
);

Dart

/// Person table definition
final class PersonTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  DateTimeColumn get dateOfBirth => dateTime()();
}

/// Passport table definition
final class PassportTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  DateTimeColumn get issueDate => dateTime()();
  DateTimeColumn get expiryDate => dateTime()();
  IntColumn get personId => integer().references(PersonTable, #id)();
}

One-to-many

In a one-to-many relationship between two tables, a row in one table can have multiple matching rows in the other table.

Consider the following code that defines one-to-many relationship between Customers and Orders:

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255),
    contact_info VARCHAR(255)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date INTEGER,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Dart

/// Customer table definition
final class CustomerTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  TextColumn get contactInfo => text()();
}

/// Order table definition
final class OrderTable extends Table {
  IntColumn get orderId => integer().autoIncrement()();
  DateTimeColumn get orderDate => dateTime()();
  IntColumn get customerId => integer().references(CustomerTable, #id)();
}

Many-to-many

In a many-to-many relationship between two tables, a row in one table can have multiple matching rows in the other table, and vice versa.

Creating a many-to-many relationship involves three tables: two main tables and a junction table that links the records in the main tables. Let’s consider an example with two entities, authors and books, where an author can write many books and a book can have many authors.

SQL

-- Create the Authors table
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    bio TEXT
);

-- Create the Books table
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    publication_year INTEGER
);

-- Create the junction table to link authors and books
CREATE TABLE author_book (
    author_id INTEGER,
    book_id INTEGER,
    PRIMARY KEY (author_id, book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Dart

/// Author table definition
final class AuthorTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  TextColumn get bio => text()();
}

/// Book table definition
final class BookTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  IntColumn get publicationYear => integer()();
}

/// AuthorBook table definition
final class AuthorBookTable extends Table {
  IntColumn get authorId => integer().references(AuthorTable, #id)();
  IntColumn get bookId => integer().references(BookTable, #id)();

  @override
  Set<Column> get primaryKey => {authorId, bookId};
}

Dart vs SQL

Dart code is known for its simplicity and beginner-friendly nature. However, SQL, with its numerous benefits, stands as a preferable choice in many scenarios. It’s recommended to invest time in understanding SQL, given its superior capabilities. SQL’s strength lies in its ability to separate table definitions from Dart code, granting access to the full spectrum of SQL’s features.

In situations demanding the creation of intricate tables or the formulation of extensive queries, SQL’s performance surpasses that of Dart code. Moreover, SQL’s syntax is not only more legible but also simpler to manage and maintain over time.