pgmi Session API Reference#

Core model: pgmi doesn’t interpret your SQL — it loads your files into PostgreSQL session tables and hands control to deploy.sql, which decides what to do with them.

How pgmi Actually Works#

When you run pgmi deploy ./myproject, here’s what happens:

┌─────────────────────────────────────────────────────────────────────────┐
│  1. CONNECT                                                              │
│     pgmi connects to PostgreSQL                                         │
└────────────────────────────────────────────┬────────────────────────────┘
                                             │
                                             ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  2. PREPARE SESSION                                                      │
│     pgmi creates temporary tables and views (two-tier API):              │
│                                                                          │
│     Internal tables: _pgmi_source, _pgmi_parameter, _pgmi_source_metadata│
│                      _pgmi_test_source, _pgmi_test_directory             │
│     Public views:    pgmi_source_view, pgmi_parameter_view, pgmi_plan_view│
│                      pgmi_source_metadata_view                           │
│                      pgmi_test_source_view, pgmi_test_directory_view     │
│                                                                          │
│     If --verbose: SET client_min_messages = 'debug' (enables RAISE DEBUG)│
│     Functions: pgmi_test_plan(), pgmi_test_generate()                    │
└────────────────────────────────────────────┬────────────────────────────┘
                                             │
                                             ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  3. EXECUTE deploy.sql                                                   │
│     YOUR deploy.sql runs and directly executes files using:              │
│                                                                          │
│     FOR v_file IN (SELECT * FROM pgmi_plan_view ORDER BY execution_order)│
│     LOOP                                                                 │
│         EXECUTE v_file.content;                                          │
│     END LOOP;                                                            │
│                                                                          │
│     Transaction boundaries, error handling, execution order—all yours.   │
└─────────────────────────────────────────────────────────────────────────┘

The key insight: deploy.sql is the deployment script. It queries pgmi_plan_view and uses EXECUTE to run files directly. You control the deployment logic — transactions, ordering, conditionals, error handling.

Connection requirement: Because everything depends on pg_temp tables surviving for the entire session, pgmi requires a direct connection or a pooler in session mode. Transaction-mode poolers (PgBouncer, RDS Proxy, etc.) will silently break deployments by reassigning the backend connection. See Production Guide — Connection Requirements .

Two-Tier API Design#

pgmi uses a two-tier naming convention for session objects:

Why this matters:

• Internal tables may change between versions
• Public views provide a stable contract for deploy.sql
• Always use views (pgmi_source_view, pgmi_plan_view) in your SQL

Public Interface (Stable API)#

These views and functions are the stable API for deploy.sql. Use these instead of querying internal tables directly.

Which View Should I Use?#

Rule of thumb: Use pgmi_plan_view for deployment loops. Use pgmi_source_view when you need raw access or custom filtering beyond what the plan provides.

File Access#

pgmi_source_view#

All project source files (excludes deploy.sql and __test__/ files).

This view provides direct access to all discovered files. For most use cases, prefer pgmi_plan_view which adds execution ordering via metadata.

-- List all SQL files in migrations/
SELECT path, name FROM pg_temp.pgmi_source_view
WHERE directory = './migrations/' AND is_sql_file
ORDER BY path;

pgmi_plan_view#

Pre-computed execution plan with metadata.

This view joins _pgmi_source with _pgmi_source_metadata and provides a clean interface for file access:

Recommended usage:

-- Deploy files in metadata-driven order using direct execution
DO $$
DECLARE v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT path, content
        FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%'
        ORDER BY execution_order
    ) LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

Parameters#

CLI parameters (passed via --param key=value) are accessible in multiple ways. This section consolidates all parameter access patterns.

Method 1: Session Variables (Recommended)#

pgmi automatically sets session variables with the pgmi. prefix. This is the simplest and most common approach:

-- Get parameter with default (the true argument prevents errors if not set)
v_env := COALESCE(current_setting('pgmi.env', true), 'development');

-- In conditional logic
IF COALESCE(current_setting('pgmi.env', true), 'dev') = 'production' THEN
    -- Production-specific logic
END IF;

-- Check if parameter was provided
IF current_setting('pgmi.feature_flag', true) IS NOT NULL THEN
    -- Parameter was explicitly set
END IF;

Important: Always pass true as the second argument to current_setting(). This returns NULL instead of raising an error when the variable is not set.

Method 2: pgmi_parameter_view (Introspection)#

For iterating over parameters or building dynamic logic:

-- List all parameters
SELECT key, value, description FROM pg_temp.pgmi_parameter_view;

-- Iterate over parameters dynamically.
-- Redact secret-like keys by default — values may be passwords, tokens, etc.
DO $$
DECLARE
    v_param RECORD;
BEGIN
    FOR v_param IN SELECT key, value FROM pg_temp.pgmi_parameter_view LOOP
        RAISE NOTICE 'Parameter: % = %',
            v_param.key,
            CASE WHEN lower(v_param.key) ~ '(password|secret|token|key|credential|auth)'
                 THEN '********' ELSE v_param.value END;
    END LOOP;
END $$;

Method 3: deployment_setting() Helper (Advanced Template Only)#

The advanced template provides a helper function with error handling:

-- Get required parameter (raises exception if missing)
v_admin_role := pg_temp.deployment_setting('database_admin_role');

-- Get optional parameter (returns NULL if missing)
v_optional := pg_temp.deployment_setting('optional_key', false);

Note: This function uses a deployment. prefix internally and normalizes key names. It’s defined in the advanced template’s deploy.sql, not in pgmi core.

Parameter Precedence#

Parameters merge from multiple sources (later wins):

pgmi.yaml params < --params-file < --param CLI flag

See Configuration Reference for details.

Type Coercion#

All parameter values are stored as text. Cast them as needed:

-- Boolean
v_enabled := COALESCE(current_setting('pgmi.feature_enabled', true), 'false')::boolean;

-- Integer
v_limit := COALESCE(current_setting('pgmi.max_rows', true), '100')::int;

-- Timestamp
v_cutoff := COALESCE(current_setting('pgmi.since', true), '2024-01-01')::timestamp;

Template Responsibility#

pgmi core provides raw parameter storage. Templates handle:

• Declaring expected parameters (advanced template uses session.xml)
• Validating required parameters
• Providing default values
• Type validation and coercion

Direct Execution Pattern#

pgmi uses a direct execution model: your deploy.sql queries pgmi_plan_view and uses EXECUTE to run files. This gives you full control over transaction boundaries, execution order, and conditional logic.

Basic pattern:

DO $$
DECLARE v_file RECORD;
BEGIN
    -- Transaction control is in your hands
    FOR v_file IN (
        SELECT path, content
        FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './schemas/%'
        ORDER BY execution_order
    ) LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

With explicit transaction boundaries:

DO $$
DECLARE v_file RECORD;
BEGIN
    -- Phase 1: Schema changes in one transaction
    BEGIN
        FOR v_file IN (
            SELECT path, content FROM pg_temp.pgmi_plan_view
            WHERE path LIKE './schemas/%' ORDER BY execution_order
        ) LOOP
            EXECUTE v_file.content;
        END LOOP;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'Schema phase failed: %', SQLERRM;
    END;

    -- Phase 2: Migrations
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%' ORDER BY execution_order
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;
END $$;

Conditional execution:

DO $$
DECLARE
    v_file RECORD;
    v_env TEXT := COALESCE(current_setting('pgmi.env', true), 'development');
BEGIN
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%' ORDER BY execution_order
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;

    -- Only seed data in development
    IF v_env = 'development' THEN
        FOR v_file IN (
            SELECT path, content FROM pg_temp.pgmi_plan_view
            WHERE path LIKE './seeds/%' ORDER BY execution_order
        ) LOOP
            EXECUTE v_file.content;
        END LOOP;
    END IF;
END $$;

Metadata#

pgmi_source_metadata_view#

Parsed <pgmi-meta> XML blocks from SQL files.

Files without metadata are not in this view (use pgmi_plan_view which handles fallbacks).

-- List files with metadata
SELECT path, id, idempotent, sort_keys
FROM pg_temp.pgmi_source_metadata_view;

-- Find non-idempotent migrations
SELECT path FROM pg_temp.pgmi_source_metadata_view
WHERE NOT idempotent;

See Metadata Guide for syntax and usage patterns.

Test Views#

pgmi_test_source_view#

Test file content from __test__/ or __tests__/ directories.

-- List all test files
SELECT path, is_fixture FROM pg_temp.pgmi_test_source_view
ORDER BY directory, filename;

-- Get fixture files only
SELECT path FROM pg_temp.pgmi_test_source_view
WHERE is_fixture;

pgmi_test_directory_view#

Hierarchical test directory structure.

-- See test directory hierarchy
SELECT path, parent_path, depth
FROM pg_temp.pgmi_test_directory_view
ORDER BY depth, path;

-- Find nested test directories
SELECT path FROM pg_temp.pgmi_test_directory_view
WHERE depth > 0;

Testing#

CALL pgmi_test() Preprocessor Macro#

Executes tests with automatic savepoint isolation.

The CALL pgmi_test() is a preprocessor macro that Go expands before sending SQL to PostgreSQL:

-- Run all tests with default callback
CALL pgmi_test();

-- Run tests matching a pattern (POSIX regex)
CALL pgmi_test('.*/integration/.*');
CALL pgmi_test('.*_critical\.sql$');

-- Run tests with custom callback function
CALL pgmi_test('.*/auth/.*', 'my_custom_callback');

Automatic behavior:

• Creates SAVEPOINTs before each _setup.sql
• Executes tests in lexicographic order
• Rolls back to SAVEPOINT after tests (no side effects)
• Includes ancestor _setup.sql files needed by matching tests
• Calls pgmi_test_generate() internally to produce inline SQL

pgmi_test_plan(pattern) Function#

Returns the test execution plan as a table (for introspection).

This is a TABLE-returning function (not a view). Files from __test__/ or __tests__/ directories are automatically organized into a depth-first execution plan with fixture/test/teardown lifecycle.

-- See what tests would run
SELECT * FROM pg_temp.pgmi_test_plan();

-- Filter by pattern (POSIX regex on script_path)
SELECT * FROM pg_temp.pgmi_test_plan('.*/auth/.*');

Test execution emits notices:

• NOTICE: [pgmi] Test suite started
• NOTICE: [pgmi] Fixture: ./path/to/_setup.sql
• NOTICE: [pgmi] Test: ./path/to/test_example.sql
• NOTICE: [pgmi] Test suite completed (N steps)

With --verbose, DEBUG messages show rollback and teardown events ([pgmi] Rollback: ..., [pgmi] Teardown: ...).

pgmi_test_generate(pattern, callback) Function#

Generates the SQL code for pgmi_test() macro expansion.

This is an internal function called by the Go preprocessor. It returns the complete SQL text that replaces the CALL pgmi_test() macro.

-- See what SQL the macro generates (for debugging)
SELECT pg_temp.pgmi_test_generate();
SELECT pg_temp.pgmi_test_generate('.*/auth/.*', 'my_callback');

Critical implementation detail: The generated SQL uses top-level SAVEPOINT commands, not PL/pgSQL savepoints. PostgreSQL’s PL/pgSQL does not support SAVEPOINT, ROLLBACK TO SAVEPOINT, or RELEASE SAVEPOINT commands directly — they must be issued as top-level SQL statements.

The generated structure looks like:

SAVEPOINT __pgmi_d1__;              -- Directory savepoint (top-level SQL)
DO $$ ... EXECUTE fixture ... $$;   -- Fixture content via EXECUTE
SAVEPOINT __pgmi_t2__;              -- Per-directory test savepoint (top-level SQL)
DO $$ ... EXECUTE test ... $$;      -- Test content via EXECUTE
ROLLBACK TO SAVEPOINT __pgmi_t2__;  -- Undoes test side effects
DO $$ ... EXECUTE test2 ... $$;     -- Next test in same directory
ROLLBACK TO SAVEPOINT __pgmi_t2__;  -- Undoes test2 side effects
ROLLBACK TO SAVEPOINT __pgmi_d1__;  -- Teardown: undoes fixture
RELEASE SAVEPOINT __pgmi_d1__;      -- Clean up savepoint

This is why CALL pgmi_test() must appear at the top level of your deploy.sql, not inside a DO block.

Custom Callbacks#

The second argument to pgmi_test() is the name of a callback function that receives lifecycle events. Your function must accept a single pg_temp.pgmi_test_event argument and return void:

CREATE FUNCTION pg_temp.my_callback(e pg_temp.pgmi_test_event)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    CASE e.event
        WHEN 'suite_start'    THEN RAISE NOTICE 'Starting test suite';
        WHEN 'suite_end'      THEN RAISE NOTICE 'Completed % steps', e.ordinal;
        WHEN 'test_start'     THEN RAISE NOTICE 'Running: %', e.path;
        WHEN 'test_end'       THEN RAISE NOTICE 'Passed: %', e.path;
        ELSE NULL;
    END CASE;
END $$;

pgmi_test_event fields:

Event types: suite_start, fixture_start, fixture_end, test_start, test_end, rollback, teardown_start, teardown_end, suite_end.

The default callback (pgmi_test_callback) emits NOTICE for fixtures and tests, DEBUG for rollback and teardown. You can call it from your custom callback for events you don’t want to handle specially.

pgmi_persist_test_plan(schema, pattern) Function#

Exports the test plan to a permanent table for external tooling.

-- Create a permanent copy of the test plan
SELECT pg_temp.pgmi_persist_test_plan('public', NULL);
-- Creates: public.pgmi_test_plan

This is useful for CI/CD pipelines that need to inspect the test plan before running, or for generating test reports.

The Direct Execution Model (Critical Concept)#

This is the most important thing to understand about pgmi.

pgmi prepares session    deploy.sql runs
      │                         │
      ▼                         ▼
┌─────────────┐           ┌─────────────┐
│   SETUP     │           │  EXECUTION  │
│             │           │             │
│ Create temp │           │ Query files │
│ tables with │──────────▶│ from views  │
│ your files  │           │ EXECUTE     │
│             │           │ directly    │
└─────────────┘           └─────────────┘

Your deploy.sql has full control. You query pgmi_plan_view, loop through files, and use EXECUTE to run them. Transaction boundaries, error handling, execution order—all in your hands.

The Basic Pattern#

DO $$
DECLARE v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT path, content
        FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%'
        ORDER BY execution_order
    ) LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

What happens:

1. pgmi loads your files into internal tables and creates public views
2. Your deploy.sql queries pgmi_plan_view (or pgmi_source_view) and executes files directly with EXECUTE

Common Patterns#

Phased Deployment#

DO $$
DECLARE v_file RECORD;
BEGIN
    -- Phase 1: Schema changes
    RAISE NOTICE '=== Phase 1: Schema ===';
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './schemas/%'
        ORDER BY execution_order
    ) LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;

    -- Phase 2: Migrations
    RAISE NOTICE '=== Phase 2: Migrations ===';
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%'
        ORDER BY execution_order
    ) LOOP
        RAISE NOTICE 'Executing: %', v_file.path;
        EXECUTE v_file.content;
    END LOOP;
END $$;

Conditional Deployment#

CALL pgmi_test() is a preprocessor macro that expands to top-level SQL (including SAVEPOINT commands), so it must appear at the top level of deploy.sql — never inside a DO block. Structure your deploy.sql with the DO block for migrations and CALL pgmi_test() as a separate top-level statement:

-- Phase 1: Migrations and seeds (inside DO block)
DO $$
DECLARE
    v_file RECORD;
    v_env TEXT := COALESCE(current_setting('pgmi.env', true), 'development');
BEGIN
    -- Always run migrations
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/%'
        ORDER BY execution_order
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;

    -- Only seed data in development
    IF v_env = 'development' THEN
        FOR v_file IN (
            SELECT path, content FROM pg_temp.pgmi_plan_view
            WHERE path LIKE './seeds/%'
            ORDER BY execution_order
        ) LOOP
            EXECUTE v_file.content;
        END LOOP;
    END IF;
END $$;

-- Phase 2: Tests (top-level — expands to SAVEPOINT commands)
CALL pgmi_test();

COMMIT;

Dynamic File Selection#

-- Deploy SQL files from a specific directory
DO $$
DECLARE v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './migrations/v2/%'
        ORDER BY execution_order
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;
END $$;

-- Or use POSIX regex for complex patterns
DO $$
DECLARE v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_source_view
        WHERE path ~ '.*/v2/.*' AND is_sql_file
        ORDER BY path
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;
END $$;

Test Isolation with Savepoints#

DO $$
DECLARE v_file RECORD;
BEGIN
    -- Deploy your schema
    FOR v_file IN (
        SELECT path, content FROM pg_temp.pgmi_plan_view
        WHERE path LIKE './schemas/%'
        ORDER BY execution_order
    ) LOOP
        EXECUTE v_file.content;
    END LOOP;

END $$;

-- Run tests (preprocessor macro handles savepoint isolation)
CALL pgmi_test();

COMMIT;

Internal Tables (Implementation Details)#

These tables are the underlying storage for the session API. Users should use the public views above rather than querying these internal tables directly.

Naming convention: Internal tables use underscore prefix (_pgmi_*)

pg_temp._pgmi_source#

Raw file storage.

pg_temp._pgmi_parameter#

Raw parameter storage.

pg_temp._pgmi_source_metadata#

Parsed XML metadata from <pgmi-meta> blocks.

pg_temp._pgmi_test_directory#

Test directory hierarchy.

pg_temp._pgmi_test_source#

Test file content.

Introspection Examples#

See What Files Are Loaded#

-- Use the view for clean access
SELECT path, execution_order, idempotent
FROM pg_temp.pgmi_plan_view
ORDER BY execution_order;

See What Parameters Are Available#

SELECT key, value, type, required, default_value, description
FROM pg_temp.pgmi_parameter_view;

Preview the Execution Plan#

-- See files in execution order
SELECT execution_order, path, left(content, 80) AS preview
FROM pg_temp.pgmi_plan_view
ORDER BY execution_order;

See Available Tests#

SELECT step_type, script_path
FROM pg_temp.pgmi_test_plan()
ORDER BY ordinal;

Philosophy: Why This Design?#

pgmi is not a migration framework. It’s an execution fabric.

pgmi’s job is to:

1. Connect to PostgreSQL
2. Load your files into session tables
3. Run your deploy.sql

Your job is to:

1. Query pgmi_plan_view to find your files
2. Use EXECUTE to run them directly
3. Control transaction boundaries and error handling
4. Decide which files run in what order

The result: Full control over deployment logic. pgmi handles infrastructure (file loading, metadata parsing, preprocessing); your SQL handles everything else. PostgreSQL is the deployment engine.

See Also#

• Testing Guide — Database testing with automatic rollback
• Metadata Guide — Script tracking and execution ordering
• MCP Integration — Model Context Protocol for AI assistants (advanced template)