Create database (e.g Flyway or other) before hibernate kicks in

Question

I am using Spring and Hibernate and I want to create a database before hibernate kicks in otherwise I am getting a Caused by: org.postgresql.util.PSQLException: FATAL: database "db" does not exist.

I have this in my properties file:

spring.datasource.url=jdbc:postgresql://postgres:5432/db
spring.datasource.username=username
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
server.port=3001

db does not exist in the postgres database so it throws the exception.

Some people say I can use Flyway:

Create PostgreSQL database on the fly using Hibernate even if the DB doesn't exist

Others say it is not possible:

How to create a database with flyway?

How can I accomplish the creation of the db with flyway?
Or if it is used just for the migrations after creation how can I create the initialization bean (can you show me some code) that runs before as stated in the accepted answer here:

Create PostgreSQL database on the fly using Hibernate even if the DB doesn't exist

And start using flyway for the migrations (first tables, etc)...

After that I could switch ddl-auto to none.

What would be the flow?

saw303 · Answer 1 · 2019-05-02T10:00:18.283

0

TL;DR: Thing is that your empty database db needs to exist. Why is that?

In your example you tell Spring to create a DataSource to jdbc:postgresql://postgres:5432/db. Which means you are now trying to connect to a database server and a specific database called db that does not exist.

The flow is that you

first make sure that the empty database you are trying to connect to always exists. The is required.
After that you can run your application and Flyway can do the migration before Hibernate starts to validate the database schema.

UPDATE

As far as I understand you are working with Docker. That's great.

Given the following docker-compose.yml we declare two images.

db that is the container that runs the PostgresDB
app that is the container that runs your Spring (Boot) application.

By declaring the environment variable POSTGRES_DB you can tell Postgres to create an empty database at startup and use the env vars name.

version: '3.5'

services:

  db:
    image: postgres:11.2-alpine
    restart: always
    environment:
      POSTGRES_DB: myDB
      POSTGRES_PASSWORD: myPassword
      POSTGRES_USER: myUser

  app:
    image: <your/spring-boot-image>
    restart: always
    ports:
      - 8080:8080

In your Spring Boot application you can then enable Flyway support and put your database scripts under classpath:db/migration. I usually start with one migration script V0_0_1__Initial.sql that contains all the CREATE TABLE statements of my JPA entities. This scripts will be executed at start when Spring successfully connected to the database.

spring.datasource.url=jdbc:postgresql://db:5432/myDB
spring.datasource.username=myUser
spring.datasource.password=myPassword
spring.jpa.hibernate.ddl-auto=validate

Please make sure that I have set spring.jpa.hibernate.ddl-auto to validate since we don't what Hibernate to modify the database schema but it should validate and abort when the schema is out of sync with your entities.

So when your Spring apps starts the following happens:

Spring creates a database connection to your database.
Flyway checks whether a migration is required and if yes it applies the migration scripts.
Hibernate validates the database schema against the JPA entity classes and would throw an exception if they are out of sync.

This is how we do database migrations with Flyway, Spring and Docker. Hope this helps. Good luck.

edited May 02 '19 at 10:00

answered Apr 30 '19 at 11:20

saw303

8,051
7
50
90

Ok. But I would like to create the db through code if it is not present. What would be my possibilities in spring? Can you help me? – cyp Apr 30 '19 at 12:13
My ideia is to create the db before spring opens the connection to it. But I can't find the location where I should be doing this. – cyp Apr 30 '19 at 12:28
1

You should do that by using a db tool (pgadmin, psql, datagrip...). It makes no sense to create a database on the startup of the application, unless you plan to use it or tests or something like that, but in that case you should checkout something like testcontainers. – Dimitar Spasovski Apr 30 '19 at 13:35
@cyp thing is that you should NOT create the database with the application that is using the database. No one does it that way. It is not a good design. – saw303 May 01 '19 at 05:59
@saw303 Yes I understand. I am using docker compose where I have a container (app) that depends on another (postgres) one and I would like to run a command in the postgres container in the middle of that dependency. Another option would be to make it part of the gitlab ci script. When I deploy to the server I would before docker-compose up the app container be sure that the db was created in the postgres container. What would be your approach? Probably I should make another question in stack overflow? – cyp May 01 '19 at 08:12
@saw303 the thing is thats completely made up and utter nonsense. Creating a database at startup literally is the purpose of ORM config parameters like hbm2ddl.auto, the flyway & liquibase - support in spring (boot) and the likes, there is an **entire industry** around application startup database creation / migration, its especially useful for microservices and/or clustered services. – specializt May 01 '19 at 08:57
1

@specializt From other questions in stack overflow I think you shouldn't, in production, be using ddl-auto and hbm2ddl.auto create. And btw, when I say create the database it is not the schema and tables but the actual database that you then put in your datasource url. In flyway, from what I could understand you can't issue a CREATE DATABASE db because Flyway needs to connect to a database already created. It is only concerned with migrations (table creation etc). – cyp May 01 '19 at 09:20
@specializt we are not on the same page. Yes, ORMs can create `TABLE` and other objects within an *existing* database schema. But they cannot to the `CREATE DATABASE` stuff when using a JDBC connection string to a database that does not exist. Connection will be refused. This is what we are talking about. Not utter nonsense. @cyp in a docker-compose world you can tell the Postgres Image to create an Docker container that will create a DB with a DB user and password. So when your Spring/Hibernate application starts the DB is already there and Flyway can start to `CREATE TABLE` you db. – saw303 May 01 '19 at 17:49
@cyp I added the way we are doing it when working with Spring, Flyway & Docker Compose. Hope this helps – saw303 May 02 '19 at 10:02
@specializt stop acting in that aggressive behaviour, please. you don't agree with my answer. I got that and I'm okay with that. But the way you act is absolutely inappropriate. By the way I can't delete your comments. You are right about the fact that "CREATE TABLE", "CREATE DATABASE" are all vaild SQL statements. But I no tool in the world can send them to a SQL database which is refusing the connection. That's what I'm trying to say. – saw303 May 02 '19 at 16:31

specializt · Answer 2 · 2019-05-02T06:18:51.980

0

Spring supports flyway and/or liquibase execution at startup (yes, even before your ORM layer tries to connect to it) :

To automatically run Flyway database migrations on startup, add the org.flywaydb:flyway-core to your classpath.

The migrations are scripts in the form V[VERSION]__[NAME].sql (with an underscore-separated version, such as ‘1’ or ‘2_1’). By default, they are in a folder called classpath:db/migration, but you can modify that location by setting spring.flyway.locations. This is a comma-separated list of one or more classpath: or filesystem: locations. For example, the following configuration would search for scripts in both the default classpath location and the /opt/migration directory

source :

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-execute-flyway-database-migrations-on-startup

please read the manual.

Also : you can (of course) even execute statements like CREATE DATABASE, you just need a seperate connection for that : How to create a database with flyway?

Some JDBC drivers even allow for skipping that step entirely : https://stackoverflow.com/a/34379819/351861

edited May 02 '19 at 06:18

answered Apr 30 '19 at 12:40

specializt

1,913
15
26

I actually read it but from the manual I cannot understand if I can actually create the database ("CREATE DATABASE db")? – cyp Apr 30 '19 at 12:46
yes, thats **literally** the **purpose** of DB migration tools like flyway and liquibase, just be sure to include the corresponding statement – specializt Apr 30 '19 at 12:48
1

@specializt doing a database migration on `jdbc:postgresql://postgres:5432/db` when `db` does not exist is not working out. As @cyp explained the thing will end up in a `org.postgresql.util.PSQLException: FATAL: database "db" does not exist` error. When you application cannot connect to the db it actually has no chance to apply any SQL statement to it – saw303 May 01 '19 at 19:26
yes. Inexistent database servers cannot be contacted. Kind of obvious. – specializt May 02 '19 at 19:30

Create database (e.g Flyway or other) before hibernate kicks in

2 Answers2