Grok 4.1 Fast· xai
Frontier Reasoning Traces
Build a declarative schema diff generator: safe plan, rollback, and approval hooks.
- User
Build a declarative schema diff generator: safe plan, rollback, and approval hooks.
- Assistant
import json import sys from typing import Dict, Any, Optional, Callable, List, Union from dataclasses import dataclass, asdict from enum import Enum # Schema format example: # { # "tables": { # "users": { # "columns": { # "id": {"type": "serial", "primary_key": True}, # "name": {"type": "varchar(255)", "nullable": False}, # "email": {"type": "varchar(255)", "nullable": True, "default": null} # } # } # } # } # Types are SQL-ish strings, e.g., "serial", "int", "varchar(100)", "text". # Supports: type (str), nullable (bool), default (str|None), primary_key (bool), auto_increment (bool, optional). @dataclass class ColumnChange: column: str from_def: Dict[str, Any] to_def: Dict[str, Any] @dataclass class TableAlter: add_columns: List[str] drop_columns: List[str] alter_columns: List[ColumnChange] class DiffOp(Enum): ADD_TABLE = "add_table" DROP_TABLE = "drop_table" ALTER_TABLE = "alter_table" @dataclass class SchemaDiff: add_tables: Dict[str, Dict[str, Any]] # {table_name: table_def} drop_tables: List[str] alter_tables: Dict[str, TableAlter] # {table_name: TableAlter} class SchemaDiffGenerator: def __init__( self, current_schema: Dict[str, Any], desired_schema: Dict[str, Any], plan_hook: Optional[Callable[[str], Optional[str]]] = None, approval_hook: Optional[Callable[[str], bool]] = None, apply_hook: Optional[Callable[[SchemaDiff], bool]] = None, rollback_hook: Optional[Callable[[SchemaDiff], bool]] = None, ): self.current_schema = self._normalize_schema(current_schema) self.desired_schema = self._normalize_schema(desired_schema) self.plan_hook = plan_hook or (lambda p: p) self.approval_hook = approval_hook or self._default_approval self.apply_hook = apply_hook self.rollback_hook = rollback_hook or self._default_rollback_warn def _normalize_schema(self, schema: Dict[str, Any]) -> Dict[str, Any]: """Normalize schema: ensure 'tables', standardize None defaults, bools.""" norm = schema.copy() tables = norm.get("tables", {}) for table_def in tables.values(): cols = table_def.setdefault("columns", {}) for col_def in cols.values(): if "default" in col_def and col_def["default"] is None: del col_def["default"] # None default is implicit for k in ("nullable", "primary_key", "auto_increment"): if k not in col_def: col_def[k] = False col_def["nullable"] = bool(col_def.get("nullable", False)) return norm def _col_def_diff(self, a: Dict[str, Any], b: Dict[str, Any]) -> bool: """Check if two column defs differ.""" keys = set(a) | set(b) for k in keys: if a.get(k) != b.get(k): return True return False def generate_diff(self) -> SchemaDiff: """Compute the declarative diff.""" current_tables = self.current_schema.get("tables", {}) desired_tables = self.desired_schema.get("tables", {}) c_names = set(current_tables) d_names = set(desired_tables) add_tables = {t: desired_tables[t] for t in d_names - c_names} drop_tables = list(c_names - d_names) alter_tables = {} for t in c_names & d_names: c_cols = current_tables[t]["columns"] d_cols = desired_tables[t]["columns"] c_col_names = set(c_cols) d_col_names = set(d_cols) add_cols = list(d_col_names - c_col_names) drop_cols = list(c_col_names - d_col_names) alter_cols = [] for col in c_col_names & d_col_names: if self._col_def_diff(c_cols[col], d_cols[col]): alter_cols.append(ColumnChange( column=col, from_def=c_cols[col], to_def=d_cols[col] )) if add_cols or drop_cols or alter_cols: alter_tables[t] = TableAlter(add_cols, drop_cols, alter_cols) return SchemaDiff(add_tables, drop_tables, alter_tables) def format_plan(self, diff: SchemaDiff) -> str: """Format diff as human-readable plan.""" lines = ["=== Schema Diff Plan ===", ""] if diff.add_tables: lines.append("📥 ADD TABLES:") for t, defn in diff.add_tables.items(): lines.append(f" - {t}") lines.append("") if diff.drop_tables: lines.append("🗑️ DROP TABLES (DESTRUCTIVE!):") for t in diff.drop_tables: lines.append(f" - {t}") lines.append("") if diff.alter_tables: lines.append("🔄 ALTER TABLES:") for t, alter in diff.alter_tables.items(): lines.append(f" {t}:") if alter.add_columns: lines.append(f" 📥 ADD COLUMNS: {', '.join(alter.add_columns)}") if alter.drop_columns: lines.append(f" 🗑️ DROP COLUMNS (DESTRUCTIVE!): {', '.join(alter.drop_columns)}") if alter.alter_columns: lines.append(f" 🔄 ALTER COLUMNS:") for chg in alter.alter_columns: lines.append(f" - {chg.column}: {json.dumps(asdict(chg.from_def))} → {json.dumps(asdict(chg.to_def))}") lines.append("") lines.append("⚠️ Review destructive operations (drops) carefully!") lines.append("========================") return "\n".join(lines) def reverse_diff(self, diff: SchemaDiff) -> SchemaDiff: """Generate reverse diff for rollback (approximate; drops/adds swapped, alters reversed).""" # Note: Rollback may not restore data! Use with caution. rev_add = {t: None for t in diff.drop_tables} # Placeholder; need original defs for full reverse rev_drop = list(diff.add_tables.keys()) rev_alter = {} for t, alter in diff.alter_tables.items(): rev_alter[t] = TableAlter( add_columns=alter.drop_columns, drop_columns=alter.add_columns, alter_columns=[ColumnChange(c.column, c.to_def, c.from_def) for c in alter.alter_columns] ) return SchemaDiff(rev_add, rev_drop, rev_alter) def plan(self, diff: Optional[SchemaDiff] = None) -> str: """Safe plan: compute/format diff, run plan_hook.""" if diff is None: diff = self.generate_diff() plan_str = self.format_plan(diff) hooked_plan = self.plan_hook(plan_str) if hooked_plan is not None: plan_str = hooked_plan print(plan_str) return plan_str def approve(self, plan_str: str) -> bool: """Run approval hook.""" return self.approval_hook(plan_str) def apply(self, diff: SchemaDiff) -> bool: """Run apply hook.""" if self.apply_hook is None: print("❌ apply_hook not provided. Skipping apply.", file=sys.stderr) return False return self.apply_hook(diff) def rollback(self, diff: SchemaDiff) -> bool: """Run rollback hook on reverse diff.""" rev_diff = self.reverse_diff(diff) return self.rollback_hook(rev_diff) def workflow(self, mode: str = "plan"): """Full workflow: plan -> approve -> apply (+rollback on fail).""" diff = self.generate_diff() plan_str = self.plan(diff) if mode == "plan": return True if not self.approve(plan_str): print("❌ Not approved.") return False success = self.apply(diff) if not success: print("❌ Apply failed. Attempting rollback...") self.rollback(diff) return False print("✅ Applied successfully.") return True # Default hooks @staticmethod def _default_approval(plan_str: str) -> bool: return input("\nApprove these changes? [y/N]: ").strip().lower() == 'y' @staticmethod def _default_rollback_warn(rev_diff: SchemaDiff) -> bool: print("⚠️ Default rollback: just warning (no action). Implement rollback_hook for real rollback.") print("Reverse plan:") SchemaDiffGenerator(None, None).format_plan(rev_diff) # Temp generator for format print("(Rollback may not restore data from drops/resizes.)") return False # No-op by default # Example SQL hooks for PostgreSQL (requires psycopg2, sqlalchemy) def postgres_sql_plan_hook(plan_str: str) -> str: """Example: Enhance plan with generated SQL.""" # In real use, generate SQL here and append. return plan_str + "\n\n(Use postgres_apply_hook for SQL execution.)" def postgres_apply_hook(diff: SchemaDiff, conn_str: str) -> bool: """Example apply hook: generates/executes Postgres SQL.""" import psycopg2 from psycopg2.extras import RealDictCursor def col_ddl(col_name: str, col_def: Dict[str, Any]) -> str: parts = [col_name, col_def["type"]] if not col_def["nullable"]: parts.append("NOT NULL") if col_def.get("primary_key"): parts[-1] += " PRIMARY KEY" if "default" in col_def: parts.append(f"DEFAULT {col_def['default']}") if col_def.get("auto_increment"): # Handled by serial type usually pass return " ".join(parts) try: conn = psycopg2.connect(conn_str) cur = conn.cursor(cursor_factory=RealDictCursor) sqls = [] # Add tables for t, tdef in diff.add_tables.items(): cols = ", ".join(col_ddl(n, d) for n, d in tdef["columns"].items()) sqls.append(f"CREATE TABLE {t} ({cols});") # Drop tables (reverse order to avoid FK issues) for t in reversed(diff.drop_tables): sqls.append(f"DROP TABLE IF EXISTS {t};") # Alter tables for t, alter in diff.alter_tables.items(): # Adds for col in alter.add_columns: col_def = {} # Need to fetch from desired? Wait, in full impl, pass desired. # Placeholder: assume you have access to generator.desired_schema sqls.append(f"ALTER TABLE {t} ADD COLUMN {col} varchar(255);") # Dummy # Drops for col in alter.drop_columns: sqls.append(f"ALTER TABLE {t} DROP COLUMN IF EXISTS {col};") # Alters (simplified; real needs type change handling) for chg in alter.alter_columns: sqls.append(f"ALTER TABLE {t} ALTER COLUMN {chg.column} TYPE {chg.to_def['type']};") if chg.from_def.get("nullable") != chg.to_def.get("nullable"): nullness = "DROP" if chg.to_def["nullable"] else "SET" sqls.append(f"ALTER TABLE {t} ALTER COLUMN {chg.column} {nullness} NOT NULL;") for sql in sqls: print(f"Executing: {sql}") cur.execute(sql) conn.commit() cur.close() conn.close() return True except Exception as e: print(f"❌ SQL Error: {e}", file=sys.stderr) return False # CLI Wrapper (no external deps) def main(): if len(sys.argv) < 3: print("Usage: python schema_diff.py <current_schema.json> <desired_schema.json> [plan|apply|rollback]", file=sys.stderr) sys.exit(1) mode = sys.argv[3] if len(sys.argv) > 3 else "plan" with open(sys.argv[1]) as f: current = json.load(f) with open(sys.argv[2]) as f: desired = json.load(f) gen = SchemaDiffGenerator(current, desired) gen.workflow(mode) if __name__ == "__main__": main()Usage Examples
-
Safe Plan:
python schema_diff.py current_schema.json desired_schema.json planOutputs human-readable diff plan. Customize with
plan_hook. -
Full Apply Workflow (with approval):
python schema_diff.py current_schema.json desired_schema.json applyPlans, asks approval, calls
apply_hook(default skips; provide custom). -
Custom Hooks (e.g., Postgres):
gen = SchemaDiffGenerator(current, desired, plan_hook=postgres_sql_plan_hook, approval_hook=lambda p: True, # Auto-approve apply_hook=lambda d: postgres_apply_hook(d, "postgresql://user:pass@host/db") ) gen.workflow("apply") -
Rollback (post-fail):
diff = gen.generate_diff() # From earlier apply gen.rollback(diff)
Features
- Declarative: JSON schemas → structured
SchemaDiff. - Safe Plan: Pretty-printed ops, warnings for destructive changes.
- Approval Hooks: Default interactive; override for CI/CD (e.g., GitHub approval).
- Rollback Hooks: Auto-generates reverse diff; default warns (data loss risk).
- Extensible: Hooks for any backend (SQL, ORM, Terraform, etc.).
- No deps for core (JSON CLI); Postgres example shown.
- Limitations: No indexes/FKs/views (extend
generate_diff). Alters simplified (no safe type changes). Rollback approximate.
Extend
generate_diff/format_planfor more schema features! 🚀 -
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.