Testing Databases and Managing Migrations

Treat the schema as code#

Schema changes made by hand on a production server can't be reviewed, repeated, or rolled back. Put every change in a numbered file under version control and run them in order. That's a migration.

migrations/
  001_create_customers.sql
  002_create_orders.sql
  003_add_customer_tier.sql
  004_index_orders_customer_date.sql

Tools like Flyway, Liquibase, Alembic, or Prisma track which files have run in a table (for example schema_migrations) and apply only the new ones. The tool matters less than the discipline: never edit a migration that already ran anywhere. Add a new one.

Safe migration habits#

• Make changes backward compatible, so old and new app versions both work during deploy.
• Add a column as nullable or with a default first, backfill, then add NOT NULL.
• Never rename or drop a column in the same release that stops using it. Do it in two steps.
• Check the ALTER algorithm on large tables (Module 2).
• Backfill in batches, not one giant UPDATE.

-- 003_add_customer_tier.sql
ALTER TABLE customers ADD COLUMN tier VARCHAR(10) NULL, ALGORITHM=INPLACE, LOCK=NONE;

A disposable test database#

Tests should run against a fresh schema, never the shared dev or production one. With Docker:

docker run --rm -d --name mysql-test -e MYSQL_ROOT_PASSWORD=test -p 3307:3306 mysql:8
mysql -h 127.0.0.1 -P 3307 -u root -ptest -e "CREATE DATABASE shop_test"
for f in migrations/*.sql; do mysql -h 127.0.0.1 -P 3307 -u root -ptest shop_test < $f; done

Run the same migrations the production database gets. Then you test the real schema, including constraints.

Test data#

Keep a small deterministic seed file with deliberately awkward rows: a customer with no orders, an order with no items, a NULL city, a product at zero stock, a tied price. Edge cases in your seed find the bugs that "happy path" data never does.

Testing SQL itself#

You can write plain assertions:

-- should be 0: orphan items
SELECT COUNT(*) AS orphans
FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

-- should be 0: negative stock
SELECT COUNT(*) FROM products WHERE stock < 0;

Run checks like these after migrations and in CI. Also test that constraints fire:

-- expect error 3819
INSERT INTO products (name, category, price, stock) VALUES ('x','y',-1,1);

For routines, call them with known inputs and compare to expected output. Frameworks such as tSQLt-style or mysql-unit style tools exist, but a script that exits non-zero on failure is enough to start.

CI: start container ▶ run migrations ▶ load seed ▶ run assertions ▶ run app tests

Rolling back#

Down migrations are optional and often unsafe for data loss. The more realistic safety net is a backup taken right before deploy, plus backward-compatible changes so you can roll back the application without touching the schema.

Common mistakes#

• Editing a migration that already ran.
• Testing against a shared database.
• Seed data that's too clean.
• Dropping a column in the same deploy that stops using it.

Practice#

Create a migrations folder with four migration files for the shop schema and a small shell script that applies them to a fresh container in order. Add a seed file with at least five awkward edge-case rows, and an assertions file with three checks that fail the script if violated.