Skip to content

Testing

Jump to bottom
Vivek edited this page Jul 14, 2026 · 1 revision

Testing

The migration engine uses a 6-layer testing strategy.

Test Layers

Layer Focus Method Status
1 Introspection Query accuracy ✅ 100% PASS
2 Diff Change detection ✅ 100% PASS
3 E2E Full pipeline ✅ 100% PASS
4 Planner Phase order ✅ VERIFIED
5 Execution Apply & rollback ⚠️ Partial
6 Recovery Drift, recovery ⚠️ Pending

Layer 1: Introspection Accuracy

Purpose: Verify introspected data matches actual PostgreSQL catalogs.

Test Database: PostgreSQL 18.1 with 25 tables, 4 schemas, 1052 objects.

Coverage:

Object Type Assertions Result
Tables 12 ✅
Columns 18 ✅
Indexes 14 ✅
Constraints 15 ✅
Views 8 ✅
Functions 10 ✅
Triggers 6 ✅
Policies 4 ✅
Enums 3 ✅
Composites 4 ✅
Total 93 100% PASS

Layer 2: Diff Detection

Purpose: Verify change detection accuracy.

Test Cases:

Case From To Detected
Create table none table users CREATE
Add column users(email) users(email,name) ALTER
Drop index idx_users_email none DROP
Rename table users accounts RENAME
Change type int bigint ALTER (widening)
Add FK none fk_users CREATE

Coverage:

Assert Category Status
Change type classification Diff ✅
Property-level changes Diff ✅
Rename detection Matcher ✅
Dependency order Planner ✅

Layer 3: E2E Pipeline

Purpose: End-to-end migration pipeline.

Test Schema:

CREATE SCHEMA test_schema;

CREATE TABLE test_schema.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  created TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON test_schema.users(email);

CREATE TABLE test_schema.posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES test_schema.users(id),
  title VARCHAR(200),
  content TEXT
);

CREATE POLICY users_policy ON test_schema.users
  USING (true);

Test Flow:

  1. Introspect empty database
  2. Apply initial schema
  3. Introspect again
  4. Apply diff (no changes)
  5. Introspect with changes
  6. Introspect with drift
  7. Compare results

Result: ✅ 12/12 assertions passed

Layer 4: Planner Order

Purpose: Verify migration phases are ordered correctly.

Phase Sequence: 0 → 4 → 9 → 14 → 19 → 24 → 29 → 30+

Verified phases:

  1. Schemas, Extensions (0-4)
  2. Types, Domains, Sequences (5-9)
  3. Tables, Columns (10-14)
  4. Indexes, Constraints (15-19)
  5. Functions, Triggers (20-24)
  6. Views, Materialized Views (25-29)
  7. Policies, RLS (30+)

Result: ✅ Correct dependency order

Layer 5: Execution

Purpose: Verify DDL execution and transaction handling.

Test Scenarios:

Scenario Status
CREATE TABLE ✅
ALTER COLUMN ✅
DROP TABLE ✅
CREATE INDEX CONCURRENTLY ⚠️ Manual
FK NOT VALID + VALIDATE ⚠️ Manual
Advisory lock acquisition ✅
Savepoint rollback ✅
Drift detection ✅

Layer 6: Recovery

Purpose: Test error recovery and rollback.

Test Scenarios:

Scenario Status
Partial rollback on failure ⚠️ Pending
Re-run after failure ⚠️ Pending
Drift detection in execution ✅
Lock timeout handling ⚠️ Pending
Multi-step recovery ⚠️ Pending

Running Tests

Prerequisites

# PostgreSQL 18 recommended
docker run -d --name pg18 -p 5432:5432 -e POSTGRES_PASSWORD=test postgres:18

Environment

export TEST_PG_URL="postgresql://postgres:test@localhost:5432/test"

Run All Tests

npm test

Run Specific Layer

# Layer 1: Introspection
npm run test:layer1

# Layer 2: Diff
npm run test:layer2

# Layer 3: E2E
npm run test:layer3

Coverage Report

npm run test:coverage

CI Integration

Tests run on:

  • Node.js: 18, 20, 22
  • PostgreSQL: 14, 16, 18

See .github/workflows/ci.yml

Test Database Snapshot

For reproducibility, a standard test database is used:

Metric Value
Schemas 4
Tables 25
Columns 142
Indexes 38
Constraints 31
Functions 12
Views 8
Triggers 6
Policies 4
Enums 3
Total Objects 1052

See Also

Clone this wiki locally