DELETE — Cleaning Up Test Data

Tests create rows. Without cleanup, those rows pile up — five test users today, fifty next week, five thousand by the end of the quarter. Soon nobody can find real data through the noise, queries slow down, and unique constraints start failing for reasons that have nothing to do with the test under inspection. DELETE is the cleanup tool. It also happens to be the one command that does the most damage when you get it wrong, so this lesson is equal parts how to use it and how not to wreck the database with it.

The basic DELETE

DELETE FROM users WHERE email = 'testuser@test.com';

Two pieces:

• DELETE FROM users — the table to remove rows from.
• WHERE email = 'testuser@test.com' — which rows to remove. Like UPDATE, this is not optional in practice — ever.

Run a verifying SELECT first:

-- 1. See what you'll delete
SELECT id, name, email FROM users WHERE email = 'testuser@test.com';
-- Result: 6 | Test User | testuser@test.com
 
-- 2. Delete it
DELETE FROM users WHERE email = 'testuser@test.com';
 
-- 3. Confirm it's gone
SELECT * FROM users WHERE email = 'testuser@test.com';
-- Result: (no rows)

Three queries, two seconds, total confidence.

The DELETE workflow, in stages

That four-step workflow — write, preview, delete, verify — is the difference between professional cleanup and "I just deleted the production users table at 3pm on a Friday."

DELETE without WHERE — every row goes

DELETE FROM users; removes every row in the users table. Same shape as UPDATE without WHERE — the database does it without a confirmation prompt. Treat the WHERE clause as a non-negotiable part of the syntax: write it before you write the rest.

Pattern-based cleanup

A pattern worth adopting from day one of test automation: every test-data row gets a recognisable suffix. Cleanup is then one targeted DELETE:

-- All test accounts, gone in one statement
DELETE FROM users WHERE email LIKE '%@qa-test.local';
 
-- Test orders older than 7 days
DELETE FROM orders WHERE created_at < DATE('now', '-7 days');

DATE('now', '-7 days') is SQLite-syntax. Equivalents:

Date arithmetic is the most-dialect-specific part of SQL — look up your database's flavour and treat it as a vocabulary detail rather than a deep concept.

DELETE with a subquery

Sometimes the rows you want to delete are defined by a relationship to another table:

-- Delete every order_item belonging to a cancelled order
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

Read it inside-out: the subquery returns ids of cancelled orders; the outer DELETE removes every order_item whose order_id is in that list. As ever, run the SELECT version first to preview which rows you'd touch.

Foreign keys and the order of deletion

Try to delete a parent row before its children and the database refuses:

DELETE FROM users WHERE id = 1;
-- ERROR: foreign key constraint violated
-- (orders.user_id still references users.id)

Two ways to handle that:

• Delete bottom-up. Delete the children first, then the parent.

  Copy

  DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);
  DELETE FROM orders     WHERE user_id = 1;
  DELETE FROM users      WHERE id = 1;

• Use ON DELETE CASCADE on the foreign key. When the schema declares FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, deleting the parent automatically deletes the children. Convenient, but explicit — the team had to design it.

For test-data cleanup, the bottom-up approach is the safer default. CASCADE is great when it's intentional, dangerous when it's accidental.

TRUNCATE vs DELETE

When you really do want to wipe an entire table, TRUNCATE is faster than DELETE FROM ... with no WHERE:

Use TRUNCATE when wiping a test environment's table is the explicit intent. For day-to-day test cleanup, DELETE with a WHERE clause is the right tool because it leaves untouched rows alone.

Soft delete — the production-friendly pattern

Many production schemas don't actually delete rows. Instead they mark a row as deleted with a flag:

-- Soft-delete: never actually remove the row
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 5;

Application queries then filter on WHERE deleted_at IS NULL to "hide" the deleted row. Why teams adopt this:

• Audit / compliance. Regulated industries need to keep data around even after a user "deletes" it.
• Mistake recovery. A user can be un-deleted without restoring from backup.
• Cascade simplicity. No foreign-key cleanup needed — the children still reference the soft-deleted parent.

As a tester: when you "delete" through the UI, don't assume the row is gone. SELECT the table directly and check whether the row vanished or simply got a deleted_at timestamp. Both are valid; both have different testing implications.

QA use cases for DELETE

-- 1. Clean up after a test suite
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@ci-test.local'));
DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@ci-test.local');
DELETE FROM users WHERE email LIKE '%@ci-test.local';
 
-- 2. Remove duplicates created by a flaky test
-- (More on duplicate detection in Chapter 5)
DELETE FROM users WHERE id IN (
    SELECT MAX(id) FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
);
 
-- 3. Reset a single test scenario
DELETE FROM order_items WHERE order_id = 99;
DELETE FROM orders WHERE id = 99;

The three-statement bottom-up cleanup is the pattern teams use most often. Bake it into your test framework's teardown and your test database stays trim forever.

⚠️ Common Mistakes

• DELETE without WHERE. Same disaster mode as UPDATE without WHERE, only worse — you don't even have an old value to look at. Always include a WHERE; always preview with SELECT first.
• Deleting parents before children. The database refuses with a foreign-key error. The fix is to delete in dependency order — children, then parents — or to design the schema with explicit CASCADE behaviour.
• Confusing TRUNCATE with DELETE. TRUNCATE has no WHERE clause and may not be transactional. If you need to keep some rows, you want DELETE — not TRUNCATE.

🎯 Practice Task

25 minutes. Against the e-commerce database.

1. Insert one disposable user — 'Cleanup Demo', 'cleanup-demo@qa.local', 'tester'. Verify it exists.
2. Delete that user with a WHERE on the email. Verify with a SELECT — zero rows.
3. Try to delete user id 2 (Bob, who has orders). Observe the foreign-key error. Fix it: delete Bob's order_items, then his orders, then re-attempt the user delete. (You can stop before actually deleting Bob if you want to keep the seed data intact — running the DELETE on order_items and orders is enough to prove the dependency order.)
4. Delete every order with status 'cancelled' (preview with SELECT first). Confirm with a count.
5. Try DELETE FROM order_items with no WHERE in your sandbox. Notice it doesn't ask permission. Re-run the seed data INSERTs to restore the table. Reflect on how invisible the danger is.
6. Stretch: write a "soft delete" UPDATE for a user — if your schema supports it. Our practice schema doesn't have a deleted_at column, but try adding one with ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;, then UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 5. SELECT to confirm Carol's row is still there with deleted_at set. The application would now hide her by filtering on deleted_at IS NULL.

Next lesson: transactions — the safety net that lets you do an INSERT, look around, and then say "actually, undo all that" with one command. Once you have transactions, the WHERE-clause paranoia gets a backup plan.