managing-database-tests

name	managing-database-tests
description	Test database testing including fixtures, transactions, and rollback management. Use when performing specialized testing. Trigger with phrases like "test the database", "run database tests", or "validate data integrity".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(test:db-*)
version	1.26.0
author	Jeremy Longshore <[e-mail]>
license	MIT
tags	["testing","database","database-tests"]
compatibility	Designed for Claude Code, also compatible with Codex and OpenClaw

Database Test Manager

Overview

Manage database testing including fixture loading, transaction-based test isolation, migration validation, query performance testing, and data integrity checks. Supports PostgreSQL, MySQL, MongoDB, SQLite (in-memory), and Redis with ORM-agnostic patterns for Prisma, TypeORM, SQLAlchemy, Knex, and Drizzle.

Prerequisites

• Database instance available for testing (Docker container, in-memory SQLite, or dedicated test server)
• Database client library and ORM installed (Prisma, TypeORM, Knex, SQLAlchemy, etc.)
• Migration files up to date and tested independently
• Test database connection string configured in environment (distinct from development/production)
• Database seed data scripts for baseline test state

Instructions

1. Set up the test database infrastructure:
   • Use Docker to spin up a dedicated test database: docker run -d -p 5433:5432 --name test-db postgres:16-alpine.
   • Or use SQLite in-memory mode for fast unit tests: sqlite::memory:.
   • Or use Testcontainers for ephemeral database per test suite.
   • Verify the test database is isolated from development data.
2. Run database migrations against the test database:
   • Execute npx prisma migrate deploy or npx knex migrate:latest --env test.
   • Verify all migrations apply cleanly to an empty database.
   • Test rollback: run migrate:rollback and verify schema reverts correctly.
3. Implement test isolation strategy (choose one):
   • Transaction rollback: Wrap each test in a transaction; roll back after assertions. Fastest option.
   • Truncation: Truncate all tables in beforeEach. Simpler but slower.
   • Database recreation: Drop and recreate the database before each test suite. Slowest, most thorough.
4. Create database fixture utilities:
   • Factory functions that insert records and return the created entity with its database-generated ID.
   • Seed functions for standard test scenarios (empty state, populated state, edge cases).
   • Cleanup utilities that handle foreign key ordering for truncation.
5. Write database-specific test cases:
   • CRUD operations: Insert, query, update, delete records and verify database state.
   • Constraint validation: Attempt invalid inserts (null on NOT NULL, duplicate on UNIQUE) and verify rejection.