db-legacy-migration-agent

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:

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:

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:

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:

Exit codes:

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

Validation Rules

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