dart-drift_skill

Overview

This skill is a practical, hands-on guide for using the Drift database library in Dart command-line and server-side applications. It covers SQLite and PostgreSQL setups, type-safe queries, reactive streams, migrations, and efficient CRUD patterns. The content focuses on production-ready patterns like connection pooling, transactions, and testing with in-memory databases.

How this skill works

The guide explains how to wire Drift to sqlite3 for local file storage or to a PostgreSQL backend via drift_postgres and PgPool. It shows database and table definitions, code generation steps, and examples for SELECT/INSERT/UPDATE/DELETE. It also walks through reactive streams, migrations, transactions, and testing with in-memory databases for fast unit tests.

When to use it

• Building CLI tools or desktop apps that need embedded SQLite storage
• Creating backend services that require type-safe queries and PostgreSQL support
• Implementing reactive, auto-updating query streams
• Running unit tests with fast in-memory databases
• Managing schema changes with versioned migrations

Best practices

• Use connection pooling for production PostgreSQL to reduce latency and resource usage
• Prefer transactions for multi-step writes to ensure data consistency
• Run code generation (build_runner) as part of CI and local development
• Keep schemaVersion and migrations under version control and test migrations in staging
• Close or dispose database connections cleanly on app shutdown
• Use indexes on frequently filtered columns and validate query plans during development

Example use cases

• CLI todo app storing data in a local db.sqlite file using sqlite3 and NativeDatabase
• Backend TodoService backed by PgPool and drift_postgres with pooled connections and typed models
• Unit tests that construct AppDatabase(NativeDatabase.memory()) for isolated, fast tests
• Server-side pattern demonstrating transaction-safe transfer and concurrent-safe writes
• PostgreSQL-specific user table using postgresUuid() and postgresJson() column types