PhoenixKit.Migrations.Postgres.Helpers (phoenix_kit v1.7.199)

Copy Markdown View Source

Shared SQL helpers for the versioned Postgres migration chain.

Centralizes the prefix-sensitive patterns that individual version modules used to hand-roll (and get subtly wrong in independent ways):

  • qualify_table/2 — schema-qualified table reference for raw SQL in NEW migration code (existing versions keep their local helpers; migrating them is opportunistic). Index names must stay bare on CREATE INDEX (Postgres rejects CREATE INDEX schema.name); only DROP INDEX schema.name accepts a qualified name.
  • validate_prefix!/1 — rejects prefixes that can't be interpolated into SQL safely. Called at the up/down entry points and by the prefix-resolving tooling (PrefixConfig.resolve_prefix/1, Install.Common, UUIDRepair).
  • ensure_extension!/1 — privilege-aware replacement for a bare CREATE EXTENSION IF NOT EXISTS. Postgres checks the CREATE privilege before the IF-NOT-EXISTS short-circuit, so the bare statement fails for low-privilege roles even when the extension is already installed. This helper checks pg_extension first and only attempts creation when the extension is genuinely missing.
  • ensure_uuid_v7_function/1 — creates uuid_generate_v7() inside the install's schema (never wherever search_path happens to point, which pollutes public and fails outright on PG15+ where public isn't world-writable).

Functions without a repo argument run in Ecto.Migration context (immediate existence checks via repo().query/3, DDL queued via execute/1). The repo-taking variants are for runtime callers such as PhoenixKit.Migrations.UUIDRepair.

Summary

Functions

ensure_extension!(name)

Ensures a Postgres extension is available, in migration context.

ensure_extension!(repo, name)

Runtime variant of ensure_extension!/1 for callers outside migration context (statements run immediately on repo).

ensure_uuid_v7_function(prefix)

Ensures uuid_generate_v7() exists in the install's schema, in migration context.

ensure_uuid_v7_function(repo, prefix)

Runtime variant of ensure_uuid_v7_function/1 (statements run immediately on repo; raises on database errors via repo.query!/3).

pgcrypto_call(function_name)

Schema-qualified pgcrypto function reference for SQL interpolation in migration context, e.g. "#{Helpers.pgcrypto_call("digest")}(...)".

public_prefix?(prefix)

Whether the prefix denotes the default public schema (nil counts).

qualify_table(table, prefix)

Schema-qualified table reference for raw SQL interpolation.

uuid_v7_call(prefix)

Schema-qualified uuid_generate_v7() call for SQL interpolation.

validate_prefix!(prefix)

Validates a schema prefix before it is interpolated into SQL.

Functions

ensure_extension!(name)

@spec ensure_extension!(String.t()) :: :ok

Ensures a Postgres extension is available, in migration context.

  • already installed → no-op (skips the CREATE EXTENSION privilege check entirely, so pre-provisioned low-privilege setups pass)
  • missing + role can create → queues CREATE EXTENSION IF NOT EXISTS
  • missing + role cannot create → raises an operator-facing error listing the extensions to pre-create as a privileged role

ensure_extension!(repo, name)

@spec ensure_extension!(Ecto.Repo.t(), String.t()) :: :ok

Runtime variant of ensure_extension!/1 for callers outside migration context (statements run immediately on repo).

ensure_uuid_v7_function(prefix)

@spec ensure_uuid_v7_function(String.t() | nil) :: :ok

Ensures uuid_generate_v7() exists in the install's schema, in migration context.

Checks pg_proc first (so an existing function — possibly owned by a different role — is never re-created) and queues a schema-qualified CREATE OR REPLACE FUNCTION only when missing. The schema must exist when this runs; V01 owns schema creation, so callers on the upgrade path (installed version > 0) are always safe.

ensure_uuid_v7_function(repo, prefix)

@spec ensure_uuid_v7_function(Ecto.Repo.t(), String.t() | nil) :: :ok

Runtime variant of ensure_uuid_v7_function/1 (statements run immediately on repo; raises on database errors via repo.query!/3).

pgcrypto_call(function_name)

@spec pgcrypto_call(String.t()) :: String.t()

Schema-qualified pgcrypto function reference for SQL interpolation in migration context, e.g. "#{Helpers.pgcrypto_call("digest")}(...)".

Resolves pgcrypto's actual installation schema (same lookup used by ensure_uuid_v7_function/1) so the call works regardless of the connecting role's search_path — required whenever pgcrypto was installed outside the default search path (e.g. alongside a custom prefix schema in a hardened multi-schema install). Call after ensure_extension!("pgcrypto") so the extension is already visible.

public_prefix?(prefix)

@spec public_prefix?(String.t() | nil) :: boolean()

Whether the prefix denotes the default public schema (nil counts).

qualify_table(table, prefix)

@spec qualify_table(String.t() | atom(), String.t() | nil) :: String.t()

Schema-qualified table reference for raw SQL interpolation.

nil and "public" both qualify explicitly as public. — an explicit schema never depends on the connection's search_path.

uuid_v7_call(prefix)

@spec uuid_v7_call(String.t() | nil) :: String.t()

Schema-qualified uuid_generate_v7() call for SQL interpolation.

validate_prefix!(prefix)

@spec validate_prefix!(term()) :: :ok

Validates a schema prefix before it is interpolated into SQL.

The migration chain interpolates the prefix into hundreds of statements, mostly unquoted, so only conventional lower-case identifiers are supported: "^[a-z_][a-z0-9_]*$". Raises ArgumentError for anything else (including uppercase or dashed names, which only ever half-worked).