A comprehensive flight management system built using ORMCP to demonstrate the complete migration of data from an Oracle Database to a PostgreSQL database using Natural Language Queries.
This project implements a flight management system that tracks airlines, airports, aircraft, crew members, passengers, flights, tickets, and crew assignments. It serves as an example of:
- Data Migration using ORMCP and natural language queries
- Object-Relational Mapping (ORM) using JDX with JSON-first object models
The most noteworthy part of this project is that the data transfer from one database to another happens entirely through the issuing of a command to an AI agent.
Here, the following instruction was given to Claude Desktop to test the project: "Transfer all the data in all the tables from the oracle database you are connected to, to the postgresql database"
| Entity | Role |
|---|---|
| Airline | Parent entity; owns aircraft fleet and schedules flights |
| Airport | Shared lookup; referenced by flights as origin/destination |
| Aircraft | One-to-one relationship with Flight; assigned by airline |
| Flight | Central entity; references airline, airports, aircraft; has many tickets and crew |
| CrewMember | Shared entity; many-to-many with flights via junction table |
| Passenger | Parent entity; has many tickets |
| Ticket | Child of Passenger/Flight; many-to-many join realized as real entity |
| FlightCrewAssignment | Pure junction table; many-to-many join between Flight and CrewMember |
The repository root contains two self-contained example projects — one for Oracle and one for PostgreSQL — plus shared metadata files.
examples/ # Repository root
├── README.md # This file
├── LICENSE
├── .gitignore
├── .gitattributes
│
├── gilhari_flight_management_oracle/ # Oracle Database 23ai variant
│ ├── Dockerfile # Container image (Gilhari + Oracle JDBC)
│ ├── gilhari_service.config # Gilhari runtime config (port, JDX file, classpath)
│ ├── compile.cmd # Compile Java model classes
│ ├── build.cmd # Build Docker image
│ ├─ run_docker_app.cmd # Run the containerised microservice
│ ├── forward.bat # Windows helper: schema bootstrap via JDXSchema
│ ├── setEnvironment.bat # Windows env vars (JAVA_HOME, JX_HOME, CLASSPATH)
│ ├── curlCommandsPopulate.cmd # Seed all 8 tables with sample data (Windows)
│ ├── sources.txt # javac source file list used by compile scripts
│ ├── config/
│ │ ├── gilhari_flight_management_oracle.jdx # JDX ORM mapping (Oracle types)
│ │ ├── gilhari_flight_management_oracle.docker.jdx # JDX ORM mapping variant for Docker
│ │ └── classnames_map_example.json # Class-name → table-name overrides
│ ├── src/
│ │ └── com/softwaretree/flight_management/model/
│ │ ├── Airline.java # Shell class: airline entity
│ │ ├── Airport.java # Shell class: shared airport lookup
│ │ ├── Aircraft.java # Shell class: aircraft (one-to-one with Flight)
│ │ ├── Flight.java # Shell class: central flight entity
│ │ ├── CrewMember.java # Shell class: crew member (many-to-many)
│ │ ├── Passenger.java # Shell class: passenger (one-to-many to Ticket)
│ │ ├── Ticket.java # Shell class: ticket (many-to-many join)
│ │ └── FlightCrewAssignment.java # Shell class: crew assignment junction table
│ └── bin/
│ └── com/softwaretree/flight_management/model/
│ └── *.class # Pre-compiled classes (ready for immediate Docker run)
│
└── gilhari_flight_management_postgres/ # PostgreSQL variant
├── Dockerfile # Container image (Gilhari + PostgreSQL JDBC)
├── gilhari_service.config # Gilhari runtime config (port, JDX file, classpath)
├── compile.cmd # Compile Java model classes
├── build.cmd # Build Docker image
├── run_docker_app.cmd # Run the containerised microservice
├── sources.txt # javac source file list used by compile scripts
├── config/
│ ├── gilhari_flight_management_postgres.jdx # JDX ORM mapping (PostgreSQL types)
│ └── classnames_map_example.json # Class-name → table-name overrides
├── src/
│ └── com/softwaretree/flight_management/model/
│ ├── Airline.java
│ ├── Airport.java
│ ├── Aircraft.java
│ ├── Flight.java
│ ├── CrewMember.java
│ ├── Passenger.java
│ ├── Ticket.java
│ └── FlightCrewAssignment.java
└── bin/
└── com/softwaretree/flight_management/model/
└── *.class # Pre-compiled classes
- ORMCP Installed from the Software Tree website
- Gilhari
- Docker (Docker Desktop on Windows/Mac, or native on Linux) with Docker Compose
- Oracle Database 23ai and PostgreSQL (can run in a separate container or external)
- curl (for Windows, macOS, Linux; pre-installed or via package manager)
- JDK 1.8
Please note that all the following commands are to be run in the Windows Command Prompt.
git clone "https://github.com/astronaut012/Migrating-Databases-Using-ORMCP.git"Edit the compile.cmd files to set JX_HOME to the root of your Gilhari installation.
Download the drivers for Oracle database and PostgreSQL and place them in the respective config directories. This project was tested on Oracle 23ai using ojdbc8.jar, and postgresql-42.2.29.jar
Edit the gilhari_flight_management_oracle.docker.jdx, gilhari_flight_management_oracle.jdx and gilhari_flight_management_postgres.jdx files to include the correct credentials for your database.
Oracle Database
compile.cmd (Compiles source java files listed in sources.txt and places them in bin/)
forward -metaForceCreate (Forces creation of JDX system tables (jdxMetadata, jdxSequence) in the Oracle database)
forward -create (Executes the generated .create script to create all 8 tables with constraints and indexes)
build.cmd (Builds the Docker image by layering compiled classes and config on top of the Gilhari base image)
run_docker_app.cmd (Starts the Gilhari microservice container, exposing REST endpoints on http://localhost:8080)PostgreSQL
compile.cmd (Compiles source java files listed in sources.txt and places them in bin/)
build.cmd (Builds the Docker image by layering compiled classes and config on top of the Gilhari base image)
run_docker_app.cmd (Starts the Gilhari microservice container, exposing REST endpoints on http://localhost:8080)Populate all 8 tables in the Oracle database with ~100 sample rows each:
curlCommandsPopulate.cmdResults are logged to curl.log.
In the claude_desktop_config.json, add the following MCP servers:
"mcpServers": {
"oracle-ormcp-server": {
"command": "path\\to\\ormcp-server.exe",
"args": [],
"env": {
"GILHARI_BASE_URL": "http://localhost:80/gilhari/v1/",
"MCP_SERVER_NAME": "OracleFlightDB"
}
},
"postgres-ormcp-server": {
"command": "path\\to\\ormcp-server.exe",
"args": [],
"env": {
"GILHARI_BASE_URL": "http://localhost:81/gilhari/v1/",
"MCP_SERVER_NAME": "PostgresFlightDB"
}
}
}Open Claude Desktop after ensuring it is not running. Run natural language queries to migrate the data as desired.
The natural language query used with Claude Desktop was: "transfer all the data from the oracle ormcp server you are connected to to the postgres server"
This transferred the foundational tables (Airlines, Airports, Passengers, CrewMembers) and asked the user to enter "yes" to complete the transfer of the remaining dependent tables.
- This project uses Oracle Database and PostgreSQL as examples of popular databases. Please edit the JDX_DRIVER and JDBC_URL in the *.jdx files, and the jdbc_driver_path in the gilhari_service.config files to use different databases.
- Claude Desktop is used as an example of an AI Agent. The project can be run as-is by connecting it to any other AI Agent that uses MCP.
Assign crew members to flights respecting licensing and duty-time rules; query crew across multiple flights.
Example query: "Find all flights in the next 7 days that lack a full crew complement (pilot, co-pilot, 2+ flight attendants)"
Book passengers on flights, assign seat classes, and track occupancy.
Example query: "List all business-class tickets for flights from SFO to LHR; calculate revenue per flight"
Analyze which airport pairs have the most flights; identify underutilized routes.
Example query: "Which origin-destination pairs have exactly 1 flight per day? Which have none?"
- Scalability: The project currently uses only two databases and it can be expanded to connect to multiple databases at once. Instead of Oracle and PostgreSQL, testing could be done with another set of databases as well.
- Complete Automation: Currently, the backend schema for the target application has to be made beforehand, as defined in JDX. Future work in this domain could create the tables as well in the target, based on the schema information retrieved from the source database.
- Frontend Design: The project could be given an interactive frontend for users to filter data and perform selective data migration. It could also be in the form of an application for people to use to manage flight booking data.
- Rigorous Testing: Due to resource constraints, the project could be tested with a limited set of sample data. More rigorous testing with larger amounts of data and batch transfer could shed light on more of its capabilities.
- ORMCP: ORMCP Documentation
- Gilhari Documentation: Gilhari REST Microservice
- JDX Manual: JDX 5.0 Object-Relational Mapping
- Oracle JDBC: Oracle Thin Driver Documentation
- PostgreSQL JDBC: PostgreSQL JDBC Driver
- Docker: Docker Documentation
Article: Medium Article for this project