Integrating Dataclasses with SQLAlchemy Tables

One of the biggest pain points for many SQLAlchemy users is the fact that it might not be immediately obvious how to integrate it with existing software. SQLAlchemy table objects might not be the perfect entities to operate on—they unfortunately don't play well with the Python type system nor with IDEs that depend on it.

Thankfully, there is a native option that might be beneficial to many Python developers who like to keep their code well structured, and that is the SQLAlchemy integration with dataclasses.

About @dataclasses

Dataclasses are a Python feature available since Python 3.7. Their goal is to allow developers to create classes that, as their name suggests, primarily store data. They do this by automatically generating special methods like __init__, __repr__, and __eq__ on the class you define and mark as a dataclass using the @dataclass decorator. This reduces boilerplate code when creating data container classes.

Creating the Integration

There are multiple ways of creating the dataclasses integration with SQLAlchemy, each one has its faults and its merits.

The ways are as follows:

• Create a class that inherits from DeclarativeBase and MappedAsDataclass
• You define classes that inherit from your Base class and MappedAsDataclass
• You use the registry object that exposes a decorator registry.mapped_as_dataclass

Inheritance Based Integration

The inheritance based dataclass mapping might be the simplest if you are working with only declarative tables. An example of this would be:

from sqlalchemy import String, Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, MappedAsDataclass
from typing import Optional

# Define our base class
class Base(DeclarativeBase):
  pass

# Define our SQLAlchemy model using MappedAsDataclass
class Alchemist(MappedAsDataclass, Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True, init=False)
  name: Mapped[str] = mapped_column(String(50))
  nickname: Mapped[Optional[str]] = mapped_column(String(100))
  age: Mapped[Optional[int]]

The MappedAsDataclass mixin provides several standard dataclass features:

• An __init__ method that initializes attributes based on parameters
• A __repr__ method that shows a nice string representation of the object
• An __eq__ method that compares objects based on their attribute values
• Proper handling of default values for attributes

This approach is clean and straightforward, without requiring any additional decorators.

You can also inherit the mixin in your Base class instead like so:

from sqlalchemy.orm import DeclarativeBase, MappedAsDataclass

# Define our base class
class Base(MappedAsDataclass, DeclarativeBase):
  pass

Decorator Based Integration

The decorator-based integration of dataclasses (using @registry.mapped_as_dataclass) is an alternative approach in SQLAlchemy that leverages the registry object. While still supported in SQLAlchemy 2.0, it's generally not the preferred method compared to using the MappedAsDataclass mixin.

To use this decorator we do the following:

from sqlalchemy import String, Integer
from sqlalchemy.orm import registry, Mapped, mapped_column
from dataclasses import dataclass
from typing import Optional

reg = registry()

@reg.mapped_as_dataclass
class Alchemist:
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True, init=False)
  name: Mapped[str] = mapped_column(String(50))
  nickname: Mapped[Optional[str]] = mapped_column(String(100))
  age: Mapped[Optional[int]]

The registry class also includes another method called registry.mapped which is another decorator which when used, provides declarative mapping to a class without the use of a declarative base class.

Which is why if we look at our new Alchemist table, we can see that our Base class is absent, and also we do not use the DeclarativeBase class, since the registry.mapped_as_dataclass decorator provides us with both the registry.mapped functionality as well as the dataclass mapping.

Attribute Configuration

As you can see in both of our examples, we set the init property in the mapped_column for the id fields to False, this indicates that when creating an instance of this model, we are not allowed to pass an id to the model (as it will be generated automatically once we push it to the DB).

This is a part of the attribute configuration of the dataclass integration with SQLAlchemy models.

SQLAlchemy native dataclasses differ from regular pythonic dataclasses in that the attributes are defined using the Mapped annotation in all cases.

Another difference is that for each field that is not required in the dataclass, you must set a default attribute, like so:

from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import registry

reg = registry()

@reg.mapped_as_dataclass
class Witch:
  __tablename__ = "witches"
  
  id: Mapped[int] = mapped_column(init=False, primary_key=True)
  name: Mapped[str]
  # Because of the default assignment, fullname is Optional
  fullname: Mapped[str] = mapped_column(default=None)

witch1 = Witch("Martha")

Inserting a Default Value

Since the Column object (generated by mapped_column) already contains a "default" attribute, which will be used when creating an instance of the class, there is another attribute called insert_default which will be used when inserting data into the database, and will override the default attribute regardless of what was there. For example, let's extend our last code example with another column:

from datetime import datetime
from sqlalchemy import func
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import registry

reg = registry()

@reg.mapped_as_dataclass
class Witch:
  __tablename__ = "witches"
  
  id: Mapped[int] = mapped_column(init=False, primary_key=True)
  name: Mapped[str]
  # Because of the default assignment, fullname is Optional
  fullname: Mapped[str] = mapped_column(default=None)
  created_at: Mapped[datetime] = mapped_column(
      insert_default=func.utc_timestamp(), default=None
  )

witch2 = Witch("Sally")

In this example we've introduced two more things:

• insert_default mapped_column attribute, which is very helpful if you need to have columns whose data can only be populated upon insert.
• the func object, which provides access to database functions (both internal and user-defined)
• The func.utc_timestamp() function will be fired on the database side when we insert our columns, note that the transaction does not have to be committed to access the inserted value.

Relationship Configuration

When working with relationships in our dataclass models, the Mapped annotation combined with relationship() functions just like we saw in our basic relationship patterns. The key difference is how we handle default values for these relationships.

For collection-based relationships (like one-to-many), we must provide the relationship.default_factory parameter, which should point to the collection class we want to use. For many-to-one and scalar relationships, we can use relationship.default when we want None as the default value:

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

class Base(DeclarativeBase):
  pass

class Alchemist(MappedAsDataclass, Base):
  __tablename__ = "alchemists"
  
  id: Mapped[int] = mapped_column(primary_key=True, init=False)
  name: Mapped[str] = mapped_column(String(50))
  
  # One-to-many relationship with default empty list
  potions: Mapped[List["Potion"]] = relationship(
      default_factory=list, back_populates="alchemist", init=False
  )

class Potion(MappedAsDataclass, Base):
  __tablename__ = "potions"
  
  id: Mapped[int] = mapped_column(primary_key=True, init=False)
  name: Mapped[str] = mapped_column(String(50))
  alchemist_id: Mapped[Optional[int]] = mapped_column(ForeignKey("alchemists.id"), default=None)
  
  # Many-to-one relationship with default None
  alchemist: Mapped[Optional["Alchemist"]] = relationship(default=None, back_populates="potions")

With this setup, when we create a new Alchemist without providing any potions, they'll automatically get an empty list for potions. Similarly, when we create a new Potion without specifying an alchemist, the alchemist attribute will be None.