# How to Set Up SQLite Database with Drizzle ORM for Persistence in Routa > Learn to set up SQLite database with Drizzle ORM in Routa. This guide covers Drizzle-Kit migrations, schema definitions, and runtime initialization for persistent data. - Repository: [Fengda Huang/routa](https://github.com/phodal/routa) - Tags: how-to-guide - Published: 2026-05-26 --- **Routa uses Drizzle ORM with the `better-sqlite3` driver to provide SQLite persistence through a three-layer setup: a Drizzle-Kit configuration file for migrations, a TypeScript schema definition using `sqliteTable`, and a runtime singleton in [`src/core/db/sqlite.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite.ts) that initializes the database and applies schema changes on first use.** Routa is an open-source multi-agent platform that leverages Drizzle ORM to abstract its persistence layer. When running the local Node.js backend, the framework automatically opts for SQLite via the `better-sqlite3` driver, providing a file-based database that requires no external server. This architecture allows developers to set up SQLite database with Drizzle ORM for persistence in Routa by configuring the schema, initializing the connection, and using type-safe store implementations. ## Configure Drizzle-Kit for SQLite Migrations The migration workflow starts with [`drizzle-sqlite.config.ts`](https://github.com/phodal/routa/blob/main/drizzle-sqlite.config.ts) at the repository root. This file instructs Drizzle-Kit how to generate migration files from your TypeScript schema. ```typescript // https://github.com/phodal/routa/blob/main/drizzle-sqlite.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ schema: "./src/core/db/sqlite-schema.ts", out: "./drizzle-sqlite", dialect: "sqlite", dbCredentials: { url: process.env.SQLITE_DB_PATH ?? "routa.db", }, }); ``` Running `npx drizzle-kit generate` creates idempotent migration files under `drizzle-sqlite/` using `CREATE TABLE IF NOT EXISTS` statements. ## Define the SQLite Schema All table definitions live in [`src/core/db/sqlite-schema.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite-schema.ts) using Drizzle's `sqlite-core` primitives. The schema uses `sqliteTable`, `text`, and `integer` types with SQLite-specific modes for JSON and timestamps. ```typescript // https://github.com/phodal/routa/blob/main/src/core/db/sqlite-schema.ts import { sqliteTable, text, integer, } from "drizzle-orm/sqlite-core"; export const workspaces = sqliteTable("workspaces", { id: text("id").primaryKey(), title: text("title").notNull(), status: text("status").notNull().default("active"), metadata: text("metadata", { mode: "json" }).$type>().default({}), createdAt: integer("created_at", { mode: "timestamp_ms" }).notNull().$defaultFn(() => new Date()), updatedAt: integer("updated_at", { mode: "timestamp_ms" }).notNull().$defaultFn(() => new Date()), }); ``` This file defines tables for agents, tasks, notes, messages, and worktrees according to the phodal/routa source code, ensuring TypeScript types remain synchronized with the database layout. ## Initialize the Runtime Database Connection The [`src/core/db/sqlite.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite.ts) file exports `getSqliteDatabase()`, a lazy-initialized singleton that opens the database file and applies the schema. ```typescript // https://github.com/phodal/routa/blob/main/src/core/db/sqlite.ts import { drizzle, BetterSQLite3Database } from "drizzle-orm/better-sqlite3"; import BetterSqlite3 from "better-sqlite3"; import * as schema from "./sqlite-schema"; export type SqliteDatabase = BetterSQLite3Database; const GLOBAL_KEY = "__routa_sqlite_db__"; export function getSqliteDatabase(dbPath?: string): SqliteDatabase { const globalObj = globalThis as Record; if (!globalObj[GLOBAL_KEY]) { const resolvedPath = dbPath ?? process.env.ROUTA_DB_PATH ?? "routa.db"; const sqlite = new BetterSqlite3(resolvedPath); sqlite.pragma("journal_mode = WAL"); sqlite.pragma("foreign_keys = ON"); const db = drizzle(sqlite, { schema }); initializeSqliteTables(db); globalObj[GLOBAL_KEY] = db; } return globalObj[GLOBAL_KEY] as SqliteDatabase; } ``` This implementation enables **Write-Ahead Logging (WAL)** for improved concurrent read performance and enforces foreign key constraints. The function also triggers `initializeSqliteTables()` to ensure all tables exist before returning the connection. For application startup, call `ensureSqliteDefaultWorkspace()` to seed a default workspace with `id: "default"` if none exists. ## Use Store Classes for Data Operations Domain-specific stores in [`src/core/db/sqlite-stores.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite-stores.ts) wrap Drizzle queries to provide a clean API. These classes accept the database instance returned by `getSqliteDatabase()` and implement CRUD operations. ```typescript import { getSqliteDatabase } from "@/core/db/sqlite"; import { SqliteTaskStore } from "@/core/db/sqlite-stores"; import { createTask } from "@/models/task"; async function createSampleTask() { const db = getSqliteDatabase(); const taskStore = new SqliteTaskStore(db); const task = createTask({ id: "task-1", title: "Implement SQLite persistence", objective: "Set up Drizzle ORM schema", workspaceId: "default", status: "PENDING", }); await taskStore.save(task); const tasks = await taskStore.listByWorkspace("default"); console.log("Persisted tasks:", tasks); } ``` The `SqliteTaskStore` and similar classes for other entities abstract the underlying SQL, allowing the rest of the application to remain agnostic to the SQLite dialect. ## Summary - **Drizzle-Kit configuration** in [`drizzle-sqlite.config.ts`](https://github.com/phodal/routa/blob/main/drizzle-sqlite.config.ts) defines the schema path and output directory for generated migrations. - **Schema definitions** in [`src/core/db/sqlite-schema.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite-schema.ts) use `sqliteTable` and SQLite-compatible column types to ensure type safety. - **Runtime initialization** via `getSqliteDatabase()` creates a global singleton that opens `routa.db`, enables WAL mode, and applies the schema lazily. - **Store implementations** in [`src/core/db/sqlite-stores.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite-stores.ts) provide high-level CRUD methods that wrap Drizzle query builders. ## Frequently Asked Questions ### What SQLite driver does Routa use for database persistence? Routa uses the `better-sqlite3` driver combined with Drizzle ORM's `drizzle-orm/better-sqlite3` package. This synchronous driver provides high performance for local development and is wrapped by the `getSqliteDatabase()` helper in [`src/core/db/sqlite.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite.ts). ### How does Routa handle database schema migrations? Routa uses Drizzle-Kit to generate migration files based on the TypeScript schema in [`src/core/db/sqlite-schema.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite-schema.ts). The runtime automatically applies these migrations through `initializeSqliteTables()`, which executes `CREATE TABLE IF NOT EXISTS` statements when the database connection is first established. ### Where is the SQLite database file stored in Routa? By default, the database is stored as `routa.db` in the project root. You can customize this location by setting the `SQLITE_DB_PATH` environment variable in [`drizzle-sqlite.config.ts`](https://github.com/phodal/routa/blob/main/drizzle-sqlite.config.ts) or the `ROUTA_DB_PATH` variable when calling `getSqliteDatabase()` at runtime. ### How do I close the SQLite connection in Routa? Use the `closeSqliteDatabase()` function exported from [`src/core/db/sqlite.ts`](https://github.com/phodal/routa/blob/main/src/core/db/sqlite.ts). This releases the file lock on the database, which is particularly important for test suites running on Windows where open file handles can prevent test cleanup.