# 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](#overview)
- [Architecture](#architecture)
- [Getting Started](#getting-started)
- [CLI Commands](#cli-commands)
- [MCP Server](#mcp-server)
- [Type Mapping Reference](#type-mapping-reference)
- [Validation Rules](#validation-rules)
- [Project Structure](#project-structure)
- [Running Tests](#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

```bash
npm install
```

### Build

```bash
npm run build
```

### Link CLI globally (optional)

```bash
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.

```bash
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.

```bash
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.

```bash
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).

```bash
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`

```json
{
  "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`

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

Returns: `schema.prisma` content as a plain string.

### Tool: `validate_type_mapping`

```json
{
  "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

```bash
# 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