Skill v1.0.0

Automated scan100/100

madteacher/mad-agents-skills/dart-drift

──Details

PublishedApril 28, 2026 at 12:05 AM

Content Hashsha256:91e1cd6da91358e4...

Git SHA69d260a36403

──Files

Files (1 file, 7.4 KB)

SKILL.md7.4 KBactive

SKILL.md · 343 lines · 7.4 KB

version: "1.0.0" name: dart-drift description: Complete guide for using drift database library in Dart applications (CLI, server-side, non-Flutter). Use when building Dart apps that need local SQLite database storage or PostgreSQL connection with type-safe queries, reactive streams, migrations, and efficient CRUD operations. Includes setup with sqlite3 package, PostgreSQL support with drift_postgres, connection pooling, and server-side patterns. metadata: author: Stanislav [MADTeacher] Chernyshev version: "1.0"

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.

All versions v1.0.1 →