name: Run Tests description: Run pgschema automated tests (go test) to validate diff logic, plan generation, and dump functionality using test fixtures

Run Tests

Use this skill to run pgschema tests for validating implementation changes. Tests cover diff logic, plan generation, dump functionality, and end-to-end migration workflows.

When to Use This Skill

Invoke this skill when:

After implementing new schema object support
After fixing bugs in diff or plan generation
After modifying dump logic
Before committing changes
When debugging test failures
When regenerating expected test outputs
When adding new test cases
When validating changes across PostgreSQL versions

Test Categories

1. Diff Tests (Fast - No Database Required)

Purpose: Test schema comparison logic without needing a live database

Command:

go test -v ./internal/diff -run TestDiffFromFiles

What it tests:

Compares old.sql vs new.sql from testdata/diff/
Generates migration DDL
Validates against expected.sql
Pure logic testing - no database required

Speed: Very fast (~1-2 seconds)

2. Plan/Apply Integration Tests

Purpose: Test full workflow with embedded PostgreSQL

Command:

go test -v ./cmd -run TestPlanAndApply

What it tests:

Creates test database with embedded-postgres
Applies old.sql schema
Generates plan by comparing new.sql with database
Applies the plan
Verifies final state matches expected schema

Speed: Slower (~30-60 seconds for all tests)

3. Dump Tests

Purpose: Test schema extraction from live databases

Command:

go test -v ./cmd/dump -run TestDumpCommand

What it tests:

Dumps schema from test databases (employee, sakila, etc.)
Validates output format
Tests database introspection logic

Speed: Medium (~10-20 seconds)

Common Test Workflows

Workflow 1: Test Specific Feature (Scoped Testing)

Use PGSCHEMA_TEST_FILTER to run specific test cases:

Pattern: PGSCHEMA_TEST_FILTER="path/to/test" go test ...

Examples:

# Test specific diff case
PGSCHEMA_TEST_FILTER="create_view/add_view_array_operators" go test -v ./internal/diff -run TestDiffFromFiles

# Test all view-related diffs
PGSCHEMA_TEST_FILTER="create_view/" go test -v ./internal/diff -run TestDiffFromFiles

# Test all trigger-related integration tests
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply

# Test specific trigger case
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_when_distinct" go test -v ./cmd -run TestPlanAndApply

Test filter paths (from testdata/diff/):

comment/ - Comment operations (8 test cases)
create_domain/ - Domain types (3 test cases)
create_function/ - Functions (4 test cases)
create_index/ - Indexes (1 test case)
create_materialized_view/ - Materialized views (3 test cases)
create_policy/ - RLS policies (8 test cases)
create_procedure/ - Procedures (3 test cases)
create_sequence/ - Sequences (3 test cases)
create_table/ - Tables (40 test cases)
create_trigger/ - Triggers (7 test cases)
create_type/ - Custom types (3 test cases)
create_view/ - Views (6 test cases)
dependency/ - Dependencies (3 test cases)
online/ - Online migrations (12 test cases)
migrate/ - Complex migrations (6 test cases)

Workflow 2: Regenerate Expected Output

When implementation changes intentionally modify generated DDL:

Command:

PGSCHEMA_TEST_FILTER="path/to/test" go test -v ./cmd -run TestPlanAndApply --generate

Example:

# After fixing trigger DDL generation
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply --generate

What --generate does:

Runs the test normally
Overwrites expected.sql with actual generated output
Use when you've intentionally changed how DDL is generated
Warning: Only use when you're sure the new output is correct!

Typical use cases:

Improved DDL formatting
Added support for new syntax
Fixed incorrect DDL generation
Changed normalization logic

Verification steps after --generate:

Review the diff in git: git diff testdata/diff/path/to/test/expected.sql
Ensure changes are intentional and correct
Run test again without --generate to verify it passes
Commit the updated expected.sql

Workflow 3: Test Across PostgreSQL Versions

Test against different PostgreSQL versions (14-18):

Command:

PGSCHEMA_POSTGRES_VERSION=<version> go test -v ./cmd -run <test>

Examples:

# Test dump on PostgreSQL 14
PGSCHEMA_POSTGRES_VERSION=14 go test -v ./cmd/dump -run TestDumpCommand_Employee

# Test dump on PostgreSQL 17
PGSCHEMA_POSTGRES_VERSION=17 go test -v ./cmd/dump -run TestDumpCommand_Employee

# Test plan/apply on PostgreSQL 15
PGSCHEMA_POSTGRES_VERSION=15 PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply

Supported versions: 14, 15, 16, 17, 18

Workflow 4: Run All Tests

Before committing changes:

# Quick check - diff tests only (fast)
go test -v ./internal/diff -run TestDiffFromFiles

# Full validation - all integration tests (slow)
go test -v ./...

# Skip integration tests - unit tests only (fast)
go test -short -v ./...

Workflow 5: Continuous Testing During Development

Watch mode (requires external tool like entr):

# Install entr (macOS)
brew install entr

# Watch Go files and re-run tests on change
find . -name "*.go" | entr -c go test -v ./internal/diff -run TestDiffFromFiles

# Watch specific test case
find . -name "*.go" | entr -c sh -c 'PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_when_distinct" go test -v ./internal/diff -run TestDiffFromFiles'

Workflow 6: Debug Failing Test

Steps:

Run failing test with verbose output:

PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply

Check test data files:

# View old schema
cat testdata/diff/create_trigger/add_trigger/old.sql

# View new schema
cat testdata/diff/create_trigger/add_trigger/new.sql

# View expected migration
cat testdata/diff/create_trigger/add_trigger/expected.sql

Run with debugger (optional):

# Using delve
dlv test ./internal/diff -- -test.run TestDiffFromFiles

Isolate the issue:

# Test just the diff logic (faster iteration)
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./internal/diff -run TestDiffFromFiles

# Test full integration if diff test passes
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply

Compare actual vs expected:

# The test will show the diff in output, or manually:
# Run test to generate actual output, then compare
# (Actual output is shown in test failure message)

Test Structure

Diff Test Structure

Located in testdata/diff/<category>/<test_name>/:

testdata/diff/create_trigger/add_trigger/
├── old.sql       # Starting schema state
├── new.sql       # Desired schema state
└── expected.sql  # Expected migration DDL

Test process:

Parse old.sql into IR
Parse new.sql into IR
Diff the two IRs
Generate migration DDL
Compare with expected.sql

Integration Test Structure

Same test data, different process:

Create test database with embedded-postgres
Apply old.sql to database
Parse new.sql into IR
Inspect database into IR
Diff database IR vs new IR
Generate plan (migration DDL)
Apply plan to database
Verify final state

Adding New Test Cases

Step 1: Create Test Directory

mkdir -p testdata/diff/create_trigger/add_trigger_new_feature

Step 2: Create old.sql

cat > testdata/diff/create_trigger/add_trigger_new_feature/old.sql << 'EOF'
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    data TEXT
);

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

Step 3: Create new.sql

cat > testdata/diff/create_trigger/add_trigger_new_feature/new.sql << 'EOF'
CREATE TABLE test_table (
    id INTEGER PRIMARY KEY,
    data TEXT
);

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

CREATE TRIGGER my_trigger
    BEFORE INSERT ON test_table
    FOR EACH ROW
    WHEN (NEW.data IS NOT NULL)
    EXECUTE FUNCTION trigger_func();
EOF

Step 4: Generate expected.sql

Option A: Use --generate flag:

PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_new_feature" go test -v ./cmd -run TestPlanAndApply --generate

Option B: Manually create:

cat > testdata/diff/create_trigger/add_trigger_new_feature/expected.sql << 'EOF'
CREATE TRIGGER my_trigger
    BEFORE INSERT ON test_table
    FOR EACH ROW
    WHEN ((NEW.data IS NOT NULL))
    EXECUTE FUNCTION trigger_func();
EOF

Step 5: Run Test

# Test diff logic
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_new_feature" go test -v ./internal/diff -run TestDiffFromFiles

# Test full integration
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_new_feature" go test -v ./cmd -run TestPlanAndApply

Step 6: Verify and Commit

git add testdata/diff/create_trigger/add_trigger_new_feature/
git commit -m "test: add test case for trigger with new feature"

Common Test Commands Reference

Diff Tests

# All diff tests
go test -v ./internal/diff -run TestDiffFromFiles

# Specific category
PGSCHEMA_TEST_FILTER="create_table/" go test -v ./internal/diff -run TestDiffFromFiles

# Specific test
PGSCHEMA_TEST_FILTER="create_table/add_column_generated" go test -v ./internal/diff -run TestDiffFromFiles

Integration Tests

# All integration tests
go test -v ./cmd -run TestPlanAndApply

# Specific category
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply

# Specific test with timeout (for slow tests)
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply -timeout 2m

# With regeneration
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply --generate

Dump Tests

# All dump tests
go test -v ./cmd/dump -run TestDumpCommand

# Specific database
go test -v ./cmd/dump -run TestDumpCommand_Employee

# With specific PostgreSQL version
PGSCHEMA_POSTGRES_VERSION=17 go test -v ./cmd/dump -run TestDumpCommand_Employee

All Tests

# Everything (slow)
go test -v ./...

# Unit tests only (fast - no embedded-postgres)
go test -short -v ./...

# Specific package
go test -v ./internal/diff/...
go test -v ./cmd/...
go test -v ./ir/...

Test Timeouts

Some integration tests may take longer, especially with embedded-postgres:

# Default timeout: 2 minutes
go test -v ./cmd -run TestPlanAndApply

# Extended timeout: 5 minutes
go test -v ./cmd -run TestPlanAndApply -timeout 5m

# Specific slow test
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply -timeout 5m

Understanding Test Failures

Diff Test Failure

--- FAIL: TestDiffFromFiles/create_trigger/add_trigger (0.00s)
    Expected:
    CREATE TRIGGER my_trigger BEFORE INSERT ON test_table

    Actual:
    CREATE TRIGGER my_trigger AFTER INSERT ON test_table

What this means: The generated migration DDL doesn't match expected.sql

How to fix:

Check if the actual output is correct
If correct: Update expected.sql (or use --generate)
If incorrect: Fix the diff logic in internal/diff/trigger.go

Integration Test Failure

--- FAIL: TestPlanAndApply/create_trigger/add_trigger (2.34s)
    Error: trigger 'my_trigger' not found in final schema

What this means: The migration was applied but final state doesn't match expected

How to fix:

Check if the plan SQL is correct
Verify the SQL is valid PostgreSQL
Check if the apply logic executed properly
Inspect database state manually using test_db skill

Timeout Failure

panic: test timed out after 2m0s

What this means: Test took too long (usually embedded-postgres startup)

How to fix:

# Increase timeout
PGSCHEMA_TEST_FILTER="slow_test" go test -v ./cmd -run TestPlanAndApply -timeout 5m

Test Environment Variables

Variable	Purpose	Example
`PGSCHEMA_TEST_FILTER`	Run specific test cases	`"create_trigger/"`
`PGSCHEMA_POSTGRES_VERSION`	Test specific PG version	`14`, `15`, `16`, `17`, `18`
`PGHOST`, `PGPORT`, `PGUSER`, etc.	Database connection (if not using embedded)	See `.env`

Best Practices

Before Committing

Run relevant tests:

# If you modified trigger logic
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./internal/diff -run TestDiffFromFiles
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply

Run full test suite:

go test -v ./...

Check for unintended changes:

git status
# Ensure only intended test files changed

When Adding Features

Start with diff test (faster iteration):

# Create test case
mkdir -p testdata/diff/create_feature/test_name

# Add old.sql, new.sql, expected.sql

# Test
PGSCHEMA_TEST_FILTER="create_feature/test_name" go test -v ./internal/diff -run TestDiffFromFiles

Then integration test:

PGSCHEMA_TEST_FILTER="create_feature/test_name" go test -v ./cmd -run TestPlanAndApply

Test across versions:

PGSCHEMA_POSTGRES_VERSION=14 PGSCHEMA_TEST_FILTER="create_feature/" go test -v ./cmd -run TestPlanAndApply
PGSCHEMA_POSTGRES_VERSION=17 PGSCHEMA_TEST_FILTER="create_feature/" go test -v ./cmd -run TestPlanAndApply

When Fixing Bugs

Create failing test first:

# Add test case that reproduces bug
mkdir -p testdata/diff/category/bug_reproduction
# Add old.sql, new.sql, expected.sql

# Verify it fails
PGSCHEMA_TEST_FILTER="category/bug_reproduction" go test -v ./internal/diff -run TestDiffFromFiles

Fix the bug:

# Modify code in internal/diff/ or ir/

Verify test passes:

PGSCHEMA_TEST_FILTER="category/bug_reproduction" go test -v ./internal/diff -run TestDiffFromFiles
PGSCHEMA_TEST_FILTER="category/bug_reproduction" go test -v ./cmd -run TestPlanAndApply

Run related tests:

PGSCHEMA_TEST_FILTER="category/" go test -v ./...

Quick Reference

Most common commands:

# Fast diff test for specific feature
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./internal/diff -run TestDiffFromFiles

# Full integration test for specific feature
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply

# Regenerate expected output (after intentional change)
PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply --generate

# Test all triggers
PGSCHEMA_TEST_FILTER="create_trigger/" go test -v ./cmd -run TestPlanAndApply

# Test everything (before commit)
go test -v ./...

# Dump tests
go test -v ./cmd/dump -run TestDumpCommand

Verification Checklist

Before committing changes:

Ran diff tests for affected areas
Ran integration tests for affected areas
Tests pass on at least one PostgreSQL version
If intentionally changed DDL, updated expected.sql files
New features have test coverage
Bug fixes have regression tests
No unintended test file modifications
All tests pass: go test -v ./...

run-tests

Installation

Details

Usage

Skill Instructions

name: Run Tests description: Run pgschema automated tests (go test) to validate diff logic, plan generation, and dump functionality using test fixtures

Run Tests

When to Use This Skill

Test Categories

1. Diff Tests (Fast - No Database Required)

2. Plan/Apply Integration Tests

3. Dump Tests

Common Test Workflows

Workflow 1: Test Specific Feature (Scoped Testing)

Workflow 2: Regenerate Expected Output

Workflow 3: Test Across PostgreSQL Versions

Workflow 4: Run All Tests

Workflow 5: Continuous Testing During Development

Workflow 6: Debug Failing Test

Test Structure

Diff Test Structure

Integration Test Structure

Adding New Test Cases

Step 1: Create Test Directory

Step 2: Create old.sql

Step 3: Create new.sql

Step 4: Generate expected.sql

Step 5: Run Test

Step 6: Verify and Commit

Common Test Commands Reference

Diff Tests

Integration Tests

Dump Tests

All Tests

Test Timeouts

Understanding Test Failures

Diff Test Failure

Integration Test Failure

Timeout Failure

Test Environment Variables

Best Practices

Before Committing

When Adding Features

When Fixing Bugs

Quick Reference

Verification Checklist

More by pgschema