Database Migrations

Sprout uses Drizzle ORM with SQLite for data persistence. All schema definitions live in TypeScript, and migrations are automatically generated.

Overview

Drizzle provides a type-safe way to define database schemas and generates SQL migration files automatically. Key Files:

src/db/schema.ts - Schema definitions (source of truth)
drizzle.config.ts - Drizzle configuration
src/db/migrate.ts - Migration runner
drizzle/ - Generated migration SQL files
sprout.db - SQLite database file (created after first migration)

Initial Setup

After installing dependencies, initialize the database:

Ensure Configuration

Verify drizzle.config.ts exists:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "sqlite",
  dbCredentials: {
    url: "./sprout.db",
  },
});

Run Migrations

Apply the database schema:

cd sprout-backend
npm run db:migrate

Expected output:

Running migrations...
Migrations complete.

This creates sprout.db in the backend directory.

Verify Database

Check that the database was created:

ls -lh sprout.db
# Should show the database file

The default user is automatically seeded when you start the backend with npm run dev.

Database Schema

The schema is defined in src/db/schema.ts using Drizzle’s SQLite dialect.

Core Tables

users
branches
nodes
node_edges

Single-user application with a default user auto-seeded on startup.

export const users = sqliteTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  title: text("title"),
  desc: text("desc"),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
});

Topics created by users (e.g., “Linear Algebra”, “Fauna of Peru”).

export const branches = sqliteTable("branches", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  userId: text("user_id").notNull().references(() => users.id),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
});

Learning graph nodes: root, concept, or subconcept.

export const nodes = sqliteTable("nodes", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  type: text("type", { enum: ["root", "concept", "subconcept"] }).notNull(),
  branchId: text("branch_id").references(() => branches.id),
  parentId: text("parent_id").references(() => nodes.id),
  title: text("title").notNull(),
  desc: text("desc"),
  accuracyScore: real("accuracy_score").notNull().default(0),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
  updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
});

Prerequisite relationships between nodes (forms a DAG).

export const nodeEdges = sqliteTable("node_edges", {
  id: text("id").primaryKey(),
  sourceNodeId: text("source_node_id").notNull().references(() => nodes.id),
  targetNodeId: text("target_node_id").notNull().references(() => nodes.id),
  createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
});

Additional Tables

Table	Purpose
`topic_documents`	S3-stored documents uploaded for topics
`node_contents`	Explanations, visualizations, and learning cards
`assessments`	Diagnostic, quiz, and recall assessments
`questions`	MCQ and open-ended questions
`answers`	Student answers with scores and feedback
`user_node_progress`	Mastery scores and completion tracking
`chat_sessions`	Tutoring chat sessions
`chat_messages`	Chat history with roles and kinds
`hint_events`	Hint requests and responses

See src/db/schema.ts:1 for the complete schema with indexes and constraints.

Migration Commands

Drizzle provides three main commands for managing migrations:

db:migrate
db:generate
db:push

Apply Migrations

npm run db:migrate

Runs all pending migrations from the drizzle/ folder.When to use:

First time setup
After pulling schema changes from Git
After generating new migrations

Implementation (src/db/migrate.ts):

import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import { db } from "./index";
import path from "path";

const migrationsFolder = path.resolve(__dirname, "../../drizzle");

console.log("Running migrations...");
migrate(db, { migrationsFolder });
console.log("Migrations complete.");

Generate Migration Files

npm run db:generate

Generates SQL migration files in drizzle/ based on changes to src/db/schema.ts.When to use:

After modifying schema definitions
Before committing schema changes

Example output:

drizzle/0004_new_feature.sql
drizzle/meta/0004_snapshot.json

Always review generated SQL before applying migrations to ensure correctness.

Push Schema Directly

npm run db:push

Directly syncs the database with src/db/schema.ts without generating migration files.When to use:

Rapid prototyping
Local development experiments
NOT for production

db:push can lose data. Use db:generate + db:migrate for production workflows.

Migration Workflow

Recommended workflow for schema changes:

Modify Schema

Edit src/db/schema.ts with your changes:

// Add a new column
export const nodes = sqliteTable("nodes", {
  // ... existing columns
  difficulty: integer("difficulty").default(1),
});

Generate Migration

Create a migration file:

npm run db:generate

Review the generated SQL in drizzle/XXXX_*.sql:

ALTER TABLE nodes ADD COLUMN difficulty INTEGER DEFAULT 1;

Apply Migration

Run the migration:

npm run db:migrate

Commit Changes

Commit both the schema and migration files:

git add src/db/schema.ts drizzle/
git commit -m "Add difficulty column to nodes table"

Existing Migrations

Sprout ships with four migrations:

drizzle/
├── 0000_milky_wilson_fisk.sql          # Initial schema
├── 0001_nosy_gunslinger.sql            # Schema updates
├── 0002_flippant_colonel_america.sql   # More updates
├── 0003_little_rawhide_kid.sql         # Latest updates
└── meta/
    ├── 0000_snapshot.json
    ├── 0001_snapshot.json
    ├── 0002_snapshot.json
    ├── 0003_snapshot.json
    └── _journal.json

Drizzle auto-generates migration names. The meta folder contains schema snapshots for diffing.

Database Location

The database file location is configured via environment variable:

.env

DB_PATH=./sprout.db        # Default: in backend root
DB_PATH=/data/sprout.db    # Custom location

Configuration (drizzle.config.ts):

dbCredentials: {
  url: "./sprout.db",  // Hardcoded, but can read from env
}

Inspecting the Database

Use SQLite tools to inspect the database:

sqlite3 sprout.db

# List tables
.tables

# Describe schema
.schema nodes

# Query data
SELECT * FROM users;

# Exit
.quit

Common Operations

Reset database

Delete the database file and re-run migrations:

cd sprout-backend
rm sprout.db
npm run db:migrate

This deletes all data. Use only in development.

Rollback migration

Drizzle doesn’t support automatic rollbacks. Manually revert:

Delete the migration file from drizzle/
Edit drizzle/meta/_journal.json to remove the entry
Run the SQL DOWN migration manually (you need to write it)

Or reset the database (see above).

Seed test data

The default user is auto-seeded on backend startup (src/index.ts:17):

const DEFAULT_USER_ID = "00000000-0000-0000-0000-000000000000";

async function ensureDefaultUser() {
  const existing = await db.select().from(users)
    .where(eq(users.id, DEFAULT_USER_ID));
  if (!existing.length) {
    await db.insert(users).values({
      id: DEFAULT_USER_ID,
      email: "[email protected]",
      title: "Default Learner",
    });
  }
}

For additional test data, add a seed script or use Drizzle Studio.

Backup database

SQLite databases are single files - just copy:

cp sprout.db sprout.db.backup

# Or with timestamp
cp sprout.db "sprout.db.$(date +%Y%m%d_%H%M%S)"

Troubleshooting

Migration fails: table already exists

The database schema is out of sync with migrations.Solution:

Backup your data
Reset the database: rm sprout.db
Re-run migrations: npm run db:migrate

Drizzle can't find schema

Error: Cannot find module './src/db/schema.ts'Solution: Check drizzle.config.ts schema path matches your directory structure.

Database is locked

Error: SQLITE_BUSY: database is lockedSolution:

Close any SQLite browser tools
Ensure only one backend instance is running
Check for abandoned connections

Generated migration looks wrong

Drizzle generated unexpected SQL.Solution:

Review the generated SQL in drizzle/XXXX_*.sql
If incorrect, delete the migration and fix your schema
Re-generate with npm run db:generate
Manually edit the SQL if needed (not recommended)

Next Steps

Schema Reference

Learn more about Drizzle schema syntax

Document Uploads

Configure S3 for document storage

Setup

Guides

Database Migrations

Overview

Initial Setup

Database Schema

Core Tables

Additional Tables

Migration Commands

Migration Workflow

Existing Migrations

Database Location

Inspecting the Database

Common Operations

Troubleshooting

Next Steps

Schema Reference

Document Uploads

Build docs developers (and LLMs) love

Setup

Guides

​Overview

​Initial Setup

​Database Schema

​Core Tables

​Additional Tables

​Migration Commands

​Migration Workflow

​Existing Migrations

​Database Location

​Inspecting the Database

​Common Operations

​Troubleshooting

​Next Steps

Schema Reference

Document Uploads

Build docs developers (and LLMs) love

Overview

Initial Setup

Database Schema

Core Tables

Additional Tables

Migration Commands

Migration Workflow

Existing Migrations

Database Location

Inspecting the Database

Common Operations

Troubleshooting

Next Steps