postgres

func CheckViolation ¶

func CheckViolation(err error) (constraint string, ok bool)

CheckViolation reports whether err is a check constraint violation and returns the constraint's name.

func ForeignKeyViolation ¶

func ForeignKeyViolation(err error) (constraint string, ok bool)

ForeignKeyViolation reports whether err is a foreign key violation and returns the constraint's name.

func HealthCheck ¶

func HealthCheck(pool *pgxpool.Pool) health.Check

HealthCheck returns a readiness check that pings the database.

func InTx ¶

func InTx(ctx context.Context, db Beginner, fn func(tx pgx.Tx) error) error

InTx runs fn in a read-write transaction with the server's default isolation level. See InTxWithOptions.

package main

import (
	"context"
	"log"

	"github.com/jackc/pgx/v5"

	"gorbital.dev/config"
	"gorbital.dev/modules/postgres"
)

func main() {
	ctx := context.Background()
	pool, err := postgres.Open(ctx, config.NewSecret("postgres://localhost:5432/acme"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	// Stores built from tx take part in the transaction.
	err = postgres.InTx(ctx, pool, func(tx pgx.Tx) error {
		if _, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance - 10 WHERE id = $1", 1); err != nil {
			return err
		}
		_, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance + 10 WHERE id = $1", 2)
		return err
	})
	if err != nil {
		log.Fatal(err)
	}
}

Output:

func InTxWithOptions ¶

func InTxWithOptions(ctx context.Context, db Beginner, opts pgx.TxOptions, fn func(tx pgx.Tx) error) error

InTxWithOptions runs fn in a transaction started with opts. It commits when fn returns nil. Otherwise it rolls back and returns fn's error unchanged, so callers can match domain errors with errors.Is. If fn panics, the transaction is rolled back and the panic continues.

Build tx-bound repositories inside fn (for example NewUserStore(tx)); never keep tx after fn returns. With pgx.Serializable, retry when IsRetryable reports true.

func IsNoRows ¶

func IsNoRows(err error) bool

IsNoRows reports whether err means a query returned no rows, as from QueryRow(...).Scan. Repositories return their not-found domain error.

func IsRetryable ¶

func IsRetryable(err error) bool

IsRetryable reports whether err is a serialization failure or deadlock, after which the whole transaction can be retried.

func Migrate ¶

func Migrate(ctx context.Context, pool *pgxpool.Pool, fsys fs.FS) ([]int64, error)

Migrate applies every pending goose migration in the root of fsys (files such as 00001_create_users.sql) and returns the versions it applied, in order. It returns nil when fsys has no migrations or none are pending.

A PostgreSQL advisory lock serialises concurrent callers, so several instances can run Migrate at once. Migrations run WithoutRowLevelSecurity, so a data migration reaches every organisation's rows (ADR-0061). Apps run migrations from a separate command, never implicitly at startup (ADR-0017).

func MigrateDown ¶

func MigrateDown(ctx context.Context, pool *pgxpool.Pool, fsys fs.FS) (int64, error)

MigrateDown rolls back the most recently applied migration in fsys, for development (ADR-0069): a migration written from the Dev Portal carries a Down section, and undoing it is how a mistake is corrected before it is released. It returns the version rolled back, or 0 when none was applied. Production apps refuse to call it.

func NotNullViolation ¶

func NotNullViolation(err error) (column string, ok bool)

NotNullViolation reports whether err is a not-null violation and returns the column's name.

func Open ¶

func Open(ctx context.Context, url config.Secret, opts ...Option) (*pgxpool.Pool, error)

Open creates a connection pool for url and pings the database, so a wrong URL or unreachable server fails at startup. Errors never include the URL, which may contain a password.

Every query becomes an OpenTelemetry client span carrying the SQL text but never its arguments.

Every connection carries the organisation of the context that acquired it in OrgSetting, and BypassSetting for a context from WithoutRowLevelSecurity, for row-level security policies (ADR-0061). Setting them costs one round trip when an acquire changes them, and nothing otherwise. Don't set these settings, or run RESET ALL or DISCARD ALL, yourself; and don't put the pool behind a pooler in transaction mode, which doesn't keep session settings with a client. Close the pool on shutdown:

cleanup.Add("postgres", func(context.Context) error { pool.Close(); return nil })

package main

import (
	"context"
	"log"

	"gorbital.dev/config"
	"gorbital.dev/modules/postgres"
)

func main() {
	ctx := context.Background()
	url, err := config.OS.Secret("DATABASE_URL")
	if err != nil {
		log.Fatal(err)
	}
	pool, err := postgres.Open(ctx, url,
		postgres.WithMaxConns(20),
		postgres.WithApplicationName("acme-api"),
	)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
}

Output:

func UniqueViolation ¶

func UniqueViolation(err error) (constraint string, ok bool)

UniqueViolation reports whether err is a unique constraint violation and returns the constraint's name, so repositories can map it to a domain error such as ErrDuplicateEmail.

func WithOrg ¶

func WithOrg(ctx context.Context, orgID string) context.Context

WithOrg returns a copy of ctx whose connections carry orgID in OrgSetting, so row-level security policies limit them to that organisation's rows. Without it, a connection carries the organisation of the context's actor (actor.Actor.OrgID, set by orgs.RequireMember after checking membership), or none. Use it where code acts in one organisation without an organisation actor, such as a job reading one organisation's rows.

The organisation is set when a connection is acquired: a transaction keeps the one its context had at BeginTx.

func WithScope ¶ added in v0.3.0

func WithScope(ctx context.Context, scopeID string) context.Context

WithScope returns a copy of ctx whose connections carry scopeID in ScopeSetting, so row-level security policies limit them to that scope's rows. It is WithOrg under the name the framework uses for tenancy since v0.3.0, and is what gorbital.Scope.Session is usually set to.

package main

import (
	"context"
	"log"

	"gorbital.dev/modules/postgres"
)

func main() {
	ctx := postgres.WithScope(context.Background(), "mch_1")

	// Connections acquired from ctx now set postgres.ScopeSetting to
	// mch_1, and policies written against it see only that merchant.
	log.Println(postgres.ScopeSetting, ctx != nil)
}

Output:

func WithoutRowLevelSecurity ¶

func WithoutRowLevelSecurity(ctx context.Context, reason string) context.Context

WithoutRowLevelSecurity returns a copy of ctx whose connections set BypassSetting to "on", so policies let them read and write every organisation's rows. It is for system paths that work across organisations, such as migrations and maintenance jobs; reason names the path ("migrate", "job:orgs_purge"). It panics when reason is empty.

Decide it in code, never from request input. The first connection acquired with the returned context is logged at info level through the pool's logger (WithLogger), and every query span it runs carries gorbital.rls_bypass with the reason.