Dart Drift

Comprehensive guide for using drift database library in Dart applications.

Overview

Dart Drift skill provides complete guidance for implementing persistent storage in Dart applications (CLI tools, backend services, non-Flutter desktop apps) using the drift library. Drift is a reactive persistence library for Dart built on SQLite, with optional PostgreSQL support, offering type-safe queries, auto-updating streams, schema migrations, and cross-platform database connections.

Quick Start

SQLite Setup

Add dependencies to pubspec.yaml:

dependencies:
  drift: ^2.30.0
  sqlite3: ^3.1.3

dev_dependencies:
  drift_dev: ^2.30.0
  build_runner: ^2.10.4

Define database:

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

  @override
  int get schemaVersion => 1;
}

Open database:

AppDatabase openConnection() {
  final file = File('db.sqlite');
  return AppDatabase(LazyDatabase(() async {
    final db = sqlite3.open(file.path);
    return NativeDatabase.createInBackground(db);
  }));
}

Run code generator:

dart run build_runner build

PostgreSQL Setup

Add PostgreSQL dependencies:

dependencies:
  drift: ^2.30.0
  postgres: ^3.5.9
  drift_postgres: ^1.3.1

dev_dependencies:
  drift_dev: ^2.30.0
  build_runner: ^2.10.4

Configure for PostgreSQL in build.yaml:

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

Open PostgreSQL connection:

import 'package:drift_postgres/drift_postgres.dart';

AppDatabase openPostgresConnection() {
  final endpoint = HostEndpoint(
      host: 'localhost',
      port: 5432,
      database: 'mydb',
      username: 'user',
      password: 'password',
    );

  return AppDatabase(
    PgDatabase(
      endpoint: endpoint,
    ),
  );
}

Reference Files

See detailed documentation for each topic:

• setup.md - Dart setup with sqlite3 or PostgreSQL
• postgres.md - PostgreSQL-specific features, connection pooling
• tables.md - Table definitions, columns, constraints
• queries.md - SELECT, WHERE, JOIN, aggregations
• writes.md - INSERT, UPDATE, DELETE, transactions
• streams.md - Reactive stream queries
• migrations.md - Database schema migrations

Common Patterns

CLI Application with SQLite

void main(List<String> args) async {
  final db = openConnection();

  final todos = await db.select(db.todoItems).get();
  print('Found ${todos.length} todos');

  await db.close();
}

Backend Service with PostgreSQL

class TodoService {
  final AppDatabase db;

  TodoService(this.db);

  Future<List<TodoItem>> getAllTodos() async {
    return await db.select(db.todoItems).get();
  }

  Future<int> createTodo(String title) async {
    return await db.into(db.todoItems).insert(
      TodoItemsCompanion.insert(title: title),
    );
  }
}

void main() async {
  final pool = PgPool(
      PgEndpoint(
        host: 'localhost',
        port: 5432,
        database: 'mydb',
        username: 'user',
        password: 'password',
      ),
      settings: PoolSettings(maxSize: 10),
    );

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

  final todoId = await service.createTodo('New task');
  print('Created todo with id: $todoId');

  final todos = await service.getAllTodos();
  print('Total todos: ${todos.length}');
}

Connection Pooling

import 'package:postgres/postgres_pool.dart';

Future<AppDatabase> openPooledConnection() {
  final pool = PgPool(
      PgEndpoint(
        host: 'localhost',
        port: 5432,
        database: 'mydb',
        username: 'user',
        password: 'password',
      ),
      settings: PoolSettings(maxSize: 20),
    );

  return AppDatabase(PgDatabase.opened(pool));
}

PostgreSQL-Specific Types

class Users extends Table {
  late final id = postgresUuid().autoGenerate()();
  late final name = text()();
  late final settings = postgresJson()();
  late final createdAt = dateTime().withDefault(
    FunctionCallExpression.currentTimestamp(),
  );
}

In-Memory Testing

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

Transaction with Data Consistency

Future<void> transferTodo(int fromId, int toId) async {
  await db.transaction(() async {
    final fromTodo = await (db.select(db.todoItems)
      ..where((t) => t.id.equals(fromId))
      ).getSingle();

    await db.update(db.todoItems).write(
      TodoItemsCompanion(
        id: Value(toId),
        title: Value(fromTodo.title),
      ),
    );

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

Platform-Specific Setup

CLI/Desktop (macOS/Windows/Linux)

Uses sqlite3 package with file-based storage.

Server/Backend (PostgreSQL)

Uses postgres package with connection pooling.

Testing

Uses in-memory database for fast unit tests.

Testing

Unit Tests

void main() {
  test('Insert and retrieve todo', () async {
    final db = createTestDatabase();
    final id = await db.into(db.todoItems).insert(
      TodoItemsCompanion.insert(title: 'Test todo'),
    );

    final todos = await db.select(db.todoItems).get();
    expect(todos.length, 1);
    expect(todos.first.title, 'Test todo');

    await db.close();
  });
}

Integration Tests

void main() {
  test('PostgreSQL connection works', () async {
    final pool = PgPool(endpoint, settings: PoolSettings(maxSize: 5));
    final db = AppDatabase(PgDatabase.opened(pool));

    final id = await db.into(db.todoItems).insert(
      TodoItemsCompanion.insert(title: 'Test'),
    );

    expect(id, greaterThan(0));

    await db.close();
  });
}

Best Practices

1. Connection pooling for PostgreSQL in production
2. In-memory databases for fast unit tests
3. Transactions for data consistency
4. Connection timeouts for robust server apps
5. Schema migrations with proper versioning
6. Indexes on frequently queried columns
7. Prepared statements (automatic in drift)
8. Close connections properly on shutdown
9. Pool management for backend services
10. Error handling for connection failures

Troubleshooting

Build Fails

dart run build_runner clean
dart run build_runner build --delete-conflicting-outputs

Migration Errors

dart run drift_dev schema validate
dart run drift_dev make-migrations

Connection Pool Exhausted

Increase pool size or reduce connection lifetime:

PoolSettings(
    maxSize: 20,
    maxLifetime: Duration(minutes: 5),
  )

PostgreSQL Type Errors

Verify dialect is configured in build.yaml.