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.

Drizzle ORM + SQLite

Version 0.1.0 dot-skills May 2026

Note:

This document is mainly for agents and LLMs to follow when maintaining,

generating, or refactoring codebases. Humans may also find it useful,

but guidance here is optimized for automation and consistency by AI-assisted workflows.

---

Abstract

Library-reference skill for Drizzle ORM with SQLite-family backends (better-sqlite3, libsql/Turso, bun:sqlite, Cloudflare D1, expo-sqlite, op-sqlite). Contains 45 rules across 8 categories ordered by execution-lifecycle impact — from CRITICAL schema and migration decisions to MEDIUM type-inference patterns. Each rule pairs an incorrect/correct example with a quantified impact and links to authoritative Drizzle, SQLite, and driver documentation.

---

Table of Contents

1. Schema Definition — CRITICAL

• 1.1 Add unique constraints for natural keys (email, slug, externalId) — HIGH (prevents race-condition duplicates and unblocks onConflict targets)
• 1.2 Always declare a primary key (single or composite) — CRITICAL (prevents implicit rowid coupling and unblocks upsert/RETURNING)
• 1.3 Declare foreign keys with explicit onDelete/onUpdate — CRITICAL (prevents orphaned rows and undefined cascade behavior)
• 1.4 Index foreign keys and frequent WHERE columns — CRITICAL (O(n) full-table scan to O(log n) index lookup)
• 1.5 Store dates as integer timestamp_ms, not text — CRITICAL (enables index-backed range queries and prevents ISO string drift)
• 1.6 Use integer mode 'boolean' for boolean columns — CRITICAL (prevents 0/1 leaking into application types)
• 1.7 Use text mode 'json' for JSON columns, not blob — HIGH (enables json_extract and indexes on JSON fields)

2. Migrations & Drizzle Kit — CRITICAL

• 2.1 Answer rename prompts explicitly to preserve column data — CRITICAL (prevents drop+add destroying renamed column data)
• 2.2 Apply migrations with the driver-specific migrator — HIGH (prevents re-applying or skipping migrations on repeated boots)
• 2.3 Commit drizzle/ migration files and snapshot to version control — HIGH (prevents diverging schemas across environments)
• 2.4 Configure drizzle.config.ts with dialect, schema, and out — HIGH (prevents config drift between developer machines and CI)
• 2.5 Edit migration SQL to backfill data atomically with DDL — HIGH (prevents NULL/inconsistent rows between deploy and worker run)
• 2.6 Use drizzle-kit generate + migrate in production, never push — CRITICAL (prevents silent column drops and lost data)

3. Query Building — HIGH

• 3.1 Always limit listing queries — HIGH (prevents unbounded memory growth as tables scale)
• 3.2 Bind parameters with eq/sql tagged template — never concatenate — HIGH (prevents SQL injection and re-enables query plan caching)
• 3.3 Select only the columns you need — HIGH (2-10x payload reduction on wide tables)
• 3.4 Use .returning() instead of a second SELECT after write — MEDIUM-HIGH (eliminates a 2nd select round trip per write)
• 3.5 Use .toSQL() and EXPLAIN QUERY PLAN to verify generated SQL — MEDIUM-HIGH (prevents O(n) full-table scans reaching production)
• 3.6 Use inArray for batch lookups instead of looping queries — HIGH (10-100x latency reduction by eliminating per-row round trips)
• 3.7 Use onConflictDoUpdate/DoNothing for upsert, not select-then-write — HIGH (1 round trip instead of 2, eliminates TOCTOU races)

4. Relations — HIGH

• 4.1 Declare relations() so db.query.* can resolve `with` — HIGH (enables single-statement nested fetches via db.query.*)
• 4.2 Drop to leftJoin when you need aggregates or flat shapes — MEDIUM-HIGH (prevents O(n) JS-side aggregation over hydrated rows)
• 4.3 Filter related rows in `with`'s where, not in JavaScript — HIGH (2-10x payload reduction on filtered nested fetches)
• 4.4 Use columns inside `with` to keep nested payloads small — HIGH (2-5x payload reduction on nested fetches)
• 4.5 Use db.query `with` for nested fetches, not manual joins + grouping — HIGH (eliminates N+1 queries and manual JS row grouping)

5. Transactions & Batching — MEDIUM-HIGH

• 5.1 Avoid Promise.all on writes — SQLite is single-writer — MEDIUM-HIGH (prevents lock contention disguised as parallelism)
• 5.2 Handle SQLITE_BUSY with bounded retries on writes — MEDIUM-HIGH (prevents transient lock contention surfacing as 500s)
• 5.3 Never await external I/O inside a transaction — MEDIUM-HIGH (prevents write-lock starvation across the cluster)
• 5.4 Use db.batch() to collapse round trips on libsql/Turso/D1 — MEDIUM-HIGH (eliminates N-1 network round trips per transaction)
• 5.5 Wrap multi-statement writes in db.transaction() — MEDIUM-HIGH (atomicity + 5-50x throughput on batched writes)

6. Prepared Statements & Hot Paths — MEDIUM-HIGH

• 6.1 Avoid count(*) over large tables — use approximations or counters — MEDIUM (O(n) full-table scan to O(1) lookup)
• 6.2 Build covering indexes for hot read queries — MEDIUM-HIGH (eliminates the table row lookup after index probe)
• 6.3 Bulk insert with one multi-row VALUES, not a loop — MEDIUM-HIGH (10-100x faster than per-row inserts)
• 6.4 Prepare hot-path queries with sql.placeholder — MEDIUM-HIGH (2-5x speedup on high-frequency lookups)
• 6.5 Use keyset pagination for deep pages, not OFFSET — MEDIUM-HIGH (O(n) to O(log n) on deep pages)

7. Connection & Driver Setup — MEDIUM

• 7.1 Enable WAL journal mode for concurrent reads + one writer — MEDIUM-HIGH (10-100x tail-latency reduction under write load)
• 7.2 Pick a SQLite driver deliberately — sync vs async matters — MEDIUM (avoids API mismatches and wrong-tool perf)
• 7.3 Reuse a singleton Drizzle client — don't construct per request — MEDIUM (keeps statement cache warm and prevents fd exhaustion)
• 7.4 Set busy_timeout so contention waits instead of failing — MEDIUM (prevents transient lock contention surfacing as 500s)
• 7.5 Set foreign_keys = ON on every connection — HIGH (prevents orphan rows from FKs that look declared)

8. Type Inference — MEDIUM

• 8.1 Derive row types with $inferSelect and $inferInsert — MEDIUM (prevents schema drift between TS and DB)
• 8.2 Narrow JSON column types with $type<Shape>() — MEDIUM (eliminates type casts at every JSON read site)
• 8.3 Pair drizzle-zod with the schema for runtime validation — MEDIUM (prevents bad inputs reaching the database)
• 8.4 Use bigint mode when storing values beyond Number.MAX_SAFE_INTEGER — MEDIUM (prevents silent precision loss)
• 8.5 Use getTableColumns to share projections without duplicating — MEDIUM (prevents projection/schema drift across endpoints)

---

References

1. https://orm.drizzle.team/docs/get-started-sqlite 2. https://orm.drizzle.team/docs/column-types/sqlite 3. https://orm.drizzle.team/docs/relations 4. https://orm.drizzle.team/docs/rqb 5. https://orm.drizzle.team/docs/transactions 6. https://orm.drizzle.team/docs/batch-api 7. https://orm.drizzle.team/docs/perf-queries 8. https://orm.drizzle.team/docs/drizzle-kit-generate 9. https://orm.drizzle.team/docs/drizzle-kit-migrate 10. https://orm.drizzle.team/docs/drizzle-kit-push 11. https://orm.drizzle.team/docs/drizzle-config-file 12. https://orm.drizzle.team/docs/zod 13. https://www.sqlite.org/lang.html 14. https://www.sqlite.org/pragma.html 15. https://www.sqlite.org/wal.html 16. https://www.sqlite.org/foreignkeys.html 17. https://www.sqlite.org/lang_altertable.html 18. https://www.sqlite.org/lang_returning.html 19. https://www.sqlite.org/lang_upsert.html 20. https://www.sqlite.org/eqp.html 21. https://www.sqlite.org/queryplanner.html 22. https://www.sqlite.org/json1.html 23. https://github.com/WiseLibs/better-sqlite3 24. https://docs.turso.tech 25. https://use-the-index-luke.com/no-offset

---

Source Files

This document was compiled from individual reference files. For detailed editing or extension:

{Same as title}

{1-3 sentence WHY block. Explain the cascade effect, what breaks without this pattern, and the SQLite or Drizzle constraint that motivates it. Concrete, specific, no hedging.}

*Incorrect ({problem label — e.g., "select scans every column"}):**

{Bad code — production-realistic, not strawman}
{// Comments explaining the cost}

Correct ({solution label — e.g., "projection narrows the query"}):

{Good code — minimal diff from incorrect}
{// Comments explaining the benefit}

{Optional sections — include only the ones that help:}

Alternative ({context}):

{Alternative valid approach when applicable}

When NOT to use this pattern:

• {Specific exception 1}
• {Specific exception 2}

Driver matrix / SQLite version note (only when relevant):

• ✅ better-sqlite3 — full support
• ✅ libsql / Turso — full support
• ⚠️ Cloudflare D1 — works, but {caveat}

Reference: {Title} · {Optional second reference}

---

Authoring notes (delete before committing)

Title patterns:

Impact descriptions:

Tags: 1. First tag MUST be the category prefix (e.g., schema, query). 2. Add 2-5 more tags for techniques, tools, concepts. 3. Lowercase, hyphenated for multi-word.

Code examples:

• TypeScript, with imports shown.
• Production-realistic — show table definitions or column shapes when needed for context.
• Minimal diff between incorrect and correct, so the change is visually obvious.
• Comments explain the cost / benefit, not the syntax.
• Use real driver names (better-sqlite3, libsql, bun:sqlite) — never "your driver".

Sections

This file defines all sections, their ordering, impact levels, and descriptions. The section ID (in parentheses) is the filename prefix used to group rules.

---

1. Schema Definition (schema)

Impact: CRITICAL Description: Column types, primary keys, foreign keys, indexes, and constraints declared in sqliteTable are the foundation. SQLite has no native boolean or date types — storing dates as text or booleans as raw integers cascades into every read/write, breaks orderBy, and forces conversions in application code. Missing indexes turn every filter into a full table scan; missing primary keys break upserts and replication.

2. Migrations & Drizzle Kit (migrate)

Impact: CRITICAL Description: SQLite's ALTER TABLE only supports RENAME, ADD COLUMN, and DROP COLUMN — every other change requires a 12-step CREATE TABLE / INSERT SELECT / DROP / RENAME dance. drizzle-kit push infers schema diffs against a live database and drops columns it cannot reconcile; using it in production destroys data. Column renames look like drop+add to the generator and must be explicitly mapped at prompt time.

3. Query Building (query)

Impact: HIGH Description: Wrong builder choice and N+1 patterns dominate runtime latency. db.select() without a column object returns every column over the wire; building filters by string concatenation defeats parameter binding; looping await db.select() calls inside for loops issues one statement per iteration when inArray() would issue one.

4. Relations (rel)

Impact: HIGH Description: The relational query builder (db.query.users.findMany({ with: { posts: true } })) compiles to a single SQL statement with subqueries — manually re-implementing it with leftJoin + post-processing usually issues more queries and loses Drizzle's column inference. Relations must be declared in a relations() call for db.query.* to see them.

5. Transactions & Batching (tx)

Impact: MEDIUM-HIGH Description: SQLite is single-writer; any write outside a transaction takes and releases the write lock per statement. Wrapping multi-statement writes in db.transaction() amortizes that cost and provides atomicity. For libsql/Turso/D1, db.batch() ships multiple statements in one round trip; using it instead of awaited sequential calls eliminates per-statement network latency.

6. Prepared Statements & Hot Paths (perf)

Impact: MEDIUM-HIGH Description: Every Drizzle query compiles its builder tree to SQL on each call. For queries that run thousands of times per second (auth lookups, feed fetches), .prepare() with sql.placeholder() caches the compiled statement; the hot path becomes parameter binding only. Skipping this in tight loops burns CPU on SQL string assembly.

7. Connection & Driver Setup (conn)

Impact: MEDIUM Description: SQLite pragmas (journal_mode=WAL, busy_timeout, foreign_keys=ON, synchronous=NORMAL) are per-connection and default to write-blocking, FK-off behavior. The Drizzle client must be a singleton — re-instantiating it per request defeats statement caching and can exhaust file handles. Driver choice (better-sqlite3 sync, libsql async, bun:sqlite, D1) constrains which APIs are available.

8. Type Inference (types)

Impact: MEDIUM Description: Drizzle infers row shapes from the schema; using those inferred types (typeof users.$inferSelect, InferSelectModel<typeof users>) keeps API boundaries in sync with the database. Manually written types drift the moment a column is added. For JSON columns, $type<Shape>() is the only way to narrow the inferred unknown.

Enable WAL journal mode for concurrent reads + one writer

SQLite's default journal_mode=DELETE uses rollback journals — readers and writers block each other on the same file. journal_mode=WAL (write-ahead log) changes the model: readers see a consistent snapshot while one writer appends to the WAL file, and readers don't block the writer. The pragma is persistent — once set on a database, it stays set across connections. Set it once, on the first connection, before any heavy traffic.

Incorrect (rollback journal — reads block on writers):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db'); // default journal_mode=DELETE
export const db = drizzle(sqlite);

Under load: a write transaction takes ~50 ms, every concurrent read waits the full 50 ms. Tail latency spikes correlate with write traffic.

Correct (WAL mode — readers proceed during writes):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');

// Persistent pragmas — only need to set once per database, but cheap to re-apply:
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('synchronous = NORMAL');         // safe with WAL; full fsync is overkill
sqlite.pragma('foreign_keys = ON');            // see conn-foreign-keys-pragma
sqlite.pragma('busy_timeout = 5000');          // see conn-set-busy-timeout

export const db = drizzle(sqlite);

WAL files to be aware of:

• app.db — the main database file.
• app.db-wal — the write-ahead log. Grows during writes, checkpointed back into app.db periodically.
• app.db-shm — shared memory file. Required for WAL coordination.

Back these up together; copying only app.db while WAL has uncheckpointed writes loses data. Use VACUUM INTO or the SQLite backup API for hot backups.

For libsql (Turso embedded / local):

import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';

const client = createClient({ url: 'file:local.db' });
// libsql defaults to WAL; no pragma needed for local files.
// Remote (Turso): no pragma — the server manages journal mode.
export const db = drizzle(client);

When NOT to use WAL:

• Network filesystems (NFS, SMB) — WAL relies on shared memory and breaks. Use rollback journal or copy the file locally.
• Read-only databases — journal_mode=OFF is fine and saves a few syscalls.

Reference: SQLite — WAL mode · SQLite — PRAGMA journal_mode

Set foreign_keys = ON on every connection

PRAGMA foreign_keys defaults to OFF in stock SQLite. The setting is per-connection, not persisted — even after you set it on one connection, the next connection comes up with foreign keys disabled. With it off, references(() => users.id, { onDelete: 'cascade' }) is purely documentation: parent rows can be deleted without cascading, child rows can reference non-existent parents, and no integrity error is raised. The fix is one pragma call on every connection — and it must happen outside any transaction.

Incorrect (FK declared in schema but never enforced):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
sqlite.pragma('journal_mode = WAL');
// Missing foreign_keys = ON

// schema declares: posts.authorId references users.id ON DELETE CASCADE.
// At runtime: delete a user, posts remain orphaned with authorId pointing nowhere.

Correct (enabled per-connection at construction time):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('busy_timeout = 5000');
sqlite.pragma('synchronous = NORMAL');
export const db = drizzle(sqlite);

Verify it took:

const [{ enabled }] = await db.all<{ enabled: number }>(sql`PRAGMA foreign_keys`);
console.assert(enabled === 1, 'foreign_keys is off!');

Existing data may already be invalid — check before turning it on:

If you've been running with FKs off, there could be orphan rows already. Turning enforcement on doesn't retroactively fix them, but it makes subsequent writes that would create new orphans fail. Audit first:

-- Find orphaned posts:
SELECT id FROM posts WHERE author_id NOT IN (SELECT id FROM users);

Fix the rows (delete or repoint), then enable the pragma.

libsql / Turso: the libsql client enables foreign keys by default. No pragma needed for the standard configuration.

Connection-pool implication: if you create new connections at runtime (e.g., per worker thread), set the pragma in your connection factory, not just at module load. Otherwise pool workers spin up with FKs off.

Reference: SQLite — PRAGMA foreign_keys · SQLite — Foreign Key Support

Pick a SQLite driver deliberately — sync vs async matters

Drizzle supports several SQLite drivers and they are not interchangeable: better-sqlite3 is synchronous (no await, blocks the event loop), libsql is asynchronous (awaits return promises), bun:sqlite is synchronous and Bun-only, op-sqlite is for React Native, Cloudflare D1 is async-only over HTTP. The wrong choice means rewriting every call site when you migrate. Pick by deployment target first, then by performance budget.

Incorrect (better-sqlite3 picked by default — fails when shipped to a serverless edge):

// src/db/client.ts — works locally on the developer's Mac
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
export const db = drizzle(sqlite);

// Then deployed to Cloudflare Workers / Vercel Edge:
// → Build error: "better-sqlite3" depends on native bindings that don't exist on the edge runtime.
// → Every call site is sync (no await); migrating to libsql means rewriting all of them.

Correct (pick the driver by deployment target — async if any target needs it):

// Deployment target: Cloudflare Workers / Vercel Edge / Turso
import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';

const client = createClient({
  url: process.env.DATABASE_URL ?? 'file:local.db', // works for local dev too
  authToken: process.env.DATABASE_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });

// All call sites are async — no rewrite when moving from local file to Turso:
const user = await db.select().from(users).get();

Decision tree:

Local file, Node.js server, single process?
  → better-sqlite3 (sync, fastest on Node)

Local file, Bun runtime?
  → bun:sqlite (sync, native, ~2x better-sqlite3 on Bun)

Edge / serverless on Cloudflare?
  → Cloudflare D1 via drizzle-orm/d1 (async, HTTP)

Remote SQLite for many serverless replicas / multi-region?
  → libsql / Turso via drizzle-orm/libsql (async, HTTP+WS)

Local file but want async API for code symmetry with prod libsql?
  → libsql with `file:` URL via drizzle-orm/libsql (async)

React Native?
  → op-sqlite via drizzle-orm/op-sqlite (sync)

Expo SQLite?
  → expo-sqlite via drizzle-orm/expo-sqlite (sync)

Alternative (sync-only deployment — better-sqlite3 is the fastest local choice):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
// pragmas — see conn-enable-wal, conn-foreign-keys-pragma
export const db = drizzle(sqlite, { schema });

// No await — synchronous return:
const user = db.select().from(users).get();

Symmetry tip — use libsql with `file:` for dev to match prod:

If production is Turso (async) and dev is a local file, prefer libsql with file:local.db for both. You get the same async signatures everywhere; the only difference is the URL.

const client = createClient({
  url: process.env.DATABASE_URL ?? 'file:local.db',
  authToken: process.env.DATABASE_AUTH_TOKEN, // undefined for local files
});

Don't mix:

• Code that imports both drizzle-orm/better-sqlite3 and drizzle-orm/libsql indicates two clients in one process. Pick one.
• bun:sqlite only works under Bun — code that conditionally imports it crashes on Node.

Performance rough-orders:

• bun:sqlite: fastest local (~2x better-sqlite3 on Bun benchmarks).
• better-sqlite3: fastest Node.js local. Blocks the event loop on heavy queries — keep statements fast.
• libsql local file: ~equal to better-sqlite3, with async overhead.
• libsql remote / D1: bound by network round-trip latency (10-100ms per call) — see tx-batch-for-libsql-roundtrips.

Reference: Drizzle — Get started with SQLite · better-sqlite3 vs node-sqlite3 benchmark

Set busy_timeout so contention waits instead of failing

The default busy_timeout is zero. With it at zero, any attempt to acquire a contended lock returns SQLITE_BUSY immediately — every transient contention surfaces as an error in application code. Setting busy_timeout = 5000 (ms) tells SQLite to retry internally for up to five seconds before giving up. Combined with WAL mode and the IMMEDIATE transaction behavior, this turns most contention into a brief wait rather than a user-visible failure. Set it per connection.

Incorrect (no busy_timeout — contention is always an error):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
sqlite.pragma('journal_mode = WAL');
// Missing busy_timeout — SQLITE_BUSY thrown on first lock conflict.
export const db = drizzle(sqlite);

Correct (5-second timeout — most contention becomes invisible):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('./app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('busy_timeout = 5000');
sqlite.pragma('synchronous = NORMAL');
sqlite.pragma('foreign_keys = ON');
export const db = drizzle(sqlite);

Choose the timeout based on workload:

• Short, fast writes (≤ 100 ms): 1-2 s is plenty — long waits suggest a real problem.
• Mixed read/write app: 5 s is the standard recommendation.
• Background imports against a serving database: 10-30 s, but pair it with retry logic (see tx-handle-busy-with-retry).

Diagnosing: when you see SQLITE_BUSY even with a timeout, the lock is held for longer than the timeout — usually a runaway transaction or a network call inside db.transaction() (see tx-no-network-io-inside-transaction).

For libsql/Turso remote: there's no busy_timeout — the server manages concurrency. Retries on the client side cover transient errors instead.

Reference: SQLite — PRAGMA busy_timeout

Reuse a singleton Drizzle client — don't construct per request

The Drizzle client wraps an underlying driver connection (better-sqlite3 / libsql / bun:sqlite). The driver maintains a cache of prepared statements; the OS reserves a file descriptor; SQLite walks its lock state. Constructing a new drizzle(...) per request throws all of that away every call: the statement cache is cold, the file descriptor count climbs until EMFILE, and on libsql you re-do the TLS handshake. The pattern is the same as any database client — module-scope singleton, never per-request.

Incorrect (constructed inside the handler — leaks file descriptors, cold cache):

// app/api/users/route.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';

export async function GET() {
  const sqlite = new Database('./app.db'); // new fd every request
  const db = drizzle(sqlite);
  // ...
  // sqlite never explicitly closed → process eventually hits ulimit -n.
}

Correct (singleton module — one connection for the process lifetime):

// src/db/client.ts
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './schema';

const sqlite = new Database(process.env.DATABASE_URL ?? './app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');
sqlite.pragma('busy_timeout = 5000');
sqlite.pragma('synchronous = NORMAL');

export const db = drizzle(sqlite, { schema });
export const rawSqlite = sqlite; // expose if you need pragmas / iterate() etc.

// app/api/users/route.ts — just import
import { db } from '@/db/client';

export async function GET() {
  return Response.json(await db.query.users.findMany({ limit: 50 }));
}

Serverless caveat (Vercel, Cloudflare Workers, AWS Lambda):

Cold starts construct the module once per container. Use module-level singletons exactly as above, but expect a brand-new connection per cold start. For very high cold-start rates, prefer libsql / Turso (HTTP-based, no persistent connection) or D1 (managed pool) over local SQLite files.

Hot reload in dev (Next.js, Vite, Bun):

Hot-module reload can re-execute the module and leak connections in dev. Guard with globalThis:

declare global {
  // eslint-disable-next-line no-var
  var __sqlite__: Database.Database | undefined;
}

const sqlite = globalThis.__sqlite__ ??= new Database('./app.db');
if (!globalThis.__sqlite__) {
  sqlite.pragma('journal_mode = WAL');
  // ...
}

export const db = drizzle(sqlite, { schema });

For multi-tenant SQLite-per-tenant, build a Map<tenantId, db> cache rather than constructing on every request — same singleton principle, scoped by tenant.

Reference: better-sqlite3 — Database constructor · Drizzle — Getting started

Apply migrations with the driver-specific migrator

Each Drizzle driver ships its own migrator module — drizzle-orm/better-sqlite3/migrator, drizzle-orm/libsql/migrator, drizzle-orm/bun-sqlite/migrator. They read the ./drizzle/ folder, look up the __drizzle_migrations log table to see what's already applied, apply unapplied files in order, and record the result. Running migrations any other way (raw sqlite3 < file.sql, hand-applied SQL) skips the bookkeeping and the same migration can run twice. Pair the migrator with drizzle-kit migrate for the CLI flow, and use the programmatic API for serverless deployments that apply on boot.

Incorrect (apply manually — no idempotency record):

import { readFileSync } from 'node:fs';
import Database from 'better-sqlite3';

const sqlite = new Database('app.db');
sqlite.exec(readFileSync('./drizzle/0001_init.sql', 'utf8'));
// On next deploy, this re-applies and fails on "table already exists".

Correct (CLI for traditional deploys):

# Run as a deploy step:
npx drizzle-kit migrate
# Reads drizzle.config.ts, applies unapplied files, records into __drizzle_migrations.

Alternative (programmatic apply on boot — serverless / containers):

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';

const sqlite = new Database(process.env.DATABASE_URL ?? './app.db');
const db = drizzle(sqlite);

migrate(db, { migrationsFolder: './drizzle' });
// Safe to call on every boot — already-applied files are skipped.

export { db };

libsql / Turso variant — async, same idea:

import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
import { migrate } from 'drizzle-orm/libsql/migrator';

const db = drizzle(createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
}));

await migrate(db, { migrationsFolder: './drizzle' });

When NOT to apply at boot:

• Multi-instance deploys where many containers boot concurrently — run the migration as a single pre-deploy job instead to avoid lock contention on the migrations table.

Reference: Drizzle — Migrations (Migration runner)

Commit drizzle/ migration files and snapshot to version control

drizzle-kit generate writes two things into ./drizzle/: the SQL files (0001_init.sql, 0002_add_slug.sql) and a meta/_journal.json + per-migration snapshot files. The snapshots are how the next generate knows the previous schema state — without them, the diff is computed against an empty schema and you get a "drop everything and re-create" migration. Both the SQL files and meta/ must be committed; gitignoring either breaks future generation. Also commit drizzle.config.ts so every developer and CI uses the same configuration.

Incorrect (.gitignore swallows the snapshots — next generate produces garbage):

# .gitignore — common mistake
drizzle/meta/
drizzle/*.json

When the next developer runs drizzle-kit generate, drizzle-kit sees no prior snapshot, treats the schema as new, and emits SQL that recreates every table — destroying all data on apply.

Correct (commit everything under drizzle/, gitignore only the local DB):

# Local databases — never commit
*.db
*.db-journal
*.db-wal
*.db-shm
local.db*

# Drizzle Studio cache — safe to ignore
.drizzle-studio/

# DO commit:
#   drizzle/0001_*.sql
#   drizzle/0002_*.sql
#   drizzle/meta/_journal.json
#   drizzle/meta/0000_snapshot.json
#   drizzle/meta/0001_snapshot.json
#   drizzle.config.ts

Pull request checklist for schema changes:

1. npx drizzle-kit generate — answer rename prompts. 2. Open the generated ./drizzle/000X_*.sql; verify it does what you intended. 3. If a data backfill is needed, hand-edit the file (see migrate-data-backfill-as-custom-sql). 4. git add drizzle/ src/db/schema.ts drizzle.config.ts. 5. Open PR; require review on the generated SQL just like any other code.

For monorepos: put ./drizzle/ next to each schema (e.g., apps/api/drizzle/, apps/worker/drizzle/) rather than sharing one folder — the journal is per-schema.

Reference: Drizzle Kit — generate workflow

Configure drizzle.config.ts with dialect, schema, and out

Without drizzle.config.ts, every drizzle-kit invocation needs --dialect, --schema, --out, and credentials on the command line — easy to drift between developer machines and CI. Define them once in drizzle.config.ts so npx drizzle-kit generate and npx drizzle-kit migrate work with no flags. dialect: 'sqlite' is the local file/better-sqlite3 mode; dialect: 'turso' enables libsql-specific features (auth token, remote URL).

Incorrect (no config — flags everywhere, drift between dev and CI):

# Each developer runs a slightly different command:
npx drizzle-kit generate --dialect=sqlite --schema=./src/db/schema.ts --out=./drizzle
# CI has its own copy that's one flag behind, etc.

Correct (single config file, zero-flag commands):

// drizzle.config.ts
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'sqlite',                   // or 'turso' for libsql remote
  schema: './src/db/schema.ts',        // glob ok: './src/db/schema/*.ts'
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  // Recommended for SQLite:
  casing: 'snake_case',                // schema can use camelCase, SQL stays snake_case
  verbose: true,
  strict: true,                        // ask before destructive ops in `push`
});

npx drizzle-kit generate
npx drizzle-kit migrate
npx drizzle-kit studio

For Turso (remote libsql):

export default defineConfig({
  dialect: 'turso',
  schema: './src/db/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN!,
  },
});

Reference: Drizzle config file

Edit migration SQL to backfill data atomically with DDL

A migration that adds a NOT NULL column needs default values for existing rows. Doing that in application code after deploy means there's a window where the column is NULL and reads fail — or worse, you forget and the migration silently leaves rows in a broken state. The generated SQL file is just a text file; open it and append the UPDATE so schema change and backfill commit together. drizzle-kit migrate runs each file in a transaction, so the backfill rolls back with the DDL if anything fails.

Incorrect (app-code backfill — leaves rows broken between deploy and worker run):

-- ./drizzle/0009_add_post_slug.sql (generated, untouched)
ALTER TABLE posts ADD COLUMN slug TEXT NOT NULL DEFAULT '';

// Then in a separate "backfill worker" deployed later:
const all = await db.select().from(posts);
for (const post of all) {
  await db.update(posts).set({ slug: slugify(post.title) }).where(eq(posts.id, post.id));
}
// Until this runs, every post has slug = '' — including new ones if you forget to wire the default.

Correct (backfill inline, atomic with the DDL):

-- ./drizzle/0009_add_post_slug.sql (hand-edited after generate)
ALTER TABLE posts ADD COLUMN slug TEXT;

UPDATE posts
   SET slug = lower(replace(replace(title, ' ', '-'), '.', ''))
 WHERE slug IS NULL;

-- For backfills SQLite can't express, prefer a two-migration pattern:
-- 0009 adds nullable column + backfills, 0010 adds NOT NULL constraint.
-- (See note below — SQLite cannot add NOT NULL via ALTER, so 0010 is a table rebuild.)

Two-step pattern when backfill must run in application code (e.g., crypto, external API lookup):

-- 0009_add_external_id_nullable.sql
ALTER TABLE users ADD COLUMN external_id TEXT;
CREATE INDEX users_external_id_idx ON users(external_id);

Deploy app code that double-writes external_id on every signup, then a one-off backfill job. Once external_id IS NULL count is zero:

-- 0010_external_id_not_null.sql — table rebuild because SQLite cannot add NOT NULL
PRAGMA foreign_keys = OFF;
CREATE TABLE users_new (...same schema with NOT NULL...);
INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
PRAGMA foreign_keys = ON;

Breakpoints: drizzle-kit inserts --> statement-breakpoint markers so the migrator runs each statement separately when the driver requires it (D1, some libsql configs). Don't remove them.

Reference: Drizzle — Custom migrations · SQLite — ALTER TABLE limitations

Answer rename prompts explicitly to preserve column data

When you rename a column or table in schema.ts, drizzle-kit generate cannot tell whether you intended RENAME COLUMN or DROP old + ADD new. It prompts interactively: Is column users.userId renamed to ownerId? (Y/n). Hitting enter or running with --yes blindly accepts drop+add — and SQLite's ALTER TABLE ... DROP COLUMN permanently deletes the data. Always run generate interactively for schema changes that touch existing tables and answer the rename prompt explicitly.

Incorrect (CI-style non-interactive generate after a rename — data lost):

# schema.ts: column `user_id` was renamed to `owner_id`
yes "" | npx drizzle-kit generate
# Defaults answer rename prompts as "no" → generated SQL is:
#   ALTER TABLE posts DROP COLUMN user_id;
#   ALTER TABLE posts ADD COLUMN owner_id INTEGER NOT NULL;
# Every existing row now has NULL owner_id (or fails NOT NULL).

Correct (interactive — confirm the rename so it becomes ALTER ... RENAME):

npx drizzle-kit generate
# ? Is column posts.user_id renamed to owner_id? (y/n) y
# Generated SQL: ALTER TABLE posts RENAME COLUMN user_id TO owner_id;
# Existing data preserved.

For changes the generator can't represent (type changes, complex restructures), hand-edit:

SQLite's ALTER TABLE cannot change a column type or drop NOT NULL. Hand-edit the generated migration using the 12-step recipe:

-- ./drizzle/0008_change_posts_id_type.sql
PRAGMA foreign_keys = OFF;

CREATE TABLE posts_new (
  id TEXT PRIMARY KEY,            -- changed from INTEGER
  owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

INSERT INTO posts_new (id, owner_id, body)
  SELECT CAST(id AS TEXT), owner_id, body FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

PRAGMA foreign_keys = ON;

Always:

• Wrap data-migrating SQL in a transaction (drizzle-kit migrate does this automatically per file).
• Test the migration against a copy of production before merging.

Reference: Drizzle Kit — handling renames · SQLite — Making other kinds of table schema changes

Use drizzle-kit generate + migrate in production, never push

drizzle-kit push introspects the live database, diffs it against the schema file, and applies the inferred changes directly — there's no SQL artifact, no review step, and the diff engine cannot tell a rename from a drop-then-add. Run it against production and a column you renamed in code becomes a DROP COLUMN against the database. The generate + migrate flow writes SQL files into ./drizzle/, gives you a review/commit/PR-review step, and applies them in order with a recorded log table.

Incorrect (push against prod — destructive, no audit trail):

# Developer renames `userId` to `ownerId` in src/schema.ts, then:
npx drizzle-kit push
# drizzle-kit sees "no userId in schema, no ownerId in DB" → DROP + ADD.
# Production data in that column is gone.

Correct (generate, review, migrate):

# 1. Generate a versioned SQL migration into ./drizzle/
npx drizzle-kit generate
#    drizzle-kit will prompt: "is owner_id a rename of user_id?" — answer yes.
#    File ./drizzle/0007_rename_user_to_owner.sql is created.

# 2. Open the file, verify the SQL (ALTER ... RENAME COLUMN), commit it.

# 3. Apply in CI/deploy:
npx drizzle-kit migrate
#    drizzle-kit migrate applies every unapplied file in order and records
#    them in __drizzle_migrations.

When `push` is fine:

• Local development of a brand-new schema where you don't care about data.
• Throwaway test databases that are re-seeded on every run.

Programmatic apply at boot (e.g., serverless/Turso):

import { drizzle } from 'drizzle-orm/libsql';
import { migrate } from 'drizzle-orm/libsql/migrator';

const db = drizzle(client);
await migrate(db, { migrationsFolder: './drizzle' });

Reference: Drizzle Kit — generate · Drizzle Kit — migrate · Drizzle Kit — push

Avoid count(*) over large tables — use approximations or counters

SELECT count(*) FROM posts is O(n) — SQLite walks every row. On a 10M-row table that's seconds, and the result is stale the moment it returns. UIs that show "Showing 1-20 of 9,847,123" pay this cost on every page load. Three better options: drop the total count (use cursor-based pagination with "more" indicators), maintain a counter row in a separate table, or use a windowing trick to fetch one page + 1 to know if there's a next page.

*Incorrect (count() per page request — full scan every time):**

import { count, eq, desc } from 'drizzle-orm';

async function listPosts(page: number, pageSize = 20) {
  const [{ total }] = await db
    .select({ total: count() })
    .from(posts)
    .where(eq(posts.published, true));
  // ↑ Full scan, even with an index on `published`.

  const rows = await db
    .select()
    .from(posts)
    .where(eq(posts.published, true))
    .orderBy(desc(posts.publishedAt))
    .limit(pageSize)
    .offset(page * pageSize);

  return { rows, total, totalPages: Math.ceil(total / pageSize) };
}

Correct (keyset pagination + "has more" flag — best for feeds):

import { and, desc, eq, lt } from 'drizzle-orm';

async function listPosts(cursor?: Date, pageSize = 20) {
  const rows = await db
    .select()
    .from(posts)
    .where(
      cursor
        ? and(eq(posts.published, true), lt(posts.publishedAt, cursor))
        : eq(posts.published, true),
    )
    .orderBy(desc(posts.publishedAt))
    .limit(pageSize + 1); // fetch one extra to detect "has more"

  const hasMore = rows.length > pageSize;
  return { rows: rows.slice(0, pageSize), hasMore, nextCursor: rows[pageSize - 1]?.publishedAt };
}

Alternative (maintained counter row — when you really need the total):

// schema.ts
export const stats = sqliteTable('stats', {
  key: text().primaryKey(),
  value: integer().notNull().default(0),
});

// Update on every insert / soft-delete inside a transaction:
await db.transaction(async (tx) => {
  await tx.insert(posts).values(newPost);
  await tx
    .insert(stats)
    .values({ key: 'published_posts', value: 1 })
    .onConflictDoUpdate({
      target: stats.key,
      set: { value: sql`${stats.value} + 1` },
    });
});

// Reads are now O(1):
const [{ value: total }] = await db
  .select({ value: stats.value })
  .from(stats)
  .where(eq(stats.key, 'published_posts'));

Alternative (sqlite_stat tables for rough estimates):

If you only need an approximate count for display ("about 9 million"), ANALYZE the table and read from sqlite_stat1 — orders of magnitude faster than count(*):

ANALYZE posts;
SELECT stat FROM sqlite_stat1 WHERE tbl = 'posts'; -- "9847123 1.2k 1" — first number is row estimate

Reference: SQLite — Query Planner & sqlite_stat1 · Use the Index, Luke — Pagination

Bulk insert with one multi-row VALUES, not a loop

db.insert(table).values([row1, row2, row3, ...]) compiles to a single INSERT INTO table (...) VALUES (?, ?, ...), (?, ?, ...), (?, ?, ...). SQLite parses, plans, and commits it once. Looping await db.insert(...).values(row) does all of that per row — even inside a transaction, each statement re-traverses the b-tree to find the insertion point. For a 10K-row import, a multi-row insert finishes in ~100 ms; the loop takes 30+ seconds.

Incorrect (per-row loop — slow even inside a transaction):

async function importCsv(rows: NewProduct[]) {
  await db.transaction(async (tx) => {
    for (const row of rows) {
      await tx.insert(products).values(row);
    }
  });
}
// 10_000 rows → 10_000 statements parsed and planned individually.

Correct (single multi-row insert):

async function importCsv(rows: NewProduct[]) {
  if (rows.length === 0) return;
  await db.insert(products).values(rows);
}
// One statement, one plan, one commit.

For very large imports — chunk to stay under the parameter limit:

SQLite's default SQLITE_MAX_VARIABLE_NUMBER is 999 in older builds and 32_766 in 3.32+. With 10 columns per row, that's 100 or 3,276 rows per statement.

async function importInChunks(rows: NewProduct[], chunkSize = 500) {
  for (let i = 0; i < rows.length; i += chunkSize) {
    const chunk = rows.slice(i, i + chunkSize);
    await db.insert(products).values(chunk);
  }
}

Wrap large imports in a transaction for atomicity + one fsync:

await db.transaction(async (tx) => {
  for (let i = 0; i < rows.length; i += 500) {
    await tx.insert(products).values(rows.slice(i, i + 500));
  }
});

Reference: Drizzle — Insert (multi-row values) · SQLite — Max parameter count

Build covering indexes for hot read queries

A regular index lets SQLite find the matching rowids quickly, but it still needs a second lookup into the table to fetch the selected columns. A covering index includes every column the query reads — WHERE, ORDER BY, and the projected columns — so SQLite never touches the table at all. The query plan changes from SEARCH USING INDEX + b-tree lookup to SEARCH USING COVERING INDEX. The win is typically 2-5x on selective queries that return small projections.

Setup — a hot-path query:

// Called on every page load — "did this user star this post?"
async function isStarred(userId: number, postId: number) {
  const [row] = await db
    .select({ starredAt: stars.starredAt })
    .from(stars)
    .where(and(eq(stars.userId, userId), eq(stars.postId, postId)))
    .limit(1);
  return row?.starredAt ?? null;
}

Incorrect (basic index on just the lookup columns — still hits the table row):

// schema.ts
(table) => [
  index('stars_user_post_idx').on(table.userId, table.postId),
]

EXPLAIN QUERY PLAN: SEARCH stars USING INDEX stars_user_post_idx (userId=? AND postId=?) — then a row read to fetch starredAt.

Correct (covering index includes the projected column):

// schema.ts
(table) => [
  // SQLite uses an index as a covering index when the index contains all
  // referenced columns. Listing `starredAt` as part of the index makes
  // `select starredAt where userId=? and postId=?` index-only.
  index('stars_user_post_starred_idx').on(table.userId, table.postId, table.starredAt),
]

EXPLAIN QUERY PLAN: SEARCH stars USING COVERING INDEX stars_user_post_starred_idx (userId=? AND postId=?) — no row read.

Trade-offs:

• Covering indexes consume more disk space — they store the extra columns.
• Writes get slightly slower — every UPDATE that touches a covered column also updates the index.
• For a column that's frequently read but rarely written (like a flag or a timestamp), the math almost always works out.

Identifying candidates with EXPLAIN QUERY PLAN:

Look for USING INDEX (not USING COVERING INDEX) on queries that run thousands of times per minute. Each one is a candidate for promotion to a covering index if the projected columns are small and stable.

For "is this row present?" existence checks, the index alone is enough — no covering needed:

// Reduces to "does the index entry exist?":
import { sql } from 'drizzle-orm';
const [{ exists }] = await db
  .select({ exists: sql<number>`exists (select 1 from stars where user_id = ${userId} and post_id = ${postId})` })
  .from(sql`(values (1))`); // any single-row source

Reference: SQLite — Covering Indexes · Drizzle — Indexes

Use keyset pagination for deep pages, not OFFSET

LIMIT 20 OFFSET 10000 doesn't skip 10_000 rows for free — SQLite reads and discards every one of them before returning page 501. Cost grows linearly with offset, so page 1 is fast and page 500 is unusable. Keyset pagination (a.k.a. seek pagination) sorts by an indexed column and uses WHERE indexed_col < last_seen to jump straight to the next page. Cost is constant regardless of how far you are.

Incorrect (OFFSET-based pagination — degrades with depth):

import { desc } from 'drizzle-orm';

async function pagePosts(page: number) {
  return db
    .select()
    .from(posts)
    .orderBy(desc(posts.publishedAt))
    .limit(20)
    .offset(page * 20);
}
// page=500 → SQLite scans 10_000 rows in publishedAt-desc order, then returns 20.

Correct (keyset — constant cost):

import { and, desc, lt, or, eq } from 'drizzle-orm';

type Cursor = { publishedAt: Date; id: number };

async function pagePosts(cursor?: Cursor, pageSize = 20) {
  const rows = await db
    .select()
    .from(posts)
    .where(
      cursor
        ? // Strict tuple comparison: (publishedAt, id) < cursor.
          //   handles ties on publishedAt deterministically.
          or(
            lt(posts.publishedAt, cursor.publishedAt),
            and(eq(posts.publishedAt, cursor.publishedAt), lt(posts.id, cursor.id)),
          )
        : undefined,
    )
    .orderBy(desc(posts.publishedAt), desc(posts.id))
    .limit(pageSize);

  const last = rows[rows.length - 1];
  return {
    rows,
    nextCursor: last ? { publishedAt: last.publishedAt!, id: last.id } : null,
  };
}

Required index — the ORDER BY columns in the same order:

import { index } from 'drizzle-orm/sqlite-core';

(table) => [
  index('posts_published_id_idx').on(desc(table.publishedAt), desc(table.id)),
]

Why the tie-break column matters: if two posts have the same publishedAt, lt(posts.publishedAt, cursor.publishedAt) skips both on the next page. Adding id as a deterministic tie-breaker prevents duplicates and skips at page boundaries.

When OFFSET is fine:

• Shallow paging where users won't go beyond page 10-20 (admin tables, dashboards).
• Total result set is small (< few thousand rows).
• The UI requires "jump to page N" navigation — keyset doesn't support that natively.

Reference: Use the Index, Luke — No Offset · SQLite — Query Planner

Prepare hot-path queries with sql.placeholder

Every Drizzle query compiles its builder tree to a SQL string on each call. For a query that runs once per request — auth lookups, feature-flag fetches, cache reads — that compile step (~50-200 µs) becomes a meaningful fraction of total latency. .prepare() plus sql.placeholder('name') compiles once and stores the statement on the underlying driver; subsequent calls only bind parameters and execute. The win is largest on better-sqlite3 where the prepared statement also caches SQLite's plan in memory.

Incorrect (rebuilt on every call — needless compile):

import { eq } from 'drizzle-orm';

// Called on every authenticated request:
async function getUserByToken(token: string) {
  const [user] = await db
    .select({ id: users.id, email: users.email })
    .from(users)
    .innerJoin(sessions, eq(sessions.userId, users.id))
    .where(eq(sessions.token, token))
    .limit(1);
  return user;
}

Correct (prepare once at module load, execute many):

import { sql, eq } from 'drizzle-orm';

const getUserByTokenStmt = db
  .select({ id: users.id, email: users.email })
  .from(users)
  .innerJoin(sessions, eq(sessions.userId, users.id))
  .where(eq(sessions.token, sql.placeholder('token')))
  .limit(1)
  .prepare(); // ← compiled once

export async function getUserByToken(token: string) {
  // .get() returns single row; .all() returns array; SQLite drivers expose both.
  return getUserByTokenStmt.get({ token });
}

*Placeholders for limit / offset (`db.query.` relational queries):**

import { sql } from 'drizzle-orm';

const recentByAuthor = db.query.posts
  .findMany({
    where: (p, { eq }) => eq(p.authorId, sql.placeholder('authorId')),
    orderBy: (p, { desc }) => desc(p.publishedAt),
    limit: sql.placeholder('limit'),
  })
  .prepare();

const top10 = await recentByAuthor.execute({ authorId: 42, limit: 10 });

When NOT to use:

• Queries built dynamically (different where clauses per call) — there's no fixed SQL to prepare. For these, the query builder cost is unavoidable.
• One-off queries (admin scripts, migrations) — preparation overhead exceeds the savings.

Anti-pattern: preparing inside a request handler. That re-prepares on every request, defeating the point. Prepare at module top-level (or inside a memoized factory).

Reference: Drizzle — Performance & prepared statements · SQLite — Prepared Statements

Always limit listing queries

A db.select().from(posts).orderBy(desc(posts.publishedAt)) works in development when posts has 50 rows and returns silently with 500_000 rows in production — exhausting Node memory, blocking the event loop on deserialization, and triggering OOM kills on the container. Every listing query needs a .limit(). For unbounded result sets that you must process completely (exports, migrations), use the streaming iterator API (.iterate() in better-sqlite3) rather than loading everything into memory.

Incorrect (no limit — works fine until production data grows):

import { desc } from 'drizzle-orm';

async function recentPosts() {
  return db.select().from(posts).orderBy(desc(posts.publishedAt));
}
// Today: 50 rows, instant. Six months from now: 500K rows, container OOMs.

Correct (explicit limit with keyset or offset pagination):

import { and, desc, lt } from 'drizzle-orm';

async function recentPosts(cursor?: { publishedAt: Date; id: number }, pageSize = 20) {
  const query = db
    .select()
    .from(posts)
    .where(
      cursor
        ? // Keyset: pick up where we left off. Stable under inserts.
          and(
            lt(posts.publishedAt, cursor.publishedAt),
            // Tie-break on id for stable ordering on duplicate timestamps:
            // (use sql`(published_at, id) < (${cursor.publishedAt}, ${cursor.id})`
            //  if you want the strict tuple comparison)
          )
        : undefined,
    )
    .orderBy(desc(posts.publishedAt), desc(posts.id))
    .limit(pageSize);
  return query;
}

Streaming for full-table operations (better-sqlite3 sync driver):

import Database from 'better-sqlite3';
import { sql } from 'drizzle-orm';

// Drop down to the raw better-sqlite3 statement for cursor semantics:
const stmt = sqlite.prepare('SELECT id, body FROM posts ORDER BY id');
for (const row of stmt.iterate()) {
  // Processed lazily — only one row in memory at a time.
}

For libsql/Turso async streaming: loop with .limit(N) + cursor — there is no .iterate() over the network.

Reference: Drizzle — limit/offset · better-sqlite3 — Statement.iterate()

Use inArray for batch lookups instead of looping queries

for (const id of ids) await db.select().from(users).where(eq(users.id, id)) issues one round trip per ID. On a 50-item list against a libsql/Turso remote, that's 50 sequential network calls — easily 2-5 seconds where one query would take 30 ms. inArray(users.id, ids) compiles to WHERE id IN (?, ?, ?, ...) and returns every match in a single statement. Map the result back to a Map<id, row> if you need ordered output.

Incorrect (N+1 — one query per ID):

import { eq } from 'drizzle-orm';

async function loadUsersByIds(ids: number[]) {
  const result = [];
  for (const id of ids) {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    if (user) result.push(user);
  }
  return result;
}
// 50 ids → 50 round trips. The "await" inside the loop is the bug.

Correct (single query with inArray):

import { inArray } from 'drizzle-orm';

async function loadUsersByIds(ids: number[]) {
  if (ids.length === 0) return [];
  return db.select().from(users).where(inArray(users.id, ids));
}
// 50 ids → 1 round trip.

Preserving caller-supplied order:

async function loadUsersByIds(ids: number[]) {
  if (ids.length === 0) return [];
  const rows = await db.select().from(users).where(inArray(users.id, ids));
  const byId = new Map(rows.map((r) => [r.id, r]));
  return ids.map((id) => byId.get(id)).filter((u) => u !== undefined);
}

Watch out for SQLite's variable limit (default 999, raised to 32_766 in modern SQLite):

// For very large lists, chunk:
async function loadInChunks(ids: number[], chunkSize = 500) {
  const chunks = [];
  for (let i = 0; i < ids.length; i += chunkSize) {
    chunks.push(ids.slice(i, i + chunkSize));
  }
  const results = await Promise.all(
    chunks.map((chunk) => db.select().from(users).where(inArray(users.id, chunk))),
  );
  return results.flat();
}

Reference: Drizzle — inArray operator

Bind parameters with eq/sql tagged template — never concatenate

Drizzle's operators (eq, inArray, gt, etc.) and the sql tagged template both produce parameterized SQL — values are sent separately from the query string and bound by the driver. Building SQL with regular string concatenation (or worse, sql.raw(...) with user input) re-introduces SQL injection and defeats SQLite's prepared-statement cache (every distinct concatenated query is a fresh compile). The sql template's ${} interpolations are bound parameters by default; only sql.raw injects unescaped text.

Incorrect (string concat — injectable AND defeats statement cache):

import { sql } from 'drizzle-orm';

async function search(term: string) {
  // ❌ Direct injection via ${term} as raw string
  return db.all(sql.raw(`SELECT id, name FROM users WHERE name LIKE '%${term}%'`));
  // term = "x'; DELETE FROM users; --" → game over.
}

Correct (sql template — parameters bound, cache shared across calls):

import { sql } from 'drizzle-orm';

async function search(term: string) {
  // ${term} is a bound parameter, not concatenated text.
  return db.all<{ id: number; name: string }>(
    sql`SELECT id, name FROM users WHERE name LIKE ${'%' + term + '%'}`,
  );
}

Correct (operator form — type-safe, idiomatic):

import { like } from 'drizzle-orm';

async function search(term: string) {
  return db
    .select({ id: users.id, name: users.name })
    .from(users)
    .where(like(users.name, `%${term}%`));
}

When you actually need dynamic SQL identifiers (table/column names):

sql.identifier() quotes them safely; never use sql.raw on user input.

import { sql } from 'drizzle-orm';

const orderColumn = req.query.sort === 'name' ? 'name' : 'created_at';
const rows = await db.all(
  sql`SELECT id, name FROM users ORDER BY ${sql.identifier(orderColumn)} DESC LIMIT 50`,
);

Never pass raw user input to `sql.identifier()` — always whitelist first (the ternary above does this). Identifiers are not parameters; they're inlined as quoted SQL names.

Reference: Drizzle — `sql` template · SQLite — Prepared statement caching

Use .returning() instead of a second SELECT after write

After db.insert(...).values(...), callers often need the inserted row — the generated ID, the $defaultFn slug, the created_at timestamp. Doing that with a second db.select().where(eq(users.email, email)) is two round trips and a logical race (another writer could update the row between the insert and the select). SQLite supports INSERT ... RETURNING, UPDATE ... RETURNING, and DELETE ... RETURNING since 3.35, and Drizzle exposes all three via .returning(). One round trip, atomic, no race.

Incorrect (insert + re-select — two round trips, race window):

import { eq } from 'drizzle-orm';

async function createUser(email: string) {
  await db.insert(users).values({ email });
  const [user] = await db.select().from(users).where(eq(users.email, email));
  return user;
}

Correct (returning — one statement):

async function createUser(email: string) {
  const [user] = await db.insert(users).values({ email }).returning();
  return user;
}

Partial returning — only what you need:

const [{ id }] = await db
  .insert(users)
  .values({ email })
  .returning({ id: users.id });

Works on update/delete too:

// Audit trail — return the old row by capturing its values via update + returning:
const [updated] = await db
  .update(users)
  .set({ status: 'deactivated' })
  .where(eq(users.id, userId))
  .returning();

// Returning from delete — useful for soft-delete + audit:
const [deleted] = await db
  .delete(sessions)
  .where(eq(sessions.token, token))
  .returning({ userId: sessions.userId });

Driver support note: .returning() works on better-sqlite3, libsql/Turso, bun:sqlite, and op-sqlite. On Cloudflare D1 it works for single-statement writes but not inside db.batch() — check the response shape.

Reference: Drizzle — Insert with returning · SQLite 3.35 — RETURNING

Select only the columns you need

db.select().from(users) is SELECT * FROM users — every column over the wire, including the 50 KB bio text and the JSON preferences blob, even when the UI just needs id and name. Drizzle's column-object form db.select({ id: users.id, name: users.name }) projects to exactly the columns you list; the result type narrows accordingly. The savings compound on libsql/Turso (network bytes), on serverless databases (egress cost), and on hot paths (deserialization time).

*Incorrect (select — every column, every row):**

import { eq } from 'drizzle-orm';

// Authentication endpoint, called on every request:
const [user] = await db
  .select() // → SELECT id, email, password_hash, name, bio, avatar_url, preferences, created_at, updated_at FROM users
  .from(users)
  .where(eq(users.id, userId));

if (user) {
  return { id: user.id, name: user.name }; // 90% of the row never used
}

Correct (projection — narrow query, narrow type):

import { eq } from 'drizzle-orm';

const [user] = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(eq(users.id, userId));
// Inferred type: { id: number; name: string } | undefined

*Tip — for `db.query. (relational queries), use columns: { ... }`:**

const post = await db.query.posts.findFirst({
  where: (p, { eq }) => eq(p.id, postId),
  columns: { id: true, title: true, publishedAt: true },
});

When NOT to use:

• You genuinely need every column (e.g., serializing the row for an export). In that case, db.select().from() is correct — the readability cost of listing 30 columns isn't worth it.

Reference: Drizzle — Select queries

Use .toSQL() and EXPLAIN QUERY PLAN to verify generated SQL

Drizzle's chainable builder makes it easy to write queries whose generated SQL you've never actually read — and those queries can have full table scans, redundant joins, or unindexed WHERE clauses hiding behind the type system. .toSQL() shows the SQL + parameters before execution. Combining that with SQLite's EXPLAIN QUERY PLAN tells you whether the planner will use an index (SEARCH USING INDEX ...) or scan (SCAN ...). Catch the missing index in development, not the on-call rotation.

Incorrect (ship the query untested — full scan only shows up under prod load):

import { and, desc, eq } from 'drizzle-orm';

// Looks fine. Tests pass. Goes live. p99 spikes to 2 seconds at peak.
async function feed(authorId: number) {
  return db
    .select({ id: posts.id, title: posts.title })
    .from(posts)
    .where(and(eq(posts.authorId, authorId), eq(posts.published, true)))
    .orderBy(desc(posts.publishedAt))
    .limit(20);
}
// Hidden in the plan: SCAN posts USE TEMP B-TREE FOR ORDER BY.
// No index covered (author_id, published, published_at).

Correct (inspect the SQL + plan during development):

import { and, desc, eq, sql } from 'drizzle-orm';

const query = db
  .select({ id: posts.id, title: posts.title })
  .from(posts)
  .where(and(eq(posts.authorId, 42), eq(posts.published, true)))
  .orderBy(desc(posts.publishedAt))
  .limit(20);

// Step 1 — see the SQL Drizzle generated:
console.log(query.toSQL());
// {
//   sql: 'select "id", "title" from "posts" where ("author_id" = ? and "published" = ?) order by "published_at" desc limit ?',
//   params: [42, 1, 20]
// }

// Step 2 — ask SQLite how it will execute:
const plan = await db.all<{ id: number; parent: number; notused: number; detail: string }>(
  sql`EXPLAIN QUERY PLAN
      SELECT id, title FROM posts
      WHERE author_id = 42 AND published = 1
      ORDER BY published_at DESC LIMIT 20`,
);
console.table(plan);
// Want: "SEARCH posts USING INDEX posts_author_published_idx (author_id=? AND published=?)"
// Bad:  "SCAN posts" or "USE TEMP B-TREE FOR ORDER BY"

Make this a test for hot paths:

import { test, expect } from 'vitest';
import { sql } from 'drizzle-orm';

test('feed query uses the author+published index', async () => {
  const plan = await db.all<{ detail: string }>(
    sql`EXPLAIN QUERY PLAN
        SELECT id, title FROM posts
        WHERE author_id = 1 AND published = 1
        ORDER BY published_at DESC LIMIT 20`,
  );
  const detail = plan.map((r) => r.detail).join(' | ');
  expect(detail).toMatch(/USING INDEX posts_author_published_idx/);
  expect(detail).not.toMatch(/SCAN/); // no full-table scans in hot paths
});

When the plan is wrong, the fix is usually one of:

• Add a composite index covering the WHERE columns in order, plus the ORDER BY column.
• Rewrite to put indexable predicates before non-indexable ones (LIKE 'foo%' is indexable; LIKE '%foo' is not).
• Use a covering index that includes the projected columns so the planner can skip the table read.

Reference: SQLite — EXPLAIN QUERY PLAN · Drizzle — Building queries dynamically

Use onConflictDoUpdate/DoNothing for upsert, not select-then-write

"Try to find the row, update if it exists, insert if it doesn't" implemented as select then insert or update is two round trips and is racy — two callers can both see "not found" and both insert, then one will fail (or you get duplicates if no unique constraint). SQLite supports INSERT ... ON CONFLICT ... DO UPDATE, which Drizzle exposes as .onConflictDoUpdate({ target, set }). It's one statement, atomic at the database level, and combines naturally with .returning().

Incorrect (two queries, race window between them):

import { eq } from 'drizzle-orm';

async function recordView(postId: number, userId: number) {
  const [existing] = await db
    .select()
    .from(postViews)
    .where(and(eq(postViews.postId, postId), eq(postViews.userId, userId)));

  if (existing) {
    await db
      .update(postViews)
      .set({ viewedAt: new Date(), count: existing.count + 1 })
      .where(eq(postViews.id, existing.id));
  } else {
    await db.insert(postViews).values({ postId, userId, count: 1, viewedAt: new Date() });
  }
  // Race: two concurrent requests both see existing=null, both insert,
  // unique constraint rejects one.
}

Correct (single atomic statement):

import { sql } from 'drizzle-orm';

async function recordView(postId: number, userId: number) {
  await db
    .insert(postViews)
    .values({ postId, userId, count: 1, viewedAt: new Date() })
    .onConflictDoUpdate({
      target: [postViews.postId, postViews.userId], // unique constraint or PK
      set: {
        count: sql`${postViews.count} + 1`,         // increment server-side
        viewedAt: new Date(),
      },
    });
}

Idempotent insert — "create if missing, otherwise leave it":

const [user] = await db
  .insert(users)
  .values({ email })
  .onConflictDoNothing({ target: users.email })
  .returning(); // returns the new row, or [] if conflict

Conditional update — only overwrite when newer:

import { sql } from 'drizzle-orm';

await db
  .insert(syncState)
  .values({ key, value, version })
  .onConflictDoUpdate({
    target: syncState.key,
    set: { value, version },
    setWhere: sql`${syncState.version} < ${version}`, // skip if local is newer
  });

Requirement: the conflict target must be a PRIMARY KEY or UNIQUE constraint/index. Without one, SQLite has nothing to conflict on and the statement falls through to a plain insert.

Reference: Drizzle — onConflict · SQLite — UPSERT

Declare relations() so db.query.* can resolve with

db.query.users.findMany({ with: { posts: true } }) only works when Drizzle knows the users → posts relationship. That knowledge is declared with the relations() helper alongside your table definitions and registered with the drizzle() client via the schema option. Skip this and db.query either won't be typed or will fail at runtime with "no relation found" — and you'll re-implement the join manually for every nested fetch.

Incorrect (no relations declared — db.query is unusable):

// src/db/schema.ts — tables only, no relations
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
});

export const posts = sqliteTable('posts', {
  id: integer().primaryKey({ autoIncrement: true }),
  authorId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text().notNull(),
});

// src/db/client.ts
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './schema';

export const db = drizzle(sqlite, { schema });

// Compile error: Property 'users' does not exist on type 'never'.
const result = await db.query.users.findMany({ with: { posts: true } });

Correct (relations declared + registered):

// src/db/schema.ts
import { relations } from 'drizzle-orm';
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
});

export const posts = sqliteTable('posts', {
  id: integer().primaryKey({ autoIncrement: true }),
  authorId: integer().notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text().notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// src/db/client.ts — register the schema (tables AND relations)
import { drizzle } from 'drizzle-orm/better-sqlite3';
import * as schema from './schema';

export const db = drizzle(sqlite, { schema });
// Now db.query.users and db.query.posts are typed and `with` resolves:
const result = await db.query.users.findMany({ with: { posts: true } });

Naming the FK relation when there are two:

If a table has two FKs to the same parent (e.g., messages.fromUserId and messages.toUserId), name the relations so Drizzle can disambiguate:

export const messages = sqliteTable('messages', {
  id: integer().primaryKey(),
  fromUserId: integer().notNull().references(() => users.id),
  toUserId: integer().notNull().references(() => users.id),
});

export const messagesRelations = relations(messages, ({ one }) => ({
  from: one(users, { fields: [messages.fromUserId], references: [users.id], relationName: 'sent' }),
  to:   one(users, { fields: [messages.toUserId],   references: [users.id], relationName: 'received' }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  sent:     many(messages, { relationName: 'sent' }),
  received: many(messages, { relationName: 'received' }),
}));

Reference: Drizzle — Relations · Drizzle — Relational Queries

Filter related rows in with's where, not in JavaScript

with: { posts: true } returns every related post; calling .posts.filter((p) => p.published) in JavaScript means the unpublished draft posts still travelled over the wire and were deserialized. The where: clause inside with pushes the filter into the SQL subquery — only matching rows leave the database. Combine with limit, offset, and orderBy to fully express the related-fetch on the server.

Incorrect (filter in JS after the fetch — wasted bandwidth and CPU):

const users = await db.query.users.findMany({
  with: { posts: true },
});

// Drafts and archived posts came across the wire only to be discarded:
const usersWithPublishedPosts = users.map((u) => ({
  ...u,
  posts: u.posts.filter((p) => p.published),
}));

Correct (where: inside with — filter at the source):

const users = await db.query.users.findMany({
  with: {
    posts: {
      where: (p, { eq, and, isNotNull }) =>
        and(eq(p.published, true), isNotNull(p.publishedAt)),
      orderBy: (p, { desc }) => desc(p.publishedAt),
      limit: 10,
    },
  },
});

Complex per-row filters use the second-arg helpers (`eq`, `and`, `or`, `not`, `inArray`, `gt`, etc.):

const usersWithRecentActivity = await db.query.users.findMany({
  where: (u, { eq }) => eq(u.status, 'active'),
  with: {
    posts: {
      where: (p, { and, gt, eq }) =>
        and(
          eq(p.published, true),
          gt(p.publishedAt, new Date(Date.now() - 7 * 86_400_000)),
        ),
    },
  },
});

Parent-side filter that depends on related rows — `where` + `exists` on the parent:

If you want "users that have at least one published post", filter on the parent with a subquery rather than expecting with to drop empty parents:

import { exists, eq, and } from 'drizzle-orm';

const authors = await db
  .select()
  .from(users)
  .where(
    exists(
      db.select({ one: sql`1` })
        .from(posts)
        .where(and(eq(posts.authorId, users.id), eq(posts.published, true))),
    ),
  );

with: { posts: { where: ... } } does not filter out parents that have zero matching related rows — they come back with posts: []. That's the expected behavior; use the exists pattern above when you need parent-side filtering.

Reference: Drizzle — Filters on relational queries

Drop to leftJoin when you need aggregates or flat shapes

db.query.* returns nested resources. When the output is one flat row per parent with aggregates over the children — "users with their post count and last-post-date" — leftJoin + groupBy is the right tool. Trying to express this in the relational query builder either doesn't compose (no aggregate over a with collection) or produces a less efficient plan than a direct GROUP BY. Use leftJoin so users with zero posts still appear (innerJoin drops them), and project explicit count() / max() expressions.

Incorrect (load all posts into JS, then aggregate — slow and memory-bound):

const users = await db.query.users.findMany({
  with: { posts: true },
});

const summary = users.map((u) => ({
  userId: u.id,
  name: u.name,
  postCount: u.posts.length,
  lastPostAt: u.posts.reduce<Date | null>(
    (max, p) => (!max || p.publishedAt! > max ? p.publishedAt : max),
    null,
  ),
}));
// Loaded every column of every post just to count and find the max.

Correct (SQL aggregate — one statement, server-side counts):

import { count, eq, max } from 'drizzle-orm';

const summary = await db
  .select({
    userId: users.id,
    name: users.name,
    postCount: count(posts.id), // count of non-null = posts joined
    lastPostAt: max(posts.publishedAt),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name);

Why `leftJoin` and not `innerJoin`: innerJoin drops users with zero posts. leftJoin keeps them with postCount = 0 and lastPostAt = null — usually the correct shape for a dashboard.

Combine with a `having` for filtered aggregates ("users with ≥ 5 posts"):

import { count, eq, gte } from 'drizzle-orm';

const prolific = await db
  .select({ userId: users.id, name: users.name, postCount: count(posts.id) })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .having(({ postCount }) => gte(postCount, 5));

Window function alternative — when you need the aggregate alongside the raw rows:

For "every post with its author's total post count", a window function avoids the GROUP BY altogether:

import { sql, eq } from 'drizzle-orm';

const rows = await db
  .select({
    postId: posts.id,
    title: posts.title,
    authorPostCount: sql<number>`count(*) over (partition by ${posts.authorId})`,
  })
  .from(posts);

Reference: Drizzle — Aggregations & GROUP BY · Drizzle — Joins

Use columns inside with to keep nested payloads small

with: { posts: true } returns every column of every related post. For a "user header + recent post titles" UI, that's loading every post body, every metadata blob, every analytics column — over and over for every user in the result set. The columns: selector inside with projects the related rows to the columns you actually need; combine it with limit to cap how many related rows come back per parent.

Incorrect (with: true loads every post column for every user):

const usersWithPosts = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: {
    posts: true, // Every column of every post per user — huge payload
  },
});

Correct (columns + limit — bounded fetch):

const usersWithPosts = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: {
    posts: {
      columns: { id: true, title: true, publishedAt: true },
      limit: 5,
      orderBy: (p, { desc }) => desc(p.publishedAt),
    },
  },
});

Excluding columns instead of including them — useful when most columns are wanted:

const post = await db.query.posts.findFirst({
  where: (p, { eq }) => eq(p.id, postId),
  columns: { internalAnalyticsBlob: false }, // every other column included
  with: {
    author: { columns: { id: true, name: true, avatarUrl: true } },
    // ↑ Don't leak email/password_hash by selecting * on the author
  },
});

Security implication: the columns: selector on author above isn't just a payload optimization — it's an authorization decision. Selecting password_hash or email_verification_token because you used with: { author: true } and then forgot to redact in the response is a real bug pattern. Project to the fields callers should see.

Reference: Drizzle — Partial fields on relational queries

Use db.query with for nested fetches, not manual joins + grouping

leftJoin-then-group-in-JS is the historical pattern: query users joined to posts, get one flat row per (user, post), then bucket them by user.id in JavaScript. It's verbose, easy to break (one missing where and you double-count), and the inferred type is (User & { posts: Post })[] — flat — not what you want. db.query.users.findMany({ with: { posts: true } }) compiles to a single SQL statement (a correlated subquery or LATERAL join depending on dialect), returns nested User & { posts: Post[] }, and stays in sync with relation declarations.

Incorrect (manual join + JS grouping — verbose, error-prone):

import { eq } from 'drizzle-orm';

async function usersWithPosts() {
  const rows = await db
    .select()
    .from(users)
    .leftJoin(posts, eq(posts.authorId, users.id));

  // rows: { users: User; posts: Post | null }[] — flat, one row per (user, post)
  const grouped = new Map<number, { user: User; posts: Post[] }>();
  for (const row of rows) {
    let bucket = grouped.get(row.users.id);
    if (!bucket) {
      bucket = { user: row.users, posts: [] };
      grouped.set(row.users.id, bucket);
    }
    if (row.posts) bucket.posts.push(row.posts);
  }
  return [...grouped.values()];
}

Correct (relational query — one statement, nested types):

async function usersWithPosts() {
  return db.query.users.findMany({
    with: { posts: true },
  });
  // Inferred: (User & { posts: Post[] })[] — nested, no grouping needed
}

Nested deeper — posts with comments with their authors:

const feed = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: {
          with: { author: { columns: { id: true, name: true } } },
        },
      },
    },
  },
});

When to drop to `leftJoin` instead:

• You need GROUP BY with aggregates (count, sum, avg) across the joined rows.
• You need explicit join control (INNER, FULL OUTER) for filtering semantics.
• You're projecting a single flat shape (e.g., dashboard table with user.name and post.title side by side).

For everything else (nested resource trees, has-many fetches), db.query is the right tool.

Reference: Drizzle — Relational Queries with `with`

Always declare a primary key (single or composite)

A SQLite table without a PRIMARY KEY still has a hidden rowid — but that rowid can be reassigned by VACUUM, is not exposed through Drizzle, cannot be referenced by foreign keys, and silently breaks .onConflictDoUpdate() because there's no conflict target. Junction tables that look like they have a "compound natural key" need an explicit composite primary key, otherwise the same (userId, roleId) pair can be inserted twice.

Incorrect (no PK on a join table — duplicates and no upsert target):

import { integer, sqliteTable } from 'drizzle-orm/sqlite-core';

export const userRoles = sqliteTable('user_roles', {
  userId: integer().notNull(),
  roleId: integer().notNull(),
});

// Both inserts succeed — table now has two identical rows:
await db.insert(userRoles).values({ userId: 1, roleId: 1 });
await db.insert(userRoles).values({ userId: 1, roleId: 1 });

Correct (composite primary key — duplicates rejected, upsert target available):

import { integer, primaryKey, sqliteTable } from 'drizzle-orm/sqlite-core';

export const userRoles = sqliteTable(
  'user_roles',
  {
    userId: integer().notNull(),
    roleId: integer().notNull(),
  },
  (table) => [
    primaryKey({ columns: [table.userId, table.roleId] }),
  ],
);

await db
  .insert(userRoles)
  .values({ userId: 1, roleId: 1 })
  .onConflictDoNothing(); // second call is a no-op, not a duplicate

For autoincrement single-PK tables, prefer `integer().primaryKey({ autoIncrement: true })` or generate UUIDs/CUIDs in `$defaultFn` rather than relying on rowid.

Reference: Drizzle SQLite — primaryKey

Declare foreign keys with explicit onDelete/onUpdate

A references(() => parent.id) without onDelete defaults to SQLite's NO ACTION — and only if PRAGMA foreign_keys = ON is set on the connection (it is off by default in SQLite). The combination means rows in the parent can be deleted while the child still references them, leaving orphaned IDs that fail any subsequent join. Always specify the cascade behavior explicitly so the intent is in the schema, not in application code.

Incorrect (no onDelete — orphans on parent delete if FK ever enforced):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
});

export const posts = sqliteTable('posts', {
  id: integer().primaryKey({ autoIncrement: true }),
  authorId: integer()
    .notNull()
    .references(() => users.id), // No cascade specified
  body: text().notNull(),
});

Correct (explicit cascade — intent recorded in schema):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
});

export const posts = sqliteTable('posts', {
  id: integer().primaryKey({ autoIncrement: true }),
  authorId: integer()
    .notNull()
    .references(() => users.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  body: text().notNull(),
});

Alternative (soft-delete style — keep the row but allow detach):

authorId: integer().references(() => users.id, { onDelete: 'set null' }),

Cascade choice cheat sheet:

• 'cascade' — child rows are deleted with the parent (comments under a deleted post)
• 'set null' — child keeps row but loses link (orders detach from a deleted customer)
• 'restrict' — block parent delete if children exist (categories with active products)

Foreign keys are only enforced when the connection has `PRAGMA foreign_keys = ON` set — see [conn-foreign-keys-pragma](conn-foreign-keys-pragma.md).

Reference: Drizzle ORM — Foreign Keys

Index foreign keys and frequent WHERE columns

SQLite does not automatically create an index on foreign key columns (unlike MySQL/InnoDB). Every WHERE authorId = ? and every parent-side cascade then scans the full child table. Add an index() for every FK column and for any column you filter on hot paths. Composite indexes serve WHERE a = ? AND b = ? and WHERE a = ? ORDER BY b patterns only when columns are in the right order — leading-column queries hit the index, trailing-column queries do not.

*Incorrect (FK with no index — `SELECT FROM posts WHERE authorId = ?` scans the whole table):**

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const posts = sqliteTable('posts', {
  id: integer().primaryKey({ autoIncrement: true }),
  authorId: integer()
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  publishedAt: integer({ mode: 'timestamp_ms' }),
  body: text().notNull(),
});

Correct (explicit indexes for FK + listing pattern):

import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const posts = sqliteTable(
  'posts',
  {
    id: integer().primaryKey({ autoIncrement: true }),
    authorId: integer()
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    publishedAt: integer({ mode: 'timestamp_ms' }),
    body: text().notNull(),
  },
  (table) => [
    index('posts_author_idx').on(table.authorId),
    // Serves "feed for author X ordered by recency":
    index('posts_author_published_idx').on(table.authorId, table.publishedAt),
  ],
);

Partial index — when most rows don't match the filter, index only the ones that do:

import { sql } from 'drizzle-orm';

(table) => [
  index('posts_published_recent_idx')
    .on(table.publishedAt)
    .where(sql`${table.publishedAt} is not null`),
]

Verify with `EXPLAIN QUERY PLAN`:

const plan = await db.all(sql`EXPLAIN QUERY PLAN
  SELECT * FROM posts WHERE author_id = 1 ORDER BY published_at DESC LIMIT 20`);
// Expect "SEARCH posts USING INDEX posts_author_published_idx", not "SCAN posts"

Reference: SQLite — Query Planner · Drizzle — Indexes

Use integer mode 'boolean' for boolean columns

SQLite has no native boolean type — it stores everything as INTEGER, REAL, TEXT, BLOB, or NULL. A raw integer() column lets 0 and 1 leak into application code as number, forcing every consumer to remember the encoding and breaking === true / === false checks. Declaring integer({ mode: 'boolean' }) tells Drizzle to convert at the driver boundary so the inferred TypeScript type is boolean.

Incorrect (raw integer leaks `0 | 1` into the app):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey(),
  email: text().notNull(),
  emailVerified: integer().notNull().default(0), // inferred as number — 0/1
});

const [user] = await db.select().from(users).limit(1);
if (user.emailVerified === true) { /* unreachable — value is 0 or 1 */ }

Correct (boolean mode — Drizzle converts at the boundary):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey(),
  email: text().notNull(),
  emailVerified: integer({ mode: 'boolean' }).notNull().default(false),
});

const [user] = await db.select().from(users).limit(1);
if (user.emailVerified) { /* works — value is true | false */ }

When NOT to use:

• The column genuinely represents a tri-state (0, 1, 2) or a small integer enum encoded as numbers. In that case integer() with a check constraint is correct.
• You're consuming an existing schema that already stores something other than 0/1 (e.g., 'Y'/'N' text). Map to the existing storage type rather than coercing.

Reference: Drizzle SQLite Column Types — Boolean

Use text mode 'json' for JSON columns, not blob

Both blob({ mode: 'json' }) and text({ mode: 'json' }) store JSON, but SQLite's built-in json1 extension (json_extract, json_each, ->, ->>) only operates on text values. A blob-stored JSON column can be read back as a typed object but cannot participate in WHERE json_extract(data, '$.role') = 'admin', indexed JSON paths, or json_patch updates — you must hydrate every row to filter. Use text({ mode: 'json' }) unless you have a specific reason (binary payload, JSONB future-compat) to store opaque bytes.

Incorrect (blob JSON — cannot query inside the document):

import { blob, integer, sqliteTable } from 'drizzle-orm/sqlite-core';

type Settings = { theme: 'light' | 'dark'; notifications: boolean };

export const userPrefs = sqliteTable('user_prefs', {
  userId: integer().primaryKey(),
  settings: blob({ mode: 'json' }).$type<Settings>().notNull(),
});

// To find dark-mode users, you must load all rows and filter in JS:
const all = await db.select().from(userPrefs);
const darkUsers = all.filter((r) => r.settings.theme === 'dark');

Correct (text JSON — server-side filtering, indexable paths):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

type Settings = { theme: 'light' | 'dark'; notifications: boolean };

export const userPrefs = sqliteTable('user_prefs', {
  userId: integer().primaryKey(),
  settings: text({ mode: 'json' }).$type<Settings>().notNull(),
});

// Filter inside the JSON document:
const darkUsers = await db
  .select()
  .from(userPrefs)
  .where(sql`json_extract(${userPrefs.settings}, '$.theme') = 'dark'`);

Index a JSON path (SQLite ≥ 3.38):

CREATE INDEX user_prefs_theme_idx
  ON user_prefs (json_extract(settings, '$.theme'));

Then WHERE json_extract(settings, '$.theme') = 'dark' uses the index instead of scanning.

When NOT to use:

• You're storing opaque binary payloads (encrypted blobs, protobuf) — blob({ mode: 'buffer' }) is correct.

Reference: SQLite — JSON1 functions · Drizzle — text/blob JSON modes

Store dates as integer timestamp_ms, not text

SQLite has no native date type. Storing dates as ISO strings means orderBy(createdAt) becomes lexicographic — it works only if every value is normalized to the same zero-padded YYYY-MM-DDTHH:MM:SS.sssZ form, and one stray timezone offset breaks ordering silently. integer({ mode: 'timestamp_ms' }) stores epoch milliseconds, gives you integer-comparison range queries, indexes correctly, and Drizzle returns Date objects on read. Use timestamp mode (seconds) only for compatibility with existing schemas.

Incorrect (text dates — lexicographic ordering, no type safety):

import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const events = sqliteTable('events', {
  id: text().primaryKey(),
  occurredAt: text().notNull(), // 'is it ISO? local? unix string?'
});

// Caller must remember to call toISOString and worry about TZ:
await db.insert(events).values({ id, occurredAt: new Date().toISOString() });

Correct (integer timestamp_ms — indexable, type-safe):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

export const events = sqliteTable('events', {
  id: text().primaryKey(),
  occurredAt: integer({ mode: 'timestamp_ms' })
    .notNull()
    .$defaultFn(() => new Date()),
});

await db.insert(events).values({ id }); // $defaultFn fills occurredAt
const recent = await db
  .select()
  .from(events)
  .where(sql`${events.occurredAt} > ${Date.now() - 86_400_000}`)
  .orderBy(events.occurredAt);

When NOT to use:

• The column must be human-readable in raw SQLite browsers and you've already paid the cost of strict ISO discipline.
• You're consuming a legacy schema you don't own (use mode: 'timestamp' to map seconds-since-epoch if that's the existing format).

Reference: Drizzle SQLite Column Types — Integer modes

Add unique constraints for natural keys (email, slug, externalId)

Application-level "check then insert" is a TOCTOU race — two concurrent requests both pass the existence check and both insert. The only reliable defense for natural keys (email, username, slug, external provider ID) is a UNIQUE constraint in the schema. A unique constraint is also the conflict target .onConflictDoUpdate({ target: users.email, ... }) needs to act as an idempotent upsert.

Incorrect (app-level "select then insert" — racy, can produce duplicates):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { eq } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull(), // No uniqueness
});

async function signUp(email: string) {
  const existing = await db.select().from(users).where(eq(users.email, email));
  if (existing.length > 0) throw new Error('exists');
  await db.insert(users).values({ email }); // Race: two callers can both reach this
}

Correct (unique constraint — database rejects the duplicate atomically):

import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
});

async function signUp(email: string) {
  // No select needed — the unique constraint guards uniqueness.
  // .onConflictDoNothing() turns the race into a benign no-op.
  const [user] = await db
    .insert(users)
    .values({ email })
    .onConflictDoNothing({ target: users.email })
    .returning();
  return user; // undefined if email already existed
}

Case-insensitive uniqueness uses `uniqueIndex` on an expression:

import { sql } from 'drizzle-orm';
import { sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable(
  'users',
  {
    email: text().notNull(),
  },
  (table) => [
    uniqueIndex('users_email_lower_idx').on(sql`lower(${table.email})`),
  ],
);

Reference: Drizzle ORM — Unique constraints

Use db.batch() to collapse round trips on libsql/Turso/D1

db.transaction() is the right tool against a local SQLite file — each statement is microseconds away. Against a remote libsql (Turso) or D1 database, each awaited statement crosses the network. Five sequential writes in a transaction become five network round trips even though they're inside one logical transaction. db.batch([s1, s2, s3, s4, s5]) ships all five statements in one request, runs them sequentially inside an implicit transaction on the server, and returns an array of results. One round trip, full atomicity. Supported on libsql, Neon, and D1 drivers.

Incorrect (sequential awaits over the network — N round trips):

import { drizzle } from 'drizzle-orm/libsql';

// 5 round trips to Turso, each ~50ms = 250ms minimum:
await db.transaction(async (tx) => {
  await tx.insert(orders).values(order);
  await tx.insert(orderItems).values(items);
  await tx.update(inventory).set({ stock: sql`${inventory.stock} - 1` });
  await tx.insert(auditLog).values({ action: 'order.created' });
  await tx.update(users).set({ orderCount: sql`${users.orderCount} + 1` });
});

Correct (single batch — one round trip):

import { eq, sql } from 'drizzle-orm';

const [createdOrder, , , , ] = await db.batch([
  db.insert(orders).values(order).returning(),
  db.insert(orderItems).values(items),
  db.update(inventory).set({ stock: sql`${inventory.stock} - 1` }).where(eq(inventory.sku, sku)),
  db.insert(auditLog).values({ action: 'order.created' }),
  db.update(users).set({ orderCount: sql`${users.orderCount} + 1` }).where(eq(users.id, userId)),
]);
// 50ms total; atomic; results in array order.

Result tuple is typed per statement:

type BatchResponse = [
  { id: number }[],   // .returning() projects to { id }
  ResultSet,          // insert without .returning()
  ResultSet,          // update
  ResultSet,          // insert
  ResultSet,          // update
];

When NOT to use db.batch():

• Statements that depend on each other's results (e.g., "insert order, then use the new ID in the items"). Batch statements run server-side without round-tripping data back, so the client can't reference an in-flight result. For dependent statements either:
• Run them in a regular db.transaction() (accepting the round trips), or
• Generate the ID on the client ($defaultFn(() => crypto.randomUUID())) so dependent statements can be prepared in advance.
• Statements that need to make a decision based on a query result mid-batch — batches are non-interactive.

Driver matrix:

• ✅ libsql / Turso — full support
• ✅ Neon HTTP driver — full support
• ✅ Cloudflare D1 — full support; .returning() works but the response shape is per-statement (each entry is a D1Result-flavored object)
• ❌ better-sqlite3 / bun:sqlite — no db.batch() (use db.transaction() — there's no network to amortize)

Reference: Drizzle — Batch API

Handle SQLITE_BUSY with bounded retries on writes

PRAGMA busy_timeout = 5000 (see conn-set-busy-timeout) tells SQLite to wait up to 5 s for a contended lock before returning SQLITE_BUSY. Under heavy write contention — many concurrent requests or many processes sharing the file — that timeout still expires and the driver throws. Without a retry wrapper, every such event bubbles up to the caller as a 500. Add a small bounded retry around db.transaction() for transient busy errors; leave non-retryable failures (constraint violation, syntax error) to propagate.

Incorrect (no retry — every transient busy is a user-visible error):

await db.transaction(async (tx) => {
  await tx.update(counters).set({ value: sql`value + 1` }).where(eq(counters.key, 'hits'));
});
// SqliteError: SQLITE_BUSY: database is locked → propagates to the request handler.

Correct (bounded retry with backoff, only on busy errors):

async function withBusyRetry<T>(fn: () => Promise<T>, attempts = 4): Promise<T> {
  for (let attempt = 0; attempt < attempts; attempt++) {
    try {
      return await fn();
    } catch (err) {
      if (!isBusyError(err) || attempt === attempts - 1) throw err;
      // Exponential backoff with jitter: 25ms, 50ms, 100ms
      const delay = 25 * 2 ** attempt + Math.random() * 25;
      await new Promise((resolve) => setTimeout(resolve, delay));
    }
  }
  throw new Error('unreachable');
}

function isBusyError(err: unknown): boolean {
  // better-sqlite3:  err.code === 'SQLITE_BUSY' (or SQLITE_BUSY_SNAPSHOT)
  // libsql:          err.code === 'SQLITE_BUSY'
  // bun:sqlite:      err.code === 'SQLITE_BUSY'
  return (
    typeof err === 'object' &&
    err !== null &&
    'code' in err &&
    typeof (err as { code: unknown }).code === 'string' &&
    (err as { code: string }).code.startsWith('SQLITE_BUSY')
  );
}

await withBusyRetry(() =>
  db.transaction(async (tx) => {
    await tx.update(counters).set({ value: sql`value + 1` }).where(eq(counters.key, 'hits'));
  }, { behavior: 'immediate' }),
);

Don't retry:

• SQLITE_CONSTRAINT (unique/foreign-key violation) — the next attempt will fail the same way.
• SQLITE_ERROR (syntax) — your code is wrong, retrying won't fix it.
• Any error you can't positively identify as transient.

Reduce busy errors before adding retries: 1. Enable WAL mode (conn-enable-wal) — readers and one writer can proceed concurrently. 2. Raise busy_timeout to 5-15 seconds (conn-set-busy-timeout). 3. Use behavior: 'immediate' for write transactions so contention surfaces at BEGIN, not mid-transaction. 4. Keep transactions short (see tx-no-network-io-inside-transaction).

Reference: SQLite — Locking and Concurrency · SQLite — Result codes (SQLITE_BUSY)