Database Setup: H2, MySQL & PostgreSQL

An in-memory database is perfect while you learn and test: it starts in a second and is empty again after every restart. A real application needs a database server that keeps data safe for years. Spring Boot lets you switch between them by changing configuration only, and profiles let you use both: H2 on your laptop, MySQL or PostgreSQL on the server.

In this lesson you will configure H2 with a fixed name, browse it with the H2 console, load sample data with data.sql, then connect to MySQL and PostgreSQL, choose the right ddl-auto setting, and meet database migrations.

H2 for development

With the H2 driver on the classpath and no URL configured, Spring Boot creates an in-memory database with a random name. Give it a fixed name and a few settings in application.properties:

spring.datasource.url=jdbc:h2:mem:academy
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.jpa.open-in-view=false
spring.h2.console.enabled=true

The startup log confirms the connection pool, the database and the console:

INFO 20800 --- [academy] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:academy user=SA
INFO 20800 --- [academy] [           main] o.s.b.h.a.H2ConsoleAutoConfiguration     : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:academy'

• HikariCP is the connection pool Spring Boot always prefers. It keeps a few database connections open and lends them to requests.
• The H2 console is a small web page for running SQL against the database. Open http://localhost:8080/h2-console, enter the JDBC URL jdbc:h2:mem:academy, user sa and an empty password. In Spring Boot 4 it lives in the separate module spring-boot-h2console, which Initializr adds for you when you pick H2. It is for development only: never enable it on a server.
• spring.jpa.open-in-view=false switches off a default that keeps a database connection open for the whole web request. Spring Boot logs a warning when you leave it on; switching it off makes lazy-loading mistakes visible early, as you will see in the next lessons.

Sample data with data.sql

Spring Boot runs schema.sql and data.sql from src/main/resources when it uses an embedded database. By default these scripts run before Hibernate creates the tables, so an insert would fail. spring.jpa.defer-datasource-initialization=true (set above) runs them after Hibernate:

insert into teams (name) values ('Deccan Strikers'), ('Shivneri Lions');

insert into players (name, role, age, city, runs, joined_on, team_id) values
  ('Aarav Patil', 'BATTER', 15, 'Pune', 412, '2025-06-02', 1),
  ('Priya Deshmukh', 'BOWLER', 14, 'Pune', 88, '2025-06-09', 1),
  ('Kabir Shaikh', 'ALL_ROUNDER', 16, 'Mumbai', 351, '2025-07-14', 2),
  ('Meera Joshi', 'WICKET_KEEPER', 13, 'Nashik', 205, '2026-01-05', 2),
  ('Rohan Gaikwad', 'BATTER', 17, 'Pune', 530, '2024-11-20', 2),
  ('Ananya Iyer', 'BOWLER', 12, 'Kochi', 40, '2026-03-01', 1);

Use column names (joined_on, team_id), not Java field names. spring.sql.init.mode controls when scripts run: embedded (the default), always or never.

The ddl-auto setting

spring.jpa.hibernate.ddl-auto decides what Hibernate does to the schema at startup:

For any database other than an embedded one, the default is none, so after switching to MySQL you must either create the tables yourself or use a migration tool.

MySQL

Add the driver (its version is managed by Spring Boot) and point the data source at your server:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

# application-prod.properties
spring.datasource.url=jdbc:mysql://localhost:3306/academy
spring.datasource.username=academy_app
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate

Create the database and a user with only the permissions the app needs, as shown in the SQL tutorial, using utf8mb4 so names in every Indian language are stored correctly. ${DB_PASSWORD} reads an environment variable, so the password never sits in the file. Spring Boot works out the driver class from the URL.

If you use XAMPP, note that it ships MariaDB, not MySQL. The two are close, but for MariaDB use the org.mariadb.jdbc:mariadb-java-client driver and a jdbc:mariadb:// URL.

PostgreSQL

PostgreSQL is equally well supported and popular for new projects:

# application-prod.yaml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/academy
    username: academy_app
    password: ${DB_PASSWORD}
  jpa:
    hibernate:
      ddl-auto: validate

With the dependency org.postgresql:postgresql (runtime scope). The entity classes do not change at all: Hibernate picks the right SQL dialect from the connection. That portability is one of the main reasons to use JPA.

Migrations with Flyway

On a real database, schema changes must be repeatable, reviewed and versioned like code. A migration tool runs numbered SQL scripts in order and records which ones have run. With Flyway, add spring-boot-starter-flyway (plus flyway-mysql for MySQL or flyway-database-postgresql for PostgreSQL) and put scripts in src/main/resources/db/migration:

-- V1__create_players.sql
create table players (
  id bigint auto_increment primary key,
  name varchar(60) not null,
  role varchar(20) not null,
  age int not null,
  city varchar(40) not null,
  runs int not null default 0,
  joined_on date
);

At startup Flyway applies any script that has not run yet. Set ddl-auto=validate so Hibernate only checks that the tables match your entities. Never edit a migration after it has run on a shared database; add V2__... instead. Liquibase (spring-boot-starter-liquibase) is the other popular choice.

Common mistakes

• Leaving ddl-auto=create-drop with MySQL. Every restart deletes all data. Use validate or none outside development.
• data.sql failing with "table not found". Scripts ran before Hibernate created the tables; set spring.jpa.defer-datasource-initialization=true, or use migrations.
• Passwords in application.properties. Read them from environment variables, as with ${DB_PASSWORD}.
• Connecting as root. Create an application user with limited rights.
• H2 console on a server. It gives anyone a SQL prompt to your database. Keep spring.h2.console.enabled for development profiles only.

Key points

• H2 in memory needs only the driver; set spring.datasource.url=jdbc:h2:mem:academy for a fixed name and enable the console for development.
• data.sql loads sample data; with Hibernate creating tables, also set spring.jpa.defer-datasource-initialization=true.
• Switch to MySQL or PostgreSQL by adding the driver and setting spring.datasource.url, username and password; entities do not change.
• ddl-auto defaults to create-drop for embedded databases and none for others; use validate with migrations in production.
• Flyway or Liquibase manage schema changes as versioned scripts.

# application-dev.properties
spring.datasource.url=jdbc:h2:mem:academy-dev
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.defer-datasource-initialization=true
spring.h2.console.enabled=true

# application-prod.properties
spring.datasource.url=jdbc:postgresql://db.internal:5432/academy
spring.datasource.username=academy_app
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate
spring.h2.console.enabled=false