acedergren/sqlite-to-oracle-planner

SQLite-to-Oracle Planner

Role

This is a planning-only skill. Search, read minimally, and produce the manifest. Do NOT implement changes. Use Glob and Grep — never read entire files to scan them.

Scan Strategy

Work in five passes. Log every hit with file path and line number.

Pass 1 — Dependency Imports

Pattern: better-sqlite3 | sqlite3 | drizzle-orm/sqlite-core | typeorm.*sqlite | @agent-state
Files:   **/*.ts, **/*.js, **/*.mjs, **/*.cjs

| Import Found | Oracle Equivalent | |--------------------------------|-------------------------------------------------------| | better-sqlite3 | oracledb or project Oracle connection pool | | sqlite3 | oracledb | | drizzle-orm/sqlite-core | Raw OCI SQL or drizzle-orm with Oracle adapter | | typeorm + sqlite driver | typeorm with Oracle driver | | @agent-state (SQLite-backed) | Oracle-backed state store |

Pass 2 — SQLite Data Files

Glob: **/*.db, **/*.sqlite, **/*.sqlite3

These are data files — flag each one. They require data migration, not just code changes.

Pass 3 — Connection Strings and File-Based DB Paths

Pattern: sqlite: | :memory: | \.db['")] | new Database( | createConnection(
Files:   **/*.ts, **/*.js, **/*.env, **/*.env.*

| SQLite Pattern | Oracle Equivalent | |---------------------------------------------------------|-------------------------------------------------------| | sqlite:./path/to/db.sqlite | OCI connection string / TNS alias | | :memory: | Oracle SGA (in-memory buffering is automatic) | | new Database('file.db') | oracledb.getConnection(config) | | createConnection({ type: 'sqlite', database: '...' }) | createConnection({ type: 'oracle', ... }) |

Pass 4 — Schema Syntax

Pattern: AUTOINCREMENT | INTEGER PRIMARY KEY | TEXT DEFAULT | BLOB | REAL | NUMERIC
Files:   **/*.sql, **/*.ts, **/*.js

| SQLite Type | Oracle Equivalent | |-------------------------------------|----------------------------------------------------------| | INTEGER PRIMARY KEY AUTOINCREMENT | NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY | | TEXT (< 4000 chars) | VARCHAR2(4000) | | TEXT (unbounded / large) | CLOB | | BLOB | BLOB | | REAL | BINARY_FLOAT or NUMBER | | NUMERIC | NUMBER | | BOOLEAN (stored 0/1) | NUMBER(1) with CHECK (col IN (0,1)) | | DATETIME (stored as text) | TIMESTAMP WITH TIME ZONE | | AUTOINCREMENT sequence in DDL | CREATE SEQUENCE + trigger, or GENERATED AS IDENTITY |

Pass 5 — Fallback Logic (Most Commonly Missed)

Pattern: sqlite | SQLite | in.memory | fallback.*db | db.*fallback (case-insensitive)
Files:   **/*.ts, **/*.js

Conditional branches that fall back to SQLite when Oracle is unavailable must be removed entirely — not just replaced.

// Examples to flag:
if (process.env.DATABASE_URL?.includes('sqlite')) { ... }    // [SQLITE_REPLACE]
const db = hasOracle ? oracleDB : sqliteDB;                  // [SQLITE_REPLACE]
const adapter = isProd ? 'oracle' : 'sqlite';                // [SQLITE_REPLACE]

Migration Manifest Format

Output this after all five passes. This is the deliverable for implementation agents.

## SQLite Migration Manifest

| File                       | Line | Pattern Found                           | Marker           | Oracle Equivalent                                        |
|----------------------------|------|-----------------------------------------|------------------|----------------------------------------------------------|
| apps/api/src/db/client.ts  | 3    | `import Database from 'better-sqlite3'` | [SQLITE_REPLACE] | `import oracledb from 'oracledb'`                        |
| apps/api/src/db/schema.ts  | 12   | `id INTEGER PRIMARY KEY AUTOINCREMENT`  | [SQLITE_REPLACE] | `id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY` |
| apps/api/data/state.sqlite | —    | SQLite data file                        | [SQLITE_REPLACE] | Migrate data to Oracle table via INSERT SELECT           |
| apps/api/src/config.ts     | 45   | `sqlite:./data/portal.db`               | [SQLITE_REPLACE] | OCI connection string                                    |
| apps/api/src/app.ts        | 88   | `const db = isProd ? oracle : sqlite`   | [SQLITE_REPLACE] | Remove fallback; Oracle only                             |

**Total touch points: N**
**Estimated implementation tasks: N**
**Data files requiring migration: N**

Each [SQLITE_REPLACE] marker = one atomic implementation unit for Agent 2+.

Common Mistakes

| Mistake | Fix | |---------|-----| | Reading entire files to search | Use Grep with -n. Never cat a file to scan it. | | Skipping Pass 5 (fallback logic) | Fallback branches are the trickiest to remove — always run it. | | Assuming TEXT → VARCHAR2 universally | Check actual usage. Content > 4000 chars needs CLOB. | | Ignoring .db data files | Flag them — they require data migration planning, not just code changes. | | Missing drizzle-orm/sqlite-core schema files | Drizzle's Oracle support differs; all schema files need flagging. | | Flagging Oracle-side code as SQLite | Filter false positives — sqlite may appear in migration docs or comments. |