// Expert in SQLite with WAL mode, Drizzle ORM, and better-sqlite3 driver.
Expert in Astro v5, Drizzle ORM, Docker Compose, OpenCode SDK v2, queue systems, and backend architecture.
Expert in Astro v5, React, shadcn/ui, and Tailwind CSS with semantic color tokens.
Use this agent for the Opencode integration. Our app should basically be like their web UI, interacting with the opencode server and sdk.
Debug this app using agent-browse on a production environment
Debug this app using agent-browse
| name | database-expert |
| description | Expert in SQLite with WAL mode, Drizzle ORM, and better-sqlite3 driver. |
# Resolve library IDs, then fetch docs
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"
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);
// Enable WAL mode for concurrent access
sqlite.pragma("journal_mode = WAL");
export const db = drizzle(sqlite, { schema });
export { sqlite }; // Use for performance, pragmas, prepared statements
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;
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";
// Single record
const record = await db.select().from(tables.tableName)
.where(eq(tables.tableName.id, id))
.get();
// Multiple with complex filter
const records = await db.select()
.from(tables.tableName)
.where(and(
eq(tables.tableName.userId, userId),
isNull(tables.tableName.deletedAt),
));
// With join
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));
// Order and limit
const recent = await db.select()
.from(tables.tableName)
.where(eq(tables.tableName.status, 'queued'))
.orderBy(tables.tableName.priority)
.limit(10);
import { nanoid } from 'nanoid';
const newRecord = await db.insert(tables.tableName).values({
id: nanoid(),
name: 'My Record',
createdAt: new Date(),
}).returning().get();
// Batch insert
await db.insert(tables.tableName).values([
{ id: nanoid(), name: 'Record 1' },
{ id: nanoid(), name: 'Record 2' },
]);
await db.update(tables.tableName)
.set({ status: 'active', updatedAt: new Date() })
.where(eq(tables.tableName.id, id));
// Soft delete
await db.update(tables.tableName)
.set({ deletedAt: new Date() })
.where(eq(tables.tableName.id, id));
// Hard delete
await db.delete(tables.tableName).where(eq(tables.tableName.id, id));
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 };
}); // Auto-commits on success, rolls back on error
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}`);
const stmt = sqlite.prepare(`SELECT * FROM projects WHERE owner_user_id = ? AND status = ?`);
const userProjects = stmt.all(userId, 'active');
const project = stmt.get(projectId);
# Generate migration from schema changes
pnpm drizzle:migration:create
# Apply pending migrations
pnpm drizzle:migration:migrate
# Launch Drizzle Studio
pnpm drizzle:studio
Migration File Structure:
-- drizzle/0003_add_field.sql
ALTER TABLE `table_name` ADD `field_name` text;--> statement-breakpoint
sqlite.pragma("journal_mode = WAL");
setInterval(() => {
sqlite.pragma("wal_checkpoint(RESTART)");
}, 60000); // Every 60s
EXPLAIN QUERY PLANexport 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),
}));
// Check query execution time
const start = Date.now();
const result = await db.select()...;
console.log(`Query took ${Date.now() - start}ms`);
// Check if index is used
const plan = await db.execute(sql`EXPLAIN QUERY PLAN SELECT * FROM projects WHERE ...`);
// List indexes
const indexes = await db.execute(sql`SELECT * FROM sqlite_master WHERE type = 'index'`);
// Check table info
const stats = await db.execute(sql`PRAGMA table_info(projects)`);