Reading · glorifiedgluer · RSS Amplifier

On the last post I talked about using SQLite's user_version pragma to keep track of your schema version without the need of a separate table. However, how can we do schema migrations and use it in practice?

One of the most elegant schema migration approaches I have found was that of Miniflux.

First you need all your migrations on a single list of transactions:

const schemaVersion = len(migrations)
// Order is important. Add new migrations at the end of the list.
var migrations = [...]func(tx *sql.Tx) error{
    func(tx *sql.Tx) error {
        sql := `
            CREATE TABLE accounts (
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                PRIMARY KEY (username)
            );
        `
        _, err := tx.Exec(sql)
        return err
    },
}

Then you have to iterate and execute those statements:

func migrate(db *sql.DB) error {
    var currentVersion int
    db.QueryRow(`PRAGMA user_version;`).Scan(&currentVersion)
    slog.Info("Running database migrations",
        slog.Int("current_version", currentVersion),
        slog.Int("latest_version", schemaVersion),
    )
    for version := currentVersion; version < schemaVersion; version++ {
        newVersion := version + 1
        tx, err := db.Begin()
        if err != nil {
            return fmt.Errorf("[Migration v%d] %v", newVersion, err)
        }
        if err := migrations[version](tx); err != nil {
            tx.Rollback()
            return fmt.Errorf("[Migration v%d] %v", newVersion, err)
        }
        // PRAGMA is not an expression, so we can't pass a parameter to it
        cmd := fmt.Sprintf(`PRAGMA user_version = %d;`, newVersion)
        if _, err := tx.Exec(cmd); err != nil {
            tx.Rollback()
            return fmt.Errorf("[Migration v%d] %v", newVersion, err)
        }
        if err := tx.Commit(); err != nil {
            return fmt.Errorf("[Migration v%d] %v", newVersion, err)
        }
    }
    return nil
}

Simple SQL schema migrations with Go

Comments