SQL Database Assistant

Category: Engineering Domain: Database Development & Optimization

Overview

Quick Start

# Analyze a SQL query for performance issues
python scripts/query_optimizer.py --file slow_query.sql

# Analyze inline SQL
python scripts/query_optimizer.py --query "SELECT * FROM users WHERE name LIKE '%john%'"

# Explore schema from DDL file
python scripts/schema_explorer.py --file schema.sql

# Generate migration from schema diff
python scripts/migration_generator.py --from old_schema.sql --to new_schema.sql

# JSON output
python scripts/query_optimizer.py --file query.sql --format json

Tools Overview

query_optimizer.py

schema_explorer.py

migration_generator.py

Workflows

Query Optimization Workflow

1. Identify slow queries - Collect queries from slow query log
2. Analyze - Run query_optimizer.py on each query
3. Review findings - Prioritize by estimated impact
4. Optimize - Apply suggested improvements
5. Verify - Re-analyze to confirm optimization

Schema Documentation Workflow

1. Export DDL - Dump schema from database
2. Explore - Run schema_explorer.py to generate docs
3. Review - Check relationships and data types
4. Publish - Include in project documentation

Migration Workflow

1. Capture current - Export current schema DDL
2. Define target - Write desired schema DDL
3. Generate migration - Run migration_generator.py
4. Review SQL - Check generated migration for safety
5. Test - Apply to staging database first
6. Deploy - Apply to production with rollback plan

CI Integration

# Lint SQL queries
python scripts/query_optimizer.py --file queries/ --format json --strict

# Generate schema docs
python scripts/schema_explorer.py --file schema.sql --format markdown > SCHEMA.md

Reference Documentation

• SQL Optimization - Index strategies, query patterns, anti-patterns

Common Patterns Quick Reference

Query Anti-Patterns

SELECT *

LIKE '%term%'

OR

Index Guidelines

WHERE col = value

WHERE col1 = v AND col2 = v

ORDER BY col

WHERE col LIKE 'prefix%'

WHERE col IN (...)

Migration Safety

• Always generate rollback SQL alongside forward migration
• Test migrations against a copy of production data
• Add columns as nullable first, then backfill, then add constraints
• Never rename columns directly; add new, migrate data, drop old