Documentation
¶
Overview ¶
Package database contains shared utilities for components that interact with Armada's PostgreSQL databases.
The package provides:
- A simple, custom migration engine (UpdateDatabase, ReadMigrations) used by the scheduler and lookout migrators. Migrations are numbered SQL files; the applied version is tracked in a database_version sequence.
- PrepareSchema and MigrationConfig, which let migrators optionally create the target schema and set search_path before running migrations. A separate "schema-creator" connection can be configured so a least-privileged migrator role can run inside a schema created by an admin role.
- Connection helpers (OpenPgxConn, OpenPgxPool, CreateConnectionString) that build pgx connections from a PostgresConfig.
- Test helpers (WithTestDb, WithTestDbCustom) that spin up a temporary database, run migrations, and clean up afterwards.
- A Querier interface that abstracts pgx connections and pools so callers can pass either to the same code.
Index ¶
- func CreateConnectionString(values map[string]string) string
- func NamesValuesFromRecord(x interface{}) ([]string, []interface{})
- func OpenPgxConn(config configuration.PostgresConfig) (*pgx.Conn, error)
- func OpenPgxPool(config configuration.PostgresConfig) (*pgxpool.Pool, error)
- func ParseNullInt32(nullInt sql.NullInt32) *int32
- func ParseNullString(nullString sql.NullString) *string
- func ParseNullStringDefault(nullString sql.NullString) string
- func ParseNullTime(nullTime sql.NullTime) *time.Time
- func PrepareSchema(ctx *armadacontext.Context, migratorDB *pgx.Conn, cfg MigrationConfig) error
- func ReadInt(rows pgx.Rows) (int, error)
- func UniqueTableName(table string) string
- func UpdateDatabase(ctx *armadacontext.Context, db Querier, migrations []Migration) error
- func Upsert[T any](ctx *armadacontext.Context, tx pgx.Tx, tableName string, records []T, ...) error
- func UpsertPartitioned[T any](ctx *armadacontext.Context, tx pgx.Tx, tableName string, conflictKey []string, ...) error
- func UpsertPartitionedWithTransaction[T any](ctx *armadacontext.Context, db *pgxpool.Pool, tableName string, ...) error
- func UpsertWithTransaction[T any](ctx *armadacontext.Context, db *pgxpool.Pool, tableName string, records []T, ...) error
- func WithTestDb(migrations []Migration, action func(db *pgxpool.Pool) error) error
- func WithTestDbCustom(migrations []Migration, config configuration.PostgresConfig, ...) error
- type Migration
- type MigrationConfig
- type Querier
- type UpsertOption
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func CreateConnectionString ¶
func NamesValuesFromRecord ¶
func NamesValuesFromRecord(x interface{}) ([]string, []interface{})
NamesValuesFromRecord returns a slice composed of the field names and another composed of the corresponding values for fields of a struct marked with "db" tags.
For example, if x is an instance of a struct with definition
type Rectangle struct {
Width int `db:"width"`
Height int `db:"height"`
},
where Width = 10 and Height = 5, it returns ["width", "height"], [10, 5].
This function does not handle pointers to structs, i.e., x must be Rectangle{} and not &Rectangle{}.
func OpenPgxConn ¶ added in v0.3.47
func OpenPgxConn(config configuration.PostgresConfig) (*pgx.Conn, error)
func OpenPgxPool ¶
func OpenPgxPool(config configuration.PostgresConfig) (*pgxpool.Pool, error)
func ParseNullInt32 ¶
func ParseNullString ¶
func ParseNullString(nullString sql.NullString) *string
func ParseNullStringDefault ¶
func ParseNullStringDefault(nullString sql.NullString) string
func PrepareSchema ¶ added in v0.21.1
func PrepareSchema(ctx *armadacontext.Context, migratorDB *pgx.Conn, cfg MigrationConfig) error
PrepareSchema prepares the migrator session according to cfg. It optionally creates the configured schema (using a separate bootstrap connection if SchemaCreatorPostgresConfig is set) and sets the migrator session's search_path so that subsequent unqualified DDL lands in the configured schema.
PrepareSchema must be called before UpdateDatabase. It is idempotent.
migratorDB must be a single connection (not a pool): SET search_path is session-local, so any subsequent migration query must run on the same connection or the search_path will not be in effect.
func UniqueTableName ¶
func UpdateDatabase ¶
func UpdateDatabase(ctx *armadacontext.Context, db Querier, migrations []Migration) error
func Upsert ¶
func Upsert[T any](ctx *armadacontext.Context, tx pgx.Tx, tableName string, records []T, options ...UpsertOption) error
Upsert is an optimised SQL call for bulk upserts.
For efficiency, this function: 1. Creates an empty temporary SQL table. 2. Inserts all records into the temporary table using the postgres-specific COPY wire protocol. 3. Upserts all records from the temporary table into the target table (as specified by tableName).
The COPY protocol can be faster than repeated inserts for as little as 5 rows; see https://www.postgresql.org/docs/current/populate.html https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Copy_Protocol
The records to write should be structs with fields marked with "db" tags. Field names and values are extracted using the NamesValuesFromRecord function; see its definition for details. The first field is used as the primary key in SQL.
Options can be provided to customize upsert behavior, such as excluding specific columns.
func UpsertPartitioned ¶ added in v0.20.42
func UpsertPartitioned[T any]( ctx *armadacontext.Context, tx pgx.Tx, tableName string, conflictKey []string, records []T, options ...UpsertOption, ) error
UpsertPartitioned is the in-transaction form of UpsertPartitionedWithTransaction. See that function for semantics.
func UpsertPartitionedWithTransaction ¶ added in v0.20.42
func UpsertPartitionedWithTransaction[T any]( ctx *armadacontext.Context, db *pgxpool.Pool, tableName string, conflictKey []string, records []T, options ...UpsertOption, ) error
UpsertPartitionedWithTransaction is the partition-safe sibling of UpsertWithTransaction. It stages records via COPY, then performs DELETE-by-conflict-key followed by plain INSERT, in a single transaction.
Unlike UpsertWithTransaction, which relies on INSERT ... ON CONFLICT DO UPDATE, this variant correctly handles rows that move between partitions (e.g. a row whose partition-key column changes between calls). It is intended for tables partitioned by a column that forms part of the primary key, where ON CONFLICT on the non-partition-key alone is not possible.
conflictKey names the column(s) that identify "the same row" across upserts — typically the logical primary key (e.g. ["job_id"]) even when the physical primary key is composite (e.g. (job_id, state)). Duplicate conflict-key values within a single batch are not deduplicated; callers must dedupe.
func UpsertWithTransaction ¶ added in v0.3.63
func UpsertWithTransaction[T any](ctx *armadacontext.Context, db *pgxpool.Pool, tableName string, records []T, options ...UpsertOption) error
func WithTestDb ¶
WithTestDb spins up a Postgres database for testing migrations: perform the list of migrations before entering the action callback action: callback for client code
func WithTestDbCustom ¶
func WithTestDbCustom(migrations []Migration, config configuration.PostgresConfig, action func(db *pgxpool.Pool) error) error
WithTestDbCustom connects to specified database for testing migrations: perform the list of migrations before entering the action callback config: PostgresConfig to specify connection details to database action: callback for client code
Types ¶
type Migration ¶
type Migration struct {
// contains filtered or unexported fields
}
Migration represents a single, versioned database migration script
type MigrationConfig ¶ added in v0.21.1
type MigrationConfig struct {
// Schema is the schema in which migrations should run. If set, the
// migrator will SET search_path to this value before applying migrations.
// If unset, the connection's default search_path is used.
Schema string
// CreateSchema, if true, ensures Schema exists before running
// migrations. Requires Schema to be set.
CreateSchema bool
// SchemaCreatorPostgresConfig optionally configures a separate "bootstrap"
// connection used only to CREATE SCHEMA and GRANT privileges to the
// migrator role. If nil and CreateSchema is true, the migrator's own
// connection is used (single-role pattern).
SchemaCreatorPostgresConfig *configuration.PostgresConfig
}
MigrationConfig configures optional schema-creation behaviour for the database migrator. The zero value preserves the historical behaviour: no search_path change, no schema creation.
type Querier ¶ added in v0.3.87
type Querier interface {
Exec(context.Context, string, ...any) (pgconn.CommandTag, error)
Query(context.Context, string, ...any) (pgx.Rows, error)
QueryRow(context.Context, string, ...any) pgx.Row
}
This is a temporary interface to act as a bridge between upgrading from pgx/v4 to pgx/v5 TODO (Mo-Fatah): Remove this after https://github.com/armadaproject/armada/pull/2659 is ready to be used in the code.
type UpsertOption ¶ added in v0.20.17
type UpsertOption func(*upsertOptions)
func WithExcludeColumns ¶ added in v0.20.17
func WithExcludeColumns(columns ...string) UpsertOption
WithExcludeColumns returns an option that excludes specific columns from upsert. This is useful for PostgreSQL GENERATED ALWAYS columns which cannot be explicitly inserted.
Source Files
Directories