sql-database-assistant

SQL Database Assistant Category: Engineering Domain: Database Development & Optimization Overview The SQL Database Assistant skill provides tools for analyzing SQL query performance, exploring database schemas from DDL files, and generating migration SQL from schema differences. It helps teams write efficient queries, maintain clean schemas, and manage database evolution safely. Quick Start Tools Overview query optimizer.py Analyzes SQL queries for performance issues and optimization opportunities. | Feature | Description | |---------|-------------| | SELECT detection | Flags queries selectin…

,\n re.IGNORECASE\n )\n PK_CONSTRAINT = re.compile(\n r'(?:CONSTRAINT\\s+\\w+\\s+)?PRIMARY\\s+KEY\\s*\$([^)]+)\$',\n re.IGNORECASE\n )\n FK_CONSTRAINT = re.compile(\n r'(?:CONSTRAINT\\s+[\"`]?(\\w+)[\"`]?\\s+)?FOREIGN\\s+KEY\\s*\$([^)]+)\$\\s*REFERENCES\\s+[\"`]?(\\w+)[\"`]?\\s*\$([^)]+)\$',\n re.IGNORECASE\n )\n UNIQUE_CONSTRAINT = re.compile(\n r'(?:CONSTRAINT\\s+\\w+\\s+)?UNIQUE\\s*\$([^)]+)\$',\n re.IGNORECASE\n )\n\n SKIP_KEYWORDS = {\"PRIMARY\", \"FOREIGN\", \"CONSTRAINT\", \"UNIQUE\", \"CHECK\", \"INDEX\", \"KEY\"}\n\n def parse(self, sql: str) -> Dict[str, Table]:\n \"\"\"Parse DDL into table definitions.\"\"\"\n tables: Dict[str, Table] = {}\n\n # Parse CREATE TABLE statements\n for match in self.CREATE_TABLE.finditer(sql):\n table_name = match.group(1)\n body = match.group(2)\n table = self._parse_table_body(table_name, body)\n tables[table_name] = table\n\n # Parse standalone CREATE INDEX statements\n for match in self.CREATE_INDEX.finditer(sql):\n is_unique = bool(match.group(1))\n idx_name = match.group(2)\n table_name = match.group(3)\n columns = [c.strip().strip('\"`') for c in match.group(4).split(\",\")]\n\n if table_name in tables:\n tables[table_name].indexes.append(Index(\n name=idx_name, columns=columns, unique=is_unique\n ))\n\n return tables\n\n def _parse_table_body(self, table_name: str, body: str) -> Table:\n \"\"\"Parse the body of a CREATE TABLE statement.\"\"\"\n table = Table(name=table_name)\n\n # Split on commas, but respect parentheses\n parts = self._split_body(body)\n\n for part in parts:\n part = part.strip()\n if not part:\n continue\n\n # Check for constraints first\n pk_match = self.PK_CONSTRAINT.search(part)\n if pk_match:\n table.primary_key = [c.strip().strip('\"`') for c in pk_match.group(1).split(\",\")]\n continue\n\n fk_match = self.FK_CONSTRAINT.search(part)\n if fk_match:\n fk = ForeignKey(\n name=fk_match.group(1),\n columns=[c.strip().strip('\"`') for c in fk_match.group(2).split(\",\")],\n ref_table=fk_match.group(3),\n ref_columns=[c.strip().strip('\"`') for c in fk_match.group(4).split(\",\")],\n )\n table.foreign_keys.append(fk)\n continue\n\n uq_match = self.UNIQUE_CONSTRAINT.search(part)\n if uq_match:\n continue\n\n # Skip constraint-like lines\n first_word = part.split()[0].upper().strip('\"`') if part.split() else \"\"\n if first_word in self.SKIP_KEYWORDS:\n continue\n\n # Parse as column definition\n col = self._parse_column(part)\n if col:\n table.columns.append(col)\n if col.is_primary_key:\n table.primary_key.append(col.name)\n\n return table\n\n def _split_body(self, body: str) -> List[str]:\n \"\"\"Split table body on commas, respecting parentheses.\"\"\"\n parts = []\n depth = 0\n current = \"\"\n for char in body:\n if char == \"(\":\n depth += 1\n current += char\n elif char == \")\":\n depth -= 1\n current += char\n elif char == \",\" and depth == 0:\n parts.append(current)\n current = \"\"\n else:\n current += char\n if current.strip():\n parts.append(current)\n return parts\n\n def _parse_column(self, definition: str) -> Optional[Column]:\n \"\"\"Parse a column definition string.\"\"\"\n definition = definition.strip()\n if not definition:\n return None\n\n parts = definition.split()\n if len(parts) \u003c 2:\n return None\n\n name = parts[0].strip('\"`')\n upper_def = definition.upper()\n\n # Extract data type (everything between name and constraints)\n type_parts = []\n i = 1\n while i \u003c len(parts):\n word = parts[i].upper().strip('\"`')\n if word in (\"NOT\", \"NULL\", \"DEFAULT\", \"PRIMARY\", \"UNIQUE\",\n \"REFERENCES\", \"CHECK\", \"CONSTRAINT\", \"AUTO_INCREMENT\",\n \"SERIAL\", \"GENERATED\"):\n break\n type_parts.append(parts[i])\n i += 1\n\n data_type = \" \".join(type_parts).strip(\",\").strip()\n nullable = \"NOT NULL\" not in upper_def\n is_pk = \"PRIMARY KEY\" in upper_def\n is_unique = \"UNIQUE\" in upper_def\n\n # Extract default value\n default = None\n default_match = re.search(r'DEFAULT\\s+(.+?)(?:\\s+(?:NOT|NULL|PRIMARY|UNIQUE|CHECK|CONSTRAINT)|$)',\n definition, re.IGNORECASE)\n if default_match:\n default = default_match.group(1).strip().rstrip(\",\")\n\n # Serial/auto-increment implies not null\n if \"SERIAL\" in upper_def or \"AUTO_INCREMENT\" in upper_def:\n nullable = False\n\n return Column(\n name=name,\n data_type=data_type,\n nullable=nullable,\n default=default,\n is_primary_key=is_pk,\n is_unique=is_unique,\n )\n\n\ndef format_text(tables: Dict[str, Table]) -> str:\n \"\"\"Format as human-readable text.\"\"\"\n lines = []\n lines.append(\"=\" * 60)\n lines.append(\"DATABASE SCHEMA DOCUMENTATION\")\n lines.append(\"=\" * 60)\n lines.append(f\"\\nTables: {len(tables)}\")\n\n for name in sorted(tables.keys()):\n lines.append(f\" - {name} ({len(tables[name].columns)} columns)\")\n\n for name in sorted(tables.keys()):\n table = tables[name]\n lines.append(f\"\\n{'=' * 40}\")\n lines.append(f\"TABLE: {name}\")\n lines.append(f\"{'=' * 40}\")\n\n if table.primary_key:\n lines.append(f\"Primary Key: {', '.join(table.primary_key)}\")\n\n lines.append(f\"\\n{'Column':\u003c25} {'Type':\u003c20} {'Nullable':\u003c10} {'Default':\u003c15}\")\n lines.append(\"-\" * 70)\n\n for col in table.columns:\n null_str = \"YES\" if col.nullable else \"NO\"\n default_str = str(col.default) if col.default else \"\"\n pk_marker = \" (PK)\" if col.is_primary_key else \"\"\n uq_marker = \" (UQ)\" if col.is_unique else \"\"\n lines.append(f\"{col.name + pk_marker + uq_marker:\u003c25} {col.data_type:\u003c20} {null_str:\u003c10} {default_str:\u003c15}\")\n\n if table.foreign_keys:\n lines.append(f\"\\nForeign Keys:\")\n for fk in table.foreign_keys:\n fk_name = fk.name or \"(unnamed)\"\n lines.append(f\" {fk_name}: ({', '.join(fk.columns)}) -> {fk.ref_table}({', '.join(fk.ref_columns)})\")\n\n if table.indexes:\n lines.append(f\"\\nIndexes:\")\n for idx in table.indexes:\n unique_str = \"UNIQUE \" if idx.unique else \"\"\n lines.append(f\" {idx.name}: {unique_str}({', '.join(idx.columns)})\")\n\n lines.append(\"\\n\" + \"=\" * 60)\n return \"\\n\".join(lines)\n\n\ndef format_markdown(tables: Dict[str, Table]) -> str:\n \"\"\"Format as Markdown documentation.\"\"\"\n lines = []\n lines.append(\"# Database Schema\\n\")\n lines.append(f\"**Tables:** {len(tables)}\\n\")\n\n # Table of contents\n lines.append(\"## Tables\\n\")\n for name in sorted(tables.keys()):\n lines.append(f\"- [{name}](#{name.lower()})\")\n lines.append(\"\")\n\n for name in sorted(tables.keys()):\n table = tables[name]\n lines.append(f\"### {name}\\n\")\n\n if table.primary_key:\n lines.append(f\"**Primary Key:** {', '.join(table.primary_key)}\\n\")\n\n lines.append(\"| Column | Type | Nullable | Default |\")\n lines.append(\"|--------|------|----------|---------|\")\n\n for col in table.columns:\n null_str = \"YES\" if col.nullable else \"NO\"\n default_str = str(col.default) if col.default else \"-\"\n col_name = f\"**{col.name}** (PK)\" if col.is_primary_key else col.name\n lines.append(f\"| {col_name} | {col.data_type} | {null_str} | {default_str} |\")\n\n if table.foreign_keys:\n lines.append(f\"\\n**Foreign Keys:**\\n\")\n for fk in table.foreign_keys:\n lines.append(f\"- `{', '.join(fk.columns)}` -> `{fk.ref_table}({', '.join(fk.ref_columns)})`\")\n\n lines.append(\"\")\n\n return \"\\n\".join(lines)\n\n\ndef format_json(tables: Dict[str, Table]) -> str:\n \"\"\"Format as JSON.\"\"\"\n data = {}\n for name, table in tables.items():\n data[name] = {\n \"columns\": [asdict(c) for c in table.columns],\n \"primary_key\": table.primary_key,\n \"foreign_keys\": [asdict(fk) for fk in table.foreign_keys],\n \"indexes\": [asdict(idx) for idx in table.indexes],\n }\n return json.dumps({\"tables\": data, \"table_count\": len(data)}, indent=2)\n\n\ndef main():\n parser = argparse.ArgumentParser(\n description=\"Generate schema documentation from SQL DDL files.\"\n )\n parser.add_argument(\"--file\", \"-f\", required=True, help=\"Path to SQL DDL file\")\n parser.add_argument(\"--format\", choices=[\"text\", \"json\", \"markdown\"], default=\"text\",\n help=\"Output format\")\n args = parser.parse_args()\n\n path = Path(args.file)\n if not path.exists():\n print(f\"Error: File not found: {args.file}\", file=sys.stderr)\n sys.exit(2)\n\n sql = path.read_text()\n ddl_parser = DDLParser()\n tables = ddl_parser.parse(sql)\n\n if not tables:\n print(\"No tables found in DDL file.\", file=sys.stderr)\n sys.exit(2)\n\n if args.format == \"json\":\n print(format_json(tables))\n elif args.format == \"markdown\":\n print(format_markdown(tables))\n else:\n print(format_text(tables))\n\n\nif __name__ == \"__main__\":\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":11793,"content_sha256":"96ce69481f2c40110f32be608b4c3c87902326cee834b892573a05a1c2f5baff"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"SQL Database Assistant","type":"text"}]},{"type":"blockquote","content":[{"type":"paragraph","content":[{"text":"Category:","type":"text","marks":[{"type":"strong"}]},{"text":" Engineering ","type":"text"},{"text":"Domain:","type":"text","marks":[{"type":"strong"}]},{"text":" Database Development & Optimization","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"The ","type":"text"},{"text":"SQL Database Assistant","type":"text","marks":[{"type":"strong"}]},{"text":" skill provides tools for analyzing SQL query performance, exploring database schemas from DDL files, and generating migration SQL from schema differences. It helps teams write efficient queries, maintain clean schemas, and manage database evolution safely.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Analyze a SQL query for performance issues\npython scripts/query_optimizer.py --file slow_query.sql\n\n# Analyze inline SQL\npython scripts/query_optimizer.py --query \"SELECT * FROM users WHERE name LIKE '%john%'\"\n\n# Explore schema from DDL file\npython scripts/schema_explorer.py --file schema.sql\n\n# Generate migration from schema diff\npython scripts/migration_generator.py --from old_schema.sql --to new_schema.sql\n\n# JSON output\npython scripts/query_optimizer.py --file query.sql --format json","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Tools Overview","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"query_optimizer.py","type":"text"}]},{"type":"paragraph","content":[{"text":"Analyzes SQL queries for performance issues and optimization opportunities.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Feature","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT * detection","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Flags queries selecting all columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing index hints","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Identifies WHERE/JOIN columns likely needing indexes","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"N+1 detection","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Flags correlated subquery patterns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full table scan","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Detects queries without WHERE clauses on large tables","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"JOIN analysis","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Checks join conditions and types","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"LIKE optimization","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Flags leading wildcard LIKE patterns","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"schema_explorer.py","type":"text"}]},{"type":"paragraph","content":[{"text":"Generates documentation from SQL DDL (CREATE TABLE) files.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Feature","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Table catalog","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Lists all tables with column counts","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Column details","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Documents types, nullability, defaults","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index listing","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Catalogs indexes and their columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Relationship mapping","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Identifies foreign key relationships","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Markdown output","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Generates schema documentation","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"migration_generator.py","type":"text"}]},{"type":"paragraph","content":[{"text":"Generates migration SQL by comparing two schema DDL files.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Feature","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Column additions","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ALTER TABLE ADD COLUMN for new columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Column removals","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ALTER TABLE DROP COLUMN for removed columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Type changes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ALTER TABLE ALTER COLUMN for type modifications","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"New tables","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"CREATE TABLE for entirely new tables","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Dropped tables","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"DROP TABLE for removed tables","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index changes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"CREATE/DROP INDEX for index differences","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Workflows","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Optimization Workflow","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Identify slow queries","type":"text","marks":[{"type":"strong"}]},{"text":" - Collect queries from slow query log","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Analyze","type":"text","marks":[{"type":"strong"}]},{"text":" - Run query_optimizer.py on each query","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review findings","type":"text","marks":[{"type":"strong"}]},{"text":" - Prioritize by estimated impact","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimize","type":"text","marks":[{"type":"strong"}]},{"text":" - Apply suggested improvements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Verify","type":"text","marks":[{"type":"strong"}]},{"text":" - Re-analyze to confirm optimization","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Schema Documentation Workflow","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Export DDL","type":"text","marks":[{"type":"strong"}]},{"text":" - Dump schema from database","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Explore","type":"text","marks":[{"type":"strong"}]},{"text":" - Run schema_explorer.py to generate docs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review","type":"text","marks":[{"type":"strong"}]},{"text":" - Check relationships and data types","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Publish","type":"text","marks":[{"type":"strong"}]},{"text":" - Include in project documentation","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Migration Workflow","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Capture current","type":"text","marks":[{"type":"strong"}]},{"text":" - Export current schema DDL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define target","type":"text","marks":[{"type":"strong"}]},{"text":" - Write desired schema DDL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Generate migration","type":"text","marks":[{"type":"strong"}]},{"text":" - Run migration_generator.py","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review SQL","type":"text","marks":[{"type":"strong"}]},{"text":" - Check generated migration for safety","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test","type":"text","marks":[{"type":"strong"}]},{"text":" - Apply to staging database first","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Deploy","type":"text","marks":[{"type":"strong"}]},{"text":" - Apply to production with rollback plan","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CI Integration","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Lint SQL queries\npython scripts/query_optimizer.py --file queries/ --format json --strict\n\n# Generate schema docs\npython scripts/schema_explorer.py --file schema.sql --format markdown > SCHEMA.md","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Documentation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQL Optimization","type":"text","marks":[{"type":"link","attrs":{"href":"references/sql-optimization.md","title":null}}]},{"text":" - Index strategies, query patterns, anti-patterns","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Patterns Quick Reference","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Anti-Patterns","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Pattern","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Issue","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fix","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SELECT *","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Fetches unnecessary data","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"List specific columns","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"LIKE '%term%'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cannot use index","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use full-text search","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Correlated subquery","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"N+1 query pattern","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Rewrite as JOIN","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"No WHERE clause","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full table scan","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add filtering conditions","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"OR","type":"text","marks":[{"type":"code_inline"}]},{"text":" in WHERE","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Poor index usage","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use UNION or IN","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Functions on indexed columns","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Prevents index use","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Apply to value side","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Index Guidelines","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Query Pattern","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Index Type","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"WHERE col = value","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"B-tree on col","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"WHERE col1 = v AND col2 = v","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Composite (col1, col2)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ORDER BY col","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"B-tree on col","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"WHERE col LIKE 'prefix%'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"B-tree on col","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"WHERE col IN (...)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"B-tree on col","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full-text search","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Full-text index","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Migration Safety","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Always generate rollback SQL alongside forward migration","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test migrations against a copy of production data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add columns as nullable first, then backfill, then add constraints","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Never rename columns directly; add new, migrate data, drop old","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"sql-database-assistant","author":"@skillopedia","source":{"stars":209,"repo_name":"claude-skills","origin_url":"https://github.com/borghei/claude-skills/blob/HEAD/engineering/sql-database-assistant/SKILL.md","repo_owner":"borghei","body_sha256":"8e39c557d1c55e1c16c1435891e63e5128afd92b124f86dfa9378b0376824833","cluster_key":"187c8b3f4980f43b5614dea305eead080b49982aabe2d2dcd0a685c2f740aad7","clean_bundle":{"format":"clean-skill-bundle-v1","source":"borghei/claude-skills/engineering/sql-database-assistant/SKILL.md","attachments":[{"id":"7871e156-d354-5c20-be10-3539430ebf65","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/7871e156-d354-5c20-be10-3539430ebf65/attachment.sql","path":"examples/queries.sql","size":3201,"sha256":"32a62721ecdab37e379b329c95b317dd359e5732568b6b06026227319f92e2c1","contentType":"application/sql"},{"id":"ccde3605-9ef7-58c3-a335-a77a273e861b","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ccde3605-9ef7-58c3-a335-a77a273e861b/attachment.sql","path":"examples/schema.sql","size":3382,"sha256":"4291d79bad13522f447d62e82f7e00e6863a64b72542678fe878291e95a91296","contentType":"application/sql"},{"id":"60bbf3b9-fa9d-5556-ad3b-98411664cd6d","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/60bbf3b9-fa9d-5556-ad3b-98411664cd6d/attachment.md","path":"references/sql-optimization.md","size":4034,"sha256":"a868f0e4cd8dd95a0a6a6103ca1a4258b36fbed895c303ad21a1242352d38724","contentType":"text/markdown; charset=utf-8"},{"id":"d7f46d21-c1e7-5da2-8940-83cd05d621bf","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/d7f46d21-c1e7-5da2-8940-83cd05d621bf/attachment.py","path":"scripts/migration_generator.py","size":13084,"sha256":"c14ce754eff632e52b28eb0b65950760cf49e52826eb482dec376b6dbc3bef8b","contentType":"text/x-python; charset=utf-8"},{"id":"9eac6d8f-e983-562e-af81-faa359feb95b","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/9eac6d8f-e983-562e-af81-faa359feb95b/attachment.py","path":"scripts/query_optimizer.py","size":14963,"sha256":"ca9f92a570323621f52b0d63a122321ad62d05479ae29eedf9d0438e26213024","contentType":"text/x-python; charset=utf-8"},{"id":"65c02b35-0eee-5d19-8cbb-727473fa4063","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/65c02b35-0eee-5d19-8cbb-727473fa4063/attachment.py","path":"scripts/schema_explorer.py","size":11793,"sha256":"96ce69481f2c40110f32be608b4c3c87902326cee834b892573a05a1c2f5baff","contentType":"text/x-python; charset=utf-8"}],"bundle_sha256":"617f3a1a0a9b775e595820feb4977e3015cfb9af77ef82c1e5b471df482e01c0","attachment_count":6,"text_attachments":6,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"engineering/sql-database-assistant/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"license":"MIT + Commons Clause","version":"v1","category":"data-analytics","metadata":{"tags":["sql","database","optimization","migration","schema"],"author":"borghei","domain":"databases","updated":"2026-04-02T00:00:00.000Z","version":"1.0.0","category":"engineering"},"import_tag":"clean-skills-v1","description":"This skill should be used when the user asks to \"optimize SQL queries\", \"explore database schemas\", \"generate migration SQL\", \"analyze query performance\", or \"document database structure\".\n"}},"renderedAt":1782979271189}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.