dart-drift

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Dart Drift

Comprehensive guide for using drift database library in Dart applications.

Dart应用中使用drift数据库库的全面指南。

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.

Dart Drift指南提供了在Dart应用（CLI工具、后端服务、非Flutter桌面应用）中使用drift库实现持久化存储的完整指导。Drift是基于SQLite构建的Dart响应式持久化库，支持可选的PostgreSQL连接，提供类型安全查询、自动更新流、模式迁移和跨平台数据库连接功能。

Quick Start

快速开始

SQLite Setup

SQLite设置

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

向

pubspec.yaml

添加依赖：

yaml

dependencies:
  drift: ^2.30.0
  sqlite3: ^3.1.3

dev_dependencies:
  drift_dev: ^2.30.0
  build_runner: ^2.10.4

定义数据库：

dart

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

  @override
  int get schemaVersion => 1;
}

打开数据库：

dart

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

运行代码生成器：

bash

dart run build_runner build

PostgreSQL Setup

PostgreSQL设置

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,
    ),
  );
}

添加PostgreSQL依赖：

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

在

build.yaml

中配置PostgreSQL：

yaml

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

打开PostgreSQL连接：

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

查看每个主题的详细文档：

setup.md - Dart中sqlite3或PostgreSQL的设置
postgres.md - PostgreSQL特定功能、连接池
tables.md - 表定义、列、约束
queries.md - SELECT、WHERE、JOIN、聚合
writes.md - INSERT、UPDATE、DELETE、事务
streams.md - 响应式流查询
migrations.md - 数据库模式迁移

Common Patterns

常见模式

CLI Application with SQLite

基于SQLite的CLI应用

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();
}

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

基于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}');
}

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));
}

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

PostgreSQL特定类型

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(),
  );
}

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());
}

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);
  });
}

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)

CLI/桌面端（macOS/Windows/Linux）

Uses

sqlite3

package with file-based storage.

使用

sqlite3

包实现基于文件的存储。

Server/Backend (PostgreSQL)

服务器/后端（PostgreSQL）

Uses

postgres

package with connection pooling.

使用

postgres

包实现连接池。

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();
  });
}

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();
  });
}

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

连接池：生产环境中PostgreSQL使用连接池
内存数据库：用于快速单元测试
事务：保证数据一致性
连接超时：构建健壮的服务器应用
模式迁移：使用正确的版本控制
索引：在频繁查询的列上创建索引
预编译语句：drift自动支持
正确关闭连接：在 shutdown 时关闭连接
连接池管理：后端服务的连接池管理
错误处理：处理连接失败情况

Troubleshooting

故障排除

Build Fails

构建失败

bash

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

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

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),
  )

增加池大小或减少连接生命周期：

dart

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

PostgreSQL Type Errors

PostgreSQL类型错误

Verify dialect is configured in

build.yaml

验证

build.yaml

中是否配置了正确的方言。