Metadata-Version: 2.4
Name: sqlalchemy-cauldron
Version: 0.1.2
Summary: Convert raw SQL queries into SQLAlchemy Core or ORM Python code.
Keywords: SQL,convert,transpile,python code
Author: Michael Faust
License-Expression: MIT
License-File: LICENSE.md
Classifier: Programming Language :: SQL
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Operating System :: OS Independent
Requires-Dist: sqlalchemy>=2.0.51,<3
Requires-Dist: sqlglot[c]>=30.12.0,<31
Requires-Dist: libcst>=1.8.6,<2
Requires-Dist: typer>=0.26.8,<1
Requires-Python: >=3.11, <4.0
Project-URL: Homepage, https://github.com/mpf82/sqlalchemy-cauldron
Project-URL: Issues, https://github.com/mpf82/sqlalchemy-cauldron/issues
Description-Content-Type: text/markdown

# sqlalchemy-cauldron

[![Python 3.11+](https://img.shields.io/badge/python-3.11+-blue.svg?logo=python)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/LICENSE.md)
[![SQLAlchemy 2.0+](https://img.shields.io/badge/sqlalchemy-2.0+-red.svg?logo=sqlalchemy)](https://www.sqlalchemy.org/)
[![sqlglot 30+](https://img.shields.io/badge/sqlglot-30.0+-red.svg)](https://sqlglot.com/)
[![Code Coverage](https://img.shields.io/badge/coverage-90+-green.svg)](https://github.com/mpf82/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](https://www.sqlalchemy.org/) 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

```bash
pip install sqlalchemy-cauldron
```

Requires Python 3.11+. Tested with SQLAlchemy 2.x

### Development

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


## CLI Example

```bash
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

```python
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](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/CLI.md).

```bash
# 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](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/EXAMPLES.md).

```python
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

```python
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:

```bash
# 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!<br>
Please read [CONTRIBUTING](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/CONTRIBUTING.md) for details.<br>
Please read [AI POLICY](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/AI_POLICY.md) if you are planning to use an LLM / AI assistant in your contributions.


## Reporting Bugs

Please read [BUG REPORT](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/BUG_REPORT.md) 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)
```bash
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.
```bash
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](https://github.com/mpf82/sqlalchemy-cauldron/wiki)
- 🐛 [Issue Tracker](https://github.com/mpf82/sqlalchemy-cauldron/issues)
- 💬 [Discussions](https://github.com/mpf82/sqlalchemy-cauldron/discussions)

### Support This Project
If you find sqlalchemy-cauldron useful, consider supporting the development:

- 💗 [GitHub Sponsors](https://github.com/sponsors/mpf82)
<!-- - ☕ [Buy me a coffee](https://buymeacoffee.com/mpf82) -->

Your support helps fund development, testing, and maintenance!

---

## License

This project is licensed under the MIT License - see [LICENSE](https://github.com/mpf82/sqlalchemy-cauldron/blob/main/LICENSE.md) for details.

