amchugh89 amchugh89 - 3 months ago 12
SQL Question

Reset specific tables with clean data

I am doing a test that updates my database each time I run it

And I cannot do the test again with the updated values

I am recreating the WHOLE database with:

postgres=# drop database mydb;
DROP DATABASE
postgres=# CREATE DATABASE mydb WITH TEMPLATE mycleandb;
CREATE DATABASE


This takes a while

Is there any way I can update just the tables that I changed with tables from mycleandb?

Answer

Transactions

You haven't mentioned what your programming language or framework are. Many of them have built in test mechanisms that take care of this sort of thing. If you are not using one of them, what you can do is to start a transaction with each test setup. Then roll it back when you tear down the test.

BEGIN;
 ...
 INSERT ...
 SELECT ...
 DELETE ...
ROLLBACK;

Rollback, as the name suggests reverses all that has been done to the database so that it remains at the original condition.

There is one small problem with this approach though, you can't do integration tests where you intentionally enter incorrect values and cause a query to fail integrity tests. If you do that the transaction ends and no new statements can be executed until rolled back.

pg_dump/pg_restore

it's possible to use the -t option of pg_dump to dump and then restore one or a few tables. This maybe the next best option when transactions are not practical.

Non Durable Settings / Ramdisk

If both above options are inapplicable please see this answer: http://stackoverflow.com/a/37221418/267540

It's on a question about django testing but there's very little django specific stuff on that. However coincidentally django's rather excellent test framework relies on the begin/update/rollback mechanis described above by default.

Comments