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

yaml

dependencies:
  drift: ^2.30.0
  sqlite3: ^3.1.3

dev_dependencies:
  drift_dev: ^2.30.0
  build_runner: ^2.10.4

Define database:

dart

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

  @override
  int get schemaVersion => 1;
}

Open database:

dart

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

Run code generator:

bash

dart run build_runner build

PostgreSQL Setup

Add PostgreSQL dependencies:

yaml

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

yaml

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

Open PostgreSQL connection:

dart

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

dart

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

dart

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

dart

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

dart

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

dart

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

Transaction with Data Consistency

dart

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

dart

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

dart

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

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

Troubleshooting

Build Fails

bash

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

Migration Errors

bash

dart run drift_dev schema validate
dart run drift_dev make-migrations

Connection Pool Exhausted

Increase pool size or reduce connection lifetime:

dart

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

PostgreSQL Type Errors

Verify dialect is configured in

build.yaml

dart-drift

NPX Install

Tags

SKILL.md Content

Dart Drift

Overview

Quick Start

SQLite Setup

PostgreSQL Setup

Reference Files

Common Patterns

CLI Application with SQLite

Backend Service with PostgreSQL

Connection Pooling

PostgreSQL-Specific Types

In-Memory Testing

Transaction with Data Consistency

Platform-Specific Setup

CLI/Desktop (macOS/Windows/Linux)

Server/Backend (PostgreSQL)

Testing

Testing

Unit Tests

Integration Tests

Best Practices

Troubleshooting

Build Fails

Migration Errors

Connection Pool Exhausted

PostgreSQL Type Errors