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.