Repository Public
1// Package db owns the PostgreSQL pool and schema migrations.2package db34import (5 "context"6 "embed"7 "fmt"8 "io/fs"9 "sort"10 "strings"11 "time"1213 "github.com/jackc/pgx/v5"14 "github.com/jackc/pgx/v5/pgtype"15 "github.com/jackc/pgx/v5/pgxpool"16)1718//go:embed migrations/*.sql19var migrations embed.FS2021// Open connects with bounded pool settings and verifies the connection.22func Open(ctx context.Context, url string) (*pgxpool.Pool, error) {23 cfg, err := pgxpool.ParseConfig(url)24 if err != nil {25 return nil, fmt.Errorf("db: parse url: %w", err)26 }27 if cfg.MaxConns < 4 {28 cfg.MaxConns = 1229 }30 cfg.MaxConnIdleTime = 5 * time.Minute31 cfg.HealthCheckPeriod = 30 * time.Second32 cfg.ConnConfig.RuntimeParams["timezone"] = "UTC"33 cfg.ConnConfig.RuntimeParams["application_name"] = "mcc"34 // Every timestamp read from the database is a UTC time.Time, so every API35 // response and log line carries UTC ("Z") regardless of the host's time zone.36 cfg.AfterConnect = func(_ context.Context, c *pgx.Conn) error {37 c.TypeMap().RegisterType(&pgtype.Type{Name: "timestamptz", OID: pgtype.TimestamptzOID,38 Codec: &pgtype.TimestamptzCodec{ScanLocation: time.UTC}})39 return nil40 }41 pool, err := pgxpool.NewWithConfig(ctx, cfg)42 if err != nil {43 return nil, fmt.Errorf("db: connect: %w", err)44 }45 pctx, cancel := context.WithTimeout(ctx, 5*time.Second)46 defer cancel()47 if err := pool.Ping(pctx); err != nil {48 pool.Close()49 return nil, fmt.Errorf("db: ping: %w", err)50 }51 return pool, nil52}5354const migrationLock = 0x6d6363_01 // advisory lock: one migrator at a time5556// Migrate applies pending migrations in filename order, each in its own57// transaction, under an advisory lock. Applied files are recorded with a58// checksum; an edited, already-applied migration is refused.59func Migrate(ctx context.Context, pool *pgxpool.Pool) ([]string, error) {60 conn, err := pool.Acquire(ctx)61 if err != nil {62 return nil, err63 }64 defer conn.Release()65 if _, err := conn.Exec(ctx, `select pg_advisory_lock($1)`, migrationLock); err != nil {66 return nil, err67 }68 defer conn.Exec(context.WithoutCancel(ctx), `select pg_advisory_unlock($1)`, migrationLock)6970 if _, err := conn.Exec(ctx, `create table if not exists schema_migrations (71 name text primary key, checksum text not null, applied_at timestamptz not null default now())`); err != nil {72 return nil, err73 }74 names, err := fs.Glob(migrations, "migrations/*.sql")75 if err != nil {76 return nil, err77 }78 sort.Strings(names)79 var applied []string80 for _, path := range names {81 name := strings.TrimPrefix(path, "migrations/")82 body, err := migrations.ReadFile(path)83 if err != nil {84 return applied, err85 }86 sum := checksum(body)87 var have string88 err = conn.QueryRow(ctx, `select checksum from schema_migrations where name=$1`, name).Scan(&have)89 if err == nil {90 if have != sum {91 return applied, fmt.Errorf("db: migration %s was modified after being applied", name)92 }93 continue94 }95 if err != pgx.ErrNoRows {96 return applied, err97 }98 tx, err := conn.Begin(ctx)99 if err != nil {100 return applied, err101 }102 if _, err := tx.Exec(ctx, string(body)); err != nil {103 tx.Rollback(ctx)104 return applied, fmt.Errorf("db: migration %s: %w", name, err)105 }106 if _, err := tx.Exec(ctx, `insert into schema_migrations(name, checksum) values ($1,$2)`, name, sum); err != nil {107 tx.Rollback(ctx)108 return applied, err109 }110 if err := tx.Commit(ctx); err != nil {111 return applied, err112 }113 applied = append(applied, name)114 }115 return applied, nil116}117