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:
```dart
@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
- 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
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.