How to configure database in a Spring Boot app for cloud and local development?

Question

I have a Spring Boot application that connects to a MySQL database. I'm planning to upload the application to the cloud and I'm concerned about the database setup. I want to ensure that my database configuration prevents database loss, errors when new tables are introduced, or modifications to existing tables.

I'm wondering if anyone can provide guidance on best practices for configuring a database for a Spring Boot application in the cloud. Specifically, I'm wondering about the following:

• Should I rely on JPA with ddl-auto=something or use some kind of migration tool?
• How can I prevent someone from accidentally using create-drop and deleting the production database?
• Should I use profiles to manage different configurations for development, testing, and production?

Any tips, suggestions, or best practices would be much appreciated. Thanks in advance for your help!

Answer 1

Should I rely on JPA with ddl-auto=something or use some kind of migration tool?

You should only use the spring.jpa.hibernate.ddl-auto=validate setting and manage your database structure manually with Flyway (tutorial) or Liquibase (tutorial). By this means, you don't risk Hibernate overriding your existing DB structures.

How can I prevent someone from accidentally using create-drop and deleting the production database?

You could create two users for your DB: springuser and flywayuser. While flywayuser is granted all rights on all objects in your DB scheme, springuser is only granted read and write access on table contents and sequences, but not the rights to alter the DB structure. Postgres example:

-- Connection rights
grant connect on database mydb TO flywayuser, springuser;

-- Schema rights
create schema if not exists myschema;
grant all privileges on schema myschema to flywayuser;
grant usage on schema myschema to springuser;

-- Sequence rights
grant all privileges on all sequences in schema myschema to flywayuser, springuser;

-- Table rights
grant all privileges on all tables in schema myschema to flywayuser;
grant select, insert, update, delete on all tables in schema myschema to springuser;

You can then specify the different credentials for the users in the application.yml:

spring:
  datasource:
    username: ${DB_USER}
    password: ${DB_PASSWORD}
  flyway:
    schemas: myschema
    user: ${FLYWAY_DB_USER}
    password: ${FLYWAY_DB_PASSWORD}

The same applies for Liquibase, check the docs for the availbale config properties.

Should I use profiles to manage different configurations for development, testing, and production?

You don't need a cloud specific profile if you use placeholders in your application.yml like in the example above. You just need to make sure, that the environment variables are available on the OS where you run your app, for example in Kubernetes via deployment & config map / secret.
However, for local and test I'd recommend an extra profile with application-local.yml and application-test.yml for easier development. You want to make it as easy as possible for other developers to get started with your code and having to configure several enviroment variables in order to being able to start the application locally is not practical at all.