SQLAthanor and Pydantic¶
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | # 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | 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.
Caution
This functionality does not support more complex table structures, including relationships, hybrid properties, or association proxies.
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.
See also
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.