dart-drift

Original source / Raw SKILL.md

---
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](references/setup.md) - Dart setup with sqlite3 or PostgreSQL
- [postgres.md](references/postgres.md) - PostgreSQL-specific features, connection pooling
- [tables.md](references/tables.md) - Table definitions, columns, constraints
- [queries.md](references/queries.md) - SELECT, WHERE, JOIN, aggregations
- [writes.md](references/writes.md) - INSERT, UPDATE, DELETE, transactions
- [streams.md](references/streams.md) - Reactive stream queries
- [migrations.md](references/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

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

```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`.


---

## Referenced Files

> The following files are referenced in this skill and included for context.

### references/setup.md

```markdown
---
title: Setup
description: Setup drift for Dart applications
---

## SQLite Setup

Add drift to your `pubspec.yaml`:

```yaml
dependencies:
  drift: ^2.14.0
  sqlite3: ^2.4.0

dev_dependencies:
  drift_dev: ^2.14.0
  build_runner: ^2.4.0
```

Or run:

```bash
dart pub add drift sqlite3 dev:drift_dev dev:build_runner
```

## PostgreSQL Setup

For PostgreSQL databases, add these dependencies:

```yaml
dependencies:
  drift: ^2.14.0
  postgres: ^2.6.0
  drift_postgres: ^1.2.0

dev_dependencies:
  drift_dev: ^2.14.0
  build_runner: ^2.4.0
```

Or run:

```bash
dart pub add drift postgres drift_postgres dev:drift_dev dev:build_runner
```

Configure for PostgreSQL in `build.yaml`:

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

## Database Class (SQLite)

Every Dart app using drift needs a database class. Create a `database.dart` file:

```dart
import 'package:drift/drift.dart';

part 'database.g.dart';

class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  DateTimeColumn get createdAt => dateTime().nullable()();
}

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

  @override
  int get schemaVersion => 1;
}
```

## Opening Database (SQLite)

```dart
import 'package:drift/drift.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:path/path.dart';

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

## Opening Database (PostgreSQL)

```dart
import 'package:drift/drift.dart';
import 'package:drift_postgres/drift_postgres.dart';
import 'package:postgres/postgres.dart';

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

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

## Connection Pooling (PostgreSQL)

For better performance with PostgreSQL:

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

  return AppDatabase(
    PgDatabase(
      endpoint: endpoint,
      settings: ConnectionSettings(
        connectTimeout: Duration(seconds: 10),
        queryTimeout: Duration(seconds: 30),
      ),
    ),
  );
}
```

## In-Memory Database (Testing)

For unit tests, use an in-memory database:

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

## Running Code Generator

Generate code with build_runner:

```bash
dart run build_runner build
```

Or watch for changes during development:

```bash
dart run build_runner watch
```

```

### references/postgres.md

```markdown
---
title: PostgreSQL
description: Use drift with PostgreSQL
---

## Setup

Add PostgreSQL dependencies:

```yaml
dependencies:
  drift: ^2.14.0
  postgres: ^2.6.0
  drift_postgres: ^1.2.0

dev_dependencies:
  drift_dev: ^2.14.0
  build_runner: ^2.4.0
```

## Configure Postgres Dialect

Add to `build.yaml`:

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

## Database Connection

### Simple Connection

```dart
import 'package:drift/drift.dart';
import 'package:drift_postgres/drift_postgres.dart';
import 'package:postgres/postgres.dart';

@DriftDatabase(...)
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor e) : super(e);

  @override
  int get schemaVersion => 1;
}

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

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

### Connection 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: 10,
      ),
    );

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

### Custom Session

Use existing PostgreSQL session:

```dart
import 'package:postgres/postgres.dart';

Future<AppDatabase> openCustomConnection(Session session) {
  return AppDatabase(PgDatabase.opened(session));
}
```

### Connection Settings

Configure timeouts and options:

```dart
final endpoint = HostEndpoint(
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    username: 'user',
    password: 'password',
  );

return AppDatabase(
    PgDatabase(
      endpoint: endpoint,
      settings: ConnectionSettings(
        connectTimeout: Duration(seconds: 10),
        queryTimeout: Duration(seconds: 30),
        applicationName: 'My Drift App',
      ),
    ),
  );
}
```

## PostgreSQL Types

### UUID Column

```dart
class Users extends Table {
  late final id = postgresUuid().autoGenerate()();
  late final name = text()();
}
```

### JSON Column

```dart
class Settings extends Table {
  late final id = integer().autoIncrement()();
  late final config = postgresJson()();
}
```

### Array Column

```dart
class Posts extends Table {
  late final id = integer().autoIncrement()();
  late final tags = postgresArray(PostgresTypes.text)();
}
```

### Custom Types

Use PostgreSQL-specific types:

```dart
class Data extends Table {
  late final id = integer().autoIncrement()();
  late final metadata = postgresJsonb()();
  late final createdAt = dateTime().withDefault(
    FunctionCallExpression.currentTimestamp(),
  );
}
```

## PostgreSQL Functions

### Generate UUID

```dart
final id = await into(users).insert(
  UsersCompanion.insert(
    name: 'John',
    id: const Value(gen_random_uuid()),
  ),
);
```

### Current Timestamp

```dart
final now = FunctionCallExpression.currentTimestamp();
```

## Queries with PostgreSQL

### JSON Query

```dart
final users = await (select(users)
  ..where((u) =>
      u.jsonData.containsString('key', 'value'))
  ).get();
```

### Array Contains

```dart
final posts = await (select(posts)
  ..where((p) =>
      p.tags.contains('tag1'))
  ).get();
```

### Full Text Search

```dart
final posts = await customSelect('''
    SELECT * FROM posts
    WHERE to_tsvector(content) @@ plainto_tsquery(?)
  ''', ['search term']).get();
```

## Indexes

### JSON Index

```dart
@TableIndex.sql('''
  CREATE INDEX data_key_idx ON data ((config->>'key'))
''')
class Data extends Table {
  late final id = integer().autoIncrement()();
  late final config = postgresJson()();
}
```

### GIN Index for Arrays

```dart
@TableIndex.sql('''
  CREATE INDEX posts_tags_gin ON posts USING GIN (tags)
''')
class Posts extends Table {
  late final id = integer().autoIncrement()();
  late final tags = postgresArray(PostgresTypes.text)();
}
```

## Migrations with PostgreSQL

### Export Schema

Export drift schema for PostgreSQL:

```bash
dart run drift_dev schema dump lib/database.dart > schema.sql
```

### Manual Migrations

Use raw SQL for PostgreSQL migrations:

```dart
from1To2: (m, schema) async {
  await m.customStatement('''
    ALTER TABLE users
    ADD COLUMN created_at TIMESTAMP DEFAULT NOW()
  ''');
}
```

### Migration Tools

Consider PostgreSQL-native tools:
- pgmigrate
- sqitch
- Flyway

## Performance Tips

### Connection Pooling

Always use pools in production:

```dart
final pool = PgPool(endpoint, settings: PoolSettings(maxSize: 20));
final database = AppDatabase(PgDatabase.opened(pool));
```

### Prepared Statements

Drift automatically prepares statements.

### Indexes

Create indexes on frequently queried columns:

```dart
@TableIndex(name: 'users_email_idx', columns: {#email})
class Users extends Table {
  late final email = text()();
}
```

## Testing with PostgreSQL

### Test Database

```dart
import 'package:drift/drift.dart';

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

For PostgreSQL integration tests, use test database:

```dart
AppDatabase createPostgresTestDatabase() {
  final endpoint = HostEndpoint(
      host: 'localhost',
      port: 5432,
      database: 'test_db',
      username: 'test_user',
      password: 'test_pass',
    );

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

## Common Patterns

### Soft Deletes

```dart
class Todos extends Table {
  late final id = integer().autoIncrement()();
  late final title = text()();
  late final deletedAt = dateTime().nullable()();
}

Future<List<Todo>> getActiveTodos() {
  return (select(todos)
    ..where((t) => t.deletedAt.isNull())
  ).get();
}
```

### Updated At

```dart
class Users extends Table {
  late final id = postgresUuid().autoGenerate()();
  late final name = text()();
  late final updatedAt = dateTime().withDefault(
    FunctionCallExpression.currentTimestamp(),
  );
}
```

## Best Practices

1. **Connection pooling**: Always use pools in production
2. **Indexes**: Index frequently queried columns
3. **Migration tools**: Use PostgreSQL-native tools for complex migrations
4. **Type safety**: Use drift's type generation for PostgreSQL types
5. **Testing**: Use in-memory SQLite for unit tests, real Postgres for integration tests
6. **Timeouts**: Set appropriate connection and query timeouts
7. **SSL**: Enable SSL in production

```

### references/tables.md

```markdown
---
title: Table Definitions
description: Define database tables in drift
---

## Basic Table Structure

All tables in drift extend the `Table` class and define columns as `late final` fields:

```dart
class TodoItems extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text()();
  DateTimeColumn get createdAt => dateTime().nullable()();
}
```

## Adding Tables to Database

Add tables to your database using the `@DriftDatabase` annotation:

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

  @override
  int get schemaVersion => 1;
}
```

## Column Types

| Dart Type | Drift Column | SQL Type |
|------------|---------------|-----------|
| `int` | `integer()` | `INTEGER` |
| `BigInt` | `int64()` | `INTEGER` |
| `String` | `text()` | `TEXT` |
| `bool` | `boolean()` | `INTEGER` (1 or 0) |
| `double` | `real()` | `REAL` |
| `Uint8List` | `blob()` | `BLOB` |
| `DateTime` | `dateTime()` | `INTEGER` or `TEXT` |

## Nullable Columns

Use `nullable()` to allow `null` values:

```dart
late final category = integer().nullable()();
```

## Default Values

### Server Default (SQL)

```dart
late final createdAt = dateTime().withDefault(currentDateAndTime)();
```

### Client Default (Dart)

```dart
late final isActive = boolean().clientDefault(() => true)();
```

## Foreign Keys

Reference another table:

```dart
class Albums extends Table {
  late final artist = integer().references(Artists, #id)();
}

class Artists extends Table {
  late final id = integer().autoIncrement()();
  late final name = text()();
}
```

Enable foreign keys:

```dart
@override
MigrationStrategy get migration {
  return MigrationStrategy(
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );
}
```

## Auto Increment Primary Key

```dart
class Items extends Table {
  late final id = integer().autoIncrement()();
  late final title = text()();
}
```

Inserting with auto increment:

```dart
await database.items.insertAll([
  ItemsCompanion.insert(title: 'First entry'),
  ItemsCompanion.insert(title: 'Another item'),
]);
```

## Custom Primary Key

```dart
class Profiles extends Table {
  late final email = text()();

  @override
  Set<Column<Object>> get primaryKey => {email};
}
```

## Unique Columns

Single column unique:

```dart
late final username = text().unique()();
```

Multiple columns unique:

```dart
class Reservations extends Table {
  late final room = text()();
  late final onDay = dateTime()();

  @override
  List<Set<Column>> get uniqueKeys => [
    {room, onDay},
  ];
}
```

## Indexes

Simple index:

```dart
@TableIndex(name: 'user_name', columns: {#name})
class Users extends Table {
  late final id = integer().autoIncrement()();
  late final name = text()();
}
```

Index with ordering:

```dart
@TableIndex(
  name: 'log_entries_at',
  columns: {IndexedColumn(#loggedAt, orderBy: OrderingMode.desc)},
)
class LogEntries extends Table {
  late final loggedAt = dateTime()();
}
```

Custom SQL index:

```dart
@TableIndex.sql('''
  CREATE INDEX pending_orders ON orders (creation_time)
    WHERE status == 'pending';
''')
class Orders extends Table {
  late final status = text()();
  late final creationTime = dateTime()();
}
```

## Table Mixins

Extract common columns:

```dart
mixin TableMixin on Table {
  late final id = integer().autoIncrement()();
  late final createdAt = dateTime().withDefault(currentDateAndTime)();
}

class Posts extends Table with TableMixin {
  late final content = text()();
}
```

## Custom Table Name

```dart
class Products extends Table {
  @override
  String get tableName => 'product_table';
}
```

## Custom Column Name

```dart
late final isAdmin = boolean().named('admin')();
```

## Length Constraints

```dart
late final name = text().withLength(min: 1, max: 50)();
```

## Check Constraints

```dart
late final Column<int> age = integer().check(age.isBiggerOrEqualValue(0))();
```

## Generated Columns

Virtual (computed on read):

```dart
class Squares extends Table {
  late final length = integer()();
  late final width = integer()();
  late final area = integer().generatedAs(length * width)();
}
```

Stored (computed on write):

```dart
class Boxes extends Table {
  late final length = integer()();
  late final width = integer()();
  late final area = integer().generatedAs(length * width, stored: true)();
}
```

## Custom Table Constraints

```dart
class TableWithCustomConstraints extends Table {
  late final foo = integer()();
  late final bar = integer()();

  @override
  List<String> get customConstraints => [
    'FOREIGN KEY (foo, bar) REFERENCES group_memberships ("group", user)',
  ];
}
```

## Strict Tables

```dart
class Preferences extends Table {
  late final key = text()();
  late final value = sqliteAny().nullable()();

  @override
  Set<Column<Object>>? get primaryKey => {key};

  @override
  bool get isStrict => true;
}
```

```

### references/queries.md

```markdown
---
title: Queries
description: SELECT queries in drift
---

## Basic Select

Get all rows from a table:

```dart
final allTodos = await select(todoItems).get();
```

Turn into stream:

```dart
final allTodosStream = select(todoItems).watch();
```

## Where Clause

Filter results with where:

```dart
final completedTodos = await (select(todoItems)
  ..where((t) => t.isCompleted.equals(true))
  ).get();
```

Multiple conditions:

```dart
final filteredTodos = await (select(todoItems)
  ..where((t) => t.isCompleted.equals(true) & t.priority.isBiggerThanValue(3))
  ).get();
```

Operators:
- `equals(value)` - equals
- `isBiggerThan(value)` - greater than
- `isSmallerThan(value)` - less than
- `isBiggerOrEqualValue(value)` - greater or equal
- `isSmallerOrEqualValue(value)` - less or equal
- `like(pattern)` - LIKE operator
- `contains(value)` - contains text
- `isNull()` - IS NULL
- `isNotNull()` - IS NOT NULL

## Limit and Offset

```dart
final pageOfTodos = await (select(todoItems)
  ..limit(20, offset: 40)
  ).get();
```

## Order By

```dart
final sortedTodos = await (select(todoItems)
  ..orderBy([(t) => OrderingTerm(expression: t.createdAt)])
  ).get();
```

Descending order:

```dart
final sortedTodosDesc = await (select(todoItems)
  ..orderBy([
    (t) => OrderingTerm(expression: t.createdAt, mode: OrderingMode.desc)
  ])
  ).get();
```

## Single Row

Get exactly one row:

```dart
final todo = await (select(todoItems)
  ..where((t) => t.id.equals(1))
  ).getSingle();
```

Watch single row as stream:

```dart
final todoStream = (select(todoItems)
  ..where((t) => t.id.equals(1))
  ).watchSingle();
```

Allow null result:

```dart
final todo = await (select(todoItems)
  ..where((t) => t.id.equals(1))
  ).getSingleOrNull();
```

## Mapping

Transform results:

```dart
final titles = await (select(todoItems)
  ..where((t) => t.title.length.isBiggerOrEqualValue(10))
  .map((row) => row.title)
  ).get();
```

## Joins

### Inner Join

```dart
class EntryWithCategory {
  EntryWithCategory(this.entry, this.category);
  final TodoItem entry;
  final Category? category;
}

final results = await (select(todoItems)
  .join([
    innerJoin(categories, categories.id.equalsExp(todoItems.category)),
  ])
  .map((row) => EntryWithCategory(
    row.readTable(todoItems),
    row.readTableOrNull(categories),
  ))
  .get();
```

### Left Outer Join

```dart
final results = await (select(todoItems)
  .join([
    leftOuterJoin(categories, categories.id.equalsExp(todoItems.category)),
  ])
  .map((row) => EntryWithCategory(
    row.readTable(todoItems),
    row.readTableOrNull(categories),
  ))
  .get();
```

### Multiple Joins

```dart
final results = await (select(todoItems)
  .join([
    innerJoin(categories, categories.id.equalsExp(todoItems.category)),
    innerJoin(users, users.id.equalsExp(todoItems.userId)),
  ])
  .get();
```

### Self Join

Join table to itself:

```dart
final otherTodos = alias(todoItems, 'other');

final results = await (select(otherTodos)
  .join([
    innerJoin(
      categories,
      categories.id.equalsExp(otherTodos.category),
      useColumns: false,
    ),
    innerJoin(
      todoItems,
      todoItems.category.equalsExp(categories.id),
      useColumns: false,
    ),
  ])
  .where(todoItems.title.contains('important'))
  .map((row) => row.readTable(otherTodos))
  .get();
```

## Aggregations

### Count

```dart
final count = await (selectOnly(todoItems)
  .addColumns([countAll()])
  .map((row) => row.read(countAll()))
  .getSingle();
```

### Count by Group

```dart
final countPerCategory = await (select(categories)
  .join([
    innerJoin(
      todoItems,
      todoItems.category.equalsExp(categories.id),
      useColumns: false,
    ),
  ])
  ..addColumns([todoItems.id.count()])
  ..groupBy([categories.id])
  .map((row) => (
    category: row.readTable(categories),
    count: row.read(todoItems.id.count())!,
  ))
  .get();
```

### Average

```dart
final avgLength = await (selectOnly(todoItems)
  .addColumns([todoItems.title.length.avg()])
  .map((row) => row.read(todoItems.title.length.avg())!)
  .getSingle();
```

## Subqueries

### In Where Clause

```dart
final latestTodos = await (select(todoItems)
  ..where((t) => t.createdAt.isBiggerThan(
    subqueryExpression(
      selectOnly(todoItems)
        .addColumns([max(todoItems.createdAt)])
        .where((t) => t.userId.equals(currentUserId)),
    ),
  ))
  .get();
```

### From Subquery

```dart
final sub = Subquery(
  select(todoItems)
    ..orderBy([(t) => OrderingTerm.desc(t.createdAt)])
    ..limit(10),
  's',
);

final results = await select(sub).get();
```

## Custom Columns

Add computed column to results:

```dart
final isImportant = todoItems.content.like('%important%');

final results = await select(todoItems)
  .addColumns([isImportant])
  .map((row) => (
    todo: row.readTable(todoItems),
    important: row.read(isImportant)!,
  ))
  .get();
```

## Exists

```dart
final hasTodo = await (selectOnly(todoItems)
  .addColumns([existsQuery(select(todoItems))])
  .map((row) => row.read(existsQuery(select(todoItems)))!)
  .getSingle();
```

## Union

Combine results from multiple queries:

```dart
final query1 = select(todoItems)
  ..where((t) => t.isCompleted.equals(true));

final query2 = select(todoItems)
  ..where((t) => t.priority.equals(1));

final results = await query1.unionAll(query2).get();
```

```

### references/writes.md

```markdown
---
title: Insert, Update, Delete
description: Write operations in drift
---

## Insert

### Simple Insert

Insert a single row:

```dart
final id = await into(todoItems).insert(
  TodoItemsCompanion.insert(
    title: 'First todo',
    content: 'Some description',
  ),
);
```

Columns with default values or auto-increment can be omitted.

### Insert with Auto Increment

```dart
final id = await into(todoItems).insert(
  TodoItemsCompanion.insert(
    title: 'First todo',
  ),
);
```

### Bulk Insert

```dart
await batch((batch) {
  batch.insertAll(todoItems, [
    TodoItemsCompanion.insert(
      title: 'First entry',
      content: 'My content',
    ),
    TodoItemsCompanion.insert(
      title: 'Another entry',
      content: 'More content',
      category: const Value(3),
    ),
  ]);
});
```

### Upsert (Insert or Update)

Insert if doesn't exist, update if exists:

```dart
final id = await into(users).insertOnConflictUpdate(
  UsersCompanion.insert(
    email: '[email protected]',
    name: 'John Doe',
  ),
);
```

Requires SQLite 3.24.0+.

### Custom Conflict Target

For upsert with custom unique constraints:

```dart
final id = await into(products).insert(
  ProductsCompanion.insert(
    sku: 'ABC123',
    name: 'Product Name',
  ),
  onConflict: DoUpdate(
    target: [sku],
  ),
);
```

### Insert Returning

Get inserted row with generated values:

```dart
final row = await into(todos).insertReturning(
  TodosCompanion.insert(
    title: 'A todo entry',
    content: 'A description',
  ),
);
```

Requires SQLite 3.35+.

## Update

### Simple Update

Update all matching rows:

```dart
await (update(todoItems)
  ..where((t) => t.id.equals(1))
  .write(TodoItemsCompanion(
    title: const Value('Updated title'),
  ));
```

### Replace

Replace entire row:

```dart
await update(todoItems).replace(
  TodoItem(
    id: 1,
    title: 'Updated title',
    content: 'Updated content',
  ),
);
```

### Partial Update

Only update specific fields:

```dart
await (update(todoItems)
  ..where((t) => t.id.equals(1))
  .write(TodoItemsCompanion(
    title: const Value('Updated title'),
    isCompleted: const Value(true),
  ));
```

### Update with SQL Expression

```dart
await (update(users)
  .write(UsersCompanion.custom(
    name: users.name.lower(),
  ));
```

## Delete

### Delete Matching Rows

```dart
await (delete(todoItems)
  ..where((t) => t.id.equals(1))
  .go();
```

### Delete Multiple

```dart
await (delete(todoItems)
  ..where((t) => t.isCompleted.equals(true))
  .go();
```

### Delete All

```dart
await delete(todoItems).go();
```

### Delete Limit

```dart
await (delete(todoItems)
  ..where((t) => t.id.isSmallerThanValue(10))
  .go();
```

## Companions vs Data Classes

### Data Class (TodoItem)

Holds all fields, represents a full row:

```dart
final todo = TodoItem(
  id: 1,
  title: 'Title',
  content: 'Content',
  createdAt: DateTime.now(),
);
```

### Companion (TodoItemsCompanion)

Used for partial data, updates, and inserts:

```dart
final companion = TodoItemsCompanion(
  title: const Value('Title'),
  content: const Value('Content'),
  createdAt: Value.absent(),
);
```

### Value States

- `Value(value)` - set to this value
- `Value.absent()` - don't update this column
- `const Value(value)` - for non-nullable values

### Important Distinctions

`category: Value(null)` - SET category = NULL
`category: Value.absent()` - don't change category

## Transactions

### Simple Transaction

```dart
await transaction(() async {
  await into(todoItems).insert(
    TodoItemsCompanion.insert(
      title: 'First todo',
    ),
  );

  await into(categories).insert(
    CategoriesCompanion.insert(
      name: 'New Category',
    ),
  );
});
```

### Transaction with Result

```dart
final result = await transaction(() async {
  final id = await into(todoItems).insert(
    TodoItemsCompanion.insert(
      title: 'First todo',
    ),
  );

  await into(categories).insert(
    CategoriesCompanion.insert(
      name: 'New Category',
    ),
  );

  return id;
});
```

### Rollback on Error

```dart
try {
  await transaction(() async {
    await update(todoItems).write(
      TodoItemsCompanion(
        title: const Value('Updated'),
      ),
    );

    await someOtherOperation();
  });
} catch (e) {
  print('Transaction rolled back: $e');
}
```

## Batch Operations

### Batch Insert and Update

```dart
await batch((batch) {
  batch.insertAll(todoItems, [
    TodoItemsCompanion.insert(title: 'First'),
    TodoItemsCompanion.insert(title: 'Second'),
  ]);

  batch.updateAll(todoItems, [
    TodoItemsCompanion(
      id: 1,
      title: const Value('Updated'),
    ),
  ]);
});
```

### Batch with Mixed Operations

```dart
await batch((batch) {
  batch.insert(todoItems, TodoItemsCompanion.insert(title: 'New'));
  batch.update(todoItems, TodoItemsCompanion(
    id: 1,
    title: const Value('Updated'),
  ));
  batch.delete(todoItems, 2);
});
```

## Warning

Always add `where` clause on updates and deletes:

```dart
// BAD - updates ALL rows!
await update(todoItems).write(
  TodoItemsCompanion(
    isCompleted: const Value(true),
  ),
);

// GOOD - only updates matching rows
await (update(todoItems)
  ..where((t) => t.id.equals(1))
  .write(
    TodoItemsCompanion(
      isCompleted: const Value(true),
    ),
  );
```

```

### references/streams.md

```markdown
---
title: Stream Queries
description: Watch queries in drift
---

## Basic Stream

Watch query results:

```dart
final todosStream = select(todoItems).watch();
```

Use with StreamBuilder in Flutter:

```dart
StreamBuilder<List<TodoItem>>(
  stream: select(todoItems).watch(),
  builder: (context, snapshot) {
    if (!snapshot.hasData) {
      return CircularProgressIndicator();
    }

    final todos = snapshot.data!;
    return ListView.builder(
      itemCount: todos.length,
      itemBuilder: (context, index) {
        return ListTile(
          title: Text(todos[index].title),
        );
      },
    );
  },
)
```

## Stream Single Item

Watch single row:

```dart
final todoStream = (select(todoItems)
  ..where((t) => t.id.equals(1))
  .watchSingle();
```

Or allow null:

```dart
final todoStream = (select(todoItems)
  ..where((t) => t.id.equals(1))
  .watchSingleOrNull();
```

## Get vs Watch

Run once vs watch continuously:

```dart
// Run once, get current results
final todos = await select(todoItems).get();

// Watch for changes, get updates
final todosStream = select(todoItems).watch();
```

## StreamBuilder Usage

Complete StreamBuilder example:

```dart
class TodoList extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    final database = Provider.of<AppDatabase>(context);

    return StreamBuilder<List<TodoItem>>(
      stream: select(todoItems).watch(),
      builder: (context, snapshot) {
        if (snapshot.connectionState == ConnectionState.waiting) {
          return CircularProgressIndicator();
        }

        if (snapshot.hasError) {
          return Text('Error: ${snapshot.error}');
        }

        final todos = snapshot.data ?? [];

        if (todos.isEmpty) {
          return Center(child: Text('No todos yet'));
        }

        return ListView.builder(
          itemCount: todos.length,
          itemBuilder: (context, index) {
            final todo = todos[index];
            return ListTile(
              title: Text(todo.title),
              trailing: Checkbox(
                value: todo.isCompleted,
                onChanged: (value) {
                  database.update(todoItems).replace(
                    TodoItem(
                      id: todo.id,
                      title: todo.title,
                      isCompleted: value ?? false,
                    ),
                  );
                },
              ),
            );
          },
        );
      },
    );
  }
}
```

## Riverpod Integration

Wrap stream with StreamProvider:

```dart
final todosProvider = StreamProvider.autoDispose<List<TodoItem>>((ref) {
  final database = ref.watch(databaseProvider);
  return select(database.todoItems).watch();
});
```

Use in widget:

```dart
class TodoList extends ConsumerWidget {
  @override
  Widget build(BuildContext context, WidgetRef ref) {
    final todos = ref.watch(todosProvider);

    return ListView.builder(
      itemCount: todos.length,
      itemBuilder: (context, index) {
        return ListTile(title: Text(todos[index].title));
      },
    );
  }
}
```

## Watch Filtered Results

Watch filtered query:

```dart
final activeTodos = (select(todoItems)
  ..where((t) => !t.isCompleted)
  .watch();
```

Watch sorted results:

```dart
final sortedTodos = (select(todoItems)
  ..orderBy([(t) => OrderingTerm.desc(t.createdAt)])
  .watch();
```

## Watch with Joins

Watch joined query results:

```dart
class EntryWithCategory {
  EntryWithCategory(this.entry, this.category);
  final TodoItem entry;
  final Category? category;
}

final todosWithCategoryStream = (select(todoItems)
  .join([
      leftOuterJoin(categories, categories.id.equalsExp(todoItems.category)),
    ])
  .map((row) => EntryWithCategory(
        row.readTable(todoItems),
        row.readTableOrNull(categories),
      ))
  .watch();
```

## Custom Query Streams

Watch custom SQL query:

```dart
Stream<List<TodoItem>> watchCompletedTodos() {
  return watch('''
    SELECT * FROM todo_items
    WHERE is_completed = ?
    ORDER BY created_at DESC
  ''', [true]);
}
```

## Table Update Events

Listen to table updates directly:

```dart
final todoUpdates = todoUpdates(todoItems).listen((event) {
  switch (event.kind) {
    case UpdateKind.insert:
      print('New todo inserted: ${event.row}');
    case UpdateKind.update:
      print('Todo updated: ${event.row}');
    case UpdateKind.delete:
      print('Todo deleted');
  }
});
```

## Manual Update Trigger

Trigger stream updates manually:

```dart
void triggerUpdates() {
  notifyTableUpdates(todoItems);
}
```

## Stream Cancellation

Cancel stream subscription:

```dart
StreamSubscription? subscription;

void startWatching() {
  subscription = select(todoItems).watch().listen((todos) {
    print('Updated todos: $todos');
  });
}

void stopWatching() {
  subscription?.cancel();
  subscription = null;
}
```

## Stream Debouncing

Debounce rapid updates:

```dart
import 'dart:async';

Stream<List<TodoItem>> watchDebouncedTodos() {
  return select(todoItems).watch().debounceTime(
    const Duration(milliseconds: 300),
  );
}
```

## Stream Transformation

Transform stream data:

```dart
final todoTitles = select(todoItems)
  .watch()
  .map((todos) => todos.map((t) => t.title).toList());

final activeCount = (select(todoItems)
  ..where((t) => !t.isCompleted)
  .watch()
  .map((todos) => todos.length);
```

## Stream Error Handling

Handle stream errors:

```dart
select(todoItems).watch().listen(
  (todos) {
    // Handle new data
    updateUI(todos);
  },
  onError: (error, stack) {
    // Handle errors
    showError(error);
  },
  onDone: () {
    // Stream closed
    print('Stream closed');
  },
);
```

## Limitations

Streams have these limitations:

1. **External changes**: Updates made outside of drift APIs don't trigger stream updates
2. **Coarse updates**: Streams update for any change to watched tables, not just relevant rows
3. **Performance**: Stream queries should be efficient (few rows, fast execution)

## Best Practices

- Use streams for UI-reactive data (lists, counters)
- Prefer `watch()` over repeated `get()` calls
- Keep stream queries efficient (limit rows, use indexes)
- Cancel stream subscriptions when not needed
- Use `watchSingle()` for single-item queries
- Filter and map streams as needed for UI requirements

```

### references/migrations.md

```markdown
---
title: Migrations
description: Database migrations in drift
---

## Configure for Migrations

Add database to `build.yaml`:

```yaml
targets:
  $default:
    builders:
      drift_dev:
        options:
          databases:
            my_database: lib/database.dart
            another_db: lib/database2.dart

          schema_dir: drift_schemas/
          test_dir: test/drift/
```

## Run Migration Generator

```bash
dart run drift_dev make-migrations
```

This generates:
- `database.steps.dart` - step-by-step migration helper
- Test files for migration verification
- Schema files for each version

## Step-by-Step Migrations

Use generated `stepByStep` function:

```dart
import 'database.steps.dart';

@DriftDatabase(...)
class AppDatabase extends _$AppDatabase {
  @override
  int get schemaVersion => 3;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
        onUpgrade: stepByStep(
          from1To2: (m, schema) async {
            // Version 1 to 2: Add column
            await m.addColumn(schema.todoItems, schema.todoItems.dueDate);
          },
          from2To3: (m, schema) async {
            // Version 2 to 3: Add indexes and alter table
            await m.create(schema.todosDelete);
            await m.create(schema.todosUpdate);
            await m.alterTable(TableMigration(schema.todoItems));
          },
        ),
      );
  }
}
```

## Common Migration Operations

### Add Column

```dart
from1To2: (m, schema) async {
  await m.addColumn(schema.users, schema.users.birthDate);
}
```

### Drop Column

```dart
from2To3: (m, schema) async {
  await m.dropColumn(schema.users, schema.users.oldField);
}
```

### Rename Column

```dart
from1To2: (m, schema) async {
  await m.renameColumn(schema.users, schema.users.name, schema.users.fullName);
}
```

### Add Table

```dart
from1To2: (m, schema) async {
  await m.createTable(schema.categories);
}
```

### Drop Table

```dart
from2To3: (m, schema) async {
  await m.deleteTable('users');
}
```

### Alter Table

Rebuild table with new constraints:

```dart
from2To3: (m, schema) async {
  await m.alterTable(TableMigration(schema.todoItems));
}
```

### Create Index

```dart
from1To2: (m, schema) async {
  await m.create(schema.usersByNameIndex);
}
```

### Drop Index

```dart
from2To3: (m, schema) async {
  await m.dropIndex('users_name_idx');
}
```

### Custom SQL

Execute custom SQL:

```dart
from1To2: (m, schema) async {
  await m.customStatement('''
    UPDATE users
    SET status = 'inactive'
    WHERE last_login < ?
  ''', [DateTime.now().subtract(Duration(days: 365)]);
}
```

## Post-Migration Callbacks

Run code after migrations:

```dart
@override
MigrationStrategy get migration {
  return MigrationStrategy(
      onUpgrade: stepByStep(...),
      beforeOpen: (details) async {
        // Enable foreign keys
        await customStatement('PRAGMA foreign_keys = ON');

        // Populate default data
        if (details.wasCreated) {
          final workId = await into(categories).insert(
            CategoriesCompanion.insert(
              name: 'Work',
            ),
          );

          await into(todoItems).insert(
            TodoItemsCompanion.insert(
              title: 'First todo',
              category: Value(workId),
            ),
          );
        }
      },
    );
}
```

## Manual Migrations

Write migrations without `make-migrations`:

```dart
@override
MigrationStrategy get migration {
  return MigrationStrategy(
      onCreate: (Migrator m) async {
        await m.createAll();
      },
      onUpgrade: (Migrator m, int from, int to) async {
        if (from == 1 && to == 2) {
          await m.addColumn(todoItems, todoItems.dueDate);
        }
        if (from == 2 && to == 3) {
          await m.create(todosDelete);
        }
      },
    );
}
```

## Testing Migrations

Generated test files verify migrations:

```bash
dart test test/drift/schema_test.dart
```

Test validates:
- Data integrity across migrations
- Schema consistency
- Migration correctness

## Data Migration Strategies

### Copy and Transform

Migrate data with transformation:

```dart
from1To2: (m, schema) async {
  // Add new column
  await m.addColumn(schema.users, schema.users.fullName);

  // Transform data
  final users = await (select(users)).get();
  await batch((batch) {
    for (final user in users) {
      batch.update(
        users,
        UsersCompanion(
          id: Value(user.id),
          fullName: Value('${user.firstName} ${user.lastName}'),
        ),
      );
    }
  });
}
```

### Rename with Data Migration

```dart
from1To2: (m, schema) async {
  // Rename column
  await m.renameColumn(schema.users, schema.users.name, schema.users.username);

  // Migrate data if needed
  await customStatement('''
    UPDATE users
    SET username = LOWER(name)
    WHERE username IS NULL
  ''');
}
```

## Debugging Migrations

### Enable Logging

```dart
@override
MigrationStrategy get migration {
  return MigrationStrategy(
      onUpgrade: stepByStep(...),
      beforeOpen: (details) async {
        print('Opening database, version: ${details.to}');
        print('Was created: ${details.wasCreated}');
      },
    );
}
```

### Check Current Schema

```dart
Future<void> debugSchema() async {
  final executor = database.executor;
  final schema = await database.schema;
  print('Current schema: $schema');
}
```

## Common Patterns

### Add Foreign Key

```dart
from1To2: (m, schema) async {
  await m.addColumn(schema.todoItems, schema.todoItems.category);
}

// Re-enable after migration
@override
MigrationStrategy get migration {
  return MigrationStrategy(
      onUpgrade: stepByStep(...),
      beforeOpen: (details) async {
        if (details.hadUpgrade) {
          await customStatement('PRAGMA foreign_keys = ON');
        }
      },
    );
}
```

### Rename Table

```dart
from1To2: (m, schema) async {
  await m.createTable(schema.newUsers);
  await m.customStatement('''
    INSERT INTO new_users
    SELECT * FROM old_users
  ''');
  await m.deleteTable('old_users');
}
```

### Add Unique Constraint

```dart
from1To2: (m, schema) async {
  await m.alterTable(
        TableMigration(
          schema.users,
          newColumns: {schema.users.email.unique()},
        ),
      );
}
```

## Best Practices

1. **Incremental migrations**: Use `stepByStep` for maintainable code
2. **Test thoroughly**: Run generated tests for each migration
3. **Backup data**: Ensure migrations don't lose user data
4. **Use transactions**: Wrap data migrations in transactions
5. **Document changes**: Comment complex transformations
6. **Version carefully**: Only bump `schemaVersion` when schema changes

```