Skip to main content

CRUD operations on nested SQLAlchemy ORM-models using Pydantic

Project description


This library makes it a lot easier to do nested database operation with SQLAlchemy. With this library it is for example possible to validate, convert, and upload a 100-level deep nested JSON (dict) to its corresponding tables in a given database, within 3 lines of code.

Pydantic is used for creating the dataclass and validating it. Pydantic already has a function called .from_orm() that can do a nested get operation, but it only supports ORM -> Pydantic and not Pydantic -> ORM. That's exactly where this library fills in, with 2 specific functions .orm_create() and .orm_update(), and one general function .to_orm() that combines the functionality of the first 2, calling one or the other, depending on if there is an id provided.


  • Python 3.8+
  • SQLAlchemy 1.4+
  • Pydantic 1.8+


$ pip install sqlalchemy-pydantic-orm

To tinker with the code yourself, install the full dependencies with:

$ pip install sqlalchemy-pydantic-orm[dev]

Useful references


Below 1 example is provided (more coming).

For a bigger and more detailed example you can look at the /examples/ folder.

Example 1 - Using manual created schemas

Create your own Pydantic schemas and link them to the SQLAlchemy ORM-models.

Create your SQLAlchemy ORM-models (one-to-one or one-to-many)

class Parent(Base):
    id = Column(Integer, primary_key=True, index=True, nullable=False)
    name = Column(String, nullable=False)
    car = relationship("Car", cascade="all, delete", uselist=False, back_populates="owner")
    children = relationship("Child", cascade="all, delete")

class Car(Base):
    id = Column(Integer, primary_key=True, index=True, nullable=False)
    color = Column(String, nullable=False)
    owner_id = Column(Integer, ForeignKey(""), nullable=False)
    owner = relationship("Parent", back_populates="car")

class Child(Base):
    id = Column(Integer, primary_key=True, index=True, nullable=False)
    name = Column(String, nullable=False)
    parent_id = Column(Integer, ForeignKey(""), nullable=False)

Create your Pydantic base and CRUD schemas using these ORM models, and the imported ORMBaseSchema

Base schemas

from sqlalchemy_pydantic_orm import ORMBaseSchema
from .models import Parent, Car, Child

class ParentBase(ORMBaseSchema):
    name: str
    _orm_model = PrivateAttr(Parent)

class CarBase(ORMBaseSchema):
    color: str
    _orm_model = PrivateAttr(models.Car)

class ChildBase(ORMBaseSchema):
    name: str
    _orm_model = PrivateAttr(models.Child)

GET schemas

class Parent(ParentBase):
    id: int
    children: List[Child]
    car: Car

class Car(CarBase):
    id: int

class Child(ChildBase):
    id: int


class ParentCreate(ParentBase):
    id: Optional[int]
    children: List[ChildCreate]
    car: CarCreate

class CarCreate(CarBase):
    id: Optional[int]

class ChildCreate(ChildBase):
    id: Optional[int]

Use your schemas to do nested CRUD operations.

with ConnectionDatabase() as db:
    create_schema = schemas.ParentCreate.parse_obj(create_dict)
    parent_db = create_schema.orm_create()

    db_create_schema = schemas.Parent.from_orm(parent_db)

    update_schema = schemas.ParentUpdate.parse_obj(update_dict)

    db_update_schema = schemas.Parent.orm_update(parent_db)

Note: with .orm_create() you have to call db.add() before calling db.commit(). With orm_update you give the db session as parameter, and you only have to call db.commit().

Example 2 - Using generated schemas

TODO: Integrate with

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqlalchemy-pydantic-orm-0.1.0.tar.gz (10.9 kB view hashes)

Uploaded Source

Built Distribution

sqlalchemy_pydantic_orm-0.1.0-py3-none-any.whl (10.4 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page