Drizzle Sqlite

dot-skills Drizzle SQLite Best Practices

Library-reference skill for Drizzle ORM with SQLite-family backends. 45 rules across 8 categories, ordered by execution-lifecycle impact: schema → migrations → query → relations → transactions → performance → connection → types.

When to Apply

Reference these guidelines when:

• Defining sqliteTable schemas — choosing column types, primary keys, indexes, foreign keys
• Running drizzle-kit generate / migrate / push, or hand-editing a migration SQL file
• Writing queries with db.select(), db.insert(), db.update(), db.delete()
• Reaching for nested data with db.query.* and the relational query builder
• Wrapping multi-statement writes in db.transaction() or db.batch() (libsql/Turso/D1)
• Optimizing a hot-path query with .prepare() + sql.placeholder() or covering indexes
• Setting up the Drizzle client (pragmas, driver choice, singleton lifecycle)
• Wiring database types into application code ($inferSelect, drizzle-zod, JSON shapes)

The skill is not specific to one driver — it covers behavior shared across better-sqlite3, libsql, bun:sqlite, expo-sqlite, op-sqlite, and Cloudflare D1, calling out driver-specific deviations where they exist.

Architectural Context

SQLite is unusual among production databases:

• No client/server. The "connection" is a file open. There is no connection pool, no auth, no network in the local-file case.
• Single writer. One writer at a time, no matter how many connections. Reads can be parallel under WAL.
• No native booleans or dates. Everything is INTEGER, REAL, TEXT, BLOB, or NULL — Drizzle column modes encode the rest.
• Limited ALTER TABLE. Only RENAME COLUMN, ADD COLUMN, DROP COLUMN. Type changes and constraint additions need a table rebuild.
• Foreign keys off by default. PRAGMA foreign_keys = ON is per-connection and not persistent.

Many rules in this skill exist because Drizzle's API abstracts over PostgreSQL/MySQL/SQLite uniformly — but the underlying SQLite engine has constraints that show up at runtime if you treat it like Postgres.

Rule Categories by Priority

Quick Reference

1. Schema Definition (CRITICAL)

• schema-integer-for-booleans — Use integer({ mode: 'boolean' }) so the inferred type is boolean, not 0 | 1
• schema-timestamp-mode-for-dates — Store dates as integer({ mode: 'timestamp_ms' }), not text
• schema-always-primary-key — Declare an explicit PK (single or composite); don't rely on hidden rowid
• schema-foreign-keys-with-actions — Specify onDelete/onUpdate on every .references()
• schema-index-foreign-keys-and-lookups — Index FK columns and frequent WHEREs — SQLite does not auto-index FKs
• schema-text-json-not-blob-json — Use text({ mode: 'json' }) so json_extract and JSON-path indexes work
• schema-unique-constraints-for-natural-keys — .unique() for email/slug/externalId so onConflict has a target

2. Migrations & Drizzle Kit (CRITICAL)

• migrate-generate-not-push-in-prod — Use generate + migrate; push drops columns it can't reconcile
• migrate-explicit-renames — Answer the rename prompt — defaults treat renames as drop+add
• migrate-config-dialect-and-out — Define drizzle.config.ts so commands work without flags
• migrate-apply-with-migrator — Apply via drizzle-kit migrate or the driver migrator module, not raw SQL
• migrate-data-backfill-as-custom-sql — Hand-edit migration SQL to backfill atomically with the DDL
• migrate-commit-migrations-to-git — Commit drizzle/ SQL and drizzle/meta/ snapshots — both are required

3. Query Building (HIGH)

• query-select-columns-not-star — Project to the columns you need with db.select({ ... })
• query-avoid-n-plus-one-with-inarray — Replace looped queries with inArray()
• query-always-limit-listings — Every listing query needs .limit() (and ideally a cursor)
• query-bind-parameters-not-concat — Use eq() / sql template — never string-concat values
• query-upsert-with-onconflict — Atomic upserts via .onConflictDoUpdate(), not select-then-write
• query-returning-instead-of-reselect — .returning() on insert/update/delete saves a round trip
• query-toSQL-and-explain — Inspect generated SQL and EXPLAIN QUERY PLAN on hot paths

4. Relations (HIGH)

• rel-declare-relations-for-rqb — relations() declarations unlock db.query.* and with
• rel-prefer-with-over-manual-joins — with for nested fetches; manual joins lose typing and add code
• rel-partial-columns-in-with — columns: { ... } inside with to limit payload and avoid leaks
• rel-filter-with-where-inside-with — Push related-row filters into with.where, not into JS
• rel-leftjoin-for-flat-aggregates — Drop to leftJoin + groupBy when you need aggregates

5. Transactions & Batching (MEDIUM-HIGH)

• tx-wrap-multi-statement-writes — Wrap related writes in db.transaction() for atomicity + throughput
• tx-batch-for-libsql-roundtrips — db.batch() on libsql/Turso/D1 collapses N round trips into 1
• tx-no-network-io-inside-transaction — No awaited HTTP / FS / Stripe calls inside a transaction
• tx-handle-busy-with-retry — Bounded retries on SQLITE_BUSY — only on transient errors
• tx-single-writer-no-parallel-writes — Promise.all of writes contends; serialize them

6. Prepared Statements & Hot Paths (MEDIUM-HIGH)

• perf-prepare-hot-paths — .prepare() + sql.placeholder() for queries running on every request
• perf-bulk-insert-multi-row-values — One values([...rows]) instead of N looped inserts
• perf-avoid-count-star-on-large-tables — Counter rows or keyset pagination instead of count(*)
• perf-keyset-not-offset-for-deep-pages — Keyset pagination keeps cost constant across pages
• perf-covering-index-for-hot-queries — Cover the projected columns so the planner skips the row read

7. Connection & Driver Setup (MEDIUM)

• conn-enable-wal — journal_mode = WAL for concurrent reads + one writer
• conn-set-busy-timeout — busy_timeout = 5000 turns contention into a wait
• conn-foreign-keys-pragma — foreign_keys = ON per connection — off by default
• conn-singleton-client — Module-scope singleton; never per-request construction
• conn-pick-driver-deliberately — Sync vs async vs HTTP — choose by deployment target

8. Type Inference (MEDIUM)

• types-infer-select-insert — Derive row types with $inferSelect / $inferInsert
• types-narrow-json-with-dollartype — .$type<Shape>() to escape unknown on JSON columns
• types-getTableColumns-for-reuse — Share projections via getTableColumns() + spread
• types-drizzle-zod-for-runtime-validation — createInsertSchema(table) derives a Zod validator from the schema
• types-bigint-mode-for-large-integers — mode: 'bigint' for IDs over Number.MAX_SAFE_INTEGER

How to Use

Read the relevant category overview in references/_sections.md, then the specific rule files for detailed explanations and code examples. Each rule has incorrect-vs-correct examples — apply the correct pattern to the code under review.

For complex changes (schema redesign, migration strategy, performance work), read all rules in the affected category before deciding.

• Section definitions — Category structure and impact levels
• Rule template — Template for adding new rules

Reference Files

Related Skills

• effect-ts — When the application is Effect-based; Drizzle integrates via Effect.tryPromise.
• nextjs-bundle-optimizer — For Next.js apps reaching for SQLite as the data layer.
• better-auth — Often paired with Drizzle SQLite for auth tables; see better-auth-scaffold for table generation.