go-sqlite-migration-schema-drift

Answer

Two independent defects produced the register 500. Both fixes mirror what the integration tests already proved.

1. Schema drift — migration now creates the full 18-column read model

The migration created only 8 columns, but the repository SELECTs 18. Any register-time INSERT/SELECT touching a missing column fails (table accounts has no column named public_id) → 500. The migration must create exactly the read-model projection, with timestamps declared DATETIME so modernc.org/sqlite scans them into time.Time:

-- migrations/0001_accounts.sql (FIXED — mirrors integration-test schema, 18 cols)
CREATE TABLE IF NOT EXISTS accounts (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    public_id      TEXT NOT NULL UNIQUE,
    email          TEXT NOT NULL UNIQUE,
    username       TEXT NOT NULL UNIQUE,
    password_hash  TEXT NOT NULL,
    display_name   TEXT NOT NULL DEFAULT '',
    bio            TEXT NOT NULL DEFAULT '',
    avatar_url     TEXT NOT NULL DEFAULT '',
    locale         TEXT NOT NULL DEFAULT 'en',
    timezone       TEXT NOT NULL DEFAULT 'UTC',
    status         TEXT NOT NULL DEFAULT 'active',
    email_verified INTEGER NOT NULL DEFAULT 0,
    mfa_enabled    INTEGER NOT NULL DEFAULT 0,
    role           TEXT NOT NULL DEFAULT 'user',
    last_login_at  DATETIME,          -- NULLABLE → *time.Time
    created_at     DATETIME NOT NULL, -- NOT NULL → time.Time
    updated_at     DATETIME NOT NULL,
    deleted_at     DATETIME
);

The repository read model (the SELECT that must stay in sync with the migration):

const readModelCols = `id, public_id, email, username, password_hash,
    display_name, bio, avatar_url, locale, timezone, status,
    email_verified, mfa_enabled, role, last_login_at, created_at,
    updated_at, deleted_at`

type Account struct {
    ID            int64
    PublicID      string
    Email         string
    Username      string
    PasswordHash  string
    DisplayName   string
    Bio           string
    AvatarURL     string
    Locale        string
    Timezone      string
    Status        string
    EmailVerified bool
    MFAEnabled    bool
    Role          string
    LastLoginAt   *time.Time
    CreatedAt     time.Time
    UpdatedAt     time.Time
    DeletedAt     *time.Time
}

// register: INSERT the 18 columns, then re-SELECT through the read model.
row := db.QueryRowContext(ctx, `SELECT `+readModelCols+` FROM accounts WHERE email = ?`, email)
err = row.Scan(&a.ID, &a.PublicID, &a.Email, &a.Username, &a.PasswordHash,
    &a.DisplayName, &a.Bio, &a.AvatarURL, &a.Locale, &a.Timezone, &a.Status,
    &a.EmailVerified, &a.MFAEnabled, &a.Role, &a.LastLoginAt, &a.CreatedAt,
    &a.UpdatedAt, &a.DeletedAt)

Why DATETIME, not INTEGER: with modernc.org/sqlite, scanning an INTEGER (epoch) column into time.Time errors with unsupported Scan, storing driver.Value type int64 into type *time.Time. Declared DATETIME columns round-trip correctly.

2. CLI — strip flag names and values before positional parsing

The old code naively filtered out tokens starting with -, leaving flag values in the positional list: migrate --target 4 silently applied migration version 4. The fix passes only fs.Args() (the positional leftovers) to the subcommand:

// cli.go
func migrateCmd(db *sql.DB, rawArgs []string) ([]int, error) {
    fs := flag.NewFlagSet("migrate", flag.ContinueOnError)
    dryRun  := fs.Bool("dry-run", false, "print SQL without executing")
    target  := fs.Int("target", 0, "migrate up to this version")
    parallel := fs.Int("parallel", 1, "parallel worker count")
    if err := fs.Parse(rawArgs); err != nil {
        return nil, fmt.Errorf("parse flags: %w", err)
    }

    // FIX: subcommand receives fs.Args(), never rawArgs.
    var applied []int
    if err := runMigrate(context.Background(), db, fs.Args(), &applied); err != nil {
        return nil, err
    }
    return applied, nil
}

// runMigrate only ever sees positionals; it rejects anything non-numeric.
func runMigrate(ctx context.Context, db *sql.DB, versions []string, applied *[]int) error {
    if len(versions) == 0 {
        return nil
    }
    for _, v := range versions {
        n, err := strconv.Atoi(v)
        if err != nil {
            return fmt.Errorf("invalid migration version %q (flag names/values must be stripped before positional parsing)", v)
        }
        if n < 0 {
            return fmt.Errorf("invalid migration version %q: must be >= 0", v)
        }
        *applied = append(*applied, n)
    }
    return nil
}