Declarative SQLite with sqlite-schema-diff

Most of my small projects end up on SQLite. It’s fast, heavily tested, and self-contained, so it’s the obvious choice for a backend tool that runs on one box.

The part I never liked was migrations. Tools like goose or golang-migrate make sense for a multi-developer codebase, but for a solo project or a small microservice, writing sequential UP and DOWN migrations to add a column is a lot of ceremony.

Stripe has pg-schema-diff for Postgres: you declare the end state you want, and the tool works out how to get there. I wanted that for SQLite. sqlite-schema-diff is my take on it.

What it does

You keep your desired schema in plain .sql files:

-- schema/users.sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

When you edit that file, the tool diffs it against your actual .db file and generates the ALTER TABLE, CREATE INDEX, or DROP statements needed to sync them, then applies them.

Install it, preview the changes, apply them:

go install github.com/mizuchilabs/sqlite-schema-diff@latest

sqlite-schema-diff diff --database app.db --schema ./schema
sqlite-schema-diff apply --database app.db --schema ./schema

Why not write a SQL parser

Writing a SQL parser from scratch is a bad idea. Too many edge cases, too many subtle bugs.

So I didn’t. The tool uses SQLite’s own native parser logic. SQLite understands SQLite, and that means fewer places for my code to get a table definition wrong.

The danger zone

This is the part you should read twice. A diff-based approach can drop data.

In a diff, a renamed column looks exactly like one column dropped and another added. When type and constraints match, the tool guesses a rename and keeps the data. It is still a guess, so it flags the change as destructive and waits for a yes. The CLI makes a backup before it applies anything. Read the prompt anyway.

Embedding it in Go

For a Go backend, embed the schema files and call diff.Apply:

import (
    "context"
    "database/sql"
    "embed"
    "io/fs"
    "log"

    "github.com/mizuchilabs/sqlite-schema-diff/pkg/diff"
    _ "modernc.org/sqlite"
)

//go:embed schema/*.sql
var schemaFS embed.FS

func main() {
    db, err := sql.Open("sqlite", "app.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    schema, err := fs.Sub(schemaFS, "schema")
    if err != nil {
        log.Fatal(err)
    }

    // Migrate app.db to match the embedded schema, with a backup first
    _, err = diff.Apply(context.Background(), db, schema, diff.ApplyOptions{
        BackupPath: "app.db.backup",
    })
    if err != nil {
        log.Fatal(err)
    }
}

I stopped writing UP and DOWN scripts for my weekend projects after building this.

sqlite-schema-diff on GitHub →