Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself.
I know that I could wrap my test in a transaction and rollback the transaction after the test run. But it would be very nice if PostgreSQL could provide some kind of global snapshoting or throwaway overlay. In an ideal case such a feature would cover all state of the database including schemas and stored procedures.
One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template.
create database test_db
with template = base_test_db;
Using this, everything that is in the template will be copied to the new database (including data, stored procedures, extensions).
After each test suite, you simple drop test_db and re-create it with the above statement.
This approach might not be fast enough if you do it before each test, but that highly depends on how large your test database is and how fast the harddisk of your build environment is.
Every time I run integration tests, I build my database from scratch off scripts stored in git. In the past, whenever slowness would become a problem, we would run the server locally on developer's workstation, with the database on RAM Disk - that should be fast enough. We did that many times for our applications powered by SQL Server
I have not yet done that for PostgreSql, but I am very new to it.
ROLLBACK TO SAVEPOINT perhaps?
"Roll back all commands that were executed after the savepoint was established. The savepoint remains valid and can be rolled back to again later, if needed.
https://www.postgresql.org/docs/9.1/static/sql-rollback-to.html
