Database Schema Testing: Constraints, Indexes, and Data Integrity
Your application code can be perfectly correct, but if the database schema doesn't enforce the invariants you depend on, you have a latent data integrity problem waiting to surface. Unique constraints that weren't added, foreign keys that got dropped for performance reasons, nullable columns that your code assumes are always populated — these schema gaps cause bugs that are notoriously hard to diagnose because the data looks reasonable until you notice the corrupted rows from three months ago.
Schema testing closes this gap. It verifies that the database enforces the rules your application depends on, that queries use indexes rather than scanning full tables, and that the schema matches your expectations rather than drifting over time.
What Deserves a Schema Test
Not every aspect of the schema needs a test, but these categories almost always do:
Constraints: NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints encode business rules at the database level. If a constraint is missing, your application is the only thing enforcing that rule — and application enforcement has gaps (raw SQL queries, admin scripts, data imports).
Indexes: A query that works correctly but scans a full table of 10 million rows is a performance time bomb. Index tests verify both that indexes exist and that the query planner actually uses them.
Data type boundaries: Integer columns have limits, VARCHAR columns have max lengths, NUMERIC columns have precision constraints. Tests that probe these boundaries catch silent truncation and overflow bugs.
Schema diff: The actual database schema should match the schema definition in your codebase. Drift — when they diverge — is how production ends up in a state that nobody intended.
Setting Up the Test Environment
All examples use Jest with Testcontainers for a real PostgreSQL instance:
npm install --save-dev jest testcontainers @testcontainers/postgresql
npm install pg// test/helpers/db.js
const { PostgreSqlContainer } = require('@testcontainers/postgresql');
const { Client } = require('pg');
let container;
let client;
async function setup() {
container = await new PostgreSqlContainer('postgres:16-alpine').start();
client = new Client({
host: container.getHost(),
port: container.getMappedPort(5432),
database: container.getDatabase(),
user: container.getUsername(),
password: container.getPassword(),
});
await client.connect();
return client;
}
async function teardown() {
await client?.end();
await container?.stop();
}
async function exec(sql) {
return client.query(sql);
}
async function execFile(filePath) {
const sql = require('fs').readFileSync(filePath, 'utf8');
return client.query(sql);
}
module.exports = { setup, teardown, exec, execFile, getClient: () => client };Testing NOT NULL Constraints
NOT NULL constraints are easy to forget and painful to add later (because adding them to a populated table requires a table rewrite). Test them at the schema level:
// test/schema/constraints.test.js
const db = require('../helpers/db');
beforeAll(async () => {
await db.setup();
// Apply your schema
await db.execFile('./schema/schema.sql');
}, 30000);
afterAll(() => db.teardown());
describe('NOT NULL constraints', () => {
test('users.email is NOT NULL', async () => {
await expect(
db.exec(`INSERT INTO users (name, created_at) VALUES ('Test', NOW())`)
).rejects.toThrow(/null value in column "email"/);
});
test('users.name is NOT NULL', async () => {
await expect(
db.exec(`INSERT INTO users (email, created_at) VALUES ('test@example.com', NOW())`)
).rejects.toThrow(/null value in column "name"/);
});
test('orders.user_id is NOT NULL', async () => {
await expect(
db.exec(`INSERT INTO orders (total, status, created_at) VALUES (9.99, 'pending', NOW())`)
).rejects.toThrow(/null value in column "user_id"/);
});
});An alternative approach queries the information schema to verify constraints declaratively:
test('required columns are all NOT NULL', async () => {
const result = await db.exec(`
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name IN ('users', 'orders', 'products')
AND is_nullable = 'YES'
AND column_name IN ('email', 'user_id', 'product_id', 'name', 'total')
ORDER BY table_name, column_name
`);
// Any row returned means a required column is nullable — should be empty
expect(result.rows).toHaveLength(0);
});Testing UNIQUE Constraints
describe('UNIQUE constraints', () => {
beforeEach(async () => {
await db.exec('TRUNCATE users CASCADE');
});
test('email must be unique across users', async () => {
await db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ('shared@example.com', 'User One', 'hash1', NOW())
`);
await expect(
db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ('shared@example.com', 'User Two', 'hash2', NOW())
`)
).rejects.toThrow(/duplicate key value violates unique constraint/);
});
test('email uniqueness is case-sensitive by default', async () => {
// If your application normalizes emails, verify the constraint matches
await db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ('user@example.com', 'Lower User', 'hash1', NOW())
`);
// This should either fail (if you have a case-insensitive unique index)
// or succeed (if you handle normalization in application code)
// Test documents which behavior your schema actually provides
const result = await db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ('USER@EXAMPLE.COM', 'Upper User', 'hash2', NOW())
RETURNING id
`);
// Document the behavior: application code must normalize before insert
expect(result.rows).toHaveLength(1);
});
});Testing Foreign Key Constraints
Foreign key constraints prevent orphaned records. Test both the insert restriction and the cascade behavior:
describe('FOREIGN KEY constraints', () => {
test('orders.user_id must reference existing user', async () => {
const fakeUserId = 99999;
await expect(
db.exec(`
INSERT INTO orders (user_id, total, status, created_at)
VALUES (${fakeUserId}, 29.99, 'pending', NOW())
`)
).rejects.toThrow(/foreign key constraint/);
});
test('deleting user cascades to orders', async () => {
// Insert user and their order
const userResult = await db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ('cascade@example.com', 'Cascade User', 'hash', NOW())
RETURNING id
`);
const userId = userResult.rows[0].id;
await db.exec(`
INSERT INTO orders (user_id, total, status, created_at)
VALUES (${userId}, 99.99, 'delivered', NOW())
`);
// Delete the user
await db.exec(`DELETE FROM users WHERE id = ${userId}`);
// Orders should be gone too (CASCADE) or blocked (RESTRICT)
// Test documents your actual FK behavior
const orphanedOrders = await db.exec(`
SELECT id FROM orders WHERE user_id = ${userId}
`);
// If ON DELETE CASCADE:
expect(orphanedOrders.rows).toHaveLength(0);
});
});Testing CHECK Constraints
CHECK constraints enforce domain rules — valid status values, positive prices, valid date ranges:
describe('CHECK constraints', () => {
test('order status must be a valid enum value', async () => {
const userId = await createTestUser();
await expect(
db.exec(`
INSERT INTO orders (user_id, total, status, created_at)
VALUES (${userId}, 9.99, 'invalid_status', NOW())
`)
).rejects.toThrow(/violates check constraint/);
});
test('product price must be positive', async () => {
await expect(
db.exec(`
INSERT INTO products (name, price, sku)
VALUES ('Negative Widget', -5.00, 'SKU-001')
`)
).rejects.toThrow(/violates check constraint/);
});
test('product price of zero is also rejected', async () => {
await expect(
db.exec(`
INSERT INTO products (name, price, sku)
VALUES ('Free Widget', 0, 'SKU-002')
`)
).rejects.toThrow(/violates check constraint/);
});
});Index Verification with EXPLAIN ANALYZE
The existence of an index doesn't mean the query planner uses it. EXPLAIN ANALYZE reveals what actually happens:
describe('index usage', () => {
beforeAll(async () => {
// Insert enough rows to make index usage likely
const inserts = Array.from({ length: 10000 }, (_, i) =>
`('user${i}@example.com', 'User ${i}', 'hash${i}', NOW())`
).join(',');
await db.exec(`
INSERT INTO users (email, name, password_hash, created_at)
VALUES ${inserts}
`);
});
test('email lookup uses index scan, not sequential scan', async () => {
const result = await db.exec(`
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT id, name FROM users WHERE email = 'user5000@example.com'
`);
const plan = result.rows[0]['QUERY PLAN'][0];
const planText = JSON.stringify(plan);
expect(planText).toContain('Index Scan');
expect(planText).not.toContain('Seq Scan');
// Should touch very few rows
expect(plan['Execution Time']).toBeLessThan(10); // milliseconds
});
test('order lookup by user_id uses index', async () => {
const userId = 1;
const result = await db.exec(`
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT id, total FROM orders WHERE user_id = ${userId}
`);
const plan = result.rows[0]['QUERY PLAN'][0];
const planText = JSON.stringify(plan);
// orders.user_id should have an index for this FK relationship
expect(planText).toMatch(/Index (Only )?Scan/);
});
});You can also verify index existence directly:
test('required indexes exist', async () => {
const result = await db.exec(`
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname
`);
const indexes = result.rows.map(r => r.indexname);
const requiredIndexes = [
'users_email_key', // unique index on email
'users_pkey', // primary key
'orders_pkey', // primary key
'orders_user_id_idx', // FK index
'orders_created_at_idx', // for time-range queries
'products_sku_key', // unique index on SKU
];
for (const required of requiredIndexes) {
expect(indexes).toContain(required);
}
});Data Type Boundary Testing
PostgreSQL is strict about data type limits, and boundary violations produce different errors depending on the type:
describe('data type boundaries', () => {
test('VARCHAR(255) rejects strings over 255 characters', async () => {
const longName = 'a'.repeat(256);
await expect(
db.exec(`INSERT INTO users (email, name, ...) VALUES ('e@e.com', '${longName}', ...)`)
).rejects.toThrow(/value too long for type character varying/);
});
test('INTEGER column rejects values over 2147483647', async () => {
await expect(
db.exec(`INSERT INTO inventory (product_id, quantity) VALUES (1, 2147483648)`)
).rejects.toThrow(/integer out of range/);
});
test('NUMERIC(10,2) rounds to 2 decimal places', async () => {
await db.exec(`
INSERT INTO products (name, price, sku)
VALUES ('Rounded Widget', 9.999, 'SKU-ROUND')
`);
const result = await db.exec(`SELECT price FROM products WHERE sku = 'SKU-ROUND'`);
// PostgreSQL rounds 9.999 to 10.00 for NUMERIC(10,2)
expect(parseFloat(result.rows[0].price)).toBe(10.00);
});
test('TIMESTAMP stores microsecond precision', async () => {
const preciseTime = '2024-01-15 12:30:45.123456';
await db.exec(`
INSERT INTO events (name, occurred_at)
VALUES ('precision test', '${preciseTime}')
`);
const result = await db.exec(`
SELECT occurred_at::text FROM events WHERE name = 'precision test'
`);
expect(result.rows[0].occurred_at).toContain('123456');
});
});Schema Diff Testing
Schema diff tests compare the actual database schema to your expected schema definition. This catches drift between what's in your migration files and what's actually in the database:
test('schema matches expected definition', async () => {
const result = await db.exec(`
SELECT
c.table_name,
c.column_name,
c.data_type,
c.character_maximum_length,
c.numeric_precision,
c.numeric_scale,
c.is_nullable,
c.column_default
FROM information_schema.columns c
JOIN information_schema.tables t
ON t.table_name = c.table_name
AND t.table_schema = c.table_schema
WHERE c.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
ORDER BY c.table_name, c.ordinal_position
`);
// Snapshot test — fails when schema changes unexpectedly
expect(result.rows).toMatchSnapshot();
});
test('all expected tables exist', async () => {
const result = await db.exec(`
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name
`);
const tables = result.rows.map(r => r.table_name);
const expectedTables = [
'users',
'products',
'orders',
'order_items',
'categories',
'product_categories',
];
for (const expected of expectedTables) {
expect(tables).toContain(expected);
}
});Update the snapshot intentionally when you add or change tables. Any unintended drift — a column that got dropped, a type that changed — fails the test automatically.
Conclusion
Schema tests are a different layer from application tests — they verify the database's enforcement guarantees rather than your application logic. The cost of writing them is low: a few queries against the information schema and some constraint violation tests. The benefit is high: you get early warning of schema drift, confidence that your constraints actually work, and documentation of the exact schema your application requires.
Run these tests on every migration, in CI on every pull request, and before every production deployment. They take seconds to run and have saved many teams from discovering that a well-intentioned "cleanup" migration quietly dropped a unique constraint that was holding data integrity together.
