1

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?

amchugh89
  • 1,276
  • 1
  • 14
  • 33

2 Answers2

1

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.

Community
  • 1
  • 1
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • it is openerp, which is in python and the method I am testing is in python. My method is small but triggers many many calls that I don't really understand, and in fact I call cr.rollback(), and it gets called in other places - here cr is a cursor object built into openerp. Is there anyway to just replace a database? – amchugh89 Aug 10 '16 at 08:19
  • replace a database? yeah what you are doing in the code sample you posted! or if it's a database with data pg_restore – e4c5 Aug 10 '16 at 08:22
  • sorry i meant a database table – amchugh89 Aug 10 '16 at 15:37
1

Test inside a transaction:

begin;
update t
set a = 1;

Check the results and then:

rollback;

It will be back to a clean state;

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260