It isn't clear if you're already doing this, but Postgres has a 'create database from template' feature. You can initialize your template database with migrations once for a whole test suite, then clone a new database at the start of each test.
It's quite fast. I run almost all of my tests this way.
I agree, forcing your app into the lowest common denominator of portable SQL is crippling. JSONB columns in particular are extremely useful in Postgres.
Nice! I did not know Postgres had that. We'll probably do something similar next time we prioritize improvements to the test runner.
Right now we are pulling a postgres:10 container down from our ECR on every run :laughcry: so definitely some low hanging fruit around that.
I think we will rebuild the postgres container up to our most recent migrations in prod branch then bake that container onto the gitlab runner AMI daily. Then the test runner can just start that and apply any migrations in the merge request and proceed with the tests.
It's quite fast. I run almost all of my tests this way.
I agree, forcing your app into the lowest common denominator of portable SQL is crippling. JSONB columns in particular are extremely useful in Postgres.