Feroz Shah

Posted on Oct 25 Originally published at migrax.org

Generate SQL migrations from your JPA entities

#java #database #postgres #hibernate

Your JPA entities already describe the schema you want. Writing the same change again by hand as SQL is slow and easy to get wrong. Migrax compares your entities with the last state you committed and writes the SQL migration for you, along with a rollback script. You review the SQL, commit it, and apply it.

This article walks through that loop on a small project: create the first tables, add a column, and catch a missing migration in CI.

Tested with: Migrax 0.16.0, Hibernate 6.6.13, PostgreSQL 17. Every output below was copied from a real run.

The project

Two entities, a customer and an order:

@Entity
@Table(name = "customers")
public class Customer {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  Long id;

  @Column(nullable = false, unique = true, length = 150)
  String email;

  @Column(nullable = false, length = 100)
  String name;
}

@Entity
@Table(name = "orders")
public class PurchaseOrder {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  Long id;

  @ManyToOne(optional = false)
  Customer customer;

  @Column(nullable = false, precision = 12, scale = 2)
  BigDecimal total;

  Instant createdAt;
}

Migrax needs to know where the database is. In src/main/resources/application.properties:

migrax.url=jdbc:postgresql://localhost:5432/shop
migrax.user=shop
migrax.password=shop

A Spring Boot, Quarkus, Micronaut or Helidon project can skip this: Migrax reads the framework's own data source settings.

Install Migrax, then run migrax init and migrax doctor in the project folder. init creates the migration folder; doctor checks that the project compiles, finds the entities and reaches the database.

The first migration

$ migrax generate
No snapshot yet: using the current database schema as the baseline.
Created src/main/resources/db/migration/0001_initial.sql:
  - create table customers
  - create table orders
  - add foreign key fk_orders_customer_id_54d4cc52 on orders
Rollback script written to src/main/resources/db/migration/rollback.
Review the SQL, then run 'migrax migrate'.

Migrax reads the entities through Hibernate's own mapping, so the SQL matches what Hibernate expects, column types and constraint names included:

db/migration/0001_initial.sql:

-- Generated by Migrax 0.16.0 for postgresql

CREATE TABLE customers (email varchar(150) NOT NULL, id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL, name varchar(100) NOT NULL, CONSTRAINT customers_pkey PRIMARY KEY (id), CONSTRAINT uk_customers_email UNIQUE (email));
CREATE TABLE orders (createdAt timestamp(6) with time zone, customer_id bigint NOT NULL, id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL, total numeric(12,2) NOT NULL, CONSTRAINT orders_pkey PRIMARY KEY (id));
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id_54d4cc52 FOREIGN KEY (customer_id) REFERENCES customers (id);

Next to it is the rollback script. It says which data a rollback would delete:

db/migration/rollback/0001_initial.sql:

-- Generated by Migrax 0.16.0 for postgresql
-- Rollback for 0001_initial.sql. 'migrax rollback' runs it.
-- Note: deletes data written to customers since the migration ran.
-- Note: deletes data written to orders since the migration ran.

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer_id_54d4cc52;
DROP TABLE customers;
DROP TABLE orders;

Both are plain SQL files. Read them, change them if you want, and apply them:

$ migrax migrate
Applying migration '0001_initial.sql'...
Successfully applied migration '0001_initial.sql'.
Applied 1 migration(s); 1 total in src/main/resources/db/migration.

Migrax also wrote .migrax/snapshot.json: the schema your entities describe right now. Commit it with the migration files. The next generate compares against it, so it doesn't need a database to work out what changed.

Adding a column

Add a field to Customer:

  @Column(length = 30)
  String phone;

$ migrax generate
Created src/main/resources/db/migration/0002_add_customers_phone.sql:
  - add column customers.phone
Rollback script written to src/main/resources/db/migration/rollback.
Review the SQL, then run 'migrax migrate'.

db/migration/0002_add_customers_phone.sql:

ALTER TABLE customers ADD COLUMN phone varchar(30);

The file name describes the change. The rollback script is ALTER TABLE customers DROP COLUMN phone;. Apply it with migrax migrate, as before.

Keeping entities and migrations in sync

Someone will eventually change an entity and forget to generate the migration. Say a teammate renames the field name to fullName (column full_name) and pushes only the Java change. migrax check catches that without a database, so it fits in CI:

$ migrax check
2 entity change(s) have no migration:
  - drop column customers.name
  - add column customers.full_name
Run 'migrax generate' and commit the result.

It exits with code 2 when a migration is missing and 0 when everything matches:

$ migrax check
OK: entities match the snapshot.

Summary

Change an entity, run migrax generate, read the SQL, commit, run migrax migrate.
Every migration comes with a rollback script that says what data it would delete.
migrax check in CI catches entity changes that have no migration.

Originally published at migrax.org.

DEV Community