tdom.dev

Go + Postgres

1 Nov 2022

I’m assuming you have a Postgres instance running on :5432 (here is how to spin one with Docker).

To connect to it via go, we will use pgx:

package main

import (
    "os"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
  var (
    postgresURL = fmt.Sprintf(
        "user=%s password=%s db=%s",
        get("POSTGRES_USER"),
        get("POSTGRES_PASSWORD"),
        get("POSTGRES_DB"))
  )
  dbpool, err := pgxpool.New(context.Background(), postgresURL)
  if err != nil {
    panic(err)
  }
  defer dbpool.Close()
}

func get(key string) string {
    if val := os.Getenv(key); val != "" {
        return val
    }
    panic(key + " required")
}

Migrations

You will often want to automatically apply migrations as your code runs. There exist fully fledged solutions such as Go Migrate, but oftentimes you just wanna keep it simple:

internal/common/db/migrate.go
package db

import (
    "github.com/jackc/pgx/v5/pgxpool"
)

func MigrateDir(db *pgxpool.Pool, dir embed.FS) error {
	files, err := fs.Glob(dir, "migrations/*.sql")
	if err != nil {
		return err
	}
	sort.Strings(files)

    ctx := context.Background()
    // table keeps track of migrations
	if _, err := db.Exec(ctx, `CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY);`); err != nil {
		return fmt.Errorf("failed creating migrations table: %w", err)
	}

    // apply all migrations in order
	for _, file := range files {
		if err := migrateFile(ctxh db, dir, file); err != nil {
			return fmt.Errorf("migration %s: %s", file, err)
		}
	}
	return nil
}

func migrateFile(ctx context.Context, db *pgxpool.Pool, dir embed.FS, file string) error {
	tx, err := db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// n is the number of times the migration has been executed
	var n int
	if err := tx.QueryRow(ctx, `SELECT COUNT(*) FROM migrations WHERE name = $1`, file).Scan(&n); err != nil {
		return err
	}
	if n > 0 {
		return nil
	}

    // read and execute file
	bs, err := fs.ReadFile(dir, file)
	if err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, string(bs)); err != nil {
		return err
	}

    // no errors, so we log it as success and commit all changes
	if _, err := tx.Exec(ctx, `INSERT INTO migrations (name) VALUES ($1)`, file); err != nil {
		return err
	}

	return tx.Commit(ctx)
}
package main

import (
    ...
    "{{mod}}/internal/common/db"
)

func main() {
    ...
    if err := db.MigrateDir(dbpool, migrateDir); err != nil {
        panic(err)
    }
}