I’m a big fan of Pydantic. Its authors have made different choices, but that doesn’t diminish my respect for their work, and that’s one of the main reasons why I have decided to extend SQLAthanor with built-in support for Pydantic.

My interpretation of the priorities below is my subjective evaluation of the library and its API semantics, and from my experience using the library in a number of web application projects of varying complexity. These observations are not meant to be a criticism: They are merely my thoughts on where the libraries have taken different philosophical and stylistic paths.

SQLAthanor and Pydantic¶

SQLAthanor, Pydantic, and FastAPI
Generating and Configuring Model Classes Using Pydantic
Generating Tables from Pydantic Models
Configuring Attributes from Pydantic Models

New in version 0.8.0.

SQLAthanor, Pydantic, and FastAPI ¶

SQLAthanor and Pydantic are both concerned with the serialization and deserialization of data. However, they approach the topic from different angles and have made a variety of (very different) architectural and stylistic choices.

To be clear, neither set of choices is “better” or “worse”, but they do reflect the authors’ different priorities and stylistic preferences:

SQLAthanor Priorities	Pydantic Priorities
Database/ORM compatibility with SQLAlchemy	Database/ORM agnosticism
The maintenance of a single representation of your data model, tied to its database implementation	Multiple representations of your data model, each of which is tied to its usage in your code
Explicit reference and conceptual documentation	Documentation by example / in code
Explicit APIs for the data model’s lifecycle	Implicit APIs relying on the Python standard library

Both libraries have their place: in general terms, if I were working on a simple web application, on a microservice, or on a relatively simple data model I would consider Pydantic as a perfectly viable “quick-and-dirty” option. Its use of Python’s native typing hints/annotation is a beautifully elegant solution.

However, if I need to build a robust API with complex data model representations, tables with multiple relationships, or complicated business logic? Then I would prefer the robust and extensible capabilities afforded by the SQLAlchemy Delarative ORM and the SQLAthanor library.

If that were it, I would consider Pydantic to be equivalent to Marshmallow and Colander: an interesting tool for serialization/deserialization, and one that has its place, but not one that SQLAthanor need be concerned with.

But there’s one major difference: FastAPI.

FastAPI is an amazing microframework, and is rapidly rising in popularity across the Python ecosystem. That’s for very good reason: It is blisteringly fast, its API is relatively simple, and it has the ability to automatically generate OpenAPI/Swagger schemas of your API endpoints. What’s not to love?

Well, its tight coupling with Pydantic, for one thing. When building an application using the FastAPI framework, I am practically forced to use Pydantic models as my API inputs, outputs, and validators. If I choose not to use Pydantic models, then I lose many of the valuable features (besides performance) which make FastAPI so attractive for writing API applications.

But using FastAPI and Pydantic in a complex API application may require a lot of “extra” code: the repetition of object models, the replication of business logic, the duplication of context, etc. All of these are concerns that SQLAthanor was explicitly designed to minimize.

So what to do? Most patterns, documentation, and best practices found on the internet for authoring FastAPI applications explicitly suggest that you (manually, in your code):

Create a SQLAlchemy model class for the database interface for each data model

Create one Pydantic model class for each “version” of your data model’s output/input. So if you need one read version and a different write version? You need two Pydantic models.

Use your Pydantic models as the validators for your API endpoints, as needed.

This is all fine and dandy, but now what happens if you need to add an attribute to your data model? You have to make a change to your SQLAlchemy model class, and to one or more Pydantic models, and possibly to your API endpoints. And let’s not get started on changes to your data model’s underlying business logic!

There has to be a better way.

Which is why I added Pydantic support to SQLAthanor. With this added support, you can effectively use your Pydantic models as the “canonical definition” of your data model. Think of the lifecycle this way:

You define your data model in one or more Pydantic models.

You programmatically create a SQLAlchemy model class whose columns are automatically derived from the underlying Pydantic models and for whom each Pydantic Model serves as a serialization/deserialization configuration set.

Thus, you remove one of the (more complicated) steps in the process of writing your FastAPI application. Now all you have to do is create your Pydantic models, and then generate your SQLAthanor model classes. Your FastAPI can still validate based on your Pydantic models, even if you choose to drive serialization/deserialization from your SQLAlchemy model classes.

In other words: It saves you code! And maintenance!

Just look at the example below. Not only does it save you a couple of lines, but most importantly when in the future you need to modify your data model (and let’s face it, that is one of the most common modifications in real applications) you make your changes in one place rather than two:

# THIS CODE SNIPPET HAS BEEN ADAPTED FROM THE OFFICIAL FASTAPI DOCUMENTATION:
# https://fastapi.tiangolo.com/tutorial/sql-databases/

# Assumes that there is a "database" module that defines your SQLAlchemy BaseModel.
from typing import List, Optional
from pydantic import BaseModel

from .database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    items = relationship("Item", back_populates="owner")

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    owner = relationship("User", back_populates="items")

class ItemBase(BaseModel):
    title: str
    description: Optional[str] = None

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True

class UserBase(BaseModel):
    email: str

class UserCreate(UserBase):
    password: str

class User(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

from typing import List, Optional
from pydantic import BaseModel as PydanticBase

from sqlathanor import BaseModel, Column, generate_model_from_pydantic
from sqlalchemy.types import String

class ItemBase(PydanticBase):
    title: str
    description: Optional[str] = None

class ItemCreate(ItemBase):
    pass

class ItemRead(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True

class UserBase(PydanticBase):
    email: str

class UserCreate(UserBase):
    password: str

class UserRead(UserBase):
    id: int
    is_active: bool
    items: List[Item] = []

    class Config:
        orm_mode = True

User = generate_model_from_pydantic({ 'create': UserCreate
                                      'read': UserRead },
                                    tablename = 'users',
                                    primary_key = 'id')


Item = generate_model_from_pydantic({ 'create': ItemCreate,
                                      'read': ItemRead },
                                    tablename = 'items',
                                    primary_key = 'id')

Item.owner = relationship("User", back_populates="items")
User.items = relationship("Item", back_populates="owner")
User.hashed_password = Column(String,
                              supports_csv = False,
                              supports_json = False,
                              supports_yaml = False,
                              supports_dict = False)

Generating and Configuring Model Classes Using Pydantic ¶

As SQLAthanor relies on the creation of model classes which both define your database representation and provide serialization/deserialization configuration instructions, the first step to using Pydantic with SQLAthanor is to generate your model classes based on your Pydantic models.

You can do this in SQLAthanor using the generate_model_from_pydantic() function. This function takes your Pydantic models as an input, and creates a SQLAthanor model class (which is a subclass of sqlathanor.declarative.BaseModel).

When generating your model classes from Pydantic models, you can supply multiple models which will then get consolidated into a single SQLAthanor BaseModel. For example:

This example shows how you would generate a single sqlathanor.BaseModel from a single pydantic.BaseModel. Since it only has one model, it would have only one serialization/deserialization configuration set by default:

from pydantic import BaseModel as PydanticBaseModel
from sqlathanor import generate_model_from_pydantic

class SinglePydanticModel(PydanticBaseModel):
    id: int
    username: str
    email: str

SingleSQLAthanorModel = generate_model_from_pydantic(SinglePydanticModel,
                                                     tablename = 'my_tablename',
                                                     primary_key = 'id')

This code will generate a single SQLAthanor model class named SingleSQLAthanorModel, which will contain three columns: id, username, and email. The column types will be set to correspond to the data types annotated in the SinglePydanticModel class definition.

This example shows how you would combine multiple Pydantic models into a single sqlathanor.BaseModel. A typical use case would be if one Pydantic model represents the output when you are retrieving/viewing a user’s data (which does not have a password field for security reasons) and hte other Pydantic model represents the input when you are writing/creating a new user (which does need the password field).

Note

Because both Pydantic models are passed to the function in a single list, they will receive a single SQLAthanor configuration set.

from pydantic import BaseModel as PydanticBaseModel
from sqlathanor import generate_model_from_pydantic

class ReadUserModel(PydanticBaseModel):
    id: int
    username: str
    email: str

class WriteUserModel(ReadUserModel):
    password: str

SingleSQLAthanorModel = generate_model_from_pydantic([ReadUserModel,
                                                      WriteUserModel],
                                                     tablename = 'my_tablename',
                                                     primary_key = 'id')

This code will generate a single SQLAthanor model class named SingleSQLAthanorModel with four columns (id, username, email, and password). However, because all models were passed in as a single list, the columns will be consolidated with only one configuration set.

Caution

In my experience, it is very rare that you would want to consolidate multiple Pydantic models with only one configuration set. Most of the type, each Pydantic model will actually represent its own configuration set as documented in the next example.

This example shows how you would combine multiple Pydantic models into a single sqlathanor.BaseModel, but configure multiple serialization/deserialization configuration sets based on those Pydantic models.

This is the most-common use case, and is fairly practical. To define multiple configuration sets, simply pass the Pydantic models as key/value pairs in the first argument:

from pydantic import BaseModel as PydanticBaseModel
from sqlathanor import generate_model_from_pydantic

class ReadUserModel(PydanticBaseModel):
    id: int
    username: str
    email: str

class WriteUserModel(ReadUserModel):
    password: str

SQLAthanorModel = generate_model_from_pydantic({ 'read': ReadUserModel,
                                                 'write': WriteUserModel
                                               },
                                               tablename = 'my_tablename',
                                               primary_key = 'id')

This code will generate a single SQLAthanor model class (SQLAthanorModel, with four columns - id, username, email, and password), but that model class will have two configuration sets: read which will serialize/de-serialize only three columns (id, username, and email) and write which will serialize/de-serialize four columns (id, username, email, and password).

This SQLAthanorModel then becomes useful when serializing your model instances to dict or de-serializing them from dict using the context-appropriate configuration set:

# Assumes that "as_dict" contains a string JSON representation with attributes as
# defined in your "WriteUserModel" Pydantic model.
model_instance = SQLAthanorModel.new_from_json(as_json, config_set = 'write')

# Produces a dict representation of the object with three attributes, corresponding
# to your "ReadUserModel" Pydantic model.
readable_as_dict = model_instance.to_dict(config_set = 'read')

Tip

When generating your SQLAthanor model classes from your Pydantic models, it is important to remember that serialization and de-serialization is disabled by default for security reasons. Therefore a best practice is to enable/disable your serialization and de-serialization at runtime.

Generating Tables from Pydantic Models ¶

Just as you can generate SQLAthanor model classes from Pydantic models, you can also create Table objects from Pydantic models, consolidating their attributes into standard SQL Column definitions.

from pydantic import BaseModel
from sqlathanor import Table

# Define Your Pydantic Models
class UserWriteModel(BaseModel):
    id: int
    username: str
    email: str
    password: str

class UserReadModel(BaseModel):
    id: int
    username: str
    email: str

# Create Your Table
pydantic_table = Table.from_pydantic([UserWriteModel, UserReadModel],
                                     tablename = 'my_tablename_goes_here',
                                     primary_key = 'id')

This code will generate a single Table instance (pydantic_table) which will have four columns: id, username, email, and password. Their column types will correspond to the type hints defined in the Pydantic models.

Configuring Attributes from Pydantic Models ¶

There may be times when you wish to configure the serialization / de-serialization of model class attributes based on a related Pydantic model. You can programmatically create a new AttributeConfiguration instance from a Pydantic model by calling the AttributeConfiguration.from_pydantic_model() class method:

from pydantic import BaseModel
from sqlathanor import Table

# Define Your Pydantic Models
class UserWriteModel(BaseModel):
    id: int
    username: str
    email: str
    password: str

class UserReadModel(BaseModel):
    id: int
    username: str
    email: str

password_config = AttributeConfiguration.from_pydantic_model(UserWriteModel,
                                                             field = 'password',
                                                             supports_csv = (True, False),
                                                             supports_json = (True, False),
                                                             supports_yaml = (True, False),
                                                             supports_dict = (True, False),
                                                             on_deserialize = my_encryption_function)

This code will produce a single AttributeConfiguration instance named password_config. It will support the de-serialization of data, but will never be serialized (a typical pattern for password fields!). Furthermore, it will execute the my_encryption_function during the de-serialization process.

A very common use case is to configure the serialization/de-serialization profile for attributes that were programmatically derived from Pydantic models.

SQLAthanor and Pydantic¶

SQLAthanor, Pydantic, and FastAPI¶

Generating and Configuring Model Classes Using Pydantic¶

Generating Tables from Pydantic Models¶

Configuring Attributes from Pydantic Models¶

SQLAthanor, Pydantic, and FastAPI ¶

Generating and Configuring Model Classes Using Pydantic ¶

Generating Tables from Pydantic Models ¶

Configuring Attributes from Pydantic Models ¶