This blog was last modified 580 days before.

ORM Introduction

In Python and Database, ORM (Object-Relational Mapping) is a technology to map the database field and relationship to a Python class.

For example, a user table in a database may be mapped into a Python class like below:

class User:
    user_id: int # primary key of this table
    username: str
    description: str
    email: list[str] # user could has more than one email bounded to this account
    password: str

Notice: The class above is just an example for quick understanding of the concept of ORM, and not representing how we actually create ORM class with SQLAlchemy.

ORM with SQLAlchemy

In SQLAlchemy, the way we create an ORM relationship is similar to create a class. It used a pattern called Declarative Mapping, see code example below:

from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

There are several things to notice.

Base class

Notice that in code snippet above, we do NOT directly inherit all our ORM class from DeclarativeBase class in SQLAlchemy. We first created a Base class inherit from DeclarativeBase, then all other ORM class is the sub-class of this our self-created Base class.

The reason for creating Base class is to help SQLAlchemy to manage the metadata of different ORM class (also could be considered as different table) in a unified way. Use those metadata, we can do some amazing things like auto-create tables based on the ORM class structure etc.

__tablename__ class field

The __tablename__ member exist in all sub-class of DeclarativeBase, which tells SQLAlchemy the table name of this ORM class refers to. Also, this tablename data will be used when you use SQLAlchemy to auto create the table in database.

mapped_column() & Type Anno & primary_key param

mapped_column() is the recommend method in SQLAlchemy 2.0 to create an relationship between ORM class field and database table field. In mapped_column, we can set several properties of this field.

And usually, mapped_column should be used with Type Annotation in Python. SQLAlchemy provides us a Mapped class to annotate the type of the ORM class member field.

Since the class is a mapping of a table in database, an ORM class must have a primary_key. Which we can declare using mapped_column(..., primary_key=True)

Here we introduce some usually-used mapped_column() params:

• *args positional arguments, you could pass some constructs like ForeignKey, Identity etc. These constructs will be passed to the Column object.
• primary_key bool, if this column is considered a primary column.
• autoincrement bool, usually used with primary_key param.
• comment The comment of this column in the table. used by table auto-creating feature.

Notice: There has an advanced pattern of Type Annotation with SQLAlchemy, which allow us to use Annotated to define our own pre-established SQLAlchemy Type Annotation. We would introduce in next part.

Override __repr__

Usually, when we create an ORM class, it's recommend to override the __repr__ magic method of that ORM class, so that we can use print(user1, user2) to quickly check the info of a particular ORM class instance (also could be consider a single row in the database)

Side Note: About Primary Key

On a side note, when constructing the database structure, it's NOT recommend that associating the primary key with any business logic. Primary key should be a column that is increasing and unique among this table with no any other meanings.

In SQLAlchemy, we can use code below:

class ORMClass(Base):
    id_: Mapped[int] = mapped_column(primary_key=True, 
                                     autoincrement=True
                                     )

Tricks / Tips when creating ORM class

DataClass

dataclasses is a new module first appeared in Python 3.7, which could decorate a Python class, and automatically add some special method to the decorated class such as __init__ and __repr__. Below is a code example:

from dataclasses import dataclass

@dataclass
class InventoryItem:
    """Class for keeping track of an item in inventory."""
    name: str
    unit_price: float
    quantity_on_hand: int = 0

    def total_cost(self) -> float:
        return self.unit_price * self.quantity_on_hand

By decorating the class with @dataclass, an auto-generated __init__() method is like below:

def __init__(self, name: str, unit_price: float, quantity_on_hand: int = 0):
    self.name = name
    self.unit_price = unit_price
    self.quantity_on_hand = quantity_on_hand

As the code above, all member field is added to the __init__() method with same order as they were declared.

dataclass decorator can not only generate init method, but also a lot of other useful and basic method, we can enable or disable them manually by passing different params to the decorator. For example, if you want it to generate __eq__() method, just write @dataclass(eq=True)

To learn more about dataclasses, check Python Docs - DataClasses

Some early SQLAlchemy version does NOT support dataclass with Declarative Mapping pattern. Here we recommend to use SQLAlchemy with version not less than 2.0, which's ORM class could be used with dataclass

Advanced Type Anno with Annotated

We can use Annotation in Python typing_extensions packages to define our own type for SQLAlchemy mapping. With this method, we can not only contains the basic type info such as int or str, but also can define some database params such as primary_key. Please check code example below:

import datetime

from typing_extensions import Annotated

from sqlalchemy import func
from sqlalchemy import String
from sqlalchemy.orm import mapped_column

# create our pre-established annotation type using Annotated

intpk = Annotated[int, mapped_column(primary_key=True)]
timestamp = Annotated[
    datetime.datetime,
    mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),
]
required_name = Annotated[str, mapped_column(String(30), nullable=False)]

# use our own annotation below

class Base(DeclarativeBase):
    pass


class SomeClass(Base):
    __tablename__ = "some_table"

    id: Mapped[intpk]
    name: Mapped[required_name]
    created_at: Mapped[timestamp]

In this way, we can encapsulate the commonly-used type into a single unified Annotated type, then reuse it in different places.

Override Rules About Type Anno

Notice that we can still use normal type anno together with our own type, look example below:

class SomeORMClass(Base):
    id: Mapped[intpk] = mapped_column(ForeignKey(...))
    create_at: Mapped[Optional[timestamp]]

For mapped_column()

The mapped column in class declaration will takes precendence over the one in our own Annotated type, and if the params are not conflict, they will merge together.

In the code example above, the id member field will be primary key, and also has a foreign key relationship.

For Optional[]

Like the code above, if we nested our own type in an Optional[] while our own type has nullable=False param in mapped_column(), that means, our type anno in class field declaration and our own pre-established type anno has a conflict.

In this case, the database field schema will still follow the settings in our type mapped_column() which is NOT nullable. However the Optional[] will still takes effect in Python scale, which means, in Python type annotation system, this create_at will be considered nullable and could be None

Here we recommend that if you know a field should be nullable in database, then explicitly declare nullable=True in mapped_column() when declaring the member field or when creating your pre-established type anno.

Also, start from 2.0.0, SQLAlchemy gradually add support to Python enum.Enum type in ORM classes, check OyaCode - SQLAlchemy Enum Type for more info.

Re-map Type relationship

We have introduced the type of a column is determined by the type anno of this member field in ORM class, for example, int will be mapped to an INT column in the database table.

To re-map this relationship, we could override type_annotation_map in Base class like below:

class Base(DeclarativeBase):
    type_annotation_map = {
        int: BIGINT,
        datetime.datetime: TIMESTAMP(timezone=True),
        str: String().with_variant(NVARCHAR, "mssql"),
    }

Auto create table in database

As talked above, we use a overall Base class, just to prepare for the auto-create feature.

To use auto-create table feature, we must already create the engine and all the ORM classes we need, then we can use metadata of Base class to create tables like below:

Base.metadata.create_all(engine)

Similar to auto-create, you could also drop all classes in the ORM relationship like below:

Base.metadata.drop_all(engine)