Integration with dataclasses and attrs

SQLAlchemy as of version 2.0 features “native dataclass” integration where an Annotated Declarative Table mapping may be turned into a Python dataclass by adding a single mixin or decorator to mapped classes.

New in version 2.0: Integrated dataclass creation with ORM Declarative classes

There are also patterns available that allow existing dataclasses to be mapped, as well as to map classes instrumented by the attrs third party integration library.

Declarative Dataclass Mapping

SQLAlchemy Annotated Declarative Table mappings may be augmented with an additional mixin class or decorator directive, which will add an additional step to the Declarative process after the mapping is complete that will convert the mapped class in-place into a Python dataclass, before completing the mapping process which applies ORM-specific instrumentation to the class. The most prominent behavioral addition this provides is generation of an __init__() method with fine-grained control over positional and keyword arguments with or without defaults, as well as generation of methods like __repr__() and __eq__().

From a PEP 484 typing perspective, the class is recognized as having Dataclass-specific behaviors, most notably by taking advantage of PEP 681 “Dataclass Transforms”, which allows typing tools to consider the class as though it were explicitly decorated using the @dataclasses.dataclass decorator.

Note

Support for PEP 681 in typing tools as of July 3, 2022 is limited and is currently known to be supported by Pyright, but not yet Mypy. When PEP 681 is not supported, typing tools will see the __init__() constructor provided by the DeclarativeBase superclass, if used, else will see the constructor as untyped.

Dataclass conversion may be added to any Declarative class either by adding the MappedAsDataclass mixin to a DeclarativeBase class hierarchy, or for decorator mapping by using the registry.mapped_as_dataclass() class decorator.

The MappedAsDataclass mixin may be applied either to the Declarative Base class or any superclass, as in the example below:

  1. from sqlalchemy.orm import DeclarativeBase
  2. from sqlalchemy.orm import Mapped
  3. from sqlalchemy.orm import mapped_column
  4. from sqlalchemy.orm import MappedAsDataclass
  7. class Base(MappedAsDataclass, DeclarativeBase):
  8.     """subclasses will be converted to dataclasses"""
  11. class User(Base):
  12.     __tablename__ = "user_account"
  14.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  15.     name: Mapped[str]

Or may be applied directly to classes that extend from the Declarative base:

  1. from sqlalchemy.orm import DeclarativeBase
  2. from sqlalchemy.orm import Mapped
  3. from sqlalchemy.orm import mapped_column
  4. from sqlalchemy.orm import MappedAsDataclass
  7. class Base(DeclarativeBase):
  8.     pass
  11. class User(MappedAsDataclass, Base):
  12.     """User class will be converted to a dataclass"""
  14.     __tablename__ = "user_account"
  16.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  17.     name: Mapped[str]

When using the decorator form, only the registry.mapped_as_dataclass() decorator is supported:

  1. from sqlalchemy.orm import Mapped
  2. from sqlalchemy.orm import mapped_column
  3. from sqlalchemy.orm import registry
  6. reg = registry()
  9. @reg.mapped_as_dataclass
  10. class User:
  11.     __tablename__ = "user_account"
  13.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  14.     name: Mapped[str]

Class level feature configuration

Support for dataclasses features is partial. Currently supported are the init, repr, eq, order and unsafe_hash features, match_args and kw_only are supported on Python 3.10+. Currently not supported are the frozen and slots features.

When using the mixin class form with MappedAsDataclass, class configuration arguments are passed as class-level parameters:

  1. from sqlalchemy.orm import DeclarativeBase
  2. from sqlalchemy.orm import Mapped
  3. from sqlalchemy.orm import mapped_column
  4. from sqlalchemy.orm import MappedAsDataclass
  7. class Base(DeclarativeBase):
  8.     pass
  11. class User(MappedAsDataclass, Base, repr=False, unsafe_hash=True):
  12.     """User class will be converted to a dataclass"""
  14.     __tablename__ = "user_account"
  16.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  17.     name: Mapped[str]

When using the decorator form with registry.mapped_as_dataclass(), class configuration arguments are passed to the decorator directly:

  1. from sqlalchemy.orm import registry
  2. from sqlalchemy.orm import Mapped
  3. from sqlalchemy.orm import mapped_column
  6. reg = registry()
  9. @reg.mapped_as_dataclass(unsafe_hash=True)
  10. class User:
  11.     """User class will be converted to a dataclass"""
  13.     __tablename__ = "user_account"
  15.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  16.     name: Mapped[str]

For background on dataclass class options, see the dataclasses documentation at @dataclasses.dataclass.

Attribute Configuration

SQLAlchemy native dataclasses differ from normal dataclasses in that attributes to be mapped are described using the Mapped generic annotation container in all cases. Mappings follow the same forms as those documented at Declarative Table with mapped_column(), and all features of mapped_column() and Mapped are supported.

Additionally, ORM attribute configuration constructs including mapped_column(), relationship() and composite() support per-attribute field options, including init, default, default_factory and repr. The names of these arguments is fixed as specified in PEP 681. Functionality is equivalent to dataclasses:

Another key difference from dataclasses is that default values for attributes must be configured using the default parameter of the ORM construct, such as mapped_column(default=None). A syntax that resembles dataclass syntax which accepts simple Python values as defaults without using @dataclases.field() is not supported.

As an example using mapped_column(), the mapping below will produce an __init__() method that accepts only the fields name and fullname, where name is required and may be passed positionally, and fullname is optional. The id field, which we expect to be database-generated, is not part of the constructor at all:

  1. from sqlalchemy.orm import Mapped
  2. from sqlalchemy.orm import mapped_column
  3. from sqlalchemy.orm import registry
  5. reg = registry()
  8. @reg.mapped_as_dataclass
  9. class User:
  10.     __tablename__ = "user_account"
  12.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  13.     name: Mapped[str]
  14.     fullname: Mapped[str] = mapped_column(default=None)
  17. # 'fullname' is optional keyword argument
  18. u1 = User("name")

Column Defaults

In order to accommodate the name overlap of the default argument with the existing Column.default parameter of the Column construct, the mapped_column() construct disambiguates the two names by adding a new parameter mapped_column.insert_default, which will be populated directly into the Column.default parameter of Column, independently of what may be set on mapped_column.default, which is always used for the dataclasses configuration. For example, to configure a datetime column with a Column.default set to the func.utc_timestamp() SQL function, but where the parameter is optional in the constructor:

  1. from datetime import datetime
  3. from sqlalchemy import func
  4. from sqlalchemy.orm import Mapped
  5. from sqlalchemy.orm import mapped_column
  6. from sqlalchemy.orm import registry
  8. reg = registry()
  11. @reg.mapped_as_dataclass
  12. class User:
  13.     __tablename__ = "user_account"
  15.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  16.     created_at: Mapped[datetime] = mapped_column(
  17.         insert_default=func.utc_timestamp(), default=None
  18.     )

With the above mapping, an INSERT for a new User object where no parameter for created_at were passed proceeds as:

  1. >>> with Session(e) as session:
  2. ...     session.add(User())
  3. ...     session.commit()
  4. BEGIN (implicit)
  5. INSERT INTO user_account (created_at) VALUES (utc_timestamp())
  6. [generated in 0.00010s] ()
  7. COMMIT

Integration with Annotated

The approach introduced at Mapping Whole Column Declarations to Python Types illustrates how to use PEP 593 Annotated objects to package whole mapped_column() constructs for re-use. This feature is supported with the dataclasses feature. One aspect of the feature however requires a workaround when working with typing tools, which is that the PEP 681-specific arguments init, default, repr, and default_factory must be on the right hand side, packaged into an explicit mapped_column() construct, in order for the typing tool to interpret the attribute correctly. As an example, the approach below will work perfectly fine at runtime, however typing tools will consider the User() construction to be invalid, as they do not see the init=False parameter present:

  1. from typing import Annotated
  3. from sqlalchemy.orm import Mapped
  4. from sqlalchemy.orm import mapped_column
  5. from sqlalchemy.orm import registry
  7. # typing tools will ignore init=False here
  8. intpk = Annotated[int, mapped_column(init=False, primary_key=True)]
  10. reg = registry()
  13. @reg.mapped_as_dataclass
  14. class User:
  15.     __tablename__ = "user_account"
  16.     id: Mapped[intpk]
  19. # typing error: Argument missing for parameter "id"
  20. u1 = User()

Instead, mapped_column() must be present on the right side as well with an explicit setting for mapped_column.init; the other arguments can remain within the Annotated construct:

  1. from typing import Annotated
  3. from sqlalchemy.orm import Mapped
  4. from sqlalchemy.orm import mapped_column
  5. from sqlalchemy.orm import registry
  7. intpk = Annotated[int, mapped_column(primary_key=True)]
  9. reg = registry()
  12. @reg.mapped_as_dataclass
  13. class User:
  14.     __tablename__ = "user_account"
  16.     # init=False and other pep-681 arguments must be inline
  17.     id: Mapped[intpk] = mapped_column(init=False)
  20. u1 = User()

Relationship Configuration

The Mapped annotation in combination with relationship() is used in the same way as described at Basic Relationship Patterns. When specifying a collection-based relationship() as an optional keyword argument, the relationship.default_factory parameter must be passed and it must refer to the collection class that’s to be used. Many-to-one and scalar object references may make use of relationship.default if the default value is to be None:

  1. from typing import List
  3. from sqlalchemy import ForeignKey
  4. from sqlalchemy.orm import Mapped
  5. from sqlalchemy.orm import mapped_column
  6. from sqlalchemy.orm import registry
  7. from sqlalchemy.orm import relationship
  9. reg = registry()
  12. @reg.mapped_as_dataclass
  13. class Parent:
  14.     __tablename__ = "parent"
  15.     id: Mapped[int] = mapped_column(primary_key=True)
  16.     children: Mapped[List["Child"]] = relationship(
  17.         default_factory=list, back_populates="parent"
  18.     )
  21. @reg.mapped_as_dataclass
  22. class Child:
  23.     __tablename__ = "child"
  24.     id: Mapped[int] = mapped_column(primary_key=True)
  25.     parent_id: Mapped[int] = mapped_column(ForeignKey("parent.id"))
  26.     parent: Mapped["Parent"] = relationship(default=None)

The above mapping will generate an empty list for Parent.children when a new Parent() object is constructed without passing children, and similarly a None value for Child.parent when a new Child() object is constructed without passsing parent.

While the relationship.default_factory can be automatically derived from the given collection class of the relationship() itself, this would break compatibility with dataclasses, as the presence of relationship.default_factory or relationship.default is what determines if the parameter is to be required or optional when rendered into the __init__() method.

Using Non-Mapped Dataclass Fields

When using Declarative dataclasses, non-mapped fields may be used on the class as well, which will be part of the dataclass construction process but will not be mapped. Any field that does not use Mapped will be ignored by the mapping process. In the example below, the fields ctrl_one and ctrl_two will be part of the instance-level state of the object, but will not be persisted by the ORM:

  1. from sqlalchemy.orm import Mapped
  2. from sqlalchemy.orm import mapped_column
  3. from sqlalchemy.orm import registry
  5. reg = registry()
  8. @reg.mapped_as_dataclass
  9. class Data:
  10.     __tablename__ = "data"
  12.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  13.     status: Mapped[str]
  15.     ctrl_one: Optional[str] = None
  16.     ctrl_two: Optional[str] = None

Instance of Data above can be created as:

  1. d1 = Data(status="s1", ctrl_one="ctrl1", ctrl_two="ctrl2")

A more real world example might be to make use of the Dataclasses InitVar feature in conjunction with the __post_init__() feature to receive init-only fields that can be used to compose persisted data. In the example below, the User class is declared using id, name and password_hash as mapped features, but makes use of init-only password and repeat_password fields to represent the user creation process (note: to run this example, replace the function your_crypt_function_here() with a third party crypt function, such as bcrypt or argon2-cffi):

  1. from dataclasses import InitVar
  2. from typing import Optional
  4. from sqlalchemy.orm import Mapped
  5. from sqlalchemy.orm import mapped_column
  6. from sqlalchemy.orm import registry
  8. reg = registry()
  11. @reg.mapped_as_dataclass
  12. class User:
  13.     __tablename__ = "user_account"
  15.     id: Mapped[int] = mapped_column(init=False, primary_key=True)
  16.     name: Mapped[str]
  18.     password: InitVar[str]
  19.     repeat_password: InitVar[str]
  21.     password_hash: Mapped[str] = mapped_column(init=False, nullable=False)
  23.     def __post_init__(self, password: str, repeat_password: str):
  24.         if password != repeat_password:
  25.             raise ValueError("passwords do not match")
  27.         self.password_hash = your_crypt_function_here(password)

The above object is created with parameters password and repeat_password, which are consumed up front so that the password_hash variable may be generated:

  1. >>> u1 = User(name="some_user", password="xyz", repeat_password="xyz")
  2. >>> u1.password_hash
  3. '$6$9ppc... (example crypted string....)'

Changed in version 2.0.0rc1: When using registry.mapped_as_dataclass() or MappedAsDataclass, fields that do not include the Mapped annotation may be included, which will be treated as part of the resulting dataclass but not be mapped, without the need to also indicate the __allow_unmapped__ class attribute. Previous 2.0 beta releases would require this attribute to be explicitly present, even though the purpose of this attribute was only to allow legacy ORM typed mappings to continue to function.

Applying ORM Mappings to an existing dataclass

SQLAlchemy’s native dataclass support builds upon the previous version of the feature first introduced in SQLAlchemy 1.4, which supports the application of ORM mappings to a class after it has been processed with the @dataclass decorator, by using either the registry.mapped() class decorator, or the registry.map_imperatively() method to apply ORM mappings to the class using Imperative. This approach is still viable for applications that are using partially or fully imperative mapping forms with dataclasses.

For fully Declarative mapping combined with dataclasses, the Declarative Dataclass Mapping approach should be preferred.

New in version 1.4: Added support for direct mapping of Python dataclasses

To map an existing dataclass, SQLAlchemy’s “inline” declarative directives cannot be used directly; ORM directives are assigned using one of three techniques:

The general process by which SQLAlchemy applies mappings to a dataclass is the same as that of an ordinary class, but also includes that SQLAlchemy will detect class-level attributes that were part of the dataclasses declaration process and replace them at runtime with the usual SQLAlchemy ORM mapped attributes. The __init__ method that would have been generated by dataclasses is left intact, as is the same for all the other methods that dataclasses generates such as __eq__(), __repr__(), etc.

Mapping dataclasses using Declarative With Imperative Table

An example of a mapping using @dataclass using Declarative with Imperative Table (a.k.a. Hybrid Declarative) is below. A complete Table object is constructed explicitly and assigned to the __table__ attribute. Instance fields are defined using normal dataclass syntaxes. Additional MapperProperty definitions such as relationship(), are placed in the __mapper_args__ class-level dictionary underneath the properties key, corresponding to the Mapper.properties parameter:

  1. from __future__ import annotations
  3. from dataclasses import dataclass, field
  4. from typing import List, Optional
  6. from sqlalchemy import Column, ForeignKey, Integer, String, Table
  7. from sqlalchemy.orm import registry, relationship
  9. mapper_registry = registry()
  12. @mapper_registry.mapped
  13. @dataclass
  14. class User:
  15.     __table__ = Table(
  16.         "user",
  17.         mapper_registry.metadata,
  18.         Column("id", Integer, primary_key=True),
  19.         Column("name", String(50)),
  20.         Column("fullname", String(50)),
  21.         Column("nickname", String(12)),
  22.     )
  23.     id: int = field(init=False)
  24.     name: Optional[str] = None
  25.     fullname: Optional[str] = None
  26.     nickname: Optional[str] = None
  27.     addresses: List[Address] = field(default_factory=list)
  29.     __mapper_args__ = {  # type: ignore
  30.         "properties": {
  31.             "addresses": relationship("Address"),
  32.         }
  33.     }
  36. @mapper_registry.mapped
  37. @dataclass
  38. class Address:
  39.     __table__ = Table(
  40.         "address",
  41.         mapper_registry.metadata,
  42.         Column("id", Integer, primary_key=True),
  43.         Column("user_id", Integer, ForeignKey("user.id")),
  44.         Column("email_address", String(50)),
  45.     )
  46.     id: int = field(init=False)
  47.     user_id: int = field(init=False)
  48.     email_address: Optional[str] = None

In the above example, the User.id, Address.id, and Address.user_id attributes are defined as field(init=False). This means that parameters for these won’t be added to __init__() methods, but Session will still be able to set them after getting their values during flush from autoincrement or other default value generator. To allow them to be specified in the constructor explicitly, they would instead be given a default value of None.

For a relationship() to be declared separately, it needs to be specified directly within the Mapper.properties dictionary which itself is specified within the __mapper_args__ dictionary, so that it is passed to the constructor for Mapper. An alternative to this approach is in the next example.

Mapping dataclasses using Declarative Mapping

Deprecated since version 2.0: This approach to Declarative mapping with dataclasses should be considered as legacy. It will remain supported however is unlikely to offer any advantages against the new approach detailed at Declarative Dataclass Mapping.

The fully declarative approach requires that Column objects are declared as class attributes, which when using dataclasses would conflict with the dataclass-level attributes. An approach to combine these together is to make use of the metadata attribute on the dataclass.field object, where SQLAlchemy-specific mapping information may be supplied. Declarative supports extraction of these parameters when the class specifies the attribute __sa_dataclass_metadata_key__. This also provides a more succinct method of indicating the relationship() association:

  1. from __future__ import annotations
  3. from dataclasses import dataclass, field
  4. from typing import List
  6. from sqlalchemy import Column, ForeignKey, Integer, String
  7. from sqlalchemy.orm import registry, relationship
  9. mapper_registry = registry()
  12. @mapper_registry.mapped
  13. @dataclass
  14. class User:
  15.     __tablename__ = "user"
  17.     __sa_dataclass_metadata_key__ = "sa"
  18.     id: int = field(
  19.         init=False, metadata={"sa": mapped_column(Integer, primary_key=True)}
  20.     )
  21.     name: str = field(default=None, metadata={"sa": mapped_column(String(50))})
  22.     fullname: str = field(default=None, metadata={"sa": mapped_column(String(50))})
  23.     nickname: str = field(default=None, metadata={"sa": mapped_column(String(12))})
  24.     addresses: List[Address] = field(
  25.         default_factory=list, metadata={"sa": relationship("Address")}
  26.     )
  29. @mapper_registry.mapped
  30. @dataclass
  31. class Address:
  32.     __tablename__ = "address"
  33.     __sa_dataclass_metadata_key__ = "sa"
  34.     id: int = field(
  35.         init=False, metadata={"sa": mapped_column(Integer, primary_key=True)}
  36.     )
  37.     user_id: int = field(
  38.         init=False, metadata={"sa": mapped_column(ForeignKey("user.id"))}
  39.     )
  40.     email_address: str = field(default=None, metadata={"sa": mapped_column(String(50))})

Using Declarative Mixins with Dataclasses

In the section Composing Mapped Hierarchies with Mixins, Declarative Mixin classes are introduced. One requirement of declarative mixins is that certain constructs that can’t be easily duplicated must be given as callables, using the declared_attr decorator, such as in the example at Mixing in Relationships:

  1. class RefTargetMixin:
  2.     @declared_attr
  3.     def target_id(cls):
  4.         return mapped_column("target_id", ForeignKey("target.id"))
  6.     @declared_attr
  7.     def target(cls):
  8.         return relationship("Target")

This form is supported within the Dataclasses field() object by using a lambda to indicate the SQLAlchemy construct inside the field(). Using declared_attr() to surround the lambda is optional. If we wanted to produce our User class above where the ORM fields came from a mixin that is itself a dataclass, the form would be:

  1. @dataclass
  2. class UserMixin:
  3.     __tablename__ = "user"
  5.     __sa_dataclass_metadata_key__ = "sa"
  7.     id: int = field(
  8.         init=False, metadata={"sa": mapped_column(Integer, primary_key=True)}
  9.     )
  11.     addresses: List[Address] = field(
  12.         default_factory=list, metadata={"sa": lambda: relationship("Address")}
  13.     )
  16. @dataclass
  17. class AddressMixin:
  18.     __tablename__ = "address"
  19.     __sa_dataclass_metadata_key__ = "sa"
  20.     id: int = field(
  21.         init=False, metadata={"sa": mapped_column(Integer, primary_key=True)}
  22.     )
  23.     user_id: int = field(
  24.         init=False, metadata={"sa": lambda: mapped_column(ForeignKey("user.id"))}
  25.     )
  26.     email_address: str = field(default=None, metadata={"sa": mapped_column(String(50))})
  29. @mapper_registry.mapped
  30. class User(UserMixin):
  31.     pass
  34. @mapper_registry.mapped
  35. class Address(AddressMixin):
  36.     pass

New in version 1.4.2: Added support for “declared attr” style mixin attributes, namely relationship() constructs as well as Column objects with foreign key declarations, to be used within “Dataclasses with Declarative Table” style mappings.

Mapping dataclasses using Imperative Mapping

As described previously, a class which is set up as a dataclass using the @dataclass decorator can then be further decorated using the registry.mapped() decorator in order to apply declarative-style mapping to the class. As an alternative to using the registry.mapped() decorator, we may also pass the class through the registry.map_imperatively() method instead, so that we may pass all Table and Mapper configuration imperatively to the function rather than having them defined on the class itself as class variables:

  1. from __future__ import annotations
  3. from dataclasses import dataclass
  4. from dataclasses import field
  5. from typing import List
  7. from sqlalchemy import Column
  8. from sqlalchemy import ForeignKey
  9. from sqlalchemy import Integer
  10. from sqlalchemy import MetaData
  11. from sqlalchemy import String
  12. from sqlalchemy import Table
  13. from sqlalchemy.orm import registry
  14. from sqlalchemy.orm import relationship
  16. mapper_registry = registry()
  19. @dataclass
  20. class User:
  21.     id: int = field(init=False)
  22.     name: str = None
  23.     fullname: str = None
  24.     nickname: str = None
  25.     addresses: List[Address] = field(default_factory=list)
  28. @dataclass
  29. class Address:
  30.     id: int = field(init=False)
  31.     user_id: int = field(init=False)
  32.     email_address: str = None
  35. metadata_obj = MetaData()
  37. user = Table(
  38.     "user",
  39.     metadata_obj,
  40.     Column("id", Integer, primary_key=True),
  41.     Column("name", String(50)),
  42.     Column("fullname", String(50)),
  43.     Column("nickname", String(12)),
  44. )
  46. address = Table(
  47.     "address",
  48.     metadata_obj,
  49.     Column("id", Integer, primary_key=True),
  50.     Column("user_id", Integer, ForeignKey("user.id")),
  51.     Column("email_address", String(50)),
  52. )
  54. mapper_registry.map_imperatively(
  55.     User,
  56.     user,
  57.     properties={
  58.         "addresses": relationship(Address, backref="user", order_by=address.c.id),
  59.     },
  60. )
  62. mapper_registry.map_imperatively(Address, address)

Applying ORM mappings to an existing attrs class

The attrs library is a popular third party library that provides similar features as dataclasses, with many additional features provided not found in ordinary dataclasses.

A class augmented with attrs uses the @define decorator. This decorator initiates a process to scan the class for attributes that define the class’ behavior, which are then used to generate methods, documentation, and annotations.

The SQLAlchemy ORM supports mapping an attrs class using Declarative with Imperative Table or Imperative mapping. The general form of these two styles is fully equivalent to the Mapping dataclasses using Declarative Mapping and Mapping dataclasses using Declarative With Imperative Table mapping forms used with dataclasses, where the inline attribute directives used by dataclasses or attrs are unchanged, and SQLAlchemy’s table-oriented instrumentation is applied at runtime.

The @define decorator of attrs by default replaces the annotated class with a new __slots__ based class, which is not supported. When using the old style annotation @attr.s or using define(slots=False), the class does not get replaced. Furthermore attrs removes its own class-bound attributes after the decorator runs, so that SQLAlchemy’s mapping process takes over these attributes without any issue. Both decorators, @attr.s and @define(slots=False) work with SQLAlchemy.

Mapping attrs with Declarative “Imperative Table”

In the “Declarative with Imperative Table” style, a Table object is declared inline with the declarative class. The @define decorator is applied to the class first, then the registry.mapped() decorator second:

  1. from __future__ import annotations
  3. from typing import List
  4. from typing import Optional
  6. from attrs import define
  7. from sqlalchemy import Column
  8. from sqlalchemy import ForeignKey
  9. from sqlalchemy import Integer
  10. from sqlalchemy import MetaData
  11. from sqlalchemy import String
  12. from sqlalchemy import Table
  13. from sqlalchemy.orm import Mapped
  14. from sqlalchemy.orm import registry
  15. from sqlalchemy.orm import relationship
  17. mapper_registry = registry()
  20. @mapper_registry.mapped
  21. @define(slots=False)
  22. class User:
  23.     __table__ = Table(
  24.         "user",
  25.         mapper_registry.metadata,
  26.         Column("id", Integer, primary_key=True),
  27.         Column("name", String(50)),
  28.         Column("FullName", String(50), key="fullname"),
  29.         Column("nickname", String(12)),
  30.     )
  31.     id: Mapped[int]
  32.     name: Mapped[str]
  33.     fullname: Mapped[str]
  34.     nickname: Mapped[str]
  35.     addresses: Mapped[List[Address]]
  37.     __mapper_args__ = {  # type: ignore
  38.         "properties": {
  39.             "addresses": relationship("Address"),
  40.         }
  41.     }
  44. @mapper_registry.mapped
  45. @define(slots=False)
  46. class Address:
  47.     __table__ = Table(
  48.         "address",
  49.         mapper_registry.metadata,
  50.         Column("id", Integer, primary_key=True),
  51.         Column("user_id", Integer, ForeignKey("user.id")),
  52.         Column("email_address", String(50)),
  53.     )
  54.     id: Mapped[int]
  55.     user_id: Mapped[int]
  56.     email_address: Mapped[Optional[str]]

Note

The attrs slots=True option, which enables __slots__ on a mapped class, cannot be used with SQLAlchemy mappings without fully implementing alternative attribute instrumentation, as mapped classes normally rely upon direct access to __dict__ for state storage. Behavior is undefined when this option is present.

Mapping attrs with Imperative Mapping

Just as is the case with dataclasses, we can make use of registry.map_imperatively() to map an existing attrs class as well:

  1. from __future__ import annotations
  3. from typing import List
  5. from attrs import define
  6. from sqlalchemy import Column
  7. from sqlalchemy import ForeignKey
  8. from sqlalchemy import Integer
  9. from sqlalchemy import MetaData
  10. from sqlalchemy import String
  11. from sqlalchemy import Table
  12. from sqlalchemy.orm import registry
  13. from sqlalchemy.orm import relationship
  15. mapper_registry = registry()
  18. @define(slots=False)
  19. class User:
  20.     id: int
  21.     name: str
  22.     fullname: str
  23.     nickname: str
  24.     addresses: List[Address]
  27. @define(slots=False)
  28. class Address:
  29.     id: int
  30.     user_id: int
  31.     email_address: Optional[str]
  34. metadata_obj = MetaData()
  36. user = Table(
  37.     "user",
  38.     metadata_obj,
  39.     Column("id", Integer, primary_key=True),
  40.     Column("name", String(50)),
  41.     Column("fullname", String(50)),
  42.     Column("nickname", String(12)),
  43. )
  45. address = Table(
  46.     "address",
  47.     metadata_obj,
  48.     Column("id", Integer, primary_key=True),
  49.     Column("user_id", Integer, ForeignKey("user.id")),
  50.     Column("email_address", String(50)),
  51. )
  53. mapper_registry.map_imperatively(
  54.     User,
  55.     user,
  56.     properties={
  57.         "addresses": relationship(Address, backref="user", order_by=address.c.id),
  58.     },
  59. )
  61. mapper_registry.map_imperatively(Address, address)

The above form is equivalent to the previous example using Declarative with Imperative Table.