CODEWITHSUNDEEP
sqlpostgresqlpsql
amchugh89
amchugh89

Reputation: 1296

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?

Upvotes: 1

Views: 126

Answers (2)

e4c5
e4c5

Reputation: 53734

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: https://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.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Test inside a transaction:

begin;
update t
set a = 1;

Check the results and then:

rollback;

It will be back to a clean state;

Upvotes: 1

Related Questions