🧪 Provisioning Strategies for Test Databases

For test runners that spin up isolated PostgreSQL databases per test flow (like noCRUD), provisioning speed has a huge impact on total runtime.

This guide compares three common provisioning methods and recommends the fastest option for day-to-day usage.

---

🧩 Why It Matters

If your runner executes dozens of flows like this:

for flow in test_flows:
    create_db()
    run_test(flow)
    drop_db()

...then your provisioning strategy directly affects overall speed.

---

🚀 Provisioning Strategies Compared

Strategy	One Flow	All Flows (~50)	Notes
✅ createdb -T template	1.3s	7.3s	Fastest – clones a pre-provisioned DB
🟡 psql -f schema.sql	2.7s	13.6s	Parses and runs DDL each time
🔴 python manage.py migrate	5.8s	26.5s	Slowest – ORM initialization overhead

---

✅ Recommended: Use a Template Database

createdb -T clones a PostgreSQL database at the file level. It's much faster than executing SQL or Django migrations.

🔧 Setup (One-Time)

Create your template database from schema:

createdb template_db
psql template_db < schema.sql

Or from Django migrations:

createdb template_db
python manage.py migrate --database=template_db

---

🧪 Test Flow Provisioning

Instead of running migrations or applying SQL on every test, use createdb:

createdb -T template_db test_run_xyz

Boom — near-instant database ready to go.

noCRUD - see provision_django_env_direct_via_template_db which calls provision_db_from_template

---

🧹 Cleanup

dropdb test_run_xyz

noCRUD - I have a psql wrapper drop_dbs_by_pattern.sh

---

🔄 Keeping the Template Up-To-Date

If your schema changes, you’ll need to refresh the template:

With SQL:

dropdb template_db
createdb template_db
psql template_db < schema.sql

With Migrations:

dropdb template_db
createdb template_db
python manage.py migrate --database=template_db

noCRUD - check out refresh-schema.sh