- Home
- Skills
- Data & Databases
- database test designer
Works with the AI Agents you already use
database test designer
Design systematic database tests for migrations, constraints, concurrency, and data integrity.
$6
database test designer
Example session with this skill installed
Design a test suite for an e-commerce order schema including unique external_id constraints, cascading user deletes, and concurrent inventory updates.
- Read your context and instructions
- Compiled the database test designer
I have analyzed the order schema requirements. Based on the DDL constraints and transactional needs, I have designed a test matrix covering integrity, concurrency, and cleanup.
Database Test Design: Order Management
| ID | Requirement/Object | Setup/State | Operation | Expected DB/App Result | Evidence/Assertion | Cleanup |
|---|---|---|---|---|---|---|
| DB-01 | order.external_id UNIQUE | Existing order with ext_123 | Insert new order with ext_123 | UniqueViolation error from DB | Catch SqlException; check count=1 | None required |
| DB-02 | user_id FK Cascade | User with 5 active orders | DELETE FROM users WHERE id = X | Orders deleted automatically | SELECT COUNT(*) FROM orders WHERE user_id = X is 0 | Rollback transaction |
| DB-03 | Inventory Concurrency | Item stock = 1; 2 sessions | Simultaneous UPDATE stock = stock - 1 | Exactly one commit; one serialization error | Inventory >= 0; one 409 conflict | Reset stock to 1 |
| DB-04 | total_amount Range | Standard order session | Insert total_amount = -1.00 | CheckConstraint violation | DB rejects row; app logs error | None required |
Integrity Check Scripts
-- Frequency: Post-Migration / Daily
-- Check: Orphaned Orders
SELECT count(*)
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- Expected: 0
-- On-Fail: Alert engineering; investigate FK constraint status.
Seed Data (Disposable Environment)
BEGIN;
INSERT INTO users (id, name) VALUES (999, 'Test User');
INSERT INTO inventory (sku, stock) VALUES ('SKU-01', 1);
INSERT INTO orders (id, external_id, user_id, total_amount) VALUES (1, 'ext_123', 999, 50.00);
COMMIT;
Next steps
- Confirm the specific isolation level (e.g., Read Committed vs Serializable) configured in your DB engine.
- Execute the seed script in a staging/disposable container to verify constraint names.
- Integrate the concurrency schedule into your integration test suite using separate DB connections.
Connects securely to your tools. The creator never sees your data.
About this skill
Writing database tests often results in superficial checks that miss critical race conditions or migration failures. This skill enforces a systematic approach to verifying data integrity, constraints, and concurrent transactions.
What it does
- Schema & Migration Validation Designs scenarios for schema changes, verifying upgrade paths from prior states and assessing rollback loss risks.
- Constraint Testing Maps null, type, range, unique, and foreign key violations based on DDL definitions.
- Concurrency Analysis Defines execution schedules to test atomicity and isolation levels under conflicting write conditions.
- Data Integrity Audits Identifies risks for orphaned records, cascading delete behaviors, and boundary value overflows.
- Test Artifact Generation Produces structured test matrices and SQL seed scripts for deterministic environment setup and cleanup.
How it works
- Map schema architecture Analyze DDL and application code to identify tables, relationships, and read/write paths.
- Derive test cases Generate a matrix of valid CRUD operations and intentional constraint violations.
- Verify state transitions Model migrations from specific prior versions using representative data.
- Define concurrency invariants Consult engine-specific documentation to predict and test isolation behavior rather than guessing.
Frameworks & tools
Works with any relational or NoSQL database engine including PostgreSQL, MySQL, and MongoDB. Supports testing at both the ORM persistence layer and direct SQL/DDL enforcement layer.
Why this beats prompting it yourself
Generic prompts fail to distinguish between application-level validation and database-level enforcement. This skill explicitly maps DDL type boundaries, such as integer overflows and timestamp ranges, while enforcing isolation between fixtures and cleanup.
Use cases
- Migration safety checks Verifying a complex schema change doesn't corrupt existing production data.
- Transaction integrity Ensuring inventory counts remain accurate during high-concurrency order spikes.
- Boundary analysis Testing application behavior when varchar limits or numeric precision constraints are hit.
- Cleanup validation Confirming cascading deletes remove all associated records without leaving orphans.
Known limitations
Does not perform actual destructive checks on production environments. It requires specific engine and version details to avoid incorrect assumptions about cross-engine semantics.
Requires engine version and DDL context. Cannot perform destructive checks on production. Does not assume down-migrations are lossless.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 2 days ago