How to Set Up SQLite Database with Drizzle ORM for Persistence in Routa

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 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 at the repository root. This file instructs Drizzle-Kit how to generate migration files from your TypeScript schema.

// 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 using Drizzle's sqlite-core primitives. The schema uses sqliteTable, text, and integer types with SQLite-specific modes for JSON and timestamps.

// 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<Record<string, string>>().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 file exports getSqliteDatabase(), a lazy-initialized singleton that opens the database file and applies the schema.

// 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<typeof schema>;

const GLOBAL_KEY = "__routa_sqlite_db__";

export function getSqliteDatabase(dbPath?: string): SqliteDatabase {
  const globalObj = globalThis as Record<string, unknown>;

  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 wrap Drizzle queries to provide a clean API. These classes accept the database instance returned by getSqliteDatabase() and implement CRUD operations.

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 defines the schema path and output directory for generated migrations.
• Schema definitions in 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 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.

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. 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 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. 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}