96 lines
2.6 KiB
Go
96 lines
2.6 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"embed"
|
|
"fmt"
|
|
"io/fs"
|
|
"sort"
|
|
"strconv"
|
|
"strings"
|
|
"time"
|
|
)
|
|
|
|
//go:embed migrations/*.sql
|
|
var migrationFiles embed.FS
|
|
|
|
// Migrate applies each embedded numbered migration exactly once.
|
|
func Migrate(ctx context.Context, db *sql.DB) error {
|
|
if _, err := db.ExecContext(ctx, `
|
|
CREATE TABLE IF NOT EXISTS schema_migrations (
|
|
version INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
applied_at TEXT NOT NULL
|
|
)
|
|
`); err != nil {
|
|
return fmt.Errorf("create schema_migrations: %w", err)
|
|
}
|
|
|
|
entries, err := fs.ReadDir(migrationFiles, "migrations")
|
|
if err != nil {
|
|
return fmt.Errorf("read embedded migrations: %w", err)
|
|
}
|
|
sort.Slice(entries, func(i, j int) bool { return entries[i].Name() < entries[j].Name() })
|
|
for _, entry := range entries {
|
|
if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") {
|
|
continue
|
|
}
|
|
versionText, _, ok := strings.Cut(entry.Name(), "_")
|
|
if !ok {
|
|
return fmt.Errorf("migration %q lacks numeric prefix", entry.Name())
|
|
}
|
|
version, err := strconv.Atoi(versionText)
|
|
if err != nil {
|
|
return fmt.Errorf("migration %q has invalid version: %w", entry.Name(), err)
|
|
}
|
|
applied, err := migrationApplied(ctx, db, version)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if applied {
|
|
continue
|
|
}
|
|
body, err := fs.ReadFile(migrationFiles, "migrations/"+entry.Name())
|
|
if err != nil {
|
|
return fmt.Errorf("read migration %q: %w", entry.Name(), err)
|
|
}
|
|
if err := applyMigration(ctx, db, version, entry.Name(), string(body)); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func migrationApplied(ctx context.Context, db *sql.DB, version int) (bool, error) {
|
|
var count int
|
|
if err := db.QueryRowContext(ctx, `SELECT COUNT(*) FROM schema_migrations WHERE version = ?`, version).Scan(&count); err != nil {
|
|
return false, fmt.Errorf("query migration %d: %w", version, err)
|
|
}
|
|
return count != 0, nil
|
|
}
|
|
|
|
func applyMigration(ctx context.Context, db *sql.DB, version int, name string, body string) error {
|
|
tx, err := db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return fmt.Errorf("begin migration %q: %w", name, err)
|
|
}
|
|
defer tx.Rollback()
|
|
if _, err := tx.ExecContext(ctx, body); err != nil {
|
|
return fmt.Errorf("apply migration %q: %w", name, err)
|
|
}
|
|
if _, err := tx.ExecContext(
|
|
ctx,
|
|
`INSERT INTO schema_migrations(version, name, applied_at) VALUES(?, ?, ?)`,
|
|
version,
|
|
name,
|
|
time.Now().UTC().Format(time.RFC3339Nano),
|
|
); err != nil {
|
|
return fmt.Errorf("record migration %q: %w", name, err)
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return fmt.Errorf("commit migration %q: %w", name, err)
|
|
}
|
|
return nil
|
|
}
|