MySQL & PostgreSQL

MySQL Configuration

Add the MySQL JDBC driver in pom.xml:

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

Configuration in application.properties:

# MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/myapp_db
spring.datasource.username=root
spring.datasource.password=secret123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Hibernate
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

Create the database first:

CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

PostgreSQL Configuration

Add the PostgreSQL JDBC driver:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration in application.properties:

# PostgreSQL
spring.datasource.url=jdbc:postgresql://localhost:5432/myapp_db
spring.datasource.username=postgres
spring.datasource.password=secret123
spring.datasource.driver-class-name=org.postgresql.Driver

# Hibernate
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Create the database:

CREATE DATABASE myapp_db;

Connection Pool: HikariCP

Spring Boot uses HikariCP as the default connection pool. It's very fast and efficient:

# HikariCP Configuration
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.pool-name=MyAppHikariPool

Profile-Specific Configuration

Use profiles to separate configuration per environment:

# application-dev.properties (development)
spring.datasource.url=jdbc:h2:mem:devdb
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

# application-staging.properties (staging)
spring.datasource.url=jdbc:mysql://staging-db:3306/myapp_db
spring.datasource.hikari.maximum-pool-size=10
spring.jpa.hibernate.ddl-auto=validate

# application-prod.properties (production)
spring.datasource.url=jdbc:postgresql://prod-db:5432/myapp_db
spring.datasource.hikari.maximum-pool-size=20
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false

Activate a profile at runtime:

# Via command line
java -jar app.jar --spring.profiles.active=prod

# Via environment variable
export SPRING_PROFILES_ACTIVE=prod

Database Migration: Flyway

Flyway manages database schema changes in a versioned way:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

# Flyway config
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true

Migration file structure:

src/main/resources/db/migration/
├── V1__create_users_table.sql
├── V2__add_email_index.sql
└── V3__create_orders_table.sql

-- V1__create_users_table.sql
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__add_email_index.sql
CREATE INDEX idx_users_email ON users(email);

Database Migration: Liquibase

Another alternative is Liquibase, using XML/YAML/SQL:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

# Liquibase config
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml
spring.liquibase.enabled=true

<!-- db/changelog/db.changelog-master.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog>
    <include file="db/changelog/changes/001-create-users.xml"/>
    <include file="db/changelog/changes/002-add-orders.xml"/>
</databaseChangeLog>

Flyway vs Liquibase Comparison

MySQL vs PostgreSQL Comparison

Choose MySQL for simplicity or PostgreSQL for a more complete feature set. Both are excellent for production. In the next chapter, we'll learn about Testing in Spring Boot.