Claude Code with SQLite: Local Dev, Turso, and Edge Integration

Why SQLite needs its own CLAUDE.md

SQLite is having a renaissance. Bun ships a bundled bun:sqlite module. Turso takes libSQL serverless with global replicas. Cloudflare D1 gives you a SQLite-compatible database inside a Worker binding. Fly.io positions SQLite-on-disk as the right database for single-region apps. The reasons that made SQLite feel like a toy database for local scripts are rapidly becoming the reasons teams choose it for production: zero configuration, no separate process, no connection pool to manage, and a transaction model that is simpler than any server-based RDBMS.

The problem is that Claude Code defaults to Postgres-flavoured patterns. When you ask Claude to "add a boolean column" without project context, you get a BOOLEAN column. That works in Postgres. In SQLite, BOOLEAN is stored as INTEGER with no native boolean type, and the client library behaviour varies by driver. When you ask Claude to "add an enum-constrained column", you get a CREATE TYPE statement that does not exist in SQLite. When you ask Claude to "remove a column from this table", you get a bare ALTER TABLE ... DROP COLUMN that only works reliably on SQLite 3.35.0 and later, and fails on any older embedded runtime.

These are not obscure edge cases. They are the first three things most developers want to do after creating a table. Without a SQLite-specific CLAUDE.md, the generated code looks right, passes linting, and fails at runtime.

This guide covers the CLAUDE.md configuration, schema conventions, migration patterns, and edge deployment rules that make Claude Code reliable for SQLite projects. If you are setting up Claude Code for the first time, the Claude Code setup guide covers installation before any of this applies. For Postgres-specific patterns, the Claude Code PostgreSQL guide covers the equivalent workflow.

The SQLite dialect: what Claude gets wrong without context

Before the CLAUDE.md template, it helps to understand exactly which SQLite behaviours cause the most failures in generated code. These are the patterns Claude produces from Postgres-trained instincts that do not translate.

No native boolean type. SQLite stores booleans as INTEGER 0/1. The column definition BOOLEAN is accepted by SQLite (it uses dynamic typing with type affinity) but the resulting column stores integers, not true booleans. Most client libraries map this correctly, but not all. The unambiguous pattern is INTEGER NOT NULL CHECK(value IN (0, 1)) DEFAULT 0. Claude generates BOOLEAN or BOOLEAN NOT NULL DEFAULT FALSE without a rule.

No native enum type. Postgres has CREATE TYPE status AS ENUM (...). SQLite has no such construct. The equivalent is a TEXT column with a CHECK constraint. Claude generates CREATE TYPE statements that cause a syntax error on the first migration run.

No native date/time type. SQLite stores dates as TEXT (ISO 8601), INTEGER (Unix epoch seconds), or REAL (Julian day numbers). There is no TIMESTAMP WITH TIME ZONE column. Claude generates Postgres timestamp types that are accepted by SQLite's type affinity system but produce inconsistent behaviour across drivers.

Limited ALTER TABLE. Pre-3.35 SQLite has four ALTER TABLE operations: RENAME TABLE, RENAME COLUMN, ADD COLUMN, and nothing else. DROP COLUMN arrived in 3.35.0 (2021). ADD COLUMN has constraints: the new column must have a default value or allow NULL, and cannot be a PRIMARY KEY or UNIQUE unless added via recreation. Claude generates ALTER TABLE ... DROP COLUMN and ALTER TABLE ... MODIFY COLUMN patterns that fail on any runtime below 3.35.

Single-writer concurrency. SQLite uses file-level locking. In WAL (Write-Ahead Log) mode, multiple readers can proceed concurrently with a single writer. In the default journal mode (DELETE), writes block reads. Claude generates concurrent write patterns without flagging the single-writer constraint.

AUTOINCREMENT vs INTEGER PRIMARY KEY. In SQLite, INTEGER PRIMARY KEY is an alias for the rowid and auto-increments by default. Adding AUTOINCREMENT changes the guarantee from "unique monotonically increasing" to "unique, never reusing deleted row IDs", at the cost of a separate sqlite_sequence table lookup on every insert. Claude generates SERIAL (Postgres), AUTO_INCREMENT (MySQL), and AUTOINCREMENT (SQLite) interchangeably. The right SQLite pattern for most tables is id INTEGER PRIMARY KEY with no AUTOINCREMENT keyword unless you genuinely need the reuse-prevention guarantee.

The SQLite CLAUDE.md template

The CLAUDE.md at your project root is read at the start of every Claude Code session. For a SQLite project it needs to answer: which driver, which runtime, where the database file lives, WAL mode status, migration strategy, and the hard rules that prevent dialect confusion.

# SQLite project rules

## Stack
- Runtime: Node.js 22.x (or Bun 1.2.x -- see driver section)
- SQLite driver: better-sqlite3 4.x (Node) | bun:sqlite (Bun) | @libsql/client (Turso/edge)
- ORM/query builder: Drizzle ORM 0.36.x with drizzle-orm/better-sqlite3
- TypeScript: 5.x strict mode
- Database file: ./data/app.db (dev) | Turso remote URL (production)

## Project structure
- src/db/schema.ts       -- Drizzle table definitions using sqliteTable()
- src/db/client.ts       -- singleton Database instance, WAL mode enabled on open
- src/db/migrations/     -- SQL migration files, numbered sequentially (0001_, 0002_)
- src/db/migrate.ts      -- migration runner script (applies pending files in order)

## SQLite dialect rules -- ALWAYS follow these
- Boolean columns: INTEGER NOT NULL CHECK(value IN (0, 1)) DEFAULT 0
  (NOT boolean, NOT BOOLEAN, NOT TINYINT -- INTEGER with CHECK)
- Enum-like columns: TEXT NOT NULL CHECK(status IN ('active','inactive','pending'))
  (NOT CREATE TYPE, NOT ENUM -- TEXT with CHECK constraint)
- Date/time columns: TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
  (NOT TIMESTAMP, NOT DATETIME WITHOUT TIME ZONE)
- Auto-increment PK: id INTEGER PRIMARY KEY
  (NOT SERIAL, NOT AUTO_INCREMENT, NOT INTEGER PRIMARY KEY AUTOINCREMENT unless reuse-prevention is required)
- JSON columns: TEXT NOT NULL DEFAULT '{}'
  (SQLite has no native JSON type; json_extract() works on TEXT columns)
- No RETURNING clause on INSERT unless SQLite >= 3.35 is confirmed
- No DROP COLUMN unless SQLite >= 3.35 is confirmed
- No MODIFY COLUMN -- use table recreation pattern instead

## WAL mode
- Enable immediately on database open: PRAGMA journal_mode=WAL
- Enable foreign keys: PRAGMA foreign_keys=ON
- Set busy timeout: PRAGMA busy_timeout=5000
- These three PRAGMAs run on every new connection before any query

## Migration rules
- NEVER use drizzle-kit push in production -- generate + apply only
- NEVER drop a column -- add nullable column, backfill, then deprecate old column
- NEVER ALTER TABLE ... MODIFY COLUMN -- use table recreation (see pattern below)
- ALWAYS number migration files: 0001_create_users.sql, 0002_add_email_index.sql
- ALWAYS test migration forward AND backward (rollback SQL in same file as comment)
- Column rename = two migrations: (1) add new column + copy data, (2) drop old column after confirmed

## Driver selection
- Node.js embedded: better-sqlite3 (synchronous, fastest Node SQLite driver)
- Bun embedded: bun:sqlite (built-in, no install required)
- Turso / libSQL (local or remote): @libsql/client + drizzle-orm/libsql
- Cloudflare D1: D1 binding via Wrangler env, drizzle-orm/d1
- NEVER use node-sqlite3 (async, slower, requires native compilation)
- NEVER use sql.js (WASM, large bundle, wrong for server use)

## Single-writer constraint
- SQLite allows ONE writer at a time (WAL mode allows concurrent reads)
- For write-heavy workloads with multiple processes: use Turso or migrate to Postgres
- For single-process servers: WAL mode + busy_timeout handles concurrency safely
- NEVER generate code that assumes concurrent writes will succeed without retry logic

Four sections in this template prevent the most destructive Claude failures.

The dialect rules section is the most important. With explicit column type rules, Claude generates INTEGER NOT NULL CHECK(value IN (0, 1)) DEFAULT 0 instead of BOOLEAN NOT NULL DEFAULT FALSE on the first attempt, every time. The same applies to enum simulation via CHECK constraints and ISO 8601 date storage.

The WAL mode section prevents a common performance pitfall. Default SQLite uses DELETE journal mode, where every write creates a rollback journal file. WAL mode separates readers and the writer by appending changes to a .wal file. Without WAL mode, a single slow write blocks every read for its duration. Most tutorials skip this step; Claude skips it too without the rule.

The migration rules block the patterns that destroy data. DROP COLUMN without a compatibility check, MODIFY COLUMN (which does not exist in SQLite), and the drizzle-kit push workflow that applies changes without a SQL artifact are the three most common causes of production migration failures in SQLite projects.

The driver selection section prevents Claude from reaching for node-sqlite3 (legacy, asynchronous API, requires native compilation) or sql.js (WASM bundle, correct for browser SQLite, wrong for servers) out of training-data inertia.

Schema design with Drizzle and sqliteTable

Drizzle supports SQLite through drizzle-orm/better-sqlite3 (Node), drizzle-orm/libsql (Turso/edge), and drizzle-orm/d1 (Cloudflare D1). The schema API uses sqliteTable() instead of pgTable(). The column types available in the drizzle-orm/sqlite-core module map directly to SQLite's type affinity system.

Add a schema conventions block to CLAUDE.md:

## Schema conventions

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

### Column type mapping (SQLite affinity -> Drizzle)
- Boolean:   integer('active', { mode: 'boolean' }).notNull().default(false)
- Integer:   integer('count').notNull().default(0)
- Float:     real('score').notNull().default(0.0)
- String:    text('name').notNull()
- JSON:      text('metadata', { mode: 'json' }).notNull().$type<Record<string,unknown>>()
- Date:      integer('created_at', { mode: 'timestamp' }).notNull()
             (stores as Unix epoch seconds; use .defaultNow() for current time)
- Binary:    blob('data', { mode: 'buffer' })

### Standard table shape
export const users = sqliteTable('users', {
  id:        integer('id').primaryKey({ autoIncrement: false }),
  email:     text('email').notNull().unique(),
  name:      text('name'),
  role:      text('role', { enum: ['admin', 'member', 'viewer'] }).notNull().default('member'),
  active:    integer('active', { mode: 'boolean' }).notNull().default(true),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().defaultNow(),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().defaultNow(),
})

### Indexes
- Foreign key columns: always add an index
- High-cardinality filter columns: add an index
- Composite indexes for filter+sort patterns used in queries

A few column details are worth calling out because Drizzle's SQLite API has SQLite-specific behaviour.

The integer('col', { mode: 'boolean' }) pattern tells Drizzle to store the value as 0/1 in SQLite and return a JavaScript boolean from the client. This is the correct abstraction. Using text('col') and storing 'true'/'false' strings, which Claude sometimes generates, causes type confusion across query results.

The integer('col', { mode: 'timestamp' }) pattern stores dates as Unix epoch seconds. This is compact and sortable. The alternative is text('col', { mode: 'text' }) with ISO 8601 strings, which is more human-readable but less efficient for range queries. Pick one and enforce it in CLAUDE.md. Mixing epoch integers and ISO strings across tables produces broken ORDER BY created_at queries.

The { enum: [...] } option on a text() column provides TypeScript-level constraint. At the database level, Drizzle generates a CHECK constraint. This is the correct SQLite equivalent of a Postgres enum type. Claude generates this correctly when the role field in the example schema uses it; without the example, it tends to generate plain text() with no constraint.

For the Drizzle-specific migration and query patterns that apply across SQLite and Postgres, the Claude Code Drizzle guide covers the full workflow including relational queries, Core API vs Relational API choice, and migration strategy.

Migration strategy: no journal, table recreation

SQLite migration strategy differs from Postgres in one important way: there is no pg_dump-style schema diffing tool that understands the full range of DDL operations. ALTER TABLE in SQLite is limited. The consequence is that the migration pattern for non-additive changes (column type changes, column removals, constraint additions to existing columns) is table recreation.

The table recreation pattern:

-- 0003_change_users_role_type.sql
-- Forward migration: change role column from TEXT to TEXT with CHECK constraint
-- (SQLite cannot ADD CONSTRAINT to existing column; requires table recreation)

BEGIN;

-- 1. Create new table with correct schema
CREATE TABLE users_new (
  id        INTEGER PRIMARY KEY,
  email     TEXT    NOT NULL UNIQUE,
  name      TEXT,
  role      TEXT    NOT NULL CHECK(role IN ('admin', 'member', 'viewer')) DEFAULT 'member',
  active    INTEGER NOT NULL CHECK(active IN (0,1)) DEFAULT 1,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- 2. Copy data (map old role values to valid new values)
INSERT INTO users_new (id, email, name, role, active, created_at, updated_at)
SELECT
  id,
  email,
  name,
  CASE WHEN role IN ('admin','member','viewer') THEN role ELSE 'member' END,
  active,
  created_at,
  updated_at
FROM users;

-- 3. Drop old table
DROP TABLE users;

-- 4. Rename new table
ALTER TABLE users_new RENAME TO users;

-- 5. Recreate indexes
CREATE UNIQUE INDEX users_email_idx ON users(email);

COMMIT;

-- Rollback (append as comment, do not execute forward)
-- BEGIN;
-- CREATE TABLE users_old (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, ...);
-- INSERT INTO users_old SELECT * FROM users;
-- DROP TABLE users; ALTER TABLE users_old RENAME TO users;
-- COMMIT;

Add the table recreation pattern to your CLAUDE.md verbatim. Claude will generate it on demand for any schema change that SQLite cannot handle with a bare ALTER TABLE. Without the pattern, Claude generates ALTER TABLE ... MODIFY COLUMN or ALTER TABLE ... ADD CONSTRAINT, both of which fail.

The migration runner is a small script that reads files from src/db/migrations/, tracks applied migrations in a _migrations table, and applies pending files in order. better-sqlite3's synchronous API makes this straightforward:

// src/db/migrate.ts
import Database from 'better-sqlite3'
import { readdirSync, readFileSync } from 'node:fs'
import { join } from 'node:path'

const DB_PATH = process.env.DB_PATH ?? './data/app.db'
const MIGRATIONS_DIR = join(import.meta.dirname, 'migrations')

const db = new Database(DB_PATH)
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')
db.pragma('busy_timeout = 5000')

db.exec(`
  CREATE TABLE IF NOT EXISTS _migrations (
    id        INTEGER PRIMARY KEY,
    filename  TEXT    NOT NULL UNIQUE,
    applied_at INTEGER NOT NULL DEFAULT (unixepoch())
  )
`)

const applied = new Set(
  db.prepare('SELECT filename FROM _migrations').all().map((r: any) => r.filename)
)

const files = readdirSync(MIGRATIONS_DIR)
  .filter((f) => f.endsWith('.sql'))
  .sort()

for (const file of files) {
  if (applied.has(file)) continue
  console.log(`applying ${file}`)
  const sql = readFileSync(join(MIGRATIONS_DIR, file), 'utf8')
    .split('\n')
    .filter((line) => !line.trimStart().startsWith('--'))
    .join('\n')
  db.exec(sql)
  db.prepare('INSERT INTO _migrations (filename) VALUES (?)').run(file)
}

db.close()
console.log('migrations complete')

This runner strips -- comments before execution (which includes the rollback SQL at the bottom of each file) and tracks applied files by filename in _migrations. Claude generates this pattern correctly when the file and path are referenced in CLAUDE.md.

Driver selection: better-sqlite3, bun:sqlite, and libSQL

The three driver choices for SQLite in 2026 are different enough that putting the wrong one in a project wastes hours of debugging.

better-sqlite3 is the right choice for Node.js applications. It uses a synchronous API, which is unusual for a Node library but correct for SQLite because SQLite I/O is local disk access rather than network I/O. The synchronous API means you do not need to await every query, which simplifies transaction handling considerably. It is the fastest Node SQLite driver and the one Drizzle's drizzle-orm/better-sqlite3 adapter targets.

bun:sqlite is the right choice for Bun applications. It is built into the runtime, requires no installation, and has performance characteristics similar to better-sqlite3 with a slightly different API. The Claude Code Bun guide covers the broader Bun setup that pairs with this driver. The Drizzle adapter is drizzle-orm/bun-sqlite.

@libsql/client is the right choice for Turso, local libSQL files, or any environment where you want the same client interface against both a local file and a remote replicated database. The @libsql/client package supports three URL schemes: file:./app.db (local), libsql://your-db.turso.io (remote), and http://localhost:8080 (local Turso dev server). Switching between local dev and production Turso is an environment variable change with no code changes.

Add a driver setup block to CLAUDE.md:

## Driver setup

### Node.js (better-sqlite3)
import Database from 'better-sqlite3'
const db = new Database(process.env.DB_PATH ?? './data/app.db')
db.pragma('journal_mode = WAL')
db.pragma('foreign_keys = ON')
db.pragma('busy_timeout = 5000')

### Bun (bun:sqlite)
import { Database } from 'bun:sqlite'
const db = new Database(process.env.DB_PATH ?? './data/app.db', { create: true })
db.exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000')

### Turso / libSQL (any runtime)
import { createClient } from '@libsql/client'
const client = createClient({
  url:       process.env.TURSO_URL ?? 'file:./data/app.db',
  authToken: process.env.TURSO_AUTH_TOKEN,
})

### Drizzle wrapper (matches driver above)
-- Node:  import { drizzle } from 'drizzle-orm/better-sqlite3'
          const drizzleDb = drizzle(db, { schema })
-- Bun:   import { drizzle } from 'drizzle-orm/bun-sqlite'
          const drizzleDb = drizzle(db, { schema })
-- Turso: import { drizzle } from 'drizzle-orm/libsql'
          const drizzleDb = drizzle(client, { schema })

### NEVER use
- node-sqlite3 (legacy async API, slower, requires node-gyp)
- sql.js (WASM, correct for browser SQLite, wrong for server use)
- sqlite3 package (older async API, fewer active maintainers)

The environment variable switching pattern matters for the Turso case. file:./data/app.db uses the local libSQL file path scheme. libsql://your-db.turso.io is the Turso remote URL. Both are valid TURSO_URL values; the @libsql/client package handles both with identical query APIs. Claude generates this conditional URL pattern when both examples are present; without them, it sometimes generates two separate client implementations.

Turso and Cloudflare D1: SQLite at the edge

Turso and Cloudflare D1 are the two main paths for running SQLite in production at scale. They have different integration patterns that Claude needs to know about.

Turso runs libSQL with global read replicas. The @libsql/client package connects to a remote Turso database over HTTP/WebSockets. You get edge-close read latency via replicas and a single primary for writes. The development workflow uses a local libSQL file (file:./data/app.db) and a remote Turso database for staging/production. The turso CLI manages database creation, replicas, and auth tokens.

// src/db/client.ts (Turso production / local dev)
import { createClient } from '@libsql/client'
import { drizzle } from 'drizzle-orm/libsql'
import * as schema from './schema'

const client = createClient({
  url:       process.env.TURSO_URL!,       // file:./data/app.db (dev) | libsql://... (prod)
  authToken: process.env.TURSO_AUTH_TOKEN, // undefined for local file, required for remote
})

export const db = drizzle(client, { schema })

Cloudflare D1 is a SQLite-compatible database bound to a Cloudflare Worker via the wrangler.toml bindings system. The D1 client is the env.DB binding, not a separate npm package. Drizzle supports D1 via drizzle-orm/d1. The key difference from Turso is that D1 queries are always HTTP calls to Cloudflare's infrastructure; there is no in-process SQLite file.

// Worker entry (Cloudflare D1)
import { drizzle } from 'drizzle-orm/d1'
import * as schema from './schema'
import type { Env } from './types'

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const db = drizzle(env.DB, { schema })
    const users = await db.select().from(schema.users).all()
    return Response.json(users)
  },
}

The wrangler.toml binding:

[[d1_databases]]
binding = "DB"
database_name = "my-app"
database_id   = "your-d1-database-id"

Add D1-specific constraints to CLAUDE.md for Cloudflare projects:

## Cloudflare D1 constraints
- D1 is eventually consistent for cross-region reads (primary write, replica reads)
- D1 does NOT support batch transactions the same way as local SQLite -- use db.batch([])
- D1 queries are async HTTP calls -- await every query even if driver looks synchronous
- NEVER use better-sqlite3 or bun:sqlite in a Worker -- use env.DB binding only
- Running migrations against D1: wrangler d1 execute <database-name> --file=<migration.sql>
- Local D1 dev: wrangler dev --local (uses SQLite file in .wrangler/state/)

The Cloudflare Workers patterns that pair with D1 are covered in the Claude Code Cloudflare Workers guide.

WAL mode, transactions, and the single-writer model

WAL mode is the most impactful single configuration change for any SQLite application that handles more than one concurrent user. It deserves a full explanation rather than just a PRAGMA.

In the default DELETE journal mode, SQLite holds an exclusive lock for the entire duration of any write transaction. Every reader that arrives during that window blocks until the write completes. For a web server handling a mix of reads and writes, this produces visible latency spikes under any meaningful load.

WAL mode separates writers and readers through a write-ahead log file. Writers append changes to the .wal file rather than modifying the database file directly. Readers read from the database file directly and periodically from the WAL. The result: reads and a single write proceed concurrently. A reader never blocks a writer; a writer never blocks readers.

The constraint that WAL does not remove is single-writer. Only one process can write to a SQLite database at a time, even in WAL mode. For most single-server applications this is not a problem. For multi-process deployments (two Node worker processes, a web server plus a background job processor) it can become one. The PRAGMA busy_timeout=5000 setting makes writes wait up to 5000ms for the lock rather than returning SQLITE_BUSY immediately.

Add a transactions section to CLAUDE.md:

## Transactions (better-sqlite3)

### Read-only transaction (allows concurrent reads, no write lock)
const getUser = db.transaction((id: number) => {
  return db.prepare('SELECT * FROM users WHERE id = ?').get(id)
})

### Write transaction (acquires write lock for the duration)
const createUser = db.transaction((email: string, name: string) => {
  const stmt = db.prepare('INSERT INTO users (email, name) VALUES (?, ?) RETURNING id')
  return stmt.get(email, name) as { id: number }
})

### Nested transactions (savepoints)
const outer = db.transaction(() => {
  createUser('a@example.com', 'Alice')
  const inner = db.transaction(() => {
    createUser('b@example.com', 'Bob')
  })
  inner() -- nested call uses SAVEPOINT, not BEGIN
})

### Hard rules
- NEVER call db.run() for write-heavy operations outside a transaction -- WAL writes accumulate in .wal file and checkpoint at close
- ALWAYS use parameterised statements (prepare().get/run/all), never string concatenation
- ALWAYS check SQLITE_BUSY handling in long-running write transactions

better-sqlite3 makes write transactions idiomatic through the db.transaction() wrapper. The wrapped function runs inside BEGIN ... COMMIT automatically and retries on SQLITE_BUSY if busy_timeout is set. Claude generates the wrapper pattern when it is in CLAUDE.md and generates manual db.run('BEGIN') calls in older style when it is not.

When SQLite is the right choice and when it is not

Claude cannot make architectural decisions without context. A CLAUDE.md that explains when SQLite is right for this project helps Claude avoid suggesting migrations to Postgres mid-session.

Add an architecture decision block to CLAUDE.md:

## Architecture decision: SQLite in this project

### Why SQLite (confirmed fit)
- Single-process server with one writer -- WAL mode handles concurrent reads
- Embedded application or desktop tool -- SQLite is the standard choice
- Prototyping / local dev -- zero infrastructure, fast reset
- Edge deployment via Turso or D1 -- write to primary, read from replicas
- Read-heavy single-server SaaS -- Turso replicas handle read scale

### When to migrate to Postgres (not applicable to this project today)
- Multiple writer processes that cannot be serialised (separate servers writing to same dataset)
- Need for native JSON operators (jsonb), full-text search (tsvector), or custom types
- Write throughput exceeds SQLite's single-writer ceiling under sustained load
- Team already runs Postgres infrastructure and has no operational reason to add SQLite

### This project is NOT at the Postgres threshold -- do not suggest migrations

The last line is deliberately blunt. Without it, Claude occasionally suggests "you might want to consider Postgres for this use case" in response to basic schema design questions. The rule prevents that distraction when SQLite is a deliberate architectural choice.

Permission hooks for SQLite projects

SQLite projects have a small set of potentially destructive operations worth gating. Database file deletion, migration execution against production, and WAL checkpointing on a live database should require explicit confirmation.

In .claude/settings.local.json:

{
  "permissions": {
    "allow": [
      "Bash(node src/db/migrate.ts*)",
      "Bash(bun src/db/migrate.ts*)",
      "Bash(sqlite3 ./data/app.db .schema*)",
      "Bash(sqlite3 ./data/app.db .tables*)",
      "Bash(turso db shell*)"
    ],
    "deny": [
      "Bash(rm ./data/app.db*)",
      "Bash(rm -rf ./data/*)",
      "Bash(sqlite3 ./data/app.db DROP*)",
      "Bash(wrangler d1 execute * --file=* --env=production*)"
    ]
  }
}

The wrangler d1 execute ... --env=production deny is the most important. Running a migration against the production D1 database should require the operator to confirm. Claude running migrations automatically against a D1 production database is possible without this gate. For the broader permission hook patterns, the Claude Code permissions guide covers the approach across project types.

Hard rules and what to review manually

Claude Code generates reliable SQLite code in several areas when the CLAUDE.md above is in place. Schema definitions using Drizzle's sqliteTable() with correct column type mappings, the WAL mode PRAGMA block on database open, parameterised prepared statements, read-heavy query patterns, and the numbered migration file structure are all consistently correct.

Four areas warrant manual review regardless of CLAUDE.md coverage.

The first is the migration script against production. Review every migration SQL file before applying it to a live database. The table recreation pattern generates correctly, but the data migration step inside it is logic Claude produces from schema inference. Verify the CASE WHEN mapping, the column list on INSERT INTO ... SELECT, and the index recreations are complete and match the new schema.

The second is WAL checkpointing in long-running processes. SQLite checkpoints the WAL file to the main database file periodically. In production, a background process that writes frequently can accumulate a large WAL file if checkpointing is blocked by a long-running read. Monitor WAL file size and add a periodic PRAGMA wal_checkpoint(PASSIVE) in processes that write heavily. Claude does not generate checkpointing code without an explicit prompt.

The third is the busy timeout under load. PRAGMA busy_timeout=5000 is a good default for most applications. Under sustained write contention from multiple readers that occasionally write, 5000ms can produce visible latency. Tune this value based on the observed write patterns of your specific application. Claude reproduces whatever value is in CLAUDE.md; the value to use is a measurement decision, not a code decision.

The fourth is Turso sync lag for read replicas. Turso's read replicas are eventually consistent. A write to the primary is not immediately visible on a replica in a different region. For user-facing operations where a write and then a read happen in the same session, route both to the primary or implement a short read-your-own-writes window. Claude does not model replica lag without explicit guidance. For the Turso-specific patterns, the Claude Code Drizzle guide covers the libSQL adapter and edge deployment patterns in more detail.

Building on SQLite without Postgres habits

The SQLite CLAUDE.md in this guide produces an implementation where the dialect rules prevent Postgres-flavoured column types, WAL mode is enabled on every connection, the migration strategy handles SQLite's ALTER TABLE limitations through table recreation, the driver is matched to the runtime, and the single-writer constraint is acknowledged rather than ignored.

The underlying principle is the same as every other Claude Code framework integration: the model performs at the level of context you provide. Without SQLite-specific rules, Claude generates code that compiles, passes type checks, and fails at runtime on the first BOOLEAN column or ALTER TABLE ... DROP COLUMN on a 3.34 runtime. With the CLAUDE.md above, Claude treats SQLite as a first-class production database with its own conventions, not as a simplified Postgres.

For the mechanics of how CLAUDE.md is read and structured, CLAUDE.md explained covers rule precedence and sectioning. For the broader context of database choices in Claude Code projects, the Claude Code database guide covers the decision framework. Claudify includes a SQLite-specific CLAUDE.md template pre-configured for the dialect rules, WAL mode setup, migration patterns, and Turso/D1 edge integration covered in this guide.