karchtho Database Testing with Prisma and PostgreSQL
Patterns for testing database operations, repositories, and transactions with Prisma ORM and PostgreSQL.
Test Database Setup
Environment Configuration
# .env.test
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp_test?schema=public"
Jest Configuration
// jest.config.js
module.exports = {
// ... other config
setupFilesAfterEnv: ['<rootDir>/test/setup.ts'],
testEnvironment: 'node',
// Use separate test database
globalSetup: '<rootDir>/test/global-setup.ts',
globalTeardown: '<rootDir>/test/global-teardown.ts'
};
Global Setup/Teardown
// test/global-setup.ts
import { execSync } from 'child_process';
export default async function globalSetup() {
// Reset test database
execSync('npx prisma migrate reset --force --skip-seed', {
env: {
...process.env,
DATABASE_URL: process.env.TEST_DATABASE_URL
}
});
}
// test/global-teardown.ts
import { prisma } from '../src/lib/prisma';
export default async function globalTeardown() {
await prisma.$disconnect();
}
Test Setup File
// test/setup.ts
import { prisma } from '../src/lib/prisma';
import { beforeAll, afterAll, afterEach } from '@jest/globals';
beforeAll(async () => {
await prisma.$connect();
});
afterEach(async () => {
// Clean all tables
const tablenames = await prisma.$queryRaw<
Array<{ tablename: string }>
>`SELECT tablename FROM pg_tables WHERE schemaname='public'`;
for (const { tablename } of tablenames) {
if (tablename !== '_prisma_migrations') {
await prisma.$executeRawUnsafe(`TRUNCATE TABLE "public"."${tablename}" CASCADE;`);
}
}
});
afterAll(async () => {
await prisma.$disconnect();
});
Repository Testing
Basic Repository Tests
// repositories/user.repository.ts
import { prisma } from '../lib/prisma';
import { User, Prisma } from '@prisma/client';
export class UserRepository {
async findById(id: string): Promise<User | null> {
return prisma.user.findUnique({ where: { id } });
}
async findByEmail(email: string): Promise<User | null> {
return prisma.user.findUnique({ where: { email } });
}
async create(data: Prisma.UserCreateInput): Promise<User> {
return prisma.user.create({ data });
}
async update(id: string, data: Prisma.UserUpdateInput): Promise<User> {
return prisma.user.update({ where: { id }, data });
}
async delete(id: string): Promise<void> {
await prisma.user.delete({ where: { id } });
}
async findWithOrders(id: string): Promise<User & { orders: Order[] } | null> {
return prisma.user.findUnique({
where: { id },
include: { orders: true }
});
}
}
// repositories/user.repository.test.ts
import { UserRepository } from './user.repository';
import { prisma } from '../lib/prisma';
import { createUserFixture } from '../test/factories/user.factory';
describe('UserRepository', () => {
const repository = new UserRepository();
describe('findById', () => {
it('should return user when exists', async () => {
// Arrange
const createdUser = await prisma.user.create({
data: createUserFixture()
});
// Act
const user = await repository.findById(createdUser.id);
// Assert
expect(user).not.toBeNull();
expect(user?.id).toBe(createdUser.id);
});
it('should return null when user does not exist', async () => {
const user = await repository.findById('non-existent-id');
expect(user).toBeNull();
});
});
describe('create', () => {
it('should create user with valid data', async () => {
const userData = createUserFixture();
const user = await repository.create(userData);
expect(user.id).toBeDefined();
expect(user.email).toBe(userData.email);
expect(user.name).toBe(userData.name);
});
it('should throw on duplicate email', async () => {
const userData = createUserFixture();
await repository.create(userData);
await expect(
repository.create({ ...userData, name: 'Different Name' })
).rejects.toThrow();
});
});
describe('findWithOrders', () => {
it('should return user with orders', async () => {
// Arrange
const user = await prisma.user.create({
data: createUserFixture()
});
await prisma.order.createMany({
data: [
{ userId: user.id, total: 100, status: 'pending' },
{ userId: user.id, total: 200, status: 'completed' }
]
});
// Act
const result = await repository.findWithOrders(user.id);
// Assert
expect(result?.orders).toHaveLength(2);
expect(result?.orders[0]).toMatchObject({ userId: user.id });
});
});
});
Transaction Testing
// services/order.service.ts
import { prisma } from '../lib/prisma';
export class OrderService {
async createOrderWithItems(
userId: string,
items: Array<{ productId: string; quantity: number }>
) {
return prisma.$transaction(async (tx) => {
// Create order
const order = await tx.order.create({
data: {
userId,
status: 'pending',
total: 0
}
});
// Create order items and calculate total
let total = 0;
for (const item of items) {
const product = await tx.product.findUniqueOrThrow({
where: { id: item.productId }
});
// Check stock
if (product.stock < item.quantity) {
throw new Error(`Insufficient stock for ${product.name}`);
}
// Decrement stock
await tx.product.update({
where: { id: item.productId },
data: { stock: { decrement: item.quantity } }
});
// Create order item
await tx.orderItem.create({
data: {
orderId: order.id,
productId: item.productId,
quantity: item.quantity,
price: product.price
}
});
total += product.price * item.quantity;
}
// Update order total
return tx.order.update({
where: { id: order.id },
data: { total },
include: { items: true }
});
});
}
}
// services/order.service.test.ts
describe('OrderService', () => {
const service = new OrderService();
describe('createOrderWithItems', () => {
it('should create order and decrement stock atomically', async () => {
// Arrange
const user = await prisma.user.create({
data: createUserFixture()
});
const product = await prisma.product.create({
data: { name: 'Widget', price: 25, stock: 10 }
});
// Act
const order = await service.createOrderWithItems(user.id, [
{ productId: product.id, quantity: 3 }
]);
// Assert
expect(order.total).toBe(75);
expect(order.items).toHaveLength(1);
const updatedProduct = await prisma.product.findUnique({
where: { id: product.id }
});
expect(updatedProduct?.stock).toBe(7);
});
it('should rollback on insufficient stock', async () => {
// Arrange
const user = await prisma.user.create({
data: createUserFixture()
});
const product = await prisma.product.create({
data: { name: 'Widget', price: 25, stock: 2 }
});
// Act & Assert
await expect(
service.createOrderWithItems(user.id, [
{ productId: product.id, quantity: 5 }
])
).rejects.toThrow('Insufficient stock');
// Verify rollback - no order created
const orders = await prisma.order.findMany({
where: { userId: user.id }
});
expect(orders).toHaveLength(0);
// Stock unchanged
const updatedProduct = await prisma.product.findUnique({
where: { id: product.id }
});
expect(updatedProduct?.stock).toBe(2);
});
it('should handle multiple items in single transaction', async () => {
const user = await prisma.user.create({
data: createUserFixture()
});
const [product1, product2] = await Promise.all([
prisma.product.create({ data: { name: 'Widget', price: 10, stock: 5 } }),
prisma.product.create({ data: { name: 'Gadget', price: 20, stock: 10 } })
]);
const order = await service.createOrderWithItems(user.id, [
{ productId: product1.id, quantity: 2 },
{ productId: product2.id, quantity: 3 }
]);
expect(order.total).toBe(80); // 20 + 60
expect(order.items).toHaveLength(2);
});
});
});
Query Testing
// repositories/product.repository.ts
export class ProductRepository {
async findWithFilters(filters: {
category?: string;
minPrice?: number;
maxPrice?: number;
inStock?: boolean;
search?: string;
}) {
const where: Prisma.ProductWhereInput = {};
if (filters.category) {
where.category = filters.category;
}
if (filters.minPrice !== undefined) {
where.price = { ...where.price as object, gte: filters.minPrice };
}
if (filters.maxPrice !== undefined) {
where.price = { ...where.price as object, lte: filters.maxPrice };
}
if (filters.inStock) {
where.stock = { gt: 0 };
}
if (filters.search) {
where.name = { contains: filters.search, mode: 'insensitive' };
}
return prisma.product.findMany({ where });
}
}
// repositories/product.repository.test.ts
describe('ProductRepository', () => {
const repository = new ProductRepository();
beforeEach(async () => {
await prisma.product.createMany({
data: [
{ name: 'Blue Widget', price: 10, stock: 5, category: 'widgets' },
{ name: 'Red Widget', price: 20, stock: 0, category: 'widgets' },
{ name: 'Green Gadget', price: 50, stock: 10, category: 'gadgets' },
{ name: 'Yellow Gadget', price: 100, stock: 3, category: 'gadgets' }
]
});
});
describe('findWithFilters', () => {
it('should filter by category', async () => {
const products = await repository.findWithFilters({ category: 'widgets' });
expect(products).toHaveLength(2);
expect(products.every(p => p.category === 'widgets')).toBe(true);
});
it('should filter by price range', async () => {
const products = await repository.findWithFilters({
minPrice: 15,
maxPrice: 60
});
expect(products).toHaveLength(2);
expect(products.map(p => p.price)).toEqual([20, 50]);
});
it('should filter in stock only', async () => {
const products = await repository.findWithFilters({ inStock: true });
expect(products).toHaveLength(3);
expect(products.every(p => p.stock > 0)).toBe(true);
});
it('should search by name (case insensitive)', async () => {
const products = await repository.findWithFilters({ search: 'widget' });
expect(products).toHaveLength(2);
});
it('should combine multiple filters', async () => {
const products = await repository.findWithFilters({
category: 'gadgets',
minPrice: 40,
inStock: true
});
expect(products).toHaveLength(2);
});
it('should return empty array when no matches', async () => {
const products = await repository.findWithFilters({
category: 'nonexistent'
});
expect(products).toHaveLength(0);
});
});
});
Test Data Isolation Strategies
Strategy 1: TRUNCATE Between Tests
afterEach(async () => {
await prisma.$executeRaw`TRUNCATE TABLE users, orders, products CASCADE`;
});
Strategy 2: Unique IDs Per Test
import { randomUUID } from 'crypto';
describe('UserService', () => {
it('should create user', async () => {
const uniqueEmail = `test-${randomUUID()}@example.com`;
const user = await userService.create({ email: uniqueEmail, name: 'Test' });
expect(user.email).toBe(uniqueEmail);
});
});
Strategy 3: Transactions with Rollback
describe('With transaction rollback', () => {
let transaction: PrismaClient;
beforeEach(async () => {
// Start transaction
transaction = await prisma.$begin();
});
afterEach(async () => {
// Rollback after each test
await transaction.$rollback();
});
it('should test with isolated data', async () => {
await transaction.user.create({ data: createUserFixture() });
// This data will be rolled back
});
});
Testing Migrations
// test/migrations.test.ts
import { execSync } from 'child_process';
import { prisma } from '../src/lib/prisma';
describe('Database Migrations', () => {
it('should apply all migrations successfully', async () => {
// Reset and apply migrations
execSync('npx prisma migrate reset --force', {
env: { ...process.env, DATABASE_URL: process.env.TEST_DATABASE_URL }
});
// Verify schema
const tables = await prisma.$queryRaw<Array<{ tablename: string }>>`
SELECT tablename FROM pg_tables WHERE schemaname='public'
`;
const tableNames = tables.map(t => t.tablename);
expect(tableNames).toContain('users');
expect(tableNames).toContain('orders');
expect(tableNames).toContain('products');
});
it('should have correct column types', async () => {
const columns = await prisma.$queryRaw<Array<{
column_name: string;
data_type: string;
}>>`
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users'
`;
const columnMap = Object.fromEntries(
columns.map(c => [c.column_name, c.data_type])
);
expect(columnMap.id).toBe('uuid');
expect(columnMap.email).toBe('character varying');
expect(columnMap.created_at).toBe('timestamp with time zone');
});
});
Factory Functions for Test Data
// test/factories/user.factory.ts
import { faker } from '@faker-js/faker';
import { Prisma } from '@prisma/client';
export function createUserFixture(
overrides?: Partial<Prisma.UserCreateInput>
): Prisma.UserCreateInput {
return {
name: faker.person.fullName(),
email: faker.internet.email(),
role: 'user',
...overrides
};
}
// test/factories/order.factory.ts
export function createOrderFixture(
userId: string,
overrides?: Partial<Prisma.OrderCreateInput>
): Prisma.OrderCreateInput {
return {
user: { connect: { id: userId } },
status: 'pending',
total: faker.number.float({ min: 10, max: 500, fractionDigits: 2 }),
...overrides
};
}
// test/factories/product.factory.ts
export function createProductFixture(
overrides?: Partial<Prisma.ProductCreateInput>
): Prisma.ProductCreateInput {
return {
name: faker.commerce.productName(),
price: faker.number.float({ min: 5, max: 200, fractionDigits: 2 }),
stock: faker.number.int({ min: 0, max: 100 }),
category: faker.commerce.department().toLowerCase(),
...overrides
};
}
Best Practices
- Use test database - Never run tests against production or development databases
- Isolate tests - Each test should be independent and not affect others
- Use unique identifiers - Generate unique IDs to prevent test interference
- Clean up data - TRUNCATE tables or use rollback between tests
- Test transactions - Verify rollback behavior on errors
- Use factories - Create consistent test data with factory functions
- Test edge cases - Include boundary conditions and error scenarios
- Verify side effects - Check that database state is correct after operations