Schema Generation, Migrations and MySQL Integration

Schema Generation, Migrations and MySQL Integration

JPA can generate your database schema from entity annotations at application startup. This is convenient during development but dangerous in production. For production systems, you need a versioned migration tool like Flyway that applies changes incrementally and tracks what has already run.

Auto-Generation in Development

Spring Boot lets Hibernate auto-generate the DDL by setting a property. During development this means you never write CREATE TABLE statements by hand:

# application.properties
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3306/shop
spring.datasource.username=root
spring.datasource.password=secret

The ddl-auto=update setting tells Hibernate to compare your entities against the existing schema and apply ALTER TABLE statements for any differences. It never drops columns or tables - only adds or modifies. Other values include create (drop and recreate every time), create-drop (create on startup, drop on shutdown), and none (do nothing).

Why Auto-Generation Fails in Production

Auto-DDL has serious problems at scale. It cannot rename columns, it cannot migrate data, it runs on every startup, and it does not coordinate with deployment pipelines. A safer approach is a dedicated migration tool that keeps a history table of applied scripts.

spring.jpa.hibernate.ddl-auto=none
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.url=jdbc:mysql://localhost:3306/shop
spring.flyway.user=root
spring.flyway.password=secret

Flyway Migrations

Flyway scans a classpath directory for SQL files named with a version prefix. It records which scripts have run in a flyway_schema_history table and only executes new ones. Name your files with a version number and description:

src/main/resources/db/migration/
  V1__create_products_table.sql
  V2__add_price_column.sql
  V3__create_orders_table.sql

-- V1__create_products_table.sql
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    price DOUBLE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When you run the application, Flyway checks flyway_schema_history, finds V1 has not been applied (for the first run), executes the script, and records success. On the next deployment it sees V1 and V2 already applied and only runs V3. This is deterministic and repeatable across environments.

Integrating with JPA Entities

The schema must match what Hibernate expects. If your entity has a @Version field or a @Column(name = "order_date") mapping, the migration SQL must create those exact columns. Hibernate will still validate the schema at startup if you set spring.jpa.hibernate.ddl-auto=validate, which throws an error if entities and schema do not match.

Key Points

• ddl-auto=update is convenient for development but unsafe for production use.
• Flyway tracks migration scripts in a history table and applies only new ones.
• Name migration files with a version prefix like V1__description.sql.
• Set ddl-auto=none in production and let Flyway manage the schema entirely.
• Use ddl-auto=validate to verify that entities match the migration-managed schema.
• The migration SQL must match Hibernate entity mappings exactly - column names, types, and constraints.