Setting Up & Tearing Down Test Data with SQL

📌 Definition:

📖 Detailed Explanation:

Reliable tests need controlled data. Building state through the UI is slow and brittle; a targeted INSERT creates precisely the scenario in one step (a user with a specific status, an order in a particular state). The discipline has three parts. Setup: insert the minimal rows the test needs, respecting foreign-key order (parent rows before children — a user before their orders). Isolation: use unique identifiers (emails/keys with a UUID or timestamp) so parallel tests don't collide on the same data. Teardown: delete what the test created, in reverse foreign-key order (children before parents) to avoid constraint violations, ideally in a finally/afterEach so cleanup runs even when the test fails. An even cleaner approach where supported is wrapping each test in a transaction and rolling it back, leaving zero residue. Testers must avoid two anti-patterns: depending on pre-existing 'magic' data that can change, and leaking test rows that accumulate and eventually break other tests or pollute reports.

🔑 Key Points:

• Seed the exact rows a scenario needs instead of building state through the slow UI
• Insert in FK order (parents first); delete in reverse FK order (children first)
• Use unique keys (UUID/timestamp) so parallel tests don't collide
• Clean up in afterEach/finally so residue is removed even on failure
• Best when available: wrap each test in a transaction and roll back

🌍 Real-World Example:

📎 Code Example:

-- SETUP: insert parent then child, with a unique key for isolation
INSERT INTO users (id, email, name, status)
VALUES (90001, 'shiporder+7f3a@test.com', 'Test User', 'active');

INSERT INTO orders (id, user_id, status, total)
VALUES (95001, 90001, 'shipped', 42.00);

-- ... test runs the cancel flow against order 95001 ...

-- TEARDOWN: delete child rows before parent to respect foreign keys
DELETE FROM order_items WHERE order_id = 95001;
DELETE FROM orders      WHERE id = 95001;
DELETE FROM users       WHERE id = 90001;

-- Cleanest when supported: run inside a transaction and roll back
-- BEGIN;  ... inserts + test ...  ROLLBACK;   -- leaves zero residue