SPB Git

Repository Public

db.go 3.4 KB 117 lines go

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
Files main
Menu

Theme