Date: 19 May 2026 Project Type: Custom Database Migration Tool (Java + Spring Boot + PostgreSQL) Current State: Functional prototype with migration execution, dependency parsing, validation, connection handling, and migration tracking partially implemented.
The project is a custom database migration framework inspired by tools such as:
- Flyway
- Liquibase
- Entity Framework Migrations
The objective of the tool is to:
- Execute SQL migration scripts in version order
- Track migration history
- Validate dependencies before execution
- Handle rollback/down scripts
- Manage multiple database connections
- Detect dirty database states
- Validate checksums
- Support repeatable migrations
- Provide CLI + REST APIs
- Potentially become a reusable enterprise-grade migration framework
- Java
- Spring Boot
- Spring JDBC
- PostgreSQL
- Spring Shell (CLI)
- REST APIs
- JSQLParser (AST SQL parsing)
| Module | Status | Notes |
|---|---|---|
| Migration Engine | Implemented | Core migration execution exists |
| SQL Executor | Implemented | Executes parsed SQL statements |
| Migration Loader | Implemented | Loads versioned and repeatable migrations |
| Dependency Parser | Implemented | Uses AST parsing through JSQLParser |
| Dependency Validator | Partial | Basic validation exists |
| Checksum Validation | Partial | Logic exists but incomplete |
| Dirty State Handling | Partial | Structure exists |
| Locking System | Partial | Lock service exists |
| REST APIs | Implemented | Basic migration APIs available |
| CLI Commands | Partial | Some commands commented out |
| Connection Management | Implemented | Multi-database handling exists |
| Logging | Implemented | Log entities and services exist |
| Rollback Support | Partial | Structure exists but incomplete |
| Migration Status Tracking | Implemented | Repository + entities exist |
| Repeatable Migration Support | Partial | Loader supports repeatables |
| Auto Dependency Recovery | Planned | Not fully implemented |
Responsibilities:
- Executes migrations
- Coordinates validation
- Runs dependency extraction
- Calls SQL executor
- Updates migration history
- Handles migration lifecycle
Current Status:
✅ Core flow exists ⚠ Validation partially disabled ⚠ Error handling still evolving ⚠ Rollback handling incomplete
Responsibilities:
-
Reads migration files
-
Detects pending migrations
-
Handles:
- Versioned migrations
- Repeatable migrations
-
Parses filenames
-
Sorts migration order
Patterns detected:
V001__description.sqlR__repeatable_name.sql
Current Status:
✅ Well-progressed ✅ Dynamic migration loading exists ✅ Regex parsing exists ⚠ Needs stronger ordering validation ⚠ Needs duplicate version protection ⚠ Needs checksum verification integration
Responsibilities:
- Splits SQL statements
- Executes prepared statements
- Runs migration SQL sequentially
Current Status:
✅ Functional ⚠ No batching optimization ⚠ Transaction rollback handling needs improvement ⚠ Better SQL splitting required for edge cases
Responsibilities:
- Dirty DB detection
- Checksum validation
- Order validation
- Missing migration detection
Current Status:
⚠ Large portions commented out ⚠ Dirty-state logic appears inverted ⚠ Checksum comparison bug exists ⚠ Sequence validation incomplete
Important Issue Found:
if (!repository.existsByDirtyTrue()) {
throw new RuntimeException("Database is in DIRTY state. Resolve before continuing.");
}The condition likely should be:
if (repository.existsByDirtyTrue())because the current logic throws when no dirty state exists.
Responsibilities:
-
Parses SQL into AST
-
Detects:
- Tables
- Columns
- Foreign keys
- Inserts
- Updates
- Select dependencies
Current Status:
✅ Strong advanced feature ✅ JSQLParser integration working ✅ Foreign key dependency extraction exists ⚠ Multi-statement parsing improvement needed ⚠ ALTER TABLE coverage incomplete ⚠ DROP handling incomplete ⚠ Procedure/function parsing absent
This is one of the strongest modules in the project.
Responsibilities:
-
Validate:
- Tables
- Columns
- Indexes
- Foreign keys
-
Ensure dependencies exist before migration execution
Current Status:
✅ Basic validation works ⚠ Validation responses inconsistent ⚠ Exception handling incomplete ⚠ Auto-recovery not implemented ⚠ Cross-migration dependency resolution pending
Implemented:
- Versioned migration loading
- Repeatable migration loading
- SQL file reading
- Pending migration detection
- Regex-based filename parsing
Implemented:
- Multiple database connections
- Active connection switching
- Connection persistence
- Dynamic connection selection
Files involved:
ConnectionContextConnectionServiceConnectionRepo
Implemented:
- Sequential migration execution
- SQL execution engine
- Prepared statement execution
- Migration tracking
- Execution timing
Implemented:
- Migration entity
- Version tracking
- Execution history
- Logs
- Dirty state structure
- Checksum structure
Implemented:
- Connect database
- Set active database
- Migration APIs
- Status APIs
Implemented:
- Spring Shell integration
- Init command
Partial:
- Status command
- Migrate command
Implemented:
- SQL AST parsing
- Dependency extraction
- Table validation
- Column validation
This is an advanced feature not commonly built in beginner migration systems.
Current:
- Dirty flag exists
- Repository query exists
- Logic incomplete
Needed:
- Mark migration failed when exception occurs
- Prevent future migrations on dirty DB
- Add repair command
- Add manual recovery
Current:
- Checksum service exists
- Validation partially implemented
Needed:
- Compare applied checksum vs current file checksum
- Detect tampered migration files
- Repair checksum command
- Validation reporting
Current:
- Down migration idea exists
- Architecture partially prepared
Needed:
- Down migration execution
- Version rollback
- Rollback safety checks
- Transaction rollback integration
- Partial rollback handling
Current:
- Lock service exists
- Lock entities exist
Needed:
- Prevent concurrent migration runs
- Distributed lock handling
- Lock timeout handling
- Crash recovery
Current:
- SQL execution works
Needed:
- Begin transaction
- Rollback on failure
- Commit on success
- Savepoints
- Per-migration transaction mode
Needed:
- Duplicate migration detection
- Gap detection
- Out-of-order migration handling
- Circular dependency handling
- Cross-script dependency validation
These are the biggest features still left.
Purpose:
Repair broken migration metadata.
Should support:
- Reset dirty state
- Fix checksum mismatch
- Recalculate checksums
- Repair failed migration entries
Priority: HIGH
Purpose:
Allow reverting schema changes.
Needed:
- Down script support
- Target version rollback
- Automatic rollback execution
Priority: HIGH
Purpose:
Preview migrations before execution.
Should show:
- What will run
- Dependency chain
- Estimated execution order
- Risk detection
Priority: MEDIUM
You already discussed this feature.
Goal:
If migration order is wrong:
- Scan all migration files
- Find missing CREATE TABLE migration
- Suggest or auto-execute prerequisite migration
This is an advanced intelligent feature.
Priority: HIGH
Currently loader supports repeatables.
Still needed:
- Checksum-based rerun detection
- Auto rerun when content changes
- Dependency tracking for repeatables
Priority: MEDIUM
Future Advanced Feature:
Compare:
- Current DB schema
- Desired schema
Then auto-generate migration scripts.
Priority: FUTURE
Generate migration files automatically.
Example:
migrate create add_users_tablePriority: MEDIUM
You already have some JavaFX/dashboard files.
Potential features:
- Migration history
- Dirty state view
- Lock view
- Connection management
- Rollback buttons
- Logs viewer
Priority: FUTURE
Goal:
Make current system reliable.
Tasks:
- Fix dirty DB logic
- Fix checksum validation bug
- Complete transaction rollback
- Complete migration ordering validation
- Finish pending migration detection
- Improve SQL splitting
- Remove commented dead code
- Add consistent exception handling
Status: IN PROGRESS
Goal:
Add production-grade reliability.
Tasks:
- Migration locking
- Rollback support
- Repair command
- Checksum repair
- Retry mechanism
- Better migration metadata
Status: NOT STARTED
Goal:
Build advanced dependency engine.
Tasks:
- Auto dependency resolution
- Migration graph building
- Circular dependency detection
- Auto prerequisite execution
- Dependency visualization
Status: EARLY RESEARCH
Goal:
Improve usability.
Tasks:
- Migration generation command
- Better CLI commands
- Better logs
- Colored terminal output
- Migration preview mode
- Dry-run mode
Status: PARTIAL
Goal:
Make framework deployable.
Tasks:
- Multi-schema support
- Multi-database support
- Concurrency testing
- Large migration optimization
- Performance benchmarking
- Security hardening
- CI/CD integration
Status: NOT STARTED
Goal:
Differentiate from Flyway-like systems.
Tasks:
- Schema diff engine
- Auto migration generation
- Visual dashboard
- AI-assisted dependency recovery
- Migration simulation engine
Status: FUTURE
Several files contain:
- Large commented blocks
- Disabled validations
- Debug print statements
Examples:
MigrationValidatorMigrationCommandsMigrationEngine
Action:
- Refactor
- Clean architecture
- Replace
System.out.println - Add structured logging
Current state:
- Many generic
RuntimeException - Limited categorized exceptions
Needed:
- Custom exception hierarchy
- Validation exceptions
- Dependency exceptions
- Migration execution exceptions
Currently missing:
- Unit tests
- Integration tests
- Migration simulation tests
- Rollback tests
Priority: VERY HIGH
Recommended order:
- Fix dirty-state logic
- Complete checksum validation
- Add transaction rollback
- Finish rollback migration support
- Add repair command
- Add migration ordering validation
- Add integration tests
- Build intelligent dependency recovery
- Improve CLI commands
- Add migration generator command
| Area | Level |
|---|---|
| Core Execution | Intermediate |
| Dependency Analysis | Advanced |
| Reliability | Beginner-Intermediate |
| Enterprise Readiness | Early Stage |
| Tool Architecture | Good Foundation |
| Innovation Potential | High |
The strongest engineering areas currently are:
Using JSQLParser for dependency analysis is a strong architectural decision.
Project separation is already reasonably good:
- Components
- Services
- Validators
- Parsers
- DTOs
- Repositories
The dynamic connection management is a good enterprise-oriented feature.
You already designed:
- Logs
- Dirty state
- Checksums
- Migration history
which are essential for serious migration systems.
This is no longer a beginner CRUD project.
You are already building:
- A migration orchestration engine
- A dependency-aware execution system
- A metadata tracking framework
- A partial migration planner
The project is currently between:
- Beginner Flyway clone and
- Early custom enterprise migration framework
The next major leap will happen when:
- Transaction safety becomes stable
- Rollback support is completed
- Dependency auto-resolution is implemented
- Validation becomes reliable
- Testing is added
At that point the project becomes genuinely production-grade.
Potential cleaner architecture:
migration/
├── core/
├── parser/
├── validator/
├── executor/
├── metadata/
├── rollback/
├── dependency/
├── locking/
├── cli/
├── api/
├── testing/
└── recovery/
- Stable migration execution
- Stable validation
- Dirty handling
- Transaction rollback
- Rollback support
- Repair command
- Locking
- Repeatable migration stability
- Intelligent dependency recovery
- Migration planning engine
- Graph-based dependency system
- Schema diff engine
- Migration generation
- Dashboard UI
- CI/CD integration
The project already contains several non-trivial engineering concepts:
- SQL AST parsing
- Dependency analysis
- Migration orchestration
- Multi-database handling
- Metadata tracking
- Checksum architecture
- Dirty-state handling
The foundation is strong.
The biggest priorities now are:
- Reliability
- Validation correctness
- Rollback safety
- Transaction management
- Testing
Once those stabilize, the project can evolve into a genuinely advanced migration framework.