db-legacy-migration-agent

CLI and MCP Server that parses legacy relational DB schemas (DB2, Oracle PL/SQL, MySQL, MSSQL) and transpiles them automatically to PostgreSQL with a generated Prisma ORM schema and TypeScript query helpers.

---

Table of Contents

• Overview
• Architecture
• Getting Started
• CLI Commands
• MCP Server
• Type Mapping Reference
• Validation Rules
• Project Structure
• Running Tests

---

Overview

Legacy enterprise systems often rely on vendor-specific SQL dialects (Oracle PL/SQL, IBM DB2, Microsoft T-SQL) that cannot be migrated directly to modern stacks without significant manual effort. This tool automates the structural translation phase:

Input	Output
CREATE TABLE (Oracle, DB2, MySQL, MSSQL)	schema.prisma model definitions
PL/SQL CREATE PROCEDURE / CREATE FUNCTION	Best-effort TypeScript equivalent
Any mix of legacy DDL	TypeScript Prisma Client query helpers
Full DDL file	Validation report with precision-loss analysis

---

Architecture

src/
├── parser/
│   └── sql-transpiler.ts     # DDL lexer/parser + Prisma/TS code generator
├── engine/
│   └── schema-validator.ts   # Precision-loss & semantic mismatch validator
├── mcp/
│   └── server.ts             # MCP server (stdio transport)
└── cli.ts                    # Commander.js interactive CLI
tests/
└── transpiler.test.ts        # Jest unit tests (40+ assertions)

Core Modules

src/parser/sql-transpiler.ts

Responsible for the full transpilation pipeline:

1. Tokenisation — strips comments, normalises whitespace, handles quoted identifiers
2. DDL parsing — CREATE TABLE with columns, constraints, FKs, indexes
3. PL/SQL parsing — CREATE [OR REPLACE] PROCEDURE/FUNCTION with parameter directions
4. Type mapping — 40+ legacy type mappings to { prismaType, postgresType }
5. Prisma schema generation — @@map, @db.* annotations, composite PKs, FK relations
6. TypeScript query generation — CRUD helpers using PrismaClient
7. PL/SQL structural translation — BEGIN/END, IF/THEN/ELSIF, FOR/WHILE LOOP, :=, DBMS_OUTPUT

src/engine/schema-validator.ts

Runs a rule engine over the transpiled table definitions and emits structured ValidationIssue records:

• Critical — data loss guaranteed (e.g., BIGINT_OVERFLOW, NULLABLE_PK)
• Warning — semantic mismatch requiring review (e.g., ORACLE_DATE_HAS_TIME, XMLTYPE_NO_NATIVE)
• Info — informational notes (e.g., LOB_TO_TEXT, DB2_GRAPHIC_TYPE)

src/mcp/server.ts

MCP server exposing three tools over stdio transport:

Tool	Description
parse_legacy_ddl	Full parse + generate: returns AST, Prisma schema, TS queries
generate_prisma_schema	Returns only the schema.prisma content
validate_type_mapping	Returns structured or text validation report

---

Getting Started

Prerequisites

• Node.js ≥ 18
• npm ≥ 9

Install

npm install

Build

npm run build

Link CLI globally (optional)

npm link
db-migrate --help

---

CLI Commands

transpile <file>

Parses a DDL file and generates schema.prisma, queries.ts, and ast.json in the output directory.

npx ts-node src/cli.ts transpile ./examples/oracle_hr.sql \
  --dialect oracle \
  --out ./output

Options:

Flag	Default	Description
-d, --dialect	oracle	Source dialect: db2 | oracle | mysql | mssql
-o, --out	./output	Output directory
--no-ts	—	Skip TypeScript query generation
--no-validate	—	Skip post-transpile validation

---

validate <file>

Validates type mappings and outputs a structured report.

npx ts-node src/cli.ts validate ./examples/oracle_hr.sql \
  --dialect oracle \
  --format text

Options:

Flag	Default	Description
-d, --dialect	oracle	Source dialect
-f, --format	text	text or json
--fail-on-warnings	—	Exit code 1 if warnings found (for CI pipelines)

Exit codes:

Code	Meaning
0	No issues or info only
1	Warnings found (only with --fail-on-warnings)
2	Critical issues found

---

parse-inline <ddl>

Quick test — parse a DDL string directly from the command line.

npx ts-node src/cli.ts parse-inline \
  "CREATE TABLE T (ID NUMBER(10) NOT NULL, NAME VARCHAR2(100), CONSTRAINT PK_T PRIMARY KEY (ID));"

---

mcp

Start the MCP server over stdio (for AI assistant integration).

npx ts-node src/cli.ts mcp

---

MCP Server

The MCP server can be registered with any MCP-compatible AI assistant (e.g., Claude Desktop, IBM Bob).

Tool: parse_legacy_ddl

{
  "tool": "parse_legacy_ddl",
  "input": {
    "ddl": "CREATE TABLE EMPLOYEES (...);",
    "dialect": "oracle",
    "include_typescript": true
  }
}

Returns: full AST, Prisma schema, TypeScript queries, warnings.

Tool: generate_prisma_schema

{
  "tool": "generate_prisma_schema",
  "input": {
    "ddl": "CREATE TABLE EMPLOYEES (...);",
    "dialect": "oracle"
  }
}

Returns: schema.prisma content as a plain string.

Tool: validate_type_mapping

{
  "tool": "validate_type_mapping",
  "input": {
    "ddl": "CREATE TABLE EMPLOYEES (...);",
    "dialect": "oracle",
    "format": "json"
  }
}

Returns: structured ValidationReport JSON or human-readable text.

---

Type Mapping Reference

Legacy Type	Prisma Type	PostgreSQL Type	Notes
NUMBER(p) / NUMERIC	Decimal	DECIMAL(p)	Precision preserved
NUMBER(p,s)	Decimal	DECIMAL(p,s)	Scale preserved
NUMBER(p) p≤9	Int	INTEGER	Fits 32-bit
NUMBER(p) 10≤p≤18	BigInt	BIGINT	Fits 64-bit
NUMBER(p) p>18	Decimal	DECIMAL(p)	⚠ BigInt would overflow
VARCHAR2(n)	String	VARCHAR(n)
CHAR(n)	String	CHAR(n)	Fixed-length padding
CLOB / NCLOB / LONG	String	TEXT	ℹ No separate LOB segment
BLOB / RAW	Bytes	BYTEA	ℹ Inline storage
DATE (Oracle)	DateTime	DATE	⚠ Oracle DATE includes time
TIMESTAMP	DateTime	TIMESTAMP
TIMESTAMP WITH TIME ZONE	DateTime	TIMESTAMPTZ
BINARY_FLOAT	Float	REAL	⚠ Single precision
BINARY_DOUBLE	Float	DOUBLE PRECISION
XMLTYPE	String	XML	⚠ No Prisma native XML
BIGINT	BigInt	BIGINT
DECIMAL(p,s)	Decimal	DECIMAL(p,s)
BOOLEAN	Boolean	BOOLEAN
JSON / JSONB	Json	JSON / JSONB

---

Validation Rules

Code	Severity	Trigger	Recommendation
ORACLE_NUMBER_NO_SCALE	warning	NUMBER(p) without scale → could be integer or float	Add explicit scale
BIGINT_OVERFLOW	critical	NUMBER(p) p>18 mapped to BigInt	Use Decimal / NUMERIC
FLOAT_SINGLE_PRECISION	warning	BINARY_FLOAT or FLOAT(≤24) → REAL	Use DOUBLE PRECISION
LOB_TO_TEXT	info	CLOB/NCLOB/LONG → TEXT	Update LOB streaming APIs
BLOB_TO_BYTEA	info	BLOB/RAW → BYTEA	Use lo API for > 1 GB values
ORACLE_DATE_HAS_TIME	warning	Oracle DATE → PostgreSQL DATE	Use TIMESTAMP if time needed
LOCAL_TZ_SEMANTICS	warning	TIMESTAMP WITH LOCAL TIME ZONE	Verify TZ conversion logic
CHAR_LARGE_LENGTH	warning	CHAR(n) n>255	Replace with VARCHAR(n)
VARCHAR2_EXCEEDS_ORACLE_LIMIT	info	VARCHAR2(n) n>4000	Use TEXT for unbounded
XMLTYPE_NO_NATIVE	warning	XMLTYPE	Use $queryRaw for XML ops
DB2_GRAPHIC_TYPE	info	DB2 GRAPHIC/VARGRAPHIC	Verify UTF-8 transcoding
NO_PRIMARY_KEY	warning	Table has no PK	Add id or @@id
NULLABLE_PK	critical	PK column parsed as nullable	Fix source DDL

---

Project Structure

db-legacy-migration-agent/
├── src/
│   ├── parser/
│   │   └── sql-transpiler.ts    # Type mappings, DDL parser, Prisma & TS generators
│   ├── engine/
│   │   └── schema-validator.ts  # Rule engine, ValidationReport, formatter
│   ├── mcp/
│   │   └── server.ts            # MCP server with 3 tools
│   └── cli.ts                   # Commander.js CLI entrypoint
├── tests/
│   └── transpiler.test.ts       # Jest unit tests
├── dist/                        # Compiled output (after `npm run build`)
├── output/                      # Generated files (schema.prisma, queries.ts, ast.json)
├── package.json
├── tsconfig.json
└── README.md

---

Running Tests

# Run all tests
npm test

# With coverage
npm test -- --coverage

# Watch mode
npm test -- --watch

Expected output: 40+ assertions across transpiler parsing, type mapping, PL/SQL translation, and validator rules.

---

Contributing

1. Fork and clone the repository
2. Run npm install to install dependencies
3. Add your feature/fix in src/
4. Add or update tests in tests/
5. Run npm test and npm run typecheck before submitting a PR

---

License

MIT