run_tests
提供 PostgreSQL 数据库模式变更的全流程验证能力,覆盖结构差异比对、迁移计划生成、模式导出及端到端迁移执行,支持基于测试用例的快速逻辑验证、嵌入式数据库集成测试、多版本 PostgreSQL 兼容性检查,并允许按类别或路径精准筛选、再生预期输出及调试失败用例。
| 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:
▼bash复制代码go test -v ./internal/diff -run TestDiffFromFiles
What it tests:
- Compares
old.sqlvsnew.sqlfromtestdata/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:
▼bash复制代码go test -v ./cmd -run TestPlanAndApply
What it tests:
- Creates test database with embedded-postgres
- Applies
old.sqlschema - Generates plan by comparing
new.sqlwith 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:
▼bash复制代码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:
▼bash复制代码# 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 (10 test cases)create_domain/- Domain types (3 test cases)create_function/- Functions (5 test cases)create_index/- Indexes (2 test cases)create_materialized_view/- Materialized views (3 test cases)create_policy/- RLS policies (10 test cases)create_procedure/- Procedures (3 test cases)create_sequence/- Sequences (3 test cases)create_table/- Tables (30 test cases)create_trigger/- Triggers (7 test cases)create_type/- Custom types (3 test cases)create_view/- Views (4 test cases)default_privilege/- Default privileges (8 test cases)privilege/- Privileges/permissions (10 test cases)dependency/- Dependencies (8 test cases)online/- Online migrations (12 test cases)migrate/- Complex migrations (5 test cases)
Workflow 2: Regenerate Expected Output
When implementation changes intentionally modify generated DDL:
Command:
▼bash复制代码PGSCHEMA_TEST_FILTER="path/to/test" go test -v ./cmd -run TestPlanAndApply --generate
Example:
▼bash复制代码# 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.sqlwith 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
--generateto verify it passes - Commit the updated expected.sql
Workflow 3: Test Across PostgreSQL Versions
Test against different PostgreSQL versions (14-18):
Command:
▼bash复制代码PGSCHEMA_POSTGRES_VERSION=<version> go test -v ./cmd -run <test>
Examples:
▼bash复制代码# 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:
▼bash复制代码# 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):
▼bash复制代码# 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:
▼bash复制代码PGSCHEMA_TEST_FILTER="create_trigger/add_trigger" go test -v ./cmd -run TestPlanAndApply
- Check test data files:
▼bash复制代码# 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):
▼bash复制代码# Using delve dlv test ./internal/diff -- -test.run TestDiffFromFiles
- Isolate the issue:
▼bash复制代码# 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:
▼bash复制代码# 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>/:
▼text复制代码testdata/diff/create_trigger/add_trigger/ ├── old.sql # Starting schema state ├── new.sql # Desired schema state └── expected.sql # Expected migration DDL
Test process:
- Apply
old.sqlto embedded PostgreSQL and inspect into IR - Apply
new.sqlto embedded PostgreSQL and inspect 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.sqlto database and inspect into "current state" IR - Apply
new.sqlto separate embedded-postgres and inspect into "desired state" IR - Diff current state IR vs desired state IR
- Generate plan (migration DDL)
- Apply plan to database
- Verify final state matches desired state
Adding New Test Cases
Step 1: Create Test Directory
▼bash复制代码mkdir -p testdata/diff/create_trigger/add_trigger_new_feature
Step 2: Create old.sql
▼bash复制代码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
▼bash复制代码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:
▼bash复制代码PGSCHEMA_TEST_FILTER="create_trigger/add_trigger_new_feature" go test -v ./cmd -run TestPlanAndApply --generate
Option B: Manually create:
▼bash复制代码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
▼bash复制代码# 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
▼bash复制代码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
▼bash复制代码# 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
▼bash复制代码# 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
▼bash复制代码# 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
▼bash复制代码# 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:
▼bash复制代码# 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
▼text复制代码--- 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
▼text复制代码--- 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
▼text复制代码panic: test timed out after 2m0s
What this means: Test took too long (usually embedded-postgres startup)
How to fix:
▼bash复制代码# 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:
▼bash复制代码# 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:
▼bash复制代码go test -v ./...
- Check for unintended changes:
▼bash复制代码git status # Ensure only intended test files changed
When Adding Features
- Start with diff test (faster iteration):
▼bash复制代码# 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:
▼bash复制代码PGSCHEMA_TEST_FILTER="create_feature/test_name" go test -v ./cmd -run TestPlanAndApply
- Test across versions:
▼bash复制代码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:
▼bash复制代码# 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:
▼bash复制代码# Modify code in internal/diff/ or ir/
- Verify test passes:
▼bash复制代码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:
▼bash复制代码PGSCHEMA_TEST_FILTER="category/" go test -v ./...
Quick Reference
Most common commands:
▼bash复制代码# 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 ./...









