Prisma Testing — Testing Database Operations and Queries

# test with separate database
DATABASE_URL="postgresql://postgres:pass@localhost:5432/dodatech_test"

# Run migrations on test database
npx prisma migrate deploy

# OR use a script to push schema
npx prisma db push --force-reset

// test/setup.ts
import { PrismaClient } from '@prisma/client';

// Test database client
const prisma = new PrismaClient({
  datasources: {
    db: { url: process.env.TEST_DATABASE_URL },
  },
});

export { prisma };

// Global setup/teardown
beforeAll(async () => {
  await prisma.$connect();
  await resetDatabase(prisma);
});

afterAll(async () => {
  await prisma.$disconnect();
});

async function resetDatabase(prisma: PrismaClient) {
  // Delete all records in reverse dependency order
  const tablenames = ['Comment', 'Post', 'User'];
  
  for (const tablename of tablenames) {
    try {
      await prisma.$executeRawUnsafe(
        `TRUNCATE TABLE "${tablename}" CASCADE;`
      );
    } catch (error) {
      console.error(`Failed to truncate ${tablename}:`, error);
    }
  }
}

# test_setup.py
# Test database setup

def test_db_setup():
    print("Test Database Setup:")
    print()
    print("Options:")
    print("  1. Separate test database URL")
    print("  2. In-memory SQLite for fast tests")
    print("  3. Docker test database (recreated per run)")
    print()
    print("Best practices:")
    print("  - Reset database between test runs")
    print("  - Truncate tables in reverse dependency order")
    print("  - Use transactions for per-test isolation")
    print("  - Never run tests against production database")

test_db_setup()

// test/mocks/prisma.ts
import { PrismaClient } from '@prisma/client';
import { DeepMockProxy, mockDeep, mockReset } from 'jest-mock-extended';

jest.mock('../../lib/db', () => ({
  __esModule: true,
  prisma: mockDeep<PrismaClient>(),
}));

import { prisma } from '../../lib/db';

beforeEach(() => {
  mockReset(prismaMock);
});

export const prismaMock = prisma as unknown as DeepMockProxy<PrismaClient>;

// Usage in tests
describe('UserService', () => {
  it('should create a user', async () => {
    const userData = { email: 'test@test.com', name: 'Test' };
    
    prismaMock.user.create.mockResolvedValue({
      id: 1,
      ...userData,
      createdAt: new Date(),
    });
    
    const result = await userService.create(userData);
    expect(result.email).toBe('test@test.com');
    expect(prismaMock.user.create).toHaveBeenCalledWith({
      data: userData,
    });
  });
});

# mocking.py
# Mocking Prisma Client

def mocking_patterns():
    print("Mocking Prisma Client:")
    print()
    print("Why mock:")
    print("  - Fast unit tests (no database needed)")
    print("  - Test business logic in isolation")
    print("  - Simulate error conditions easily")
    print()
    print("What to test without mocking:")
    print("  - Actual query correctness")
    print("  - Data integrity and constraints")
    print("  - Transaction behavior")
    print()
    print("Tools:")
    print("  - jest-mock-extended (TypeScript)")
    print("  - vi.mock with vitest")
    print("  - Manual mocks for simple cases")

mocking_patterns()

// test/integration/user.service.test.ts
import { prisma } from '../setup';
import { UserService } from '../../services/user.service';

describe('UserService Integration', () => {
  const service = new UserService();
  
  it('should create and retrieve a user', async () => {
    // Create
    const user = await service.create({
      email: 'test@test.com',
      name: 'Test User',
    });
    
    expect(user.email).toBe('test@test.com');
    expect(user.id).toBeDefined();
    
    // Retrieve
    const found = await service.findById(user.id);
    expect(found?.name).toBe('Test User');
  });
  
  it('should enforce unique email constraint', async () => {
    await service.create({ email: 'unique@test.com', name: 'First' });
    
    await expect(
      service.create({ email: 'unique@test.com', name: 'Second' })
    ).rejects.toThrow(); // P2002 unique constraint
  });
  
  it('should return null for non-existent user', async () => {
    const result = await service.findById(99999);
    expect(result).toBeNull();
  });
});

# integration_tests.py
# Integration test patterns

def integration_patterns():
    print("Integration Test Patterns:")
    print()
    print("What to test:")
    print("  - CRUD operations with real database")
    print("  - Unique constraint violations")
    print("  - Foreign key enforcement")
    print("  - Cascade deletes")
    print("  - Transaction rollbacks")
    print("  - Edge cases (null values, empty results)")
    print()
    print("Setup/teardown patterns:")
    print("  - beforeEach: Start transaction")
    print("  - afterEach: Rollback transaction")
    print("  - This provides isolated tests without truncation")

integration_patterns()

// test/factories.ts
import { prisma } from './setup';

export async function createUser(overrides = {}) {
  return prisma.user.create({
    data: {
      email: `user-${Date.now()}@test.com`,
      name: 'Test User',
      ...overrides,
    },
  });
}

export async function createPost(overrides = {}) {
  const user = await createUser();
  
  return prisma.post.create({
    data: {
      title: 'Test Post',
      content: 'Test content',
      authorId: user.id,
      ...overrides,
    },
  });
}

// Usage in tests
it('should create a post', async () => {
  const user = await createUser();
  const post = await createPost({ authorId: user.id, title: 'Custom Title' });
  
  expect(post.title).toBe('Custom Title');
});

# factories.py
# Test factory patterns

def factory_patterns():
    print("Test Factory Patterns:")
    print()
    print("Benefits:")
    print("  - Reusable test data creation")
    print("  - Default values with override support")
    print("  - Handles required relations automatically")
    print("  - Reduces test boilerplate")
    print()
    print("Pattern:")
    print("  async function createUser(overrides = {}) {")
    print("    return prisma.user.create({")
    print("      data: {")
    print("        email: `test-${Date.now()}@test.com`,")
    print("        name: 'Test',")
    print("        ...overrides")
    print("      }")
    print("    })")
    print("  }")

factory_patterns()

def test_suite_plan():
    print("Test Suite Plan:")
    print()
    print("Unit tests (mocked Prisma):")
    print("  - createUser validates input")
    print("  - createUser handles unique email error")
    print("  - findById returns null for missing user")
    print()
    print("Integration tests (real database):")
    print("  - create and retrieve user")
    print("  - unique email constraint enforcement")
    print("  - cascade delete user with posts")
    print("  - update user name + password")
    print()
    print("Helpers:")
    print("  - createUser(overrides) factory")
    print("  - createPost(overrides) factory")
    print("  - withTransaction test wrapper")

test_suite_plan()