2

I'm integrating SQL database (PostgreSQL) into my first ever Java backend system.

My dilemma now is should the application create the database and the tables by itself, or should it just assume that the required database initialized with the tables will be available?

If the application should not manage the database by itself, then what's the best approach to reproducing the database on developers' machines (I want to be able to run integration tests with real database locally)?

Vasiliy
  • 16,221
  • 11
  • 71
  • 127
  • If the app doesn't create/init the database, then someone else will have to. If you let your app worry about this, then you can try to have consistent behavior. – Tim Biegeleisen Jul 02 '17 at 09:19
  • 1
    Have a look at [liquibase](http://www.liquibase.org/) or [FlyWay](https://flywaydb.org/) – Max Jul 02 '17 at 09:23
  • Usually, you don't let the application manage database. If you ever decide to switch away from psotgres, less rework. Better to simply provide an SQL script for others to run. Even better - give docker/vagrant images. Other don't even have to worry about having postgres installed. – Vineet Jul 02 '17 at 09:23
  • https://stackoverflow.com/questions/175451/how-do-you-version-your-database-schema - once you explore what you need, I think it boils down to versioning of databases – Neville Kuyt Jul 02 '17 at 09:23
  • 1
    I would create the DB outside the app. PL/pgSQL scripts are an excellent way to create/populate and maintain your developers databases. – Paulb Jul 02 '17 at 09:41
  • 1
    Thanks everybody for comments and ideas. After a quick review of my options, I decided to proceed with FlyWay as suggested by @Max. Will report back on my experience once get it working. – Vasiliy Jul 02 '17 at 11:09
  • @Vasiliy good luck! – Max Jul 02 '17 at 11:12

1 Answers1

0

I ended up using FlyWay as @Max suggested in his comment.

The setup was quite easy and intuitive. I used the default settings for time being.

Setup of integration testing on development machine was a breeze.

The only thing that I don't particularly like is that cleaning the database between tests using FlyWay is very slow because it doesn't just clean the tables, but erases the entire scheme.

This, however, is not a big issue for me right now (currently have ~100 tests), and, probably, will not be difficult to optimize in the future if needed.

Vasiliy
  • 16,221
  • 11
  • 71
  • 127