Declarative SQLite with sqlite-schema-diff

If you’ve ever built a small project or a backend tool, you’ve probably used SQLite. It’s incredibly fast, heavily tested, and completely self-contained.

But dealing with database migrations? That is still a pain.

Tools like goose or golang-migrate are amazing for production-grade, multi-developer environments. But for a solo project or a small microservice, writing sequential UP and DOWN migrations just to add a single column feels like massive overkill.

Stripe solved this for Postgres with pg-schema-diff, allowing developers to simply declare the desired end-state of the database, and the tool figures out how to get there.

I wanted exactly that, but for SQLite.

The solution: sqlite-schema-diff

sqlite-schema-diff is a CLI tool and Go library that brings declarative schema management to SQLite.

Instead of writing sequential migration files, you just maintain your ideal schema in standard .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 change this file, sqlite-schema-diff compares it against your actual .db file and automatically generates and applies the exact ALTER TABLE, CREATE INDEX, or DROP statements required to sync them.

Building it the smart way

Writing a SQL parser from scratch is a terrible idea. It’s prone to edge cases and subtle bugs.

So, I didn’t.

Instead, the tool leverages SQLite’s own native parser logic. This keeps sqlite-schema-diff incredibly lightweight and bulletproof because it literally relies on SQLite to understand SQLite. It doesn’t need massive amounts of custom logic to figure out what a table should look like.

Handling the danger zone

The inherent disadvantage of a diff-based approach is that changes can drop data if you aren’t careful (like accidentally renaming a column and the tool interpreting it as a DROP and ADD).

To mitigate this, I spent a lot of time writing tests for edge cases to ensure it’s smart enough to prevent accidental data loss during common operations.

More importantly, it generates a local backup of your database by default before applying any changes. It is built to be reliable enough for production, provided you have a sane backup strategy in place.

How to use it

You can use it as a standalone CLI tool in your CI/CD pipelines, or directly embed it into your Go applications.

CLI Usage

Install it:

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

Preview the changes without breaking anything:

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

Apply them:

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

Go Library Usage

If you’re writing a Go backend, you can embed the migrations directly into your application:

import (
    "database/sql"
    "log"

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

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

func main() {
    db, _ := sql.Open("sqlite", "app.db")
    defer db.Close()

    // Embedded schema files
    parser.SetBaseFS(schemaFS)

    // Automatically migrate to match the ./schema directory
    // A backup is created automatically if changes are detected
    err := diff.Apply(db, "schemas", diff.ApplyOptions{});
    if err != nil {
        log.Fatal(err)
    }
}

If you’re tired of writing UP and DOWN scripts for your weekend projects or small apps, give it a try. It drastically simplifies database handling and gets out of your way.

Check it out on GitHub →