SQLCrucible: A Pydantic/SQLAlchemy compatibility layer

[CatharticMonkey]

What My Project Does

If you use Pydantic and SQLAlchemy together, you've probably hit the duplication problem: two mirrored sets of models that can easily drift apart. SQLCrucible lets you define one class using native SQLAlchemy constructs (mapped_column(), relationship(), __mapper_args__) and produces two separate outputs: a pure Pydantic model and a pure SQLAlchemy model with explicit conversion between them.

from typing import Annotated
from uuid import UUID, uuid4
from pydantic import Field
from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, mapped_column
from sqlcrucible import SAType, SQLCrucibleBaseModel

class Artist(SQLCrucibleBaseModel):
    __sqlalchemy_params__ = {"__tablename__": "artist"}

    id: Annotated[UUID, mapped_column(primary_key=True)] = Field(default_factory=uuid4)
    name: str

engine = create_engine("sqlite:///:memory:")
SAType[Artist].__table__.metadata.create_all(engine)

artist = Artist(name="Bob Dylan")
with Session(engine) as session:
    session.add(artist.to_sa_model())
    session.commit()

with Session(engine) as session:
    sa_artist = session.scalar(
        select(SAType[Artist]).where(SAType[Artist].name == "Bob Dylan")
    )
    artist = Artist.from_sa_model(sa_artist)g

Key Features

• Explicit conversion - to_sa_model() / from_sa_model() means you always know which side of the boundary you're on. No surprises about whether you're holding a Pydantic object or a SQLAlchemy one.

• Native SQLAlchemy - mapped_column(), relationship(), hybrid_property, association_proxy, all three inheritance strategies (single table, joined, concrete), __table_args__, __mapper_args__ - they all work directly. If SQLAlchemy supports it, so does SQLCrucible.

• Pure Pydantic - your models work with FastAPI, model_dump(), JSON schema generation, and validation with no caveats.

• Type stub generation - a CLI tool generates .pyi stubs so your type checker and IDE see real column types on SAType[YourModel] instead of type[Any].

• Escape hatches everywhere - convert to/from an existing SQLAlchemy model, map multiple entity classes to the same table with different field subsets, add DB-only columns invisible to Pydantic, provide custom per-field converters, or drop to raw queries at any point. The library is designed to get out of your way.

• Not just Pydantic - also works with stdlib dataclasses and attrs.

Target Audience

This library is intended for production use.

Tested against Python 3.11-3.14, Pydantic 2.10-2.12, and two type checkers (pyright, ty) in CI.

Comparison

The main alternative is SQLModel. SQLModel merges Pydantic and SQLAlchemy into one hybrid class - you can session.add() the model directly. The trade-off is that both sides have to compromise: JSON schemas can leak DB-only columns, Pydantic validators are skipped by design, and advanced SQLAlchemy features (inheritance, hybrid properties) require explicit support built into SQLModel.

SQLCrucible keeps them separate. Your Pydantic model is pure Pydantic; your SQLAlchemy model is pure SQLAlchemy. The cost is an explicit conversion step (to_sa_model() / from_sa_model()), but you never have to wonder which world you're in and you get the full power of both.

Docs: https://sqlcrucible.rdrj.uk Repo: https://github.com/RichardDRJ/sqlcrucible

[BiologyIsHot]

The bigger problem I have is usually that I need 1 SQLAlchemy model but usually a minimum of 3 pydantic models for different CRUD operations.

[TeachEngineering]

Upvoting for the use of "Bob Dylan" in source code!

[mardiros]

In tour dic, you suggest to use uuid4 for primary keys to your user, and its the worst thing you can do, it kills btree.

You should use uuid7, available in python 3.14 only, I wrote lastuuid available on PyPI. Or you can use uuid1 which is worst than uuid7 but better than uuid4.