package database import ( "context" "crypto/sha256" "encoding/hex" "errors" "fmt" "io/fs" "path/filepath" "regexp" "sort" "strconv" "time" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgxpool" ) const ( migrationLockID int64 = 0x4d54555349324c42 migrationUnlockTimeout = 5 * time.Second ) var migrationNamePattern = regexp.MustCompile(`^(\d+)_([a-z0-9_]+)\.sql$`) type migration struct { version int64 name string checksum string sql string } func applyMigrations(ctx context.Context, pool *pgxpool.Pool, migrationFS fs.FS) (returnErr error) { migrations, err := readMigrations(migrationFS) if err != nil { return err } connection, err := pool.Acquire(ctx) if err != nil { return fmt.Errorf("acquire connection for migrations: %w", err) } defer connection.Release() if _, err := connection.Exec(ctx, "SELECT pg_advisory_lock($1)", migrationLockID); err != nil { return fmt.Errorf("lock migrations: %w", err) } defer func() { unlockCtx, cancel := context.WithTimeout(context.Background(), migrationUnlockTimeout) defer cancel() if _, err := connection.Exec(unlockCtx, "SELECT pg_advisory_unlock($1)", migrationLockID); err != nil { _ = connection.Conn().Close(unlockCtx) if returnErr == nil { returnErr = fmt.Errorf("unlock migrations: %w", err) } } }() if _, err := connection.Exec(ctx, ` CREATE TABLE IF NOT EXISTS schema_migrations ( version BIGINT PRIMARY KEY, name TEXT NOT NULL, checksum TEXT NOT NULL, applied_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() )`); err != nil { return fmt.Errorf("create migration history: %w", err) } for _, migration := range migrations { if err := applyMigration(ctx, connection, migration); err != nil { return err } } return nil } func readMigrations(migrationFS fs.FS) ([]migration, error) { entries, err := fs.ReadDir(migrationFS, ".") if err != nil { return nil, fmt.Errorf("read embedded migrations: %w", err) } migrations := make([]migration, 0, len(entries)) versions := make(map[int64]string) for _, entry := range entries { if entry.IsDir() || filepath.Ext(entry.Name()) != ".sql" { continue } matches := migrationNamePattern.FindStringSubmatch(entry.Name()) if matches == nil { return nil, fmt.Errorf("invalid migration filename %q", entry.Name()) } version, err := strconv.ParseInt(matches[1], 10, 64) if err != nil || version < 1 { return nil, fmt.Errorf("invalid migration version in %q", entry.Name()) } if previous, exists := versions[version]; exists { return nil, fmt.Errorf("migration version %d is used by %q and %q", version, previous, entry.Name()) } data, err := fs.ReadFile(migrationFS, entry.Name()) if err != nil { return nil, fmt.Errorf("read migration %q: %w", entry.Name(), err) } hash := sha256.Sum256(data) versions[version] = entry.Name() migrations = append(migrations, migration{ version: version, name: entry.Name(), checksum: hex.EncodeToString(hash[:]), sql: string(data), }) } if len(migrations) == 0 { return nil, errors.New("no database migrations found") } sort.Slice(migrations, func(i, j int) bool { return migrations[i].version < migrations[j].version }) return migrations, nil } func applyMigration(ctx context.Context, connection *pgxpool.Conn, migration migration) error { var checksum string err := connection.QueryRow(ctx, "SELECT checksum FROM schema_migrations WHERE version = $1", migration.version).Scan(&checksum) switch { case err == nil && checksum != migration.checksum: return fmt.Errorf("migration %q checksum does not match applied version", migration.name) case err == nil: return nil case !errors.Is(err, pgx.ErrNoRows): return fmt.Errorf("read migration %q state: %w", migration.name, err) } transaction, err := connection.Begin(ctx) if err != nil { return fmt.Errorf("begin migration %q: %w", migration.name, err) } defer func() { _ = transaction.Rollback(context.WithoutCancel(ctx)) }() if _, err := transaction.Exec(ctx, migration.sql, pgx.QueryExecModeSimpleProtocol); err != nil { return fmt.Errorf("apply migration %q: %w", migration.name, err) } if _, err := transaction.Exec(ctx, ` INSERT INTO schema_migrations (version, name, checksum) VALUES ($1, $2, $3)`, migration.version, migration.name, migration.checksum); err != nil { return fmt.Errorf("record migration %q: %w", migration.name, err) } if err := transaction.Commit(ctx); err != nil { return fmt.Errorf("commit migration %q: %w", migration.name, err) } return nil }