package main import ( "context" "embed" "fmt" "io/fs" "sort" "github.com/jmoiron/sqlx" ) //go:embed migrations/*.sql var migrationFS embed.FS func initDB(ctx context.Context, db *sqlx.DB) error { // Make sure that we have a table for the migrations tx, err := db.BeginTxx(ctx, nil) if err != nil { return fmt.Errorf("creating tx: %w", err) } defer func() { if err != nil { tx.Rollback() return } tx.Commit() }() _, err = tx.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS migrations (name string)`) if err != nil { return fmt.Errorf("creating migrations table: %w", err) } _, err = tx.ExecContext(ctx, `CREATE UNIQUE INDEX IF NOT EXISTS migrations_name ON migrations (name)`) if err != nil { return fmt.Errorf("creating migrations index: %w", err) } entries, err := migrationFS.ReadDir("migrations") if err != nil { return fmt.Errorf("reading embedded migration FS: %w", err) } sort.Slice(entries, func(i, j int) bool { return entries[i].Name() < entries[j].Name() }) for _, e := range entries { // Check if we need to apply that migration var count int err = tx.GetContext(ctx, &count, `SELECT count(*) FROM migrations WHERE name = ?1`, e.Name()) if err != nil { return fmt.Errorf("checking for migration %q: %w", e.Name(), err) } if count > 1 { return fmt.Errorf("migration %q applied more than once", e.Name()) } if count == 1 { continue } content, err := fs.ReadFile(migrationFS, "migrations/"+e.Name()) if err != nil { return fmt.Errorf("reading migration %q: %w", e.Name(), err) } _, err = tx.ExecContext(ctx, string(content)) if err != nil { return fmt.Errorf("applying migration %q: %w", e.Name(), err) } // Mark migration as applied _, err = tx.ExecContext(ctx, `INSERT INTO migrations (name) VALUES (?1)`, e.Name()) if err != nil { return fmt.Errorf("marking migration %q as applied: %w", e.Name(), err) } } return nil }