Files
mtusi2026lab5/internal/database/migrate.go

65 lines
1.7 KiB
Go

package database
import (
"context"
"embed"
"fmt"
"io/fs"
"sort"
"strconv"
"strings"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
//go:embed migrations/*.sql
var migrations embed.FS
func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
tx, err := pool.BeginTx(ctx, pgx.TxOptions{})
if err != nil {
return err
}
defer tx.Rollback(ctx)
if _, err = tx.Exec(ctx, `SELECT pg_advisory_xact_lock(202602)`); err != nil {
return err
}
if _, err = tx.Exec(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (version integer PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT clock_timestamp())`); err != nil {
return err
}
entries, err := fs.ReadDir(migrations, "migrations")
if err != nil {
return 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
}
part := strings.SplitN(entry.Name(), "_", 2)[0]
version, parseErr := strconv.Atoi(part)
if parseErr != nil {
return fmt.Errorf("invalid migration name %s", entry.Name())
}
var applied bool
if err = tx.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE version=$1)`, version).Scan(&applied); err != nil {
return err
}
if applied {
continue
}
sqlBytes, readErr := migrations.ReadFile("migrations/" + entry.Name())
if readErr != nil {
return readErr
}
if _, err = tx.Exec(ctx, string(sqlBytes)); err != nil {
return fmt.Errorf("migration %s: %w", entry.Name(), err)
}
if _, err = tx.Exec(ctx, `INSERT INTO schema_migrations(version) VALUES($1)`, version); err != nil {
return err
}
}
return tx.Commit(ctx)
}