sqlalchemy-cauldron

SQL to SQLAlchemy code generator - Convert raw SQL queries into SQLAlchemy 2.x Core or ORM Python code.

Drop a SQL query into cauldron, call brew(), and watch a SQLAlchemy potion emerge. The library parses your raw SQL string and generates equivalent SQLAlchemy code, making migrations away from handwritten SQL significantly easier.

No schema, model, database connection, or other context is required. Tables, columns, and basic types are inferred from the query.

Currently, the primary focus is accurate conversion of SELECT statements.

Installation

From PyPI

pip install sqlalchemy-cauldron

Requires Python 3.11+. Tested with SQLAlchemy 2.x

Development

# Clone and install with uv
git clone https://github.com/mpf82/sqlalchemy-cauldron
cd sqlalchemy-cauldron
uv sync

CLI Example

cauldron orm "SELECT users.id, COUNT(orders.id) AS order_count FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id GROUP BY users.id"

Output

from sqlalchemy import Column, Integer, Select, func, select
from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

class Orders(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)

class Users(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)

query: Select = (
    select(Users.id, func.count(Orders.id).label("order_count"))
    .select_from(Users)
    .join(Orders, Users.id == Orders.user_id, isouter=True)
    .group_by(Users.id)
)
sql = query.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})

Column types are inferred best-effort; complex/ambiguous types may require editing generated code.

Features

• Dual Interface: CLI for quick code generation and a Python library for programmatic SQL-to-code conversion
• CLI Commands: orm, core, normalize, schema, and json
• ORM code generation: Generate SQLAlchemy 2.x declarative models (including relationships where they can be inferred)
• CORE code generation: Generate SQLAlchemy 2.x Table/MetaData definitions
• Schema Extraction: Parse SQL to extract table and column information
• Normalize: Normalize/Transpile your SQL query
• Generates runnable SQLAlchemy 2.x Python code for supported statements
• Best-effort column type inference from SQL
• Optional self-contained test code generation for ORM models using an in-memory SQLite DB
• Supports Multiple SQL Dialects
  • PostgreSQL
  • SQLite
  • Oracle
  • MySQL / MariaDB
  • MSSQL / T-SQL
  • Generic (no specific dialect)

Quick Start

As a CLI Tool

For a deep dive into the CLI commands and examples, see the Command Line Interface page.

# Generate ORM code
cauldron orm "SELECT id, name FROM users WHERE active = 1"

# Generate CORE code with specific dialect
cauldron core "SELECT * FROM orders" --dialect sqlite

# Extract schema as JSON
cauldron json "SELECT u.id, o.order_id FROM users u JOIN orders o"

# Normalize/Transpile SQL query
cauldron normalize "SELECT * FROM users AS u LIMIT 10" --dialect oracle

# Save to file
cauldron orm --file query.sql --output generated.py

As a Python Library

For more examples, see the API EXAMPLES page.

from sqlalchemy_cauldron import brew, GeneratorConfig, Generator, Dialect, normalize_and_parse, extract_schema_and_tables

# Simple: ORM code generation (default)
# -------------------------------------------------------------------
sql = "SELECT id, name, email FROM users WHERE status = 'active'"
code = brew(sql)
print(code)

# Advanced: CORE with optimization
# -------------------------------------------------------------------
config = GeneratorConfig(
    generator=Generator.CORE,
    optimize_sql=True,
    dialect=Dialect.POSTGRES
)
code = brew(sql, config)
print(code)

# Normalize and Parse
# -------------------------------------------------------------------
result = normalize_and_parse(
    sql="SELECT id FROM users",
    dialect="sqlite",
    optimize=True,
    resolve_aliases=True
)

print(result.normalized_sql)  # Optimized/normalized SQL
print(result.expr)            # SQLGlot Expression (AST)

# Extract Schema and Tables
# -------------------------------------------------------------------
result = normalize_and_parse("SELECT u.id, u.name, o.total FROM users u JOIN orders o")
schema = extract_schema_and_tables(result.expr)

for table_info in schema.table_infos:
    print(f"Table: {table_info.name}")
    for col in table_info.columns:
        print(f"  - {col.name}")

# schema.tables contains SQLAlchemy Table/MetaData objects
print(schema.tables)  # {'users': Table(...), 'orders': Table(...)}

Configuration

from sqlalchemy_cauldron import GeneratorConfig, Generator, Dialect
from pathlib import Path

config = GeneratorConfig(
    # Choice of generator
    generator=Generator.CORE,         # or Generator.ORM

    # SQL processing options
    dialect=Dialect.POSTGRES,         # SQL dialect to parse
    optimize_sql=False,               # Enable SQLGlot optimization
    resolve_aliases=False,            # Resolve table aliases

    # Output
    path=Path("generated.py"),        # Optional: write to file

    # Modify generated code
    output_imports=True,              # Whether to include import statements in the generated code
    output_definitions=True,          # Whether to include ORM model definitions in the generated code
    output_original_sql=True,         # Whether to include the original SQL query as a comment in the generated code
    output_compile=True,              # Whether to include code that compiles the SQLAlchemy models to SQL

    # ORM-specific options
    output_self_contained_test=False, # Whether to generate test code for ORM models
)

Testing

Run the comprehensive test suite:

# All tests
uv run pytest

# Specific test file
uv run pytest tests/test_main.py -v

# CLI tests only
uv run pytest tests/test_cli.py -v

# With coverage
uv run pytest --cov=sqlalchemy_cauldron

Test Coverage

• more than 4700 tests
• over 99% code coverage
• CLI integration tests
• Library API tests
• Edge case handling
• Multiple SQL dialects
• Complex query support
• Roundtrip tests (SQL -> SQLAlchemy -> SQL)

Known Limitations

• The library focuses on the SELECT statement, support for other DDL/DML statements is rudimentary at best
• RIGHT OUTER JOIN is not directly supported by SQLAlchemy code generation - use an equivalent LEFT OUTER JOIN by reversing tables
• Uncommon SQL extensions (vendor-specific) may have limited support
• The DB dialects are limited by the dialects supported by both SQLGlot and SQLAlchemy
• The generated Python code contains a large imports section

Planned Features

• Write documentation and setup documentation generation with zensical
• Improve support of INSERT, UPDATE, and DELETE statements
• Optionally provide a path to existing model(s) -> no need to infer tables, columns and types from the raw SQL statement

Contributing

Contributions welcome!
Please read CONTRIBUTING for details.
Please read AI POLICY if you are planning to use an LLM / AI assistant in your contributions.

Reporting Bugs

Please read BUG REPORT before you create an issue.

Troubleshooting

Invalid SQL Error

Error: Invalid expression / Unexpected token

If parsing fails, try --dialect matching your source database (or the closest supported one)

cauldron orm "SELECT * FROM users" --dialect postgres

Generated Code Mixes Table and Aliased objects

Try using --resolve-aliases to resolve table aliases in the generated code.

cauldron orm "SELECT u.id, COUNT(o.id) AS order_count FROM users AS u LEFT OUTER JOIN orders AS o ON u.id = o.user_id GROUP BY u.id" --resolve-aliases

Generated Code Does Not Match My SQL Statement

Due to parsing, normalization, different dialects, and the conversion to SQLAlchemy, the generated code is not always 100% identical to the input.

Type Inference Issues

Type inference is best-effort and may not always be accurate. The generated code may require manual editing to correct column types, especially for complex types or vendor-specific types.

Support

Get Help

• 📖 Documentation
• 🐛 Issue Tracker
• 💬 Discussions

Support This Project

If you find sqlalchemy-cauldron useful, consider supporting the development:

• 💗 GitHub Sponsors

Your support helps fund development, testing, and maintenance!

---

License

This project is licensed under the MIT License - see LICENSE for details.