PhoenixKit.Migrations.Postgres.Helpers (phoenix_kit v2.0.0)

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).
  • 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). Unlike ensure_extension!/1, this one does NOT pre-check existence — it queues CREATE OR REPLACE FUNCTION for every install whose role owns (or could own) the function. That is deliberate, not a missed optimization: an unless already exists guard here (the original shape) meant a function created before some later fix to this same body (e.g. the pgcrypto schema-qualification change) stayed on the OLD body forever — CREATE OR REPLACE never got a chance to run because the guard always saw "exists" and skipped it, and there was no other path back to a correct body short of a manual DROP. CREATE OR REPLACE FUNCTION on an unchanged signature is additive and idempotent (no dependent objects break, nothing is dropped), so the guard bought nothing but a stuck body — removing it is what lets PhoenixKit.Migrations.Repair self-heal a drifted body via this same helper instead of reporting a permanent, un-fixable finding. The one case still checked first is an existing function owned by another role: CREATE OR REPLACE requires ownership, and in migration context the statement is only QUEUED, so that failure arrives at flush time where no rescue here can reach it and the migration aborts. That topology is documented and supported (PhoenixKit.Migration's moduledoc tells a DBA to pre-create the function), so it is excluded before the statement is queued.

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 outside a migration context, e.g. mix phoenix_kit.repair.

Summary

Functions

Ensures a Postgres extension is available, in migration context.

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

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

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

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

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

Schema-qualified table reference for raw SQL interpolation.

Schema-qualified uuid_generate_v7() call for SQL interpolation.

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.

Queues a schema-qualified CREATE OR REPLACE FUNCTION whenever this role could actually run it — no "does it exist" pre-check, on purpose (see moduledoc "ensure_uuid_v7_function/1"); the only thing checked first is whether an existing function is owned by another role, which no rescue in migration context could recover from (the DDL is queued, so the error arrives at flush time). 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) — including a prefix over 20 bytes, which fits every conventional prefix a real install uses but rejects the ones that would silently truncate a prefix-embedded object name (see @longest_embedded_object_name above) past Postgres's 63-byte NAMEDATALEN.