Dart Drift

Dart Drift

You are a Dart persistence engineer for non-Flutter apps using Drift.

Principle 0

Do not write Drift database code from memory. First identify the runtime backend, read the routed reference for the operation, then verify generated code with the Dart toolchain. Broken persistence code is worse than no persistence code because schema and migration mistakes can silently lose data.

Workflow

1. Inspect the project before changing code: pubspec.yaml, existing database classes, build.yaml, generated parts, tests, migration files, and whether the app targets SQLite, PostgreSQL, or both. 2. Choose the backend path:

• For Dart CLI, server jobs, or native desktop local storage, use SQLite via

package:drift/native.dart.

• For server-side PostgreSQL, use drift_postgres with package:postgres

and enable the Postgres SQL dialect in build.yaml.

• For Flutter UI integration, stop and use the flutter-drift skill instead.

3. Read only the reference files needed for the requested task. 4. Implement the smallest safe change using the APIs and caveats from those references. 5. Run mandatory validation. If validation cannot run, report the blocker and the concrete risk instead of presenting the change as verified.

Resource Routing

Mandatory Validation

• After dependency changes, run dart pub get.
• After changing Drift tables, database classes, DAOs, SQL files, or generated

parts, run dart run build_runner build and dart analyze.

• After changing behavior, run the narrowest relevant dart test target. If no

tests exist, add or describe a focused smoke test for the database operation.

• After migration changes, run dart run drift_dev make-migrations and the

generated migration tests from the configured test_dir.

• After editing this skill, references, or reusable examples, run

dart-drift/scripts/verify-examples.sh.

Constraints

• Keep this skill scoped to Dart CLI, server-side, and non-Flutter desktop apps.

Do not add StreamBuilder, Riverpod, Provider, drift_flutter, or Flutter-specific storage patterns here.

• Prefer dart pub add ... for new dependencies. Only hardcode versions when

matching an existing repository policy.

• Do not use deprecated PostgreSQL examples based on HostEndpoint,

PgEndpoint, postgres_pool.dart, postgresUuid(), postgresJson(), PostgresTypes, or gen_random_uuid.

• For PostgreSQL, avoid SQLite-only helpers such as currentDateAndTime and

most dateTime() convenience APIs. Use PgTypes.date, PgTypes.timestampWithTimezone, PgTypes.timestampNoTimezone, and now().

• Do not replace code generation or migration validation with manual reasoning

unless the user explicitly waives the risk.

Fallback

If the requested repository lacks enough context to choose a backend, ask whether the target is SQLite or PostgreSQL. If network, database, or toolchain access prevents validation, finish with the exact command that failed and the remaining risk.

Migrations

Use this reference when changing a Drift schema after data may already exist. Schema changes must be generated and tested, not reasoned about by inspection alone.

Configure Guided Migrations

Add database paths to build.yaml:

targets:
  $default:
    builders:
      drift_dev:
        options:
          databases:
            app_database: lib/database.dart
          test_dir: test/drift/
          schema_dir: drift_schemas/

Generate an initial schema before changing the database:

dart run drift_dev make-migrations

After changing tables, bump schemaVersion and run it again:

dart run drift_dev make-migrations

This creates schema snapshots, generated migration helpers, and migration tests.

Step-by-Step Migrations

Use the generated stepByStep helper from the generated steps file:

import 'package:drift/drift.dart';

import 'database.steps.dart';

part 'database.g.dart';

@DriftDatabase(tables: [TodoItems, Categories])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.executor);

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onUpgrade: _schemaUpgrade,
      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON');
      },
    );
  }
}

extension MigrationSteps on GeneratedDatabase {
  OnUpgrade get _schemaUpgrade => stepByStep(
        from1To2: (m, schema) async {
          await m.addColumn(schema.todoItems, schema.todoItems.dueDate);
        },
      );
}

Keep migration step logic outside the database class body when practical so it does not accidentally reference the latest table getters instead of a generated schema snapshot.

Common Operations

Add a column:

from1To2: (m, schema) async {
  await m.addColumn(schema.todoItems, schema.todoItems.dueDate);
}

Create a table:

from1To2: (m, schema) async {
  await m.createTable(schema.categories);
}

Create an index or trigger generated by Drift:

from1To2: (m, schema) async {
  await m.create(schema.todosByDueDate);
}

Alter a table when constraints or generated columns changed:

from2To3: (m, schema) async {
  await m.alterTable(TableMigration(schema.todoItems));
}

Use raw SQL only when Drift does not expose a safer API:

from2To3: (m, schema) async {
  await m.customStatement('''
    UPDATE todo_items
    SET title = TRIM(title)
    WHERE title != TRIM(title)
  ''');
}

Manual Migrations

Manual migrations are more error-prone than guided migrations. If a project does not use make-migrations, keep steps monotonic and guarded by version checks:

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (m) async {
      await m.createAll();
    },
    onUpgrade: (m, from, to) async {
      if (from < 2) {
        await m.addColumn(todoItems, todoItems.dueDate);
      }
      if (from < 3) {
        await m.createTable(categories);
      }
    },
  );
}

Avoid running normal table queries inside migration callbacks. Generated query APIs usually assume the latest schema, which may not exist yet during an upgrade. Prefer migrator APIs or carefully scoped custom SQL.

Data Migration

If data must be transformed, make the schema change first, then update data:

from1To2: (m, schema) async {
  await m.addColumn(schema.users, schema.users.displayName);

  await m.customStatement('''
    UPDATE users
    SET display_name = first_name || ' ' || last_name
    WHERE display_name IS NULL
  ''');
}

For large tables, prefer chunked custom SQL or database-native migration tools instead of loading every row into Dart memory.

Testing

Run the generated migration tests:

dart test test/drift/

Migration tests should cover:

• upgrading from every retained schema snapshot;
• data integrity across transformations;
• created indexes, constraints, and triggers;
• foreign key behavior if SQLite foreign keys are enabled.

PostgreSQL Caveats

Drift can generate PostgreSQL-compatible SQL, but PostgreSQL migration practices often differ from SQLite. For complex production PostgreSQL migrations, consider database-native tools and use Drift for typed access after the migration.

For PostgreSQL date/time columns, prefer PgTypes.date, PgTypes.timestampWithTimezone, and PgTypes.timestampNoTimezone over SQLite-oriented dateTime() helpers.

PostgreSQL

Use this reference for drift_postgres, package:postgres, PostgreSQL custom types, pooling, and Postgres-specific caveats.

Dependencies

Prefer commands:

dart pub add drift drift_postgres postgres
dart pub add dev:drift_dev dev:build_runner

Enable PostgreSQL SQL generation in build.yaml:

targets:
  $default:
    builders:
      drift_dev:
        options:
          sql:
            dialects:
              - sqlite
              - postgres

Remove sqlite from the dialect list only when the database will never target SQLite.

Basic Database

import 'package:drift/drift.dart';
import 'package:drift_postgres/drift_postgres.dart';
import 'package:postgres/postgres.dart' as pg;

part 'postgres_database.g.dart';

class Users extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  TextColumn get name => text()();
  JsonColumn get settings => customType(PgTypes.jsonb)();
  TimestampColumn get createdAt =>
      customType(PgTypes.timestampWithTimezone).withDefault(now())();

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

@DriftDatabase(tables: [Users])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.executor);

  @override
  int get schemaVersion => 1;
}

Single Connection

AppDatabase openPostgresDatabase() {
  return AppDatabase(
    PgDatabase(
      endpoint: pg.Endpoint(
        host: 'localhost',
        database: 'mydb',
        username: 'user',
        password: 'password',
      ),
      settings: pg.ConnectionSettings(
        sslMode: pg.SslMode.disable,
        connectTimeout: const Duration(seconds: 10),
        queryTimeout: const Duration(seconds: 30),
        applicationName: 'my-dart-service',
      ),
    ),
  );
}

Use pg.SslMode.verifyFull or another production-appropriate SSL mode for public network connections.

Connection Pooling

Use pg.Pool.withEndpoints for services:

final pool = pg.Pool.withEndpoints(
  [
    pg.Endpoint(
      host: 'localhost',
      database: 'mydb',
      username: 'user',
      password: 'password',
    ),
  ],
  settings: pg.PoolSettings(
    maxConnectionCount: 20,
    maxConnectionAge: const Duration(hours: 1),
    applicationName: 'my-dart-service',
  ),
);

final db = AppDatabase(PgDatabase.opened(pool));

Close both layers:

await db.close();
await pool.close();

Do not use old examples based on postgres_pool.dart, PgPool, or PgEndpoint.

Custom Types

drift_postgres exposes PgTypes:

class Events extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  JsonColumn get payload => customType(PgTypes.jsonb)();
  Column<List<String>> get tags => customType(PgTypes.textArray)();
  PgDateColumn get eventDate => customType(PgTypes.date)();
  TimestampColumn get createdAt =>
      customType(PgTypes.timestampWithTimezone).withDefault(now())();
}

Common types:

• PgTypes.uuid maps to UuidValue
• PgTypes.json and PgTypes.jsonb map to JSON-like Dart objects
• PgTypes.textArray maps to List<String>
• PgTypes.date maps to PgDate
• PgTypes.timestampWithTimezone and PgTypes.timestampNoTimezone map to

PgDateTime

PostgreSQL Functions

Use exported helpers when available:

UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
TimestampColumn get createdAt =>
    customType(PgTypes.timestampWithTimezone).withDefault(now())();

For PostgreSQL functions that drift_postgres does not wrap, use FunctionCallExpression or customSelect.

Queries

Array contains:

final tagged = await (db.select(db.events)
      ..where((event) => event.tags.contains('dart')))
    .get();

JSON operators are often clearest as custom SQL:

final rows = await db.customSelect(
  "SELECT * FROM events WHERE payload ->> 'kind' = ?",
  variables: [Variable.withString('created')],
  readsFrom: {db.events},
).get();

Full text search is also a custom SQL use case:

final rows = await db.customSelect(
  '''
  SELECT * FROM posts
  WHERE to_tsvector('english', content) @@ plainto_tsquery('english', ?)
  ''',
  variables: [Variable.withString(searchTerm)],
  readsFrom: {db.posts},
).get();

Indexes

Use raw SQL indexes for PostgreSQL-specific index types:

@TableIndex.sql('''
  CREATE INDEX events_payload_kind_idx ON events ((payload ->> 'kind'));
''')
class Events extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  JsonColumn get payload => customType(PgTypes.jsonb)();
}

@TableIndex.sql('''
  CREATE INDEX events_tags_gin_idx ON events USING GIN (tags);
''')
class Events extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  Column<List<String>> get tags => customType(PgTypes.textArray)();
}

Caveats

• PostgreSQL support is stable, but Drift has more SQLite-first helper APIs than

PostgreSQL-specific wrappers.

• Avoid SQLite-oriented currentDateAndTime and most dateTime() helper APIs

in PostgreSQL schemas.

• Use real PostgreSQL integration tests for Postgres behavior. SQLite in-memory

tests only validate shared query-builder behavior.

• For complex production migrations, consider PostgreSQL-native migration tools

and keep Drift focused on typed access after the migration.

Queries

Use this reference for Drift SELECTs, filters, ordering, joins, aggregates, and custom SQL reads.

Basic Select

final allTodos = await db.select(db.todoItems).get();
final todoStream = db.select(db.todoItems).watch();

Where

final completedTodos = await (db.select(db.todoItems)
      ..where((t) => t.isCompleted.equals(true)))
    .get();

Combine expressions with & and |:

final importantOpenTodos = await (db.select(db.todoItems)
      ..where(
        (t) => t.isCompleted.equals(false) & t.priority.isBiggerThanValue(3),
      ))
    .get();

Common operators:

• equals(value)
• isBiggerThanValue(value)
• isBiggerOrEqualValue(value)
• isSmallerThanValue(value)
• isSmallerOrEqualValue(value)
• like(pattern)
• contains(value) for text contains
• isNull() and isNotNull()

Limit and Order

final page = await (db.select(db.todoItems)
      ..orderBy([(t) => OrderingTerm.desc(t.createdAt)])
      ..limit(20, offset: 40))
    .get();

Single Row

final todo = await (db.select(db.todoItems)
      ..where((t) => t.id.equals(id)))
    .getSingle();

final maybeTodo = await (db.select(db.todoItems)
      ..where((t) => t.id.equals(id)))
    .getSingleOrNull();

Use watchSingle() and watchSingleOrNull() for reactive single-row streams.

Mapping Rows

final titles = await (db.select(db.todoItems)
      ..where((t) => t.title.length.isBiggerOrEqualValue(10)))
    .map((row) => row.title)
    .get();

Joins

class TodoWithCategory {
  TodoWithCategory({required this.todo, required this.category});

  final TodoItem todo;
  final Category? category;
}

final rows = await db.select(db.todoItems).join([
  leftOuterJoin(
    db.categories,
    db.categories.id.equalsExp(db.todoItems.categoryId),
  ),
]).map((row) {
  return TodoWithCategory(
    todo: row.readTable(db.todoItems),
    category: row.readTableOrNull(db.categories),
  );
}).get();

For self-joins, alias the table:

final otherTodos = alias(db.todoItems, 'other_todos');

final related = await db.select(otherTodos).join([
  innerJoin(
    db.todoItems,
    db.todoItems.categoryId.equalsExp(otherTodos.categoryId),
    useColumns: false,
  ),
]).map((row) => row.readTable(otherTodos)).get();

Aggregates

Count all rows:

final countExp = db.todoItems.id.count();

final count = await (db.selectOnly(db.todoItems)..addColumns([countExp]))
    .map((row) => row.read(countExp) ?? 0)
    .getSingle();

Group by a column:

final category = db.todoItems.categoryId;
final countExp = db.todoItems.id.count();

final counts = await (db.selectOnly(db.todoItems)
      ..addColumns([category, countExp])
      ..groupBy([category]))
    .map((row) {
  return (
    categoryId: row.read(category),
    count: row.read(countExp) ?? 0,
  );
}).get();

Average:

final avgExp = db.todoItems.priority.avg();

final averagePriority = await (db.selectOnly(db.todoItems)..addColumns([avgExp]))
    .map((row) => row.read(avgExp))
    .getSingle();

Custom Columns

final isImportant = db.todoItems.title.like('%important%');

final rows = await (db.select(db.todoItems)..addColumns([isImportant]))
    .map((row) {
  return (
    todo: row.readTable(db.todoItems),
    important: row.read(isImportant) ?? false,
  );
}).get();

Exists

final existsExp = existsQuery(
  db.select(db.todoItems)..where((t) => t.id.equals(id)),
);

final exists = await (db.selectOnly(db.todoItems)..addColumns([existsExp]))
    .map((row) => row.read(existsExp) ?? false)
    .getSingle();

Custom SQL Reads

Use customSelect when the query builder does not expose a database-specific feature. Always pass variables separately and declare readsFrom for streams:

final rows = await db.customSelect(
  'SELECT * FROM todo_items WHERE title LIKE ?',
  variables: [Variable.withString('%drift%')],
  readsFrom: {db.todoItems},
).get();

For PostgreSQL-specific SQL, prefer customSelect over inventing unsupported expression APIs.

Setup

Use this reference when adding Drift to Dart CLI tools, server processes, or native desktop apps. For Flutter apps, use the flutter-drift skill.

Dependencies

Prefer commands so the project receives current compatible versions:

dart pub add drift sqlite3
dart pub add dev:drift_dev dev:build_runner

For PostgreSQL support, also add:

dart pub add drift_postgres postgres

If a project requires manual pubspec.yaml edits, keep drift and drift_dev on the same minor release when possible and follow the existing repository version policy.

Code Generation

Every generated database file needs a part directive and a build step:

import 'package:drift/drift.dart';

part 'database.g.dart';

dart run build_runner build

Use dart run build_runner watch only during interactive development.

SQLite Database

Use package:drift/native.dart for non-Flutter native Dart apps:

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';

part 'database.g.dart';

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 200)();
  TextColumn get content => text().nullable()();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

@DriftDatabase(tables: [TodoItems])
class AppDatabase extends _$AppDatabase {
  AppDatabase([QueryExecutor? executor]) : super(executor ?? _openConnection());

  @override
  int get schemaVersion => 1;
}

QueryExecutor _openConnection() {
  return NativeDatabase.createInBackground(File('db.sqlite'));
}

Do not open SQLite manually with sqlite3.open() before passing it to Drift unless the project has a specific reason to manage the low-level connection.

PostgreSQL Database

Configure the SQL dialect before generating code:

targets:
  $default:
    builders:
      drift_dev:
        options:
          sql:
            dialects:
              - sqlite
              - postgres

Use pg.Endpoint from package:postgres:

import 'package:drift/drift.dart';
import 'package:drift_postgres/drift_postgres.dart';
import 'package:postgres/postgres.dart' as pg;

part 'database.g.dart';

@DriftDatabase(tables: [Users])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.executor);

  @override
  int get schemaVersion => 1;
}

AppDatabase openPostgresDatabase() {
  return AppDatabase(
    PgDatabase(
      endpoint: pg.Endpoint(
        host: 'localhost',
        database: 'mydb',
        username: 'user',
        password: 'password',
      ),
      settings: pg.ConnectionSettings(
        sslMode: pg.SslMode.disable,
      ),
    ),
  );
}

For production services, prefer a pool. Close both the Drift database and the pool during shutdown:

final pool = pg.Pool.withEndpoints(
  [
    pg.Endpoint(
      host: 'localhost',
      database: 'mydb',
      username: 'user',
      password: 'password',
    ),
  ],
  settings: pg.PoolSettings(maxConnectionCount: 10),
);

final db = AppDatabase(PgDatabase.opened(pool));

Future<void> shutdown() async {
  await db.close();
  await pool.close();
}

Tests

Use an in-memory SQLite database for fast unit tests:

AppDatabase createTestDatabase() {
  return AppDatabase(NativeDatabase.memory());
}

PostgreSQL behavior needs integration tests against a real PostgreSQL database. Do not claim PostgreSQL behavior is verified from an in-memory SQLite test.

Streams

Use this reference for Drift streams in Dart services, CLI tools, and native desktop apps. Do not add Flutter StreamBuilder, Provider, or Riverpod examples to this skill.

Basic Watch

Stream<List<TodoItem>> watchTodos(AppDatabase db) {
  return db.select(db.todoItems).watch();
}

Filtered stream:

Stream<List<TodoItem>> watchOpenTodos(AppDatabase db) {
  return (db.select(db.todoItems)
        ..where((t) => t.isCompleted.equals(false))
        ..orderBy([(t) => OrderingTerm.desc(t.createdAt)]))
      .watch();
}

Single-row stream:

Stream<TodoItem> watchTodo(AppDatabase db, int id) {
  return (db.select(db.todoItems)..where((t) => t.id.equals(id))).watchSingle();
}

Nullable single-row stream:

Stream<TodoItem?> watchMaybeTodo(AppDatabase db, int id) {
  return (db.select(db.todoItems)..where((t) => t.id.equals(id)))
      .watchSingleOrNull();
}

Watch Joins

class TodoWithCategory {
  TodoWithCategory({required this.todo, required this.category});

  final TodoItem todo;
  final Category? category;
}

Stream<List<TodoWithCategory>> watchTodosWithCategories(AppDatabase db) {
  final query = db.select(db.todoItems).join([
    leftOuterJoin(
      db.categories,
      db.categories.id.equalsExp(db.todoItems.categoryId),
    ),
  ]);

  return query.map((row) {
    return TodoWithCategory(
      todo: row.readTable(db.todoItems),
      category: row.readTableOrNull(db.categories),
    );
  }).watch();
}

Custom SQL Streams

For custom SQL, use customSelect(...).watch() and declare readsFrom. Without readsFrom, Drift cannot know which table changes should re-run the stream.

Stream<List<QueryRow>> watchCompletedRows(AppDatabase db) {
  return db.customSelect(
    '''
    SELECT * FROM todo_items
    WHERE is_completed = ?
    ORDER BY created_at DESC
    ''',
    variables: [Variable.withBool(true)],
    readsFrom: {db.todoItems},
  ).watch();
}

If a generated table mapper is available, map QueryRow.data:

Stream<List<TodoItem>> watchCompletedTodos(AppDatabase db) {
  return watchCompletedRows(db).map(
    (rows) => rows.map((row) => db.todoItems.map(row.data)).toList(),
  );
}

Custom Writes and Stream Updates

When using custom writes, declare affected tables:

Future<void> markDone(AppDatabase db, int id) {
  return db.customUpdate(
    'UPDATE todo_items SET is_completed = ? WHERE id = ?',
    variables: [
      Variable.withBool(true),
      Variable.withInt(id),
    ],
    updates: {db.todoItems},
    updateKind: UpdateKind.update,
  );
}

For changes made outside Drift, manually notify the stream query manager:

void notifyExternalTodoUpdate(AppDatabase db) {
  db.notifyUpdates({
    TableUpdate.onTable(db.todoItems, kind: UpdateKind.update),
  });
}

Table Update Events

Listen to raw table update events with tableUpdates:

Stream<Set<TableUpdate>> todoUpdateEvents(AppDatabase db) {
  return db.tableUpdates(TableUpdateQuery.onTable(db.todoItems));
}

final subscription = todoUpdateEvents(db).listen((updates) {
  for (final update in updates) {
    print('${update.table} changed: ${update.kind}');
  }
});

Cancel long-lived subscriptions during shutdown:

await subscription.cancel();

Operational Notes

• Streams re-run when watched tables change, not only when matching rows change.
• Writes made outside Drift do not update streams until notifyUpdates is

called.

• Keep stream queries fast and indexed. A stream query may run many times during

one process lifetime.

• Use standard Dart stream transforms unless the project already depends on a

stream utility package.

Table Definitions

Use this reference for table classes, constraints, indexes, defaults, and backend-specific column choices.

Basic Table

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 200)();
  TextColumn get content => text().nullable()();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

Register tables on the database:

@DriftDatabase(tables: [TodoItems, Categories])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.executor);

  @override
  int get schemaVersion => 1;
}

Common Column Types

Use nullable() to allow null values:

IntColumn get categoryId => integer().nullable()();

Defaults

SQLite server-side default:

DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();

Dart-side default:

BoolColumn get isActive => boolean().clientDefault(() => true)();

For PostgreSQL timestamps, prefer PgTypes and now() from drift_postgres instead of SQLite-oriented dateTime() helpers.

Keys and Constraints

Auto-increment primary key:

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

Custom primary key:

class Profiles extends Table {
  TextColumn get email => text()();

  @override
  Set<Column<Object>>? get primaryKey => {email};
}

Foreign key:

class Albums extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get artistId => integer().references(Artists, #id)();
}

Enable SQLite foreign keys when opening the database:

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );
}

Unique constraints:

TextColumn get username => text().unique()();

class Reservations extends Table {
  TextColumn get room => text()();
  DateTimeColumn get onDay => dateTime()();

  @override
  List<Set<Column<Object>>>? get uniqueKeys => [
        {room, onDay},
      ];
}

Check constraint:

IntColumn get age => integer().check(age.isBiggerOrEqualValue(0))();

Indexes

@TableIndex(name: 'users_by_name', columns: {#name})
class Users extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
}

@TableIndex(
  name: 'log_entries_by_time',
  columns: {IndexedColumn(#loggedAt, orderBy: OrderingMode.desc)},
)
class LogEntries extends Table {
  IntColumn get id => integer().autoIncrement()();
  DateTimeColumn get loggedAt => dateTime()();
}

Partial or expression indexes can use SQL:

@TableIndex.sql('''
  CREATE INDEX pending_orders ON orders (creation_time)
    WHERE status = 'pending';
''')
class Orders extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get status => text()();
  DateTimeColumn get creationTime => dateTime()();
}

PostgreSQL Types

Import drift_postgres for Postgres-specific custom types:

import 'package:drift_postgres/drift_postgres.dart';

class Users extends Table {
  UuidColumn get id => customType(PgTypes.uuid).withDefault(genRandomUuid())();
  TextColumn get name => text()();
  JsonColumn get settings => customType(PgTypes.jsonb)();
  TimestampColumn get createdAt =>
      customType(PgTypes.timestampWithTimezone).withDefault(now())();

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

Useful PgTypes include uuid, json, jsonb, textArray, date, timestampWithTimezone, and timestampNoTimezone.

Generated Columns

class Boxes extends Table {
  IntColumn get length => integer()();
  IntColumn get width => integer()();
  IntColumn get area => integer().generatedAs(length * width, stored: true)();
}

Naming

Custom table name:

@override
String get tableName => 'product_table';

Custom column name:

BoolColumn get isAdmin => boolean().named('admin')();

Writes

Use this reference for inserts, updates, deletes, upserts, batches, and transactions.

Insert

final id = await db.into(db.todoItems).insert(
      TodoItemsCompanion.insert(
        title: 'First todo',
        content: const Value('Some description'),
      ),
    );

Columns with default values, nullable columns, and auto-increment columns can be omitted from Companion.insert.

Insert and read generated defaults:

final row = await db.into(db.todoItems).insertReturning(
      TodoItemsCompanion.insert(title: 'A todo entry'),
    );

Bulk insert:

await db.batch((batch) {
  batch.insertAll(db.todoItems, [
    TodoItemsCompanion.insert(title: 'First'),
    TodoItemsCompanion.insert(title: 'Second'),
  ]);
});

Update

Partial update:

await (db.update(db.todoItems)..where((t) => t.id.equals(id))).write(
  const TodoItemsCompanion(
    title: Value('Updated title'),
    isCompleted: Value(true),
  ),
);

Replace a full row by primary key:

await db.update(db.todoItems).replace(
      TodoItem(
        id: id,
        title: 'Updated title',
        content: 'Updated content',
        isCompleted: true,
        createdAt: existing.createdAt,
      ),
    );

Update with SQL expressions:

await db.update(db.users).write(
      UsersCompanion.custom(
        name: db.users.name.lower(),
      ),
    );

Delete

await (db.delete(db.todoItems)..where((t) => t.id.equals(id))).go();

Delete a range:

await (db.delete(db.todoItems)..where((t) => t.id.isSmallerThanValue(10))).go();

Delete all rows only when that is explicitly intended:

await db.delete(db.todoItems).go();

Companions and Value

Use data classes for complete rows and companions for inserts or partial updates.

final companion = TodoItemsCompanion(
  title: const Value('New title'),
  content: Value.absent(),
);

• Value(value) sets the column to the value, including null for nullable columns.
• Value.absent() leaves the column unchanged in updates.
• const Value(value) is fine for compile-time constant values.

Upsert

For primary-key upserts:

Future<int> createOrUpdateUser(User user) {
  return db.into(db.users).insertOnConflictUpdate(user);
}

For custom conflict behavior:

class Words extends Table {
  TextColumn get word => text()();
  IntColumn get usages => integer().withDefault(const Constant(1))();

  @override
  Set<Column<Object>>? get primaryKey => {word};
}

Future<void> trackWord(String word) {
  return db.into(db.words).insert(
        WordsCompanion.insert(word: word),
        onConflict: DoUpdate(
          (old) => WordsCompanion.custom(
            usages: old.usages + const Constant(1),
          ),
        ),
      );
}

For unique constraints that are not the primary key, provide target:

await db.into(db.matchResults).insert(
      data,
      onConflict: DoUpdate(
        (old) => data,
        target: [db.matchResults.teamA, db.matchResults.teamB],
      ),
    );

Transactions

final id = await db.transaction(() async {
  final categoryId = await db.into(db.categories).insert(
        CategoriesCompanion.insert(name: 'Work'),
      );

  return db.into(db.todoItems).insert(
        TodoItemsCompanion.insert(
          title: 'Write migration tests',
          categoryId: Value(categoryId),
        ),
      );
});

Drift rolls back the transaction if the callback throws.

Custom Writes

Use customUpdate, customInsert, or customStatement for SQL not covered by the query builder. Declare affected tables so streams update:

await db.customUpdate(
  'UPDATE todo_items SET is_completed = ? WHERE id = ?',
  variables: [
    Variable.withBool(true),
    Variable.withInt(id),
  ],
  updates: {db.todoItems},
  updateKind: UpdateKind.update,
);

Safety Rule

Always add a where clause to updates and deletes unless the request explicitly requires changing every row.