Troubleshooting Integration Tests

Version: 1.0
Date: 2025-11-07
Audience: All Developers

Table of Contents

1. Common Errors
2. Database Issues
3. Fixture Problems
4. Performance Issues
5. CI/CD Failures
6. Debugging Techniques
7. Getting Help

Common Errors

Error: "database does not exist"

Full error:

Error: database "test_db" does not exist

Causes:

1. Test database was not created
2. Database was already cleaned up
3. Wrong database name in connection string
4. Test tried to connect before database creation completed

Solutions:

// ✅ SOLUTION 1: Verify database creation order
test('correct order', async ({ asSystem }) => {
    // 1. First create database
    await asSystem(async (client) => {
        await client.query('CREATE DATABASE test_db');
    });
    
    // 2. Then connect to it
    const pool = new Pool({ database: 'test_db' });
    // ...
});

// ✅ SOLUTION 2: Add existence check
test('check before connect', async ({ asSystem }) => {
    const exists = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT 1 FROM pg_database WHERE datname = $1
        `, ['test_db']);
        return result.rows.length > 0;
    });
    
    if (!exists) {
        throw new Error('Test database not created');
    }
});

Prevention:

• Always create database before connecting
• Use unique database names to avoid conflicts
• Add proper error handling in fixtures

---

Error: "connection refused" / "ECONNREFUSED"

Full error:

Error: connect ECONNREFUSED 127.0.0.1:5432

Causes:

1. PostgreSQL is not running
2. Wrong host/port configuration
3. Firewall blocking connection
4. PostgreSQL listening on different address

Solutions:

# Check if PostgreSQL is running
pg_isready -h localhost -p 5432

# Start PostgreSQL (macOS with Homebrew)
brew services start postgresql@14

# Start PostgreSQL (Linux systemd)
sudo systemctl start postgresql

# Check PostgreSQL status
brew services list  # macOS
sudo systemctl status postgresql  # Linux

# Check what port PostgreSQL is listening on
psql -h localhost -p 5432 -U postgres -c "SHOW port;"

Environment check:

# Verify environment variables
echo $POSTGRES_HOST
echo $POSTGRES_PORT
echo $DATABASE_URL

# Test manual connection
psql -h localhost -p 5432 -U postgres -d postgres

In tests:

test('diagnose connection', async ({ asSystem }) => {
    try {
        await asSystem(async (client) => {
            const result = await client.query('SELECT version()');
            console.log('PostgreSQL version:', result.rows[0].version);
        });
    } catch (error) {
        console.error('Connection failed:', error);
        console.log('Check:');
        console.log('1. Is PostgreSQL running? (pg_isready)');
        console.log('2. Environment variables set? (.env.test)');
        console.log('3. Correct host/port?');
        throw error;
    }
});

Prevention:

• Add PostgreSQL to startup items
• Document setup in team wiki
• Add connection health check to test setup
• Use docker-compose for consistent environment

---

Error: "permission denied for schema"

Full error:

Error: permission denied for schema public

Causes:

1. User doesn't have schema permissions
2. Wrong role being used
3. RLS policies blocking access
4. Missing GRANT statements

Solutions:

// Check current user and permissions
test('check permissions', async ({ asSystem }) => {
    await asSystem(async (client) => {
        // Check current user
        const user = await client.query('SELECT current_user');
        console.log('Current user:', user.rows[0].current_user);
        
        // Check schema permissions
        const perms = await client.query(`
            SELECT 
                schema_name,
                schema_owner,
                has_schema_privilege(current_user, schema_name, 'USAGE') as can_use,
                has_schema_privilege(current_user, schema_name, 'CREATE') as can_create
            FROM information_schema.schemata
            WHERE schema_name NOT LIKE 'pg_%'
            AND schema_name != 'information_schema'
        `);
        console.log('Schema permissions:', perms.rows);
    });
});

// Grant necessary permissions
test('grant permissions', async ({ asSystem }) => {
    await asSystem(async (client) => {
        await client.query(`
            GRANT USAGE ON SCHEMA public TO studio_user;
            GRANT CREATE ON SCHEMA public TO studio_user;
        `);
    });
});

Prevention:

• Ensure migration files include proper GRANT statements
• Use asSystem for infrastructure setup
• Document required permissions in setup guide

---

Error: "relation does not exist"

Full error:

Error: relation "public.users" does not exist

Causes:

1. Table not created yet
2. Migrations not run
3. Wrong schema name
4. Transaction rolled back

Solutions:

// Check if table exists
test('verify table exists', async ({ asSystem }) => {
    const exists = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT EXISTS (
                SELECT FROM information_schema.tables
                WHERE table_schema = 'public'
                AND table_name = 'users'
            )
        `);
        return result.rows[0].exists;
    });
    
    if (!exists) {
        throw new Error('Table "users" does not exist. Run migrations?');
    }
});

// List available tables
test('list tables', async ({ asSystem }) => {
    const tables = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT schemaname, tablename
            FROM pg_tables
            WHERE schemaname NOT LIKE 'pg_%'
            AND schemaname != 'information_schema'
            ORDER BY schemaname, tablename
        `);
        return result.rows;
    });
    console.log('Available tables:', tables);
});

Prevention:

• Run migrations before tests
• Add table existence checks in test setup
• Use proper schema qualification (e.g., public.users)

---

Error: "fixture lifecycle error"

Full error:

Error: Fixture "myFixture" was not properly cleaned up

Causes:

1. Missing use() call in fixture
2. Exception thrown before cleanup
3. Incorrect fixture structure
4. Missing try/finally

Solutions:

// ❌ WRONG - Missing use() call
const badFixture = async ({}, use) => {
    const resource = await createResource();
    // Missing await use(resource)!
    await cleanup(resource);
};

// ✅ CORRECT - Proper fixture structure
const goodFixture = async ({}, use) => {
    const resource = await createResource();
    
    try {
        await use(resource);  // Must call use()!
    } finally {
        await cleanup(resource);  // Always runs
    }
};

// ✅ CORRECT - Simple fixture
const simpleFixture = async ({}, use) => {
    const resource = await createResource();
    await use(resource);
    await cleanup(resource);  // Runs even if test fails
};

Prevention:

• Always call await use(resource)
• Use try/finally for guaranteed cleanup
• Test fixture cleanup with failing tests

---

Error: "too many clients"

Full error:

Error: sorry, too many clients already

Causes:

1. Connection pool exhausted
2. Connections not being returned
3. Tests running in parallel without limits
4. Connection leaks in code

Solutions:

// Check active connections
test('check connections', async ({ asSystem }) => {
    const conns = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT 
                datname,
                count(*) as connections,
                max(now() - query_start) as longest_query
            FROM pg_stat_activity
            WHERE datname IS NOT NULL
            GROUP BY datname
            ORDER BY connections DESC
        `);
        return result.rows;
    });
    console.log('Active connections:', conns);
});

// Limit parallel tests in vitest.config.ts
export default defineConfig({
    test: {
        maxConcurrency: 5,  // Limit parallel tests
        fileParallelism: false,  // Run test files sequentially
    }
});

Prevention:

• Always return connections to pool
• Use fixtures for automatic cleanup
• Limit test parallelism
• Monitor connection count

---

Database Issues

Connection Pool Exhaustion

Symptoms:

• Tests hang waiting for connections
• "too many clients" errors
• Slow test execution

Diagnosis:

test('diagnose pool exhaustion', async ({ asSystem }) => {
    // Check pool status
    await asSystem(async (client) => {
        const result = await client.query(`
            SELECT 
                count(*) as total_connections,
                count(*) FILTER (WHERE state = 'active') as active,
                count(*) FILTER (WHERE state = 'idle') as idle,
                count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
            FROM pg_stat_activity
            WHERE datname = current_database()
        `);
        console.log('Connection pool status:', result.rows[0]);
        
        // Idle in transaction is bad - indicates connection leak
        if (parseInt(result.rows[0].idle_in_transaction) > 0) {
            console.warn('⚠️ Connections stuck in transaction!');
        }
    });
});

Solutions:

1. Reduce test parallelism
2. Increase pool size (if appropriate)
3. Find and fix connection leaks
4. Use fixtures that guarantee cleanup

---

Transaction Deadlocks

Symptoms:

Error: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678

Causes:

• Two tests modifying same data in different order
• Parallel tests with insufficient isolation

Solutions:

// ✅ Use row-level locking
test('avoid deadlock', async ({ asUser }) => {
    await asUser('user-123', 'org-456', async (client) => {
        await client.query('BEGIN');
        
        // Lock rows in consistent order (by ID)
        await client.query(`
            SELECT * FROM items 
            WHERE id IN ($1, $2)
            ORDER BY id  -- Consistent order prevents deadlock
            FOR UPDATE
        `, [id1, id2]);
        
        // Update rows
        await client.query('UPDATE items SET ... WHERE id = $1', [id1]);
        await client.query('UPDATE items SET ... WHERE id = $2', [id2]);
        
        await client.query('COMMIT');
    });
});

// ✅ Use per-test isolation
test('use unique data', async ({ asUser }) => {
    // Each test creates its own data
    const uniqueId = `test_${Date.now()}_${Math.random()}`;
    // No conflicts with other tests
});

Prevention:

• Lock rows in consistent order
• Use per-test data isolation
• Reduce test parallelism
• Use shorter transactions

---

Migration Failures

Symptoms:

• SQL syntax errors during migration
• Tables not created
• Constraints failing

Diagnosis:

test('debug migration', async ({ emptyDatabase }) => {
    try {
        // Run migrations one at a time
        for (const file of migrationFiles) {
            console.log(`Running ${file}...`);
            await runMigrationFile(emptyDatabase.client, file);
            console.log(`✓ ${file} completed`);
        }
    } catch (error) {
        console.error('Migration failed:', error);
        
        // Check what was created
        const tables = await emptyDatabase.client.query(`
            SELECT schemaname, tablename
            FROM pg_tables
            WHERE schemaname NOT LIKE 'pg_%'
            ORDER BY schemaname, tablename
        `);
        console.log('Tables created so far:', tables.rows);
        
        throw error;
    }
});

Common issues:

1. Missing semicolons - Ensure each statement ends with ;
2. Dependency order - Create tables before foreign keys
3. Idempotency - Use IF NOT EXISTS clauses
4. Syntax errors - Test SQL files individually with psql

---

Cleanup Problems

Symptoms:

• Test databases not being deleted
• Orphaned test data
• Disk space filling up

Manual cleanup:

-- Find test databases
SELECT datname 
FROM pg_database 
WHERE datname LIKE 'test_%'
ORDER BY datname;

-- Terminate all connections to test databases
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname LIKE 'test_%'
AND pid <> pg_backend_pid();

-- Drop test databases
DO $$
DECLARE
    db_name TEXT;
BEGIN
    FOR db_name IN 
        SELECT datname FROM pg_database WHERE datname LIKE 'test_%'
    LOOP
        EXECUTE 'DROP DATABASE IF EXISTS ' || quote_ident(db_name);
        RAISE NOTICE 'Dropped database: %', db_name;
    END LOOP;
END $$;

Automated cleanup:

// Add to test setup
afterAll(async () => {
    // Cleanup any remaining test databases
    const pool = new Pool({
        host: process.env.POSTGRES_HOST,
        user: process.env.POSTGRES_USER,
        password: process.env.POSTGRES_PASSWORD,
        database: 'postgres',
    });
    
    const client = await pool.connect();
    try {
        const result = await client.query(`
            SELECT datname FROM pg_database 
            WHERE datname LIKE 'test_%'
            AND age(now(), (SELECT stats_reset FROM pg_stat_database WHERE datname = datname)) > interval '1 hour'
        `);
        
        for (const row of result.rows) {
            await client.query(`
                SELECT pg_terminate_backend(pid)
                FROM pg_stat_activity
                WHERE datname = $1
            `, [row.datname]);
            
            await client.query(`DROP DATABASE IF EXISTS ${row.datname}`);
            console.log(`Cleaned up stale test database: ${row.datname}`);
        }
    } finally {
        client.release();
        await pool.end();
    }
});

---

Fixture Problems

Fixture Not Available

Error:

test('example', async ({ myFixture }) => {
    // Error: Property 'myFixture' does not exist
});

Causes:

1. Fixture not exported from test.ts
2. Using base test instead of extended test
3. Typo in fixture name
4. Wrong test import

Solutions:

// ✅ CORRECT - Import extended test
import test from './test';  // Extended test with fixtures

test('uses fixture', async ({ myFixture }) => {
    // myFixture is available
});

// ❌ WRONG - Import base test
import { test } from 'vitest';  // Base test without fixtures

test('missing fixture', async ({ myFixture }) => {
    // Error: myFixture doesn't exist on base test
});

---

Fixture Dependency Errors

Error:

Error: Circular fixture dependency detected

Cause:

// ❌ Circular dependency
const test = baseTest.extend({
    fixtureA: async ({ fixtureB }, use) => {
        await use(await createA(fixtureB));
    },
    fixtureB: async ({ fixtureA }, use) => {
        await use(await createB(fixtureA));
    },
});

Solution:

// ✅ Resolve dependency order
const test = baseTest.extend({
    // Base fixture (no dependencies)
    database: async ({}, use) => {
        const db = await createDatabase();
        await use(db);
    },
    
    // Depends on database
    fixtureA: async ({ database }, use) => {
        await use(await createA(database));
    },
    
    // Depends on fixtureA
    fixtureB: async ({ fixtureA }, use) => {
        await use(await createB(fixtureA));
    },
});

---

Cleanup Not Running

Problem:

test('cleanup issue', async ({ myFixture }) => {
    // Test fails here
    expect(false).toBe(true);
    
    // Cleanup after use() should still run, but doesn't seem to
});

Common causes:

1. Missing try/finally:

// ❌ Cleanup may not run on error
myFixture: async ({}, use) => {
    const resource = await create();
    await use(resource);
    await cleanup(resource);  // Skipped if use() throws
}

// ✅ Cleanup always runs
myFixture: async ({}, use) => {
    const resource = await create();
    try {
        await use(resource);
    } finally {
        await cleanup(resource);  // Always runs
    }
}

2. Test timeout:

// ✅ Increase timeout if cleanup is slow
test('slow cleanup', async ({ myFixture }) => {
    // Test code
}, { timeout: 30000 });  // 30 second timeout

---

Performance Issues

Slow Tests

Symptoms:

• Test suite takes minutes to complete
• Individual tests taking >1 second

Profiling:

test('profile test', async ({ asSystem }) => {
    const start = Date.now();
    
    // Setup
    const setupStart = Date.now();
    await setupTestData();
    console.log('Setup:', Date.now() - setupStart, 'ms');
    
    // Query 1
    const q1Start = Date.now();
    await asSystem(async (client) => {
        await client.query('SELECT * FROM large_table');
    });
    console.log('Query 1:', Date.now() - q1Start, 'ms');
    
    // Query 2
    const q2Start = Date.now();
    await asSystem(async (client) => {
        await client.query('SELECT * FROM another_table WHERE ...');
    });
    console.log('Query 2:', Date.now() - q2Start, 'ms');
    
    console.log('Total:', Date.now() - start, 'ms');
});

Common solutions:

// ✅ Add indexes for test queries
beforeAll(async ({ asSystem }) => {
    await asSystem(async (client) => {
        await client.query(`
            CREATE INDEX IF NOT EXISTS idx_items_status 
            ON items(status)
        `);
    });
});

// ✅ Use smaller test datasets
const testData = items.slice(0, 10);  // Not all 10,000 items

// ✅ Batch operations
await client.query(`
    INSERT INTO items (name) VALUES 
    ($1), ($2), ($3)
`, [name1, name2, name3]);

// Instead of:
await client.query('INSERT INTO items (name) VALUES ($1)', [name1]);
await client.query('INSERT INTO items (name) VALUES ($1)', [name2]);
await client.query('INSERT INTO items (name) VALUES ($1)', [name3]);

---

Memory Leaks

Symptoms:

• Tests slow down over time
• Memory usage increases
• "JavaScript heap out of memory"

Diagnosis:

# Run with heap profiler
node --expose-gc --max-old-space-size=4096 node_modules/vitest/vitest.mjs

# Monitor memory in tests

test('check memory', async () => {
    const memBefore = process.memoryUsage();
    
    // Run test operations
    await heavyOperation();
    
    // Force GC if available
    if (global.gc) {
        global.gc();
    }
    
    const memAfter = process.memoryUsage();
    const leakMB = (memAfter.heapUsed - memBefore.heapUsed) / 1024 / 1024;
    
    if (leakMB > 50) {
        console.warn(`⚠️ Possible memory leak: ${leakMB.toFixed(2)} MB`);
    }
});

Common causes:

1. Not closing database connections
2. Large objects in test scope
3. Event listeners not removed
4. Circular references

---

Connection Leaks

Symptoms:

• Connection pool fills up
• Tests hang waiting for connections
• Database has many idle connections

Detection:

test('detect connection leak', async ({ asSystem }) => {
    const countBefore = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT count(*) FROM pg_stat_activity 
            WHERE datname = current_database()
        `);
        return parseInt(result.rows[0].count);
    });
    
    // Run test operations
    await testOperations();
    
    const countAfter = await asSystem(async (client) => {
        const result = await client.query(`
            SELECT count(*) FROM pg_stat_activity 
            WHERE datname = current_database()
        `);
        return parseInt(result.rows[0].count);
    });
    
    if (countAfter > countBefore) {
        console.warn(`⚠️ Connection leak: ${countAfter - countBefore} connections`);
    }
});

Solutions:

• Use fixtures that guarantee cleanup
• Always call client.release() or pool.end()
• Use try/finally blocks

---

CI/CD Failures

Tests Pass Locally, Fail in CI

Common causes:

1. Environment differences:

// Check environment in CI
test('diagnose CI environment', async ({ asSystem }) => {
    console.log('Node version:', process.version);
    console.log('Platform:', process.platform);
    console.log('Environment:', {
        POSTGRES_HOST: process.env.POSTGRES_HOST,
        POSTGRES_PORT: process.env.POSTGRES_PORT,
        NODE_ENV: process.env.NODE_ENV,
    });
    
    await asSystem(async (client) => {
        const result = await client.query('SELECT version()');
        console.log('PostgreSQL:', result.rows[0].version);
    });
});

2. Timing issues:

// ❌ Flaky - depends on timing
test('flaky test', async ({ asSystem }) => {
    await startBackgroundProcess();
    await asSystem(async (client) => {
        // Background process might not be ready yet
        const result = await client.query('SELECT * FROM processed_items');
        expect(result.rows.length).toBeGreaterThan(0);
    });
});

// ✅ Robust - wait for condition
test('robust test', async ({ asSystem }) => {
    await startBackgroundProcess();
    
    // Wait for background process to complete
    await waitFor(async () => {
        const result = await asSystem(async (client) => {
            return await client.query('SELECT COUNT(*) FROM processed_items');
        });
        return parseInt(result.rows[0].count) > 0;
    }, { timeout: 5000 });
});

3. Resource constraints:

# .gitlab-ci.yml or similar
test:
  script:
    - pnpm test
  variables:
    # Reduce parallelism for CI
    VITEST_MAX_CONCURRENCY: 3
    # Increase timeouts
    VITEST_TEST_TIMEOUT: 60000

---

Flaky Tests

Definition: Tests that sometimes pass, sometimes fail

Common causes:

1. Non-deterministic data:

// ❌ Flaky - uses NOW()
test('flaky timestamp', async ({ asSystem }) => {
    await asSystem(async (client) => {
        await client.query(`
            INSERT INTO events (timestamp) VALUES (NOW())
        `);
        
        const result = await client.query(`
            SELECT * FROM events 
            WHERE timestamp > NOW() - interval '1 second'
        `);
        expect(result.rows.length).toBe(1);  // Might fail!
    });
});

// ✅ Deterministic - fixed timestamp
test('stable timestamp', async ({ asSystem }) => {
    const fixedTime = '2024-01-01T00:00:00Z';
    await asSystem(async (client) => {
        await client.query(`
            INSERT INTO events (timestamp) VALUES ($1)
        `, [fixedTime]);
        
        const result = await client.query(`
            SELECT * FROM events WHERE timestamp = $1
        `, [fixedTime]);
        expect(result.rows.length).toBe(1);  // Always works
    });
});

2. Race conditions:

// ❌ Flaky - parallel tests interfere
test('flaky count', async ({ asSystem }) => {
    const result = await asSystem(async (client) => {
        return await client.query('SELECT COUNT(*) FROM items');
    });
    expect(result.rows[0].count).toBe('5');  // Other tests may add items
});

// ✅ Stable - uses isolated data
test('stable count', async ({ asSystem }) => {
    const testId = `test_${Date.now()}`;
    await asSystem(async (client) => {
        await client.query('INSERT INTO items (id) VALUES ($1)', [testId]);
    });
    
    const result = await asSystem(async (client) => {
        return await client.query('SELECT COUNT(*) FROM items WHERE id = $1', [testId]);
    });
    expect(result.rows[0].count).toBe('1');  // Only our item
});

---

Debugging Techniques

Enable Debug Logging

// Enable PostgreSQL query logging
test('with query logging', async ({ asSystem }) => {
    await asSystem(async (client) => {
        // Enable query logging
        await client.query('SET log_statement = \'all\'');
        await client.query('SET log_duration = on');
        
        // Your queries - will be logged
        await client.query('SELECT * FROM items');
        
        // Reset
        await client.query('RESET log_statement');
        await client.query('RESET log_duration');
    });
});

// Vitest debug mode
// Run with: DEBUG=* pnpm test
import debug from 'debug';
const log = debug('test:mytest');

test('with debug', async ({ asSystem }) => {
    log('Starting test');
    await asSystem(async (client) => {
        log('Executing query');
        await client.query('SELECT 1');
        log('Query complete');
    });
});

Using console.log Effectively

test('effective logging', async ({ asSystem }) => {
    console.log('\n=== Test: effective logging ===');
    
    // Log inputs
    const input = { userId: 'user-123', action: 'create' };
    console.log('Input:', JSON.stringify(input, null, 2));
    
    // Log intermediate results
    const result = await asSystem(async (client) => {
        console.log('Executing query...');
        const r = await client.query('SELECT * FROM items WHERE user_id = $1', [input.userId]);
        console.log(`Found ${r.rows.length} items`);
        return r;
    });
    
    // Log outputs
    console.log('Output:', JSON.stringify(result.rows, null, 2));
    
    console.log('=== Test complete ===\n');
});

VSCode Debugging

Add to .vscode/launch.json:

{
    "version": "0.2.0",
    "configurations": [
        {
            "type": "node",
            "request": "launch",
            "name": "Debug Vitest Tests",
            "runtimeExecutable": "pnpm",
            "runtimeArgs": [
                "test",
                "--run",
                "--threads",
                "false",
                "${file}"
            ],
            "console": "integratedTerminal",
            "internalConsoleOptions": "neverOpen"
        }
    ]
}

Set breakpoints in VSCode, then press F5 to debug.

Database Query Logging

// Log all queries during test
import { Pool } from 'pg';

const pool = new Pool({
    // ... connection config
});

// Intercept queries
const originalQuery = pool.query.bind(pool);
pool.query = function(...args) {
    console.log('Query:', args[0]);
    if (args[1]) {
        console.log('Params:', args[1]);
    }
    return originalQuery(...args);
};

---

Getting Help

Before Asking for Help

1. Check this guide - Review relevant sections above
2. Read error messages carefully - They often contain the solution
3. Search existing issues - Someone may have had the same problem
4. Isolate the problem - Create minimal reproduction

Creating a Minimal Reproduction

// Minimal test that reproduces the issue
import test from './test';

test('minimal reproduction', async ({ asSystem }) => {
    // Simplest possible code that shows the problem
    await asSystem(async (client) => {
        const result = await client.query('SELECT 1');
        console.log(result.rows);
    });
});

Information to Include

When reporting an issue:

1. Error message - Full stack trace
2. Environment - OS, Node version, PostgreSQL version
3. Code - Minimal reproduction
4. Expected vs actual - What should happen vs what does happen
5. Steps to reproduce - Exact commands to run

Reporting Issues

Use the /reportbug command with:

Problem: [Brief description]

Error: [Full error message]

Code: [Minimal reproduction]

Environment:
- OS: macOS 13.0
- Node: v18.17.0
- PostgreSQL: 14.9
- Vitest: 1.0.0

Steps to reproduce