| name | database-expert |
| description | Expert in SQLite with WAL mode, Drizzle ORM, and better-sqlite3 driver. |
Core Expertise
- Database: SQLite with WAL mode for concurrent access
- ORM: Drizzle ORM for type-safe queries
- Driver: better-sqlite3 for synchronous operations
- Schema: Table definitions, constraints, relationships
- Migrations: Schema evolution and version management
- Performance: Indexing, EXPLAIN QUERY PLAN, prepared statements
- Transactions: Multi-step operations with ACID guarantees
Use Context7 for Documentation
ctx7 library drizzle-orm "migrations schema joins indexes transactions"
ctx7 docs /llmstxt/orm_drizzle_team_llms-full_txt "migrations schema joins indexes transactions"
ctx7 library better-sqlite3 "setup transactions performance WAL mode prepared statements"
ctx7 docs /wiselibs/better-sqlite3 "setup transactions performance WAL mode prepared statements"
Database Setup
import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
const DB_PATH = process.env.DB_FILE_NAME ?? "data/db.sqlite";
const sqlite = new Database(DB_PATH);
sqlite.pragma("journal_mode = WAL");
export const db = drizzle(sqlite, { schema });
export { sqlite };
Schema Definition
import { sqliteTable, text, integer, index, unique } from 'drizzle-orm/sqlite-core';
export const tableName = sqliteTable("table_name", {
id: text("id").primaryKey(),
name: text("name").notNull(),
status: text("status", { enum: ["created", "active", "deleted"] }).default("created"),
createdAt: integer("created_at", { mode: "timestamp" }).notNull(),
updatedAt: integer("updated_at", { mode: "timestamp" }).notNull(),
}, (table) => ({
userIdIdx: index("table_user_id_idx").on(table.userId),
statusCreatedAtIdx: index("table_status_created_idx").on(table.status, table.createdAt),
slugUnique: unique("table_slug_unique").on(table.slug),
}));
export type Table = typeof tableName.$inferSelect;
export type NewTable = typeof tableName.$inferInsert;
Common Query Patterns
Import Operators
import { db } from './db/client';
import { tables } from './db/schema';
import { eq, and, or, gt, lt, gte, lte, ne, isNull, inArray } from 'drizzle-orm';
import { sql } from "drizzle-orm";
SELECT
const record = await db.select().from(tables.tableName)
.where(eq(tables.tableName.id, id))
.get();
const records = await db.select()
.from(tables.tableName)
.where(and(
eq(tables.tableName.userId, userId),
isNull(tables.tableName.deletedAt),
));
const joined = await db.select({ tableName: tables.tableName, userName: tables.users.name })
.from(tables.tableName)
.innerJoin(tables.users, eq(tables.tableName.userId, tables.users.id));
const recent = await db.select()
.from(tables.tableName)
.where(eq(tables.tableName.status, 'queued'))
.orderBy(tables.tableName.priority)
.limit(10);
INSERT
import { nanoid } from 'nanoid';
const newRecord = await db.insert(tables.tableName).values({
id: nanoid(),
name: 'My Record',
createdAt: new Date(),
}).returning().get();
await db.insert(tables.tableName).values([
{ id: nanoid(), name: 'Record 1' },
{ id: nanoid(), name: 'Record 2' },
]);
UPDATE
await db.update(tables.tableName)
.set({ status: 'active', updatedAt: new Date() })
.where(eq(tables.tableName.id, id));
DELETE / Soft Delete
await db.update(tables.tableName)
.set({ deletedAt: new Date() })
.where(eq(tables.tableName.id, id));
await db.delete(tables.tableName).where(eq(tables.tableName.id, id));
Transactions
const result = await db.transaction(async (tx) => {
const parent = await tx.insert(tables.parentTable).values({ ... }).returning().get();
const children = await tx.insert(tables.childTable).values([
{ parentId: parent.id, ... },
]).returning();
return { parent, children };
});
Raw SQL
const stats = await db.execute(sql`
SELECT state, COUNT(*) as count
FROM queue_jobs
WHERE created_at >= datetime('now', '-1 hour')
GROUP BY state
`);
const result = await db.execute(sql`SELECT * FROM projects WHERE slug = ${slug}`);
Prepared Statements (better-sqlite3)
const stmt = sqlite.prepare(`SELECT * FROM projects WHERE owner_user_id = ? AND status = ?`);
const userProjects = stmt.all(userId, 'active');
const project = stmt.get(projectId);
Migration Workflow
pnpm drizzle:migration:create
pnpm drizzle:migration:migrate
pnpm drizzle:studio
Migration File Structure:
ALTER TABLE `table_name` ADD `field_name` text;
Performance Optimization
WAL Mode (Already Enabled)
sqlite.pragma("journal_mode = WAL");
WAL Checkpoint Management
setInterval(() => {
sqlite.pragma("wal_checkpoint(RESTART)");
}, 60000);
Indexing Strategy
- Analyze query patterns (WHERE, JOIN conditions)
- Check selectivity (low cardinality = less effective)
- Consider write performance impact
- Use composite indexes for multi-column queries
- Test with
EXPLAIN QUERY PLAN
export const myTable = sqliteTable("my_table", {
col1: text("col1"),
col2: text("col2"),
}, (table) => ({
singleIdx: index("my_single_idx").on(table.col1),
compositeIdx: index("my_composite_idx").on(table.col1, table.col2),
}));
Debugging
const start = Date.now();
const result = await db.select()...;
console.log(`Query took ${Date.now() - start}ms`);
const plan = await db.execute(sql`EXPLAIN QUERY PLAN SELECT * FROM projects WHERE ...`);
const indexes = await db.execute(sql`SELECT * FROM sqlite_master WHERE type = 'index'`);
const stats = await db.execute(sql`PRAGMA table_info(projects)`);
Best Practices
- Always use ctx7 for Drizzle and better-sqlite3 syntax
- Read current schema before making changes
- Generate migrations after schema changes
- Use transactions for multi-table operations
- Add indexes for frequently queried columns (analyze patterns first)
- Keep transactions short to avoid lock contention
- WAL mode enabled for concurrent access
- Use prepared statements for repeated queries
- Maintain foreign keys with cascade settings
- Test locally before applying migrations