borghei/Claude-Skills / engineering/sql-database-assistant
sql-database-assistant
VerifiedReview SQL queries for index and scan problems, document schemas from DDL, and generate migrations with rollback and destructive-change flags. Use when tuning a query, documenting a schema, or diffing two schemas.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 9.5 KB |
| examples/queries.sql | other | 3.1 KB |
| examples/schema.sql | other | 3.3 KB |
| examples/schema_additive.sql | other | 3.3 KB |
| examples/schema_v2.sql | other | 2.9 KB |
| references/sql-optimization.md | markdown | 3.9 KB |
| scripts/migration_generator.py | script | 14.1 KB |
| scripts/query_optimizer.py | script | 14.9 KB |
| scripts/schema_explorer.py | script | 11.5 KB |
scripts/migration_generator.py
View on GitHub#!/usr/bin/env python3
"""
Migration Generator - Generate SQL migration from schema differences.
Compares two SQL DDL files and generates ALTER TABLE, CREATE TABLE,
and DROP TABLE statements to migrate from one schema to another, with a DOWN script.
PostgreSQL syntax. Index changes are not diffed.
Exit codes: 0 no destructive step (or --allow-destructive), 1 destructive steps present,
2 bad input (file not found).
License: MIT
"""
import argparse
import json
import re
import sys
from dataclasses import dataclass, asdict, field
from pathlib import Path
from typing import List, Dict, Optional, Set
@dataclass
class Column:
"""A table column."""
name: str
data_type: str
nullable: bool
default: Optional[str]
@dataclass
class Table:
"""A table definition."""
name: str
columns: Dict[str, Column] = field(default_factory=dict)
@dataclass
class MigrationStep:
"""A single migration step."""
operation: str # add_table, drop_table, add_column, drop_column, alter_column
table: str
column: Optional[str]
sql_up: str
sql_down: str
description: str
class SimpleDDLParser:
"""Minimal DDL parser for schema comparison."""
CREATE_TABLE = re.compile(
r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?["`]?(\w+)["`]?\s*\((.*?)\)\s*;',
re.IGNORECASE | re.DOTALL
)
SKIP_KEYWORDS = {"PRIMARY", "FOREIGN", "CONSTRAINT", "UNIQUE", "CHECK", "INDEX", "KEY"}
def parse(self, sql: str) -> Dict[str, Table]:
"""Parse DDL into table definitions."""
tables = {}
for match in self.CREATE_TABLE.finditer(sql):
table_name = match.group(1)
body = match.group(2)
table = Table(name=table_name)
table.columns = self._parse_columns(body)
tables[table_name] = table
return tables
def _parse_columns(self, body: str) -> Dict[str, Column]:
"""Parse column definitions from table body."""
columns = {}
parts = self._split_commas(body)
for part in parts:
part = part.strip()
if not part:
continue
first_word = part.split()[0].upper().strip('"`')
if first_word in self.SKIP_KEYWORDS:
continue
col = self._parse_column(part)
if col:
columns[col.name] = col
return columns
def _split_commas(self, text: str) -> List[str]:
"""Split on commas respecting parentheses."""
parts = []
depth = 0
current = ""
for ch in text:
if ch == "(":
depth += 1
current += ch
elif ch == ")":
depth -= 1
current += ch
elif ch == "," and depth == 0:
parts.append(current)
current = ""
else:
current += ch
if current.strip():
parts.append(current)
return parts
def _parse_column(self, definition: str) -> Optional[Column]:
"""Parse a single column definition."""
parts = definition.split()
if len(parts) < 2:
return None
name = parts[0].strip('"`')
upper_def = definition.upper()
# Extract type
type_parts = []
for i in range(1, len(parts)):
word = parts[i].upper().strip('"`')
if word in ("NOT", "NULL", "DEFAULT", "PRIMARY", "UNIQUE",
"REFERENCES", "CHECK", "CONSTRAINT", "AUTO_INCREMENT", "GENERATED"):
break
type_parts.append(parts[i])
if word in ("SERIAL", "BIGSERIAL", "SMALLSERIAL"):
break # SERIAL is the type itself
data_type = " ".join(type_parts).strip(",")
nullable = "NOT NULL" not in upper_def
default = None
default_match = re.search(r'DEFAULT\s+(.+?)(?:\s+(?:NOT|NULL|PRIMARY|UNIQUE|CHECK)|$)',
definition, re.IGNORECASE)
if default_match:
default = default_match.group(1).strip().rstrip(",")
return Column(name=name, data_type=data_type, nullable=nullable, default=default)
class MigrationGenerator:
"""Generate migration SQL from schema differences."""
def __init__(self, from_tables: Dict[str, Table], to_tables: Dict[str, Table]):
self.from_tables = from_tables
self.to_tables = to_tables
self.steps: List[MigrationStep] = []
def generate(self) -> List[MigrationStep]:
"""Generate migration steps."""
from_names = set(self.from_tables.keys())
to_names = set(self.to_tables.keys())
# New tables
for table_name in sorted(to_names - from_names):
self._add_create_table(table_name)
# Dropped tables
for table_name in sorted(from_names - to_names):
self._add_drop_table(table_name)
# Modified tables
for table_name in sorted(from_names & to_names):
self._compare_table(table_name)
return self.steps
def _add_create_table(self, name: str):
"""Generate CREATE TABLE migration."""
table = self.to_tables[name]
col_defs = []
for col in table.columns.values():
col_def = f" {col.name} {col.data_type}"
if not col.nullable:
col_def += " NOT NULL"
if col.default:
col_def += f" DEFAULT {col.default}"
col_defs.append(col_def)
sql_up = f"CREATE TABLE {name} (\n{','.join(chr(10) + d for d in col_defs)}\n);"
sql_down = f"DROP TABLE IF EXISTS {name};"
self.steps.append(MigrationStep(
operation="add_table",
table=name,
column=None,
sql_up=sql_up,
sql_down=sql_down,
description=f"Create new table '{name}' with {len(table.columns)} columns.",
))
def _add_drop_table(self, name: str):
"""Generate DROP TABLE migration."""
table = self.from_tables[name]
col_defs = []
for col in table.columns.values():
col_def = f" {col.name} {col.data_type}"
if not col.nullable:
col_def += " NOT NULL"
if col.default:
col_def += f" DEFAULT {col.default}"
col_defs.append(col_def)
sql_up = f"DROP TABLE IF EXISTS {name};"
sql_down = f"CREATE TABLE {name} (\n{','.join(chr(10) + d for d in col_defs)}\n);"
self.steps.append(MigrationStep(
operation="drop_table",
table=name,
column=None,
sql_up=sql_up,
sql_down=sql_down,
description=f"Drop table '{name}'.",
))
def _compare_table(self, name: str):
"""Compare two versions of a table and generate column migrations."""
from_cols = self.from_tables[name].columns
to_cols = self.to_tables[name].columns
from_col_names = set(from_cols.keys())
to_col_names = set(to_cols.keys())
# New columns
for col_name in sorted(to_col_names - from_col_names):
col = to_cols[col_name]
null_str = "" if col.nullable else " NOT NULL"
default_str = f" DEFAULT {col.default}" if col.default else ""
sql_up = f"ALTER TABLE {name} ADD COLUMN {col.name} {col.data_type}{null_str}{default_str};"
sql_down = f"ALTER TABLE {name} DROP COLUMN {col.name};"
self.steps.append(MigrationStep(
operation="add_column",
table=name,
column=col_name,
sql_up=sql_up,
sql_down=sql_down,
description=f"Add column '{col_name}' ({col.data_type}) to '{name}'.",
))
# Dropped columns
for col_name in sorted(from_col_names - to_col_names):
col = from_cols[col_name]
null_str = "" if col.nullable else " NOT NULL"
default_str = f" DEFAULT {col.default}" if col.default else ""
sql_up = f"ALTER TABLE {name} DROP COLUMN {col_name};"
sql_down = f"ALTER TABLE {name} ADD COLUMN {col.name} {col.data_type}{null_str}{default_str};"
self.steps.append(MigrationStep(
operation="drop_column",
table=name,
column=col_name,
sql_up=sql_up,
sql_down=sql_down,
description=f"Drop column '{col_name}' from '{name}'.",
))
# Modified columns (type or nullability changed)
for col_name in sorted(from_col_names & to_col_names):
from_col = from_cols[col_name]
to_col = to_cols[col_name]
if from_col.data_type.upper() != to_col.data_type.upper() or from_col.nullable != to_col.nullable:
null_str_to = "" if to_col.nullable else " NOT NULL"
null_str_from = "" if from_col.nullable else " NOT NULL"
sql_up = f"ALTER TABLE {name} ALTER COLUMN {col_name} TYPE {to_col.data_type};"
if from_col.nullable != to_col.nullable:
if to_col.nullable:
sql_up += f"\nALTER TABLE {name} ALTER COLUMN {col_name} DROP NOT NULL;"
else:
sql_up += f"\nALTER TABLE {name} ALTER COLUMN {col_name} SET NOT NULL;"
sql_down = f"ALTER TABLE {name} ALTER COLUMN {col_name} TYPE {from_col.data_type};"
if from_col.nullable != to_col.nullable:
if from_col.nullable:
sql_down += f"\nALTER TABLE {name} ALTER COLUMN {col_name} DROP NOT NULL;"
else:
sql_down += f"\nALTER TABLE {name} ALTER COLUMN {col_name} SET NOT NULL;"
changes = []
if from_col.data_type.upper() != to_col.data_type.upper():
changes.append(f"type {from_col.data_type} -> {to_col.data_type}")
if from_col.nullable != to_col.nullable:
changes.append(f"nullable {from_col.nullable} -> {to_col.nullable}")
self.steps.append(MigrationStep(
operation="alter_column",
table=name,
column=col_name,
sql_up=sql_up,
sql_down=sql_down,
description=f"Alter column '{col_name}' in '{name}': {', '.join(changes)}.",
))
def format_text(steps: List[MigrationStep]) -> str:
"""Format as human-readable migration script."""
lines = []
lines.append("-- " + "=" * 58)
lines.append("-- MIGRATION SCRIPT")
lines.append("-- " + "=" * 58)
lines.append(f"-- Steps: {len(steps)}")
destructive = [st for st in steps if is_destructive(st)]
if destructive:
lines.append(f"-- WARNING: {len(destructive)} destructive step(s). The DOWN script recreates")
lines.append("-- dropped tables and columns but NOT their data: take a backup or copy the data")
lines.append("-- out first, and apply to staging before production.")
lines.append("")
if not steps:
lines.append("-- No schema differences found.")
return "\n".join(lines)
lines.append("-- ==================== UP ====================")
lines.append("BEGIN;")
lines.append("")
for i, step in enumerate(steps, 1):
lines.append(f"-- Step {i}: {step.description}{' [DESTRUCTIVE]' if is_destructive(step) else ''}")
lines.append(step.sql_up)
lines.append("")
lines.append("COMMIT;")
lines.append("")
lines.append("-- ==================== DOWN (ROLLBACK) ====================")
lines.append("-- BEGIN;")
for step in reversed(steps):
lines.extend(f"-- {line}" for line in step.sql_down.splitlines() if line.strip())
lines.append("-- COMMIT;")
return "\n".join(lines)
def is_destructive(step: MigrationStep) -> bool:
"""Steps that can lose data: drops, and type changes (which may truncate or fail)."""
return step.operation in ("drop_table", "drop_column", "alter_column")
def format_json(steps: List[MigrationStep]) -> str:
"""Format as JSON."""
return json.dumps({
"steps": [asdict(s) for s in steps],
"summary": {
"total": len(steps),
"add_table": sum(1 for s in steps if s.operation == "add_table"),
"drop_table": sum(1 for s in steps if s.operation == "drop_table"),
"add_column": sum(1 for s in steps if s.operation == "add_column"),
"drop_column": sum(1 for s in steps if s.operation == "drop_column"),
"alter_column": sum(1 for s in steps if s.operation == "alter_column"),
"destructive": sum(1 for s in steps if is_destructive(s)),
}
}, indent=2)
def main():
parser = argparse.ArgumentParser(
description="Generate SQL migration by comparing two schema DDL files."
)
parser.add_argument("--from", dest="from_file", required=True, help="Path to source (current) DDL file")
parser.add_argument("--to", dest="to_file", required=True, help="Path to target (desired) DDL file")
parser.add_argument("--format", choices=["text", "json"], default="text", help="Output format")
parser.add_argument("--allow-destructive", action="store_true",
help="Exit 0 even when the migration drops or retypes columns or tables")
args = parser.parse_args()
from_path = Path(args.from_file)
to_path = Path(args.to_file)
for p, label in [(from_path, "source"), (to_path, "target")]:
if not p.exists():
print(f"Error: {label} file not found: {p}", file=sys.stderr)
sys.exit(2)
ddl_parser = SimpleDDLParser()
from_tables = ddl_parser.parse(from_path.read_text())
to_tables = ddl_parser.parse(to_path.read_text())
generator = MigrationGenerator(from_tables, to_tables)
steps = generator.generate()
if args.format == "json":
print(format_json(steps))
else:
print(format_text(steps))
# Destructive steps need a human decision before they run anywhere
if any(is_destructive(st) for st in steps) and not args.allow_destructive:
sys.exit(1)
if __name__ == "__main__":
main()
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |