Validate with Database

Validate with Database

Use this skill to connect to the test PostgreSQL database, validate assumptions about schema behavior, and cross-validate between pg_dump and pgschema implementations.

When to Use This Skill

Invoke this skill when:

• Validating how PostgreSQL actually stores or represents schema objects
• Comparing pg_dump output with pgschema output
• Testing a new feature implementation against real database
• Debugging schema introspection issues
• Verifying system catalog query results
• Understanding how PostgreSQL formats specific DDL
• Checking version-specific behavior (PostgreSQL 14-17)
• Validating migration plans before implementing new features

Database Connection Information

Connection details are stored in .env file at project root:

PGHOST=localhost
PGDATABASE=employee
PGUSER=postgres
PGPASSWORD=testpwd1

Default connection:

• Host: localhost
• Port: 5432 (default)
• Database: employee
• User: postgres
• Password: testpwd1

Connection Methods

Method 1: Using psql (Interactive Queries)

Basic connection:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee

One-off query:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d employee -c "SELECT version();"

Execute multi-line query:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    t.tgname,
    CASE
        WHEN t.tgqual IS NOT NULL
        THEN pg_get_expr(t.tgqual, t.tgrelid, false)
        ELSE 'NO WHEN CLAUSE'
    END as when_clause
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'test_table'
ORDER BY t.tgname;
"

Method 2: Using pg_dump (Schema Export)

Dump entire database schema:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public

Dump specific table:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --table=employees

Dump only specific object types:

# Only triggers
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public | grep -A 20 "CREATE TRIGGER"

# Only indexes
PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public | grep -A 10 "CREATE INDEX"

Method 3: Using pgschema (Project Tool)

Dump with pgschema:

./pgschema dump --host localhost --port 5432 --db employee --user postgres --schema public

Or using environment variables (from .env):

# .env is automatically loaded by pgschema
./pgschema dump --schema public

Dump to file:

./pgschema dump --schema public -o /tmp/schema_dump.sql

Method 4: Database Setup for Testing

Create a test database:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "DROP DATABASE IF EXISTS test_validation;"
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -c "CREATE DATABASE test_validation;"

Create test schema objects:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d test_validation -c "
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER test_trigger
    BEFORE INSERT ON test_table
    FOR EACH ROW
    WHEN (NEW.name IS NOT NULL)
    EXECUTE FUNCTION my_trigger_func();
"

Common Validation Workflows

Workflow 1: Compare pg_dump vs pgschema Output

Purpose: Verify pgschema produces comparable output to pg_dump

Steps:

1. Dump with pg_dump:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d employee --schema-only --schema=public > /tmp/pg_dump_output.sql

2. Dump with pgschema:

./pgschema dump --schema public -o /tmp/pgschema_output.sql

3. Compare outputs:

# Side-by-side comparison
diff -u /tmp/pg_dump_output.sql /tmp/pgschema_output.sql

# Or use a better diff tool
code --diff /tmp/pg_dump_output.sql /tmp/pgschema_output.sql

4. Analyze differences:

• Formatting differences (expected)
• Missing objects (bugs to fix)
• Different DDL structure (may need investigation)
• Comments handling
• Ordering differences

Workflow 2: Validate System Catalog Queries

Purpose: Test system catalog queries return expected data

Steps:

1. Create test object:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
CREATE TABLE test_triggers (
    id INTEGER PRIMARY KEY,
    data TEXT
);

CREATE OR REPLACE FUNCTION trigger_func() RETURNS TRIGGER AS \$\$
BEGIN
    RETURN NEW;
END;
\$\$ LANGUAGE plpgsql;

CREATE TRIGGER test_when_trigger
    BEFORE INSERT ON test_triggers
    FOR EACH ROW
    WHEN (NEW.data <> '')
    EXECUTE FUNCTION trigger_func();
"

2. Query system catalogs:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    t.tgname,
    t.tgtype,
    CASE
        WHEN t.tgqual IS NOT NULL
        THEN pg_get_expr(t.tgqual, t.tgrelid, false)
        ELSE NULL
    END as when_clause,
    pg_get_triggerdef(t.oid) as full_definition
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'test_triggers'
  AND t.tgisinternal = false;
"

3. Verify pgschema extracts same data:

./pgschema dump --schema public | grep -A 20 "test_when_trigger"

4. Cleanup:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
DROP TRIGGER IF EXISTS test_when_trigger ON test_triggers;
DROP TABLE IF EXISTS test_triggers;
DROP FUNCTION IF EXISTS trigger_func();
"

Workflow 3: Test Plan/Apply Workflow

Purpose: Validate pgschema plan and apply work correctly

Steps:

1. Create initial schema:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
DROP SCHEMA IF EXISTS test_workflow CASCADE;
CREATE SCHEMA test_workflow;
SET search_path TO test_workflow;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
"

2. Dump current state:

./pgschema dump --schema test_workflow -o /tmp/current_schema.sql

3. Modify schema file (edit /tmp/current_schema.sql):

-- Add a new column
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- NEW
);

4. Generate plan:

./pgschema plan --schema test_workflow --file /tmp/current_schema.sql

5. Review migration DDL - should show:

ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

6. Apply migration:

./pgschema apply --schema test_workflow --file /tmp/current_schema.sql --auto-approve

7. Verify result:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "\d test_workflow.users"

8. Cleanup:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "DROP SCHEMA IF EXISTS test_workflow CASCADE;"

Workflow 4: Validate Specific DDL Formatting

Purpose: Understand how PostgreSQL formats specific constructs

Steps:

1. Create object with specific feature:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
CREATE TABLE test_pk_order (
    b INTEGER,
    a INTEGER,
    c INTEGER,
    PRIMARY KEY (a, b)  -- Note: different order than column definition
);
"

2. Check how PostgreSQL stores it:

# Use \d+ to see structure
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "\d+ test_pk_order"

3. See pg_dump format:

PGPASSWORD='testpwd1' pg_dump -h localhost -p 5432 -U postgres -d postgres --schema-only --table=test_pk_order

4. Query system catalogs directly:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT
    c.relname as table_name,
    con.conname as constraint_name,
    pg_get_constraintdef(con.oid) as constraint_def
FROM pg_constraint con
JOIN pg_class c ON con.conrelid = c.oid
WHERE c.relname = 'test_pk_order';
"

5. Compare with pgschema:

./pgschema dump --schema public | grep -A 10 "test_pk_order"

Workflow 5: Cross-Version Testing

Purpose: Validate behavior across PostgreSQL versions 14-17

Steps:

1. Check current version:

PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT version();"

2. Run version-specific integration tests:

# Test against specific version
PGSCHEMA_POSTGRES_VERSION=14 go test -v ./cmd/dump -run TestDumpCommand_Employee
PGSCHEMA_POSTGRES_VERSION=17 go test -v ./cmd/dump -run TestDumpCommand_Employee

3. Check for version-specific features:

# PostgreSQL 15+ feature: UNIQUE NULLS NOT DISTINCT
PGPASSWORD='testpwd1' psql -h localhost -p 5432 -U postgres -d postgres -c "
SELECT version();
CREATE TABLE test_nulls (
    id INTEGER,
    email TEXT UNIQUE NULLS NOT DISTINCT
);
"

Useful System Catalog Queries

Inspect Tables and Columns

-- All tables in schema
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public';

-- Columns with types
SELECT
    a.attname as column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type,
    a.attnotnull as not_null,
    pg_get_expr(ad.adbin, ad.adrelid) as default_value,
    a.attgenerated as generated
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
WHERE a.attrelid = 'public.employees'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Inspect Constraints

-- All constraints on a table
SELECT
    con.conname as constraint_name,
    con.contype as constraint_type,
    pg_get_constraintdef(con.oid) as definition
FR

---

*Content truncated.*