One of the first things you run into trying to build with SQLite in Durable Objects is handling SQL migrations.

I’ve been using Drizzle to manage my Durable Object schemas, which has worked well, but felt that it was a bit heavier than what I needed for my current project. Cloudflare has recently released the @cloudflare/actors library which has a much simpler approach (originally based on Lambros’ durable-utils).

With this library you can define your migrations in-line and run them yourself before you access your SQL tables.

import { Storage } from "@cloudflare/actors/storage";

export class ChatRoom extends DurableObject<Env> {
  storage: Storage;

  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.storage = new Storage(ctx.storage);
    this.storage.migrations = [
      {
        idMonotonicInc: 1,
        description: "Create users table",
        sql: "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY)",
      },
    ];
  }
  async fetch(request: Request): Promise<Response> {
    // Run migrations before executing SQL query
    await this.storage.runMigrations();

    // Query with SQL template
    let userId = new URL(request.url).searchParams.get("userId");
    const query = this.storage.sql`SELECT * FROM users WHERE id = ${userId};`;
    return new Response(`${JSON.stringify(query)}`);
  }
}

I appreciate the simplicity of this approach, however once you have more than a few migrations, it can be difficult to keep track of what your current schema even is. I don’t want to re-play SQL statements in my head to understand how to query what I need.

I worked around this by extracting the migrations to a separate file, which I can import in the Durable Object directly:

export default [
  {
    idMonotonicInc: 1,
    description: "Create exports table",
    sql: `
      CREATE TABLE IF NOT EXISTS example (
        id TEXT PRIMARY KEY,
        created_at INTEGER NOT NULL DEFAULT (unixepoch()),
        name TEXT NOT NULL
      );
    `,
  },
  {
    idMonotonicInc: 2,
    description: "Add description column to example table",
    sql: `ALTER TABLE example ADD COLUMN description TEXT NOT NULL DEFAULT 'default';`,
  },
  {
    idMonotonicInc: 3,
    description: "Add age column to example table",
    sql: `ALTER TABLE example ADD COLUMN age INTEGER NOT NULL DEFAULT 0;`,
  },
];

import { Storage } from "@cloudflare/actors/storage";
import migrations from "./example-migrations";

export class Example extends DurableObject {
  storage: Storage;
  env: Env;

  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.env = env;
    this.storage = new Storage(ctx.storage);
    this.storage.migrations = migrations;
  }

However, since the database in the Durable Object is “just” SQLite, and our migrations module contains a plain JavaScript object we can write a script to run them against an empty SQLite database and dump the resulting schema. Any time we add a new migration we can re-run this and check the output in as documentation.

import sqlite3 from "sqlite3";
import { open } from "sqlite";
import fs from "node:fs";
import sqlFormatter from "@sqltools/formatter";

async function getSchema(migrationsPath, schemaWritePath) {
  let migrations = await import(migrationsPath).then((m) => m.default);

  let dbPath = `/tmp/test-${Math.random().toString(36).slice(2)}.db`;

  let db = await open({
    filename: dbPath,
    driver: sqlite3.Database,
  });

  for (const migration of migrations) {
    await db.exec(migration.sql);
  }

  let schema = await db.all(`
    SELECT sql 
    FROM sqlite_master 
    ORDER BY type DESC, name
  `);

  let out = `-- This file was generated by a script
-- DO NOT EDIT THIS FILE MANUALLY
`;

  for (let item of schema) {
    if (item.sql) {
      out += "\n" + sqlFormatter.format(item.sql) + ";\n";
    }
  }

  await db.close();

  fs.rmSync(dbPath);

  fs.writeFileSync(schemaWritePath, out);
}

// A list of each of the Durable Objects with migrations to document
let DurableObjects = [
  {
    migrations: "path/to/example-migrations.ts",
    schema: "path/to/example-schema.sql",
  },
];

for (let durableObject of DurableObjects) {
  await getSchema(durableObject.migrations, durableObject.schema);
}

Now instead of trying to play back all of the migration SQL commands in my head, I have a nice up-to-date schema to reference while I’m working on my logic.

-- This file was generated by a script
-- DO NOT EDIT THIS FILE MANUALLY

CREATE TABLE example (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT 'default',
  age INTEGER NOT NULL DEFAULT 0
);