3

How do I create a temporary database for PostgreSQL using initdb? Also, how do I populate it with dummy data and how do I delete it after usage?

I have an entire schema of the database. I don't want to create temporary tables one by one. Using initdb, I would like to be able to give the schema and get a database with the tables, etcetera created.

This is for running integration tests of a spring boot application. I use mybatis to access the database.

Can I use any other tool like arquillian?

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
Tarun Maganti
  • 3,076
  • 2
  • 35
  • 64
  • Look things about H2 data base. – Zorglube Apr 20 '17 at 13:28
  • 1
    There is no such thing as a "temporary database" in Postgres. initdb creates a complete "cluster" (aka "instance"), not a database. If you want temporary tables, then just run the necessary `create temporary table` statements –  Apr 20 '17 at 13:35
  • @Zorglube [H2's PostgreSQL mode limitations](https://stackoverflow.com/questions/38869723/h2-and-postgresql-compatibility-mode-limitations). I don't know how many such things are there. – Tarun Maganti Apr 21 '17 at 04:08
  • @a_horse_with_no_name There should be a way to atleast mimic the scenario of temporary database. – Tarun Maganti Apr 21 '17 at 04:10
  • @TarunMaganti: no, there isn't –  Apr 21 '17 at 05:41
  • @a_horse_with_no_name They how to test such a database? – Tarun Maganti Apr 21 '17 at 05:43
  • not database, but the application using such database. I don't want to mock it out. I want to verify if SQL queries are appropriate or not. – Tarun Maganti Apr 21 '17 at 05:43
  • Create a dedicated test database and run your integrations tests against that. Testing against a different database then you use in production doesn't really make sense. To manage the schema use Liquibase. –  Apr 21 '17 at 05:48
  • 1
    @TarunMaganti, you can't exactly mimic PostgresSQL with H2. By the way to make some DB simulation, I recommend you `JUnit` (http://junit.org) + `H2` (http://www.h2database.com) + `DBSetup` (http://dbsetup.ninja-squad.com) or `DBUnit` (http://dbunit.sourceforge.net) ; it's probably not the best package but it's a working one. – Zorglube Apr 21 '17 at 09:18

1 Answers1

1

There's a workaround this, just change the data directory to a RAM file system, like this once you restart your computer, the data is gone. better you won't change anything in your scripts.

rachid el kedmiri
  • 2,376
  • 2
  • 18
  • 40
  • What do you mean "better you won't change anything in your scripts". And can I do this in CI? – Tarun Maganti Apr 21 '17 at 04:15
  • It seems to be an quite risky solution, but why not. At the end the DB will be in RAM. – Zorglube Apr 21 '17 at 09:11
  • I mean by "better ... scripts", that you won't change the script creating your tables and the one inserting data into it. this use case is just for testing, CI is all about testing. – rachid el kedmiri Apr 21 '17 at 10:22