sql-schema-parser skill

# sql-schema-parser skill

## When to use

Use this skill when implementing or modifying the SQL parser, schema state machine, or differ in migration-diff-tool.

## Parser approach

The parser uses line-by-line regex matching, not a full SQL grammar. This is intentional: it handles the common 90% of migration patterns without the complexity of a full parser, and emits warnings for unrecognized statements rather than failing.

## Handled statement patterns

All patterns are case-insensitive for SQL keywords. Table and column names preserve their original case.

```
CREATE TABLE [IF NOT EXISTS] <name> (
  col_def, col_def, ...
  [, PRIMARY KEY (col,...)]
);

CREATE TABLE [IF NOT EXISTS] <schema>.<name> (...)  -- schema prefix stripped

ALTER TABLE <name> ADD COLUMN <col_def>;
ALTER TABLE <name> ADD <col_def>;                    -- MySQL shorthand (no COLUMN keyword)
ALTER TABLE <name> DROP COLUMN <col>;
ALTER TABLE <name> DROP <col>;                       -- MySQL shorthand
ALTER TABLE <name> ALTER COLUMN <col> TYPE <type>;   -- PostgreSQL
ALTER TABLE <name> MODIFY COLUMN <col_def>;          -- MySQL full redefinition
ALTER TABLE <name> MODIFY <col_def>;                 -- MySQL shorthand
ALTER TABLE <name> RENAME COLUMN <old> TO <new>;

DROP TABLE [IF EXISTS] <name>;
DROP TABLE [IF EXISTS] <schema>.<name>;
```

## Column definition parsing

A column definition has the form: `<name> <type> [<constraints>...]`

Type extraction captures everything up to the first constraint keyword. Supported constraints:

- `NOT NULL` - sets `nullable: false`
- `NULL` (explicit) - sets `nullable: true`
- `DEFAULT <value>` - captures value (string literal, number, function call like `NOW()`)
- `PRIMARY KEY` - sets `primaryKey: true`
- `UNIQUE` - sets `unique: true`
- `REFERENCES ...` - silently ignored (FK constraints not tracked)
- `CHECK (...)` - silently ignored

Default nullable is `true` unless `NOT NULL` is present.

## Parser implementation reference

```typescript
// packages/core/src/parser.ts

export type StatementType =
  | 'create_table'
  | 'alter_add_column'
  | 'alter_drop_column'
  | 'alter_type_column'
  | 'alter_modify_column'
  | 'alter_rename_column'
  | 'drop_table'
  | 'unknown';

export interface ParsedStatement {
  type: StatementType;
  tableName: string;
  columnName?: string;
  newColumnName?: string;
  columnDef?: ColumnDef;
  columns?: ColumnDef[];     // for create_table
  raw: string;
  warning?: string;
}

// Normalization before parsing:
// 1. Strip SQL comments (-- line comments and /* block comments */)
// 2. Normalize whitespace (collapse runs of whitespace to single space)
// 3. Split on semicolons to get individual statements
// 4. Trim each statement

function stripComments(sql: string): string {
  // Remove block comments first (may contain --)
  sql = sql.replace(/\/\*[\s\S]*?\*\//g, ' ');
  // Remove line comments
  sql = sql.replace(/--[^\n]*/g, ' ');
  return sql;
}

function splitStatements(sql: string): string[] {
  return stripComments(sql)
    .split(';')
    .map(s => s.trim().replace(/\s+/g, ' '))
    .filter(s => s.length > 0);
}
```

## CREATE TABLE parsing

```typescript
// Pattern: CREATE TABLE [IF NOT EXISTS] [schema.]name (body)
const CREATE_TABLE = /^CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?:\w+\.)?(\w+)\s*\(([\s\S]+)\)$/i;

function parseCreateTable(stmt: string): ParsedStatement | null {
  const m = CREATE_TABLE.exec(stmt);
  if (!m) return null;
  const tableName = m[1];
  const body = m[2];
  const columns = parseColumnList(body);
  return { type: 'create_table', tableName, columns, raw: stmt };
}

function parseColumnList(body: string): ColumnDef[] {
  // Split on commas that are not inside parentheses
  const parts = splitOnTopLevelCommas(body);
  const cols: ColumnDef[] = [];
  for (const part of parts) {
    const trimmed = part.trim();
    // Skip table constraints
    if (/^(PRIMARY\s+KEY|UNIQUE|CONSTRAINT|CHECK|FOREIGN\s+KEY)/i.test(trimmed)) continue;
    const col = parseColumnDef(trimmed);
    if (col) cols.push(col);
  }
  return cols;
}

function splitOnTopLevelCommas(s: string): string[] {
  const parts: string[] = [];
  let depth = 0;
  let start = 0;
  for (let i = 0; i < s.length; i++) {
    if (s[i] === '(') depth++;
    else if (s[i] === ')') depth--;
    else if (s[i] === ',' && depth === 0) {
      parts.push(s.slice(start, i));
      start = i + 1;
    }
  }
  parts.push(s.slice(start));
  return parts;
}
```

## Column definition parsing

```typescript
function parseColumnDef(s: string): ColumnDef | null {
  // Name is the first token
  const tokens = s.match(/^(\S+)\s+(.+)$/);
  if (!tokens) return null;
  const name = tokens[1].replace(/["'`]/g, ''); // strip quoting
  const rest = tokens[2];

  // Extract type: everything up to the first constraint keyword
  const CONSTRAINT_KW = /\b(NOT\s+NULL|NULL\b|DEFAULT\b|PRIMARY\s+KEY|UNIQUE\b|REFERENCES\b|CHECK\b)/i;
  const typeEnd = CONSTRAINT_KW.exec(rest);
  const type = (typeEnd ? rest.slice(0, typeEnd.index) : rest).trim();

  const nullable = !/NOT\s+NULL/i.test(rest);
  const primaryKey = /PRIMARY\s+KEY/i.test(rest);
  const unique = /\bUNIQUE\b/i.test(rest);

  let defaultValue: string | null = null;
  const defaultMatch = /DEFAULT\s+(.+?)(?:\s+(?:NOT\s+NULL|NULL|PRIMARY|UNIQUE|REFERENCES|CHECK)|$)/i.exec(rest);
  if (defaultMatch) {
    defaultValue = defaultMatch[1].trim();
  }

  return { name, type, nullable, primaryKey, unique, defaultValue };
}
```

## Schema state machine

```typescript
// packages/core/src/state.ts

function applyStatement(state: Map<string, TableDef>, stmt: ParsedStatement, warnings: string[]): void {
  switch (stmt.type) {
    case 'create_table': {
      if (state.has(stmt.tableName)) {
        warnings.push(`CREATE TABLE: table "${stmt.tableName}" already exists, skipping`);
        return;
      }
      state.set(stmt.tableName, { name: stmt.tableName, columns: stmt.columns ?? [] });
      break;
    }
    case 'drop_table': {
      if (!state.has(stmt.tableName)) {
        warnings.push(`DROP TABLE: table "${stmt.tableName}" not found in state`);
      }
      state.delete(stmt.tableName);
      break;
    }
    case 'alter_add_column': {
      const table = state.get(stmt.tableName);
      if (!table) { warnings.push(`ADD COLUMN: table "${stmt.tableName}" not found`); return; }
      if (table.columns.find(c => c.name === stmt.columnName)) {
        warnings.push(`ADD COLUMN: column "${stmt.columnName}" already exists in "${stmt.tableName}"`);
        return;
      }
      table.columns.push(stmt.columnDef!);
      break;
    }
    case 'alter_drop_column': {
      const table = state.get(stmt.tableName);
      if (!table) { warnings.push(`DROP COLUMN: table "${stmt.tableName}" not found`); return; }
      const idx = table.columns.findIndex(c => c.name === stmt.columnName);
      if (idx === -1) { warnings.push(`DROP COLUMN: column "${stmt.columnName}" not found in "${stmt.tableName}"`); return; }
      table.columns.splice(idx, 1);
      break;
    }
    case 'alter_type_column':
    case 'alter_modify_column': {
      const table = state.get(stmt.tableName);
      if (!table) { warnings.push(`ALTER COLUMN: table "${stmt.tableName}" not found`); return; }
      const col = table.columns.find(c => c.name === stmt.columnName);
      if (!col) { warnings.push(`ALTER COLUMN: column "${stmt.columnName}" not found`); return; }
      if (stmt.columnDef) {
        Object.assign(col, stmt.columnDef);
      }
      break;
    }
    case 'alter_rename_column': {
      const table = state.get(stmt.tableName);
      if (!table) { warnings.push(`RENAME COLUMN: table "${stmt.tableName}" not found`); return; }
      const col = table.columns.find(c => c.name === stmt.columnName);
      if (!col) { warnings.push(`RENAME COLUMN: column "${stmt.columnName}" not found`); return; }
      col.name = stmt.newColumnName!;
      break;
    }
    // unknown: add to warnings, continue
    case 'unknown': {
      if (stmt.raw.trim()) {
        warnings.push(`Unrecognized statement: ${stmt.raw.slice(0, 80)}...`);
      }
      break;
    }
  }
}
```

## Deep clone for state snapshots

```typescript
function cloneState(state: Map<string, TableDef>): Map<string, TableDef> {
  const clone = new Map<string, TableDef>();
  for (const [name, table] of state) {
    clone.set(name, {
      name: table.name,
      columns: table.columns.map(c => ({ ...c })),
    });
  }
  return clone;
}
```

Always deep-clone the state after processing each migration file. Never store references to mutable state.

## Differ implementation

```typescript
// packages/core/src/differ.ts

export function computeDiff(from: SchemaState, to: SchemaState): SchemaDiff {
  const entries: DiffEntry[] = [];

  // Tables added
  for (const [name] of to.tables) {
    if (!from.tables.has(name)) {
      entries.push({ changeType: 'table_added', tableName: name });
    }
  }

  // Tables removed
  for (const [name] of from.tables) {
    if (!to.tables.has(name)) {
      entries.push({ changeType: 'table_removed', tableName: name });
    }
  }

  // Columns within shared tables
  for (const [tableName, toTable] of to.tables) {
    const fromTable = from.tables.get(tableName);
    if (!fromTable) continue;

    const fromCols = new Map(fromTable.columns.map(c => [c.name, c]));
    const toCols = new Map(toTable.columns.map(c => [c.name, c]));

    for (const [colName, toCol] of toCols) {
      if (!fromCols.has(colName)) {
        entries.push({ changeType: 'column_added', tableName, columnName: colName });
        continue;
      }
      const fromCol = fromCols.get(colName)!;
      if (fromCol.type !== toCol.type) {
        entries.push({ changeType: 'column_type_changed', tableName, columnName: colName, before: fromCol.type, after: toCol.type });
      }
      if (fromCol.nullable !== toCol.nullable) {
        entries.push({ changeType: 'column_nullable_changed', tableName, columnName: colName, before: String(fromCol.nullable), after: String(toCol.nullable) });
      }
      if (fromCol.defaultValue !== toCol.defaultValue) {
        entries.push({ changeType: 'column_default_changed', tableName, columnName: colName, before: fromCol.defaultValue, after: toCol.defaultValue });
      }
    }

    for (const [colName] of fromCols) {
      if (!toCols.has(colName)) {
        entries.push({ changeType: 'column_removed', tableName, columnName: colName });
      }
    }
  }

  const stats = computeStats(entries);
  return { fromVersion: from.version, toVersion: to.version, fromFileIndex: from.fileIndex, toFileIndex: to.fileIndex, entries, stats };
}
```

## Common pitfalls

- Do not split on all commas to parse column lists - commas appear inside type parameters like `VARCHAR(255, utf8)` and CHECK expressions. Always split on top-level commas only.
- Do not strip parentheses before parsing types - type parameters like `NUMERIC(10,2)` need the parens.
- Table names may be schema-qualified (`public.users`). Strip the schema prefix before storing.
- Column names may be quoted (`"name"`, `` `name` ``, `'name'`). Strip quotes before storing.
- SQL is case-insensitive for keywords but case-sensitive for names in most databases. Preserve original name case.
- The first token after `CREATE TABLE [IF NOT EXISTS]` may include a schema prefix. Use `/(?:\w+\.)?(\w+)/` to extract the table name.