Feature Request: JBang-Based Database Migration Tool with DDL Generation and Rollback Support

[anandjaisy]

Problem

Most Java projects using frameworks such as Spring Boot, Micronaut, or Quarkus require a database migration solution such as Flyway or Liquibase.

While these tools are powerful and widely adopted, one pain point is that database migrations are typically tied to the application's build system. Developers often need to use Maven or Gradle to generate, configure, or execute migrations.
I would like to propose a lightweight, standalone database migration approach using JBang.
The idea is to provide a project-level CLI that can:

1. Generate DDL scripts from JPA/Hibernate entities.
2. Apply migrations to the database.
3. Track applied migration versions.
4. Roll back the latest migration.
5. Move the database forward or backward to a specific version.

This would allow database migrations to be managed independently of whether the application uses Maven, Gradle, Spring Boot, Micronaut, or Quarkus.

Example Entity

For example, given the following JPA entity:

@Entity
public class Facility extends AuditTrail {

    @Length(max = 200)
    public String name;

    @Length(max = 50)
    public String code;

    @Length(max = 500)
    public String address;

    @Length(max = 100)
    public String city;

    @Length(max = 100)
    public String state;

    public int zipCode;

    @OneToMany(
        mappedBy = "facility",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    public List<UserFacility> userFacilities = new ArrayList<>();

    @OneToMany(
        mappedBy = "facility",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    public List<Ward> wards = new ArrayList<>();
}

The tool could use the entity metadata and JPA annotations to help generate the corresponding DDL migration.
For example:

CREATE TABLE Facility
(
    id               UUID NOT NULL,
    createdBy        VARCHAR(255),
    createdDate      TIMESTAMP WITH TIME ZONE,
    lastModifiedBy   VARCHAR(255),
    lastModifiedDate TIMESTAMP WITH TIME ZONE,
    name             VARCHAR(200),
    code             VARCHAR(50),
    address          VARCHAR(500),
    city             VARCHAR(100),
    state            VARCHAR(100),
    zipCode          INTEGER NOT NULL,

    CONSTRAINT pk_facility PRIMARY KEY (id)
);

Proposed Workflow

The migration process could consist of three main steps:

1. Create a Migration Script
   jbang add script initial_migration
   This command could:

• Generate a new migration version.
• Create a migration file using the supplied name.
• Optionally generate DDL based on entity metadata.
• Create both an up and down migration definition.
  For example:

migrations/
├── V001__initial_migration.sql
├── V002__add_ward_table.sql
└── ...
Rollback scripts could follow a separate naming convention:
migrations/
├── V001__initial_migration.up.sql
├── V001__initial_migration.down.sql
├── V002__add_ward_table.up.sql
└── V002__add_ward_table.down.sql

2. Apply Database Migrations
   jbang update database
   This command would:

• Identify the current database migration version.
• Detect pending migrations.
• Apply them sequentially.
• Record successful migrations in a migration history table.
  For example:
  jbang update database

Current version: V003
Applying: V004__add_staff_table
Applying: V005__add_shift_table

Database successfully updated to V005

3. Roll Back the Latest Migration
   jbang undo database
   This command would:

• Identify the most recently applied migration.
• Execute its corresponding rollback script.
• Update the migration history table.

Example:
jbang undo database

Rolling back: V005__add_shift_table

Database successfully rolled back to V004

Version-Based Migration

It would also be useful to support moving the database to a specific version.

For example:
jbang update database 10
The tool would migrate forward until version 10 is reached.

Similarly:
jbang undo database 7

The tool would execute rollback migrations until the database reaches version 7.
This would allow developers to move both forward and backward between migration versions:

V001 → V002 → V003 → V004 → V005
                     ↓
                    V003
                     ↓
                    V004 → V005

Suggested Migration History Table
The tool could maintain its own migration history table, for example:

CREATE TABLE jbang_schema_history (
    version         VARCHAR(50) NOT NULL PRIMARY KEY,
    description     VARCHAR(255),
    script          VARCHAR(500) NOT NULL,
    checksum        VARCHAR(100),
    applied_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    execution_time  BIGINT,
    success         BOOLEAN NOT NULL
);

This would allow the CLI to determine:

• Which migrations have already been applied.
• The current database version.
• Whether a migration was successfully executed.
• Whether a migration file has been modified after being applied.

Potential Commands
The initial CLI could support something similar to:

Create a new migration

jbang add script <migration_name>

Generate DDL from entities

jbang generate ddl

Apply all pending migrations

jbang update database

Apply migrations up to a specific version

jbang update database <version>

Roll back the latest migration

jbang undo database

Roll back to a specific version

jbang undo database <version>

Show current migration status

jbang database status

Why JBang?

Using JBang could provide a lightweight and framework-independent experience.
The migration tool could be executed consistently across projects without requiring developers to interact directly with:

• Maven plugins
• Gradle tasks
• Framework-specific migration integrations
  For example, the same migration workflow could potentially be used with:
• Spring Boot
• Micronaut
• Quarkus
• Plain Java applications
  The JBang CLI could act as a standalone developer tool while still allowing applications to use the generated migration scripts during deployment.

Important Considerations
One important requirement would be safe and explicit rollback support.
Automatic generation of a rollback script is not always possible. For example, while adding a column can usually be reversed by dropping the column, a data migration may not have a safe automatic rollback.
For this reason, it may be better to support explicit migration pairs:

V001__initial_schema.up.sql
V001__initial_schema.down.sql

This would make rollback behavior predictable and avoid potentially unsafe automatic database changes.

Expected Benefits

• Framework-independent database migrations.
• No direct dependency on Maven or Gradle commands for migration operations.
• Simple and consistent CLI commands.
• Version-based forward and backward migration.
• Support for DDL generation from JPA/Hibernate entities.
• Explicit rollback support.
• A lightweight developer experience powered by JBang.
  I believe this could be particularly useful for teams working across multiple Java frameworks while wanting a consistent and standalone approach to database schema migrations.

[maxandersen]

Use hibernate tools jar ? It has those features ?