--- name: sqlalchemy description: SQLAlchemy Python SQL toolkit and ORM. Use for database models, queries, sessions, relationships, migrations, async database operations, and working with PostgreSQL, MySQL, SQLite, Oracle, SQL Server. --- # Sqlalchemy Skill Sqlalchemy python sql toolkit and orm. use for database models, queries, sessions, relationships, migrations, async database operations, and working with postgresql, mysql, sqlite, oracle, sql server., generated from official documentation. ## When to Use This Skill This skill should be triggered when: - Working with sqlalchemy - Asking about sqlalchemy features or APIs - Implementing sqlalchemy solutions - Debugging sqlalchemy code - Learning sqlalchemy best practices ## Quick Reference ### Common Patterns **Pattern 1:** Release: 2.0.45 current release | Release Date: December 9, 2025 SQLAlchemy 2.0 Documentation SQLAlchemy 2.0 Documentation current release Home | Download this Documentation Search terms: AI and large language models (LLMs) are revolutionizing the way businesses use and process data.www.mongodb.comAds by EthicalAds SQLAlchemy ORM ORM Quick Start ORM Mapped Class Configuration▼ ORM Mapped Class Overview Mapping Classes with Declarative▼ Declarative Mapping Styles Table Configuration with Declarative Mapper Configuration with Declarative Composing Mapped Hierarchies with Mixins¶▼ Augmenting the Base Mixing in Columns Mixing in Relationships Mixing in column_property() and other MapperProperty classes Using Mixins and Base Classes with Mapped Inheritance Patterns▶ Using declared_attr() with inheriting Table and Mapper arguments Using declared_attr() to generate table-specific inheriting columns Combining Table\/Mapper Arguments from Multiple Mixins Creating Indexes and Constraints with Naming Conventions on Mixins Integration with dataclasses and attrs SQL Expressions as Mapped Attributes Changing Attribute Behavior Composite Column Types Mapping Class Inheritance Hierarchies Non-Traditional Mappings Configuring a Version Counter Class Mapping API Mapping SQL Expressions Relationship Configuration ORM Querying Guide Using the Session Events and Internals ORM Extensions ORM Examples Project Versions Version 2.1 (development)Version 2.0Version 1.4Version 1.3 Search terms: Home | Download this Documentation Previous: Mapper Configuration with Declarative Next: Integration with dataclasses and attrs Up: Home SQLAlchemy ORM ORM Mapped Class Configuration Mapping Classes with Declarative On this page: Composing Mapped Hierarchies with Mixins Augmenting the Base Mixing in Columns Mixing in Relationships Mixing in _orm.column_property() and other _orm.MapperProperty classes Using Mixins and Base Classes with Mapped Inheritance Patterns Using _orm.declared_attr() with inheriting Table and Mapper arguments Using _orm.declared_attr() to generate table-specific inheriting columns Combining Table\/Mapper Arguments from Multiple Mixins Creating Indexes and Constraints with Naming Conventions on Mixins Composing Mapped Hierarchies with Mixins¶ A common need when mapping classes using the Declarative style is to share common functionality, such as particular columns, table or mapper options, naming schemes, or other mapped properties, across many classes. When using declarative mappings, this idiom is supported via the use of mixin classes, as well as via augmenting the declarative base class itself. Tip In addition to mixin classes, common column options may also be shared among many classes using PEP 593 Annotated types; see Mapping Multiple Type Configurations to Python Types and Mapping Whole Column Declarations to Python Types for background on these SQLAlchemy 2.0 features. An example of some commonly mixed-in idioms is below: from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr 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 CommonMixin: """define a series of common elements that may be applied to mapped classes using this class as a mixin class.""" @declared_attr.directive def __tablename__(cls) -> str: return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id: Mapped[int] = mapped_column(primary_key=True) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self) -> Mapped["LogRecord"]: return relationship("LogRecord") class LogRecord(CommonMixin, Base): log_info: Mapped[str] class MyModel(CommonMixin, HasLogRecord, Base): name: Mapped[str] Copy to clipboard The above example illustrates a class MyModel which includes two mixins CommonMixin and HasLogRecord in its bases, as well as a supplementary class LogRecord which also includes CommonMixin, demonstrating a variety of constructs that are supported on mixins and base classes, including: columns declared using mapped_column(), Mapped or Column are copied from mixins or base classes onto the target class to be mapped; above this is illustrated via the column attributes CommonMixin.id and HasLogRecord.log_record_id. Declarative directives such as __table_args__ and __mapper_args__ can be assigned to a mixin or base class, where they will take effect automatically for any classes which inherit from the mixin or base. The above example illustrates this using the __table_args__ and __mapper_args__ attributes. All Declarative directives, including all of __tablename__, __table__, __table_args__ and __mapper_args__, may be implemented using user-defined class methods, which are decorated with the declared_attr decorator (specifically the declared_attr.directive sub-member, more on that in a moment). Above, this is illustrated using a def __tablename__(cls) classmethod that generates a Table name dynamically; when applied to the MyModel class, the table name will be generated as "mymodel", and when applied to the LogRecord class, the table name will be generated as "logrecord". Other ORM properties such as relationship() can be generated on the target class to be mapped using user-defined class methods also decorated with the declared_attr decorator. Above, this is illustrated by generating a many-to-one relationship() to a mapped object called LogRecord. The features above may all be demonstrated using a select() example: >>> from sqlalchemy import select >>> print(select(MyModel).join(MyModel.log_record)) SELECT mymodel.name, mymodel.id, mymodel.log_record_id FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id Copy to clipboard Tip The examples of declared_attr will attempt to illustrate the correct PEP 484 annotations for each method example. The use of annotations with declared_attr functions are completely optional, and are not consumed by Declarative; however, these annotations are required in order to pass Mypy --strict type checking. Additionally, the declared_attr.directive sub-member illustrated above is optional as well, and is only significant for PEP 484 typing tools, as it adjusts for the expected return type when creating methods to override Declarative directives such as __tablename__, __mapper_args__ and __table_args__. Added in version 2.0: As part of PEP 484 typing support for the SQLAlchemy ORM, added the declared_attr.directive to declared_attr to distinguish between Mapped attributes and Declarative configurational attributes There’s no fixed convention for the order of mixins and base classes. Normal Python method resolution rules apply, and the above example would work just as well with: class MyModel(Base, HasLogRecord, CommonMixin): name: Mapped[str] = mapped_column() Copy to clipboard This works because Base here doesn’t define any of the variables that CommonMixin or HasLogRecord defines, i.e. __tablename__, __table_args__, id, etc. If the Base did define an attribute of the same name, the class placed first in the inherits list would determine which attribute is used on the newly defined class. Tip While the above example is using Annotated Declarative Table form based on the Mapped annotation class, mixin classes also work perfectly well with non-annotated and legacy Declarative forms, such as when using Column directly instead of mapped_column(). Changed in version 2.0: For users coming from the 1.4 series of SQLAlchemy who may have been using the mypy plugin, the declarative_mixin() class decorator is no longer needed to mark declarative mixins, assuming the mypy plugin is no longer in use. Augmenting the Base¶ In addition to using a pure mixin, most of the techniques in this section can also be applied to the base class directly, for patterns that should apply to all classes derived from a particular base. The example below illustrates some of the previous section’s example in terms of the Base class: from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base(DeclarativeBase): """define a series of common elements that may be applied to mapped classes using this class as a base class.""" @declared_attr.directive def __tablename__(cls) -> str: return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id: Mapped[int] = mapped_column(primary_key=True) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self) -> Mapped["LogRecord"]: return relationship("LogRecord") class LogRecord(Base): log_info: Mapped[str] class MyModel(HasLogRecord, Base): name: Mapped[str] Copy to clipboard Where above, MyModel as well as LogRecord, in deriving from Base, will both have their table name derived from their class name, a primary key column named id, as well as the above table and mapper arguments defined by Base.__table_args__ and Base.__mapper_args__. When using legacy declarative_base() or registry.generate_base(), the declarative_base.cls parameter may be used as follows to generate an equivalent effect, as illustrated in the non-annotated example below: # legacy declarative_base() use from sqlalchemy import Integer, String from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr from sqlalchemy.orm import declarative_base from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base: """define a series of common elements that may be applied to mapped classes using this class as a base class.""" @declared_attr.directive def __tablename__(cls): return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id = mapped_column(Integer, primary_key=True) Base = declarative_base(cls=Base) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self): return relationship("LogRecord") class LogRecord(Base): log_info = mapped_column(String) class MyModel(HasLogRecord, Base): name = mapped_column(String) Copy to clipboard Mixing in Columns¶ Columns can be indicated in mixins assuming the Declarative table style of configuration is in use (as opposed to imperative table configuration), so that columns declared on the mixin can then be copied to be part of the Table that the Declarative process generates. All three of the mapped_column(), Mapped, and Column constructs may be declared inline in a declarative mixin: class TimestampMixin: created_at: Mapped[datetime] = mapped_column(default=func.now()) updated_at: Mapped[datetime] class MyModel(TimestampMixin, Base): __tablename__ = "test" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] Copy to clipboard Where above, all declarative classes that include TimestampMixin in their class bases will automatically include a column created_at that applies a timestamp to all row insertions, as well as an updated_at column, which does not include a default for the purposes of the example (if it did, we would use the Column.onupdate parameter which is accepted by mapped_column()). These column constructs are always copied from the originating mixin or base class, so that the same mixin\/base class may be applied to any number of target classes which will each have their own column constructs. All Declarative column forms are supported by mixins, including: Annotated attributes - with or without mapped_column() present: class TimestampMixin: created_at: Mapped[datetime] = mapped_column(default=func.now()) updated_at: Mapped[datetime] Copy to clipboard mapped_column - with or without Mapped present: class TimestampMixin: created_at = mapped_column(default=func.now()) updated_at: Mapped[datetime] = mapped_column() Copy to clipboard Column - legacy Declarative form: class TimestampMixin: created_at = Column(DateTime, default=func.now()) updated_at = Column(DateTime) Copy to clipboard In each of the above forms, Declarative handles the column-based attributes on the mixin class by creating a copy of the construct, which is then applied to the target class. Changed in version 2.0: The declarative API can now accommodate Column objects as well as mapped_column() constructs of any form when using mixins without the need to use declared_attr(). Previous limitations which prevented columns with ForeignKey elements from being used directly in mixins have been removed. Mixing in Relationships¶ Relationships created by relationship() are provided with declarative mixin classes exclusively using the declared_attr approach, eliminating any ambiguity which could arise when copying a relationship and its possibly column-bound contents. Below is an example which combines a foreign key column and a relationship so that two classes Foo and Bar can both be configured to reference a common target class via many-to-one: from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base(DeclarativeBase): pass class RefTargetMixin: target_id: Mapped[int] = mapped_column(ForeignKey("target.id")) @declared_attr def target(cls) -> Mapped["Target"]: return relationship("Target") class Foo(RefTargetMixin, Base): __tablename__ = "foo" id: Mapped[int] = mapped_column(primary_key=True) class Bar(RefTargetMixin, Base): __tablename__ = "bar" id: Mapped[int] = mapped_column(primary_key=True) class Target(Base): __tablename__ = "target" id: Mapped[int] = mapped_column(primary_key=True) Copy to clipboard With the above mapping, each of Foo and Bar contain a relationship to Target accessed along the .target attribute: >>> from sqlalchemy import select >>> print(select(Foo).join(Foo.target)) SELECT foo.id, foo.target_id FROM foo JOIN target ON target.id = foo.target_id >>> print(select(Bar).join(Bar.target)) SELECT bar.id, bar.target_id FROM bar JOIN target ON target.id = bar.target_id Copy to clipboard Special arguments such as relationship.primaryjoin may also be used within mixed-in classmethods, which often need to refer to the class that’s being mapped. For schemes that need to refer to locally mapped columns, in ordinary cases these columns are made available by Declarative as attributes on the mapped class which is passed as the cls argument to the decorated classmethod. Using this feature, we could for example rewrite the RefTargetMixin.target method using an explicit primaryjoin which refers to pending mapped columns on both Target and cls: class Target(Base): __tablename__ = "target" id: Mapped[int] = mapped_column(primary_key=True) class RefTargetMixin: target_id: Mapped[int] = mapped_column(ForeignKey("target.id")) @declared_attr def target(cls) -> Mapped["Target"]: # illustrates explicit 'primaryjoin' argument return relationship("Target", primaryjoin=Target.id == cls.target_id) Copy to clipboard Mixing in column_property() and other MapperProperty classes¶ Like relationship(), other MapperProperty subclasses such as column_property() also need to have class-local copies generated when used by mixins, so are also declared within functions that are decorated by declared_attr. Within the function, other ordinary mapped columns that were declared with mapped_column(), Mapped, or Column will be made available from the cls argument so that they may be used to compose new attributes, as in the example below which adds two columns together: from sqlalchemy.orm import column_property from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class SomethingMixin: x: Mapped[int] y: Mapped[int] @declared_attr def x_plus_y(cls) -> Mapped[int]: return column_property(cls.x + cls.y) class Something(SomethingMixin, Base): __tablename__ = "something" id: Mapped[int] = mapped_column(primary_key=True) Copy to clipboard Above, we may make use of Something.x_plus_y in a statement where it produces the full expression: >>> from sqlalchemy import select >>> print(select(Something.x_plus_y)) SELECT something.x + something.y AS anon_1 FROM something Copy to clipboard Tip The declared_attr decorator causes the decorated callable to behave exactly as a classmethod. However, typing tools like Pylance may not be able to recognize this, which can sometimes cause it to complain about access to the cls variable inside the body of the function. To resolve this issue when it occurs, the @classmethod decorator may be combined directly with declared_attr as: class SomethingMixin: x: Mapped[int] y: Mapped[int] @declared_attr @classmethod def x_plus_y(cls) -> Mapped[int]: return column_property(cls.x + cls.y) Copy to clipboard Added in version 2.0: - declared_attr can accommodate a function decorated with @classmethod to help with PEP 484 integration where needed. Using Mixins and Base Classes with Mapped Inheritance Patterns¶ When dealing with mapper inheritance patterns as documented at Mapping Class Inheritance Hierarchies, some additional capabilities are present when using declared_attr either with mixin classes, or when augmenting both mapped and un-mapped superclasses in a class hierarchy. When defining functions decorated by declared_attr on mixins or base classes to be interpreted by subclasses in a mapped inheritance hierarchy, there is an important distinction made between functions that generate the special names used by Declarative such as __tablename__, __mapper_args__ vs. those that may generate ordinary mapped attributes such as mapped_column() and relationship(). Functions that define Declarative directives are invoked for each subclass in a hierarchy, whereas functions that generate mapped attributes are invoked only for the first mapped superclass in a hierarchy. The rationale for this difference in behavior is based on the fact that mapped properties are already inheritable by classes, such as a particular column on a superclass’ mapped table should not be duplicated to that of a subclass as well, whereas elements that are specific to a particular class or its mapped table are not inheritable, such as the name of the table that is locally mapped. The difference in behavior between these two use cases is demonstrated in the following two sections. Using declared_attr() with inheriting Table and Mapper arguments¶ A common recipe with mixins is to create a def __tablename__(cls) function that generates a name for the mapped Table dynamically. This recipe can be used to generate table names for an inheriting mapper hierarchy as in the example below which creates a mixin that gives every class a simple table name based on class name. The recipe is illustrated below where a table name is generated for the Person mapped class and the Engineer subclass of Person, but not for the Manager subclass of Person: from typing import Optional from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class Tablename: @declared_attr.directive def __tablename__(cls) -> Optional[str]: return cls.__name__.lower() class Person(Tablename, Base): id: Mapped[int] = mapped_column(primary_key=True) discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True) primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} class Manager(Person): @declared_attr.directive def __tablename__(cls) -> Optional[str]: """override __tablename__ so that Manager is single-inheritance to Person""" return None __mapper_args__ = {"polymorphic_identity": "manager"} Copy to clipboard In the above example, both the Person base class as well as the Engineer class, being subclasses of the Tablename mixin class which generates new table names, will have a generated __tablename__ attribute, which to Declarative indicates that each class should have its own Table generated to which it will be mapped. For the Engineer subclass, the style of inheritance applied is joined table inheritance, as it will be mapped to a table engineer that joins to the base person table. Any other subclasses that inherit from Person will also have this style of inheritance applied by default (and within this particular example, would need to each specify a primary key column; more on that in the next section). By contrast, the Manager subclass of Person overrides the __tablename__ classmethod to return None. This indicates to Declarative that this class should not have a Table generated, and will instead make use exclusively of the base Table to which Person is mapped. For the Manager subclass, the style of inheritance applied is single table inheritance. The example above illustrates that Declarative directives like __tablename__ are necessarily applied to each subclass individually, as each mapped class needs to state which Table it will be mapped towards, or if it will map itself to the inheriting superclass’ Table. If we instead wanted to reverse the default table scheme illustrated above, so that single table inheritance were the default and joined table inheritance could be defined only when a __tablename__ directive were supplied to override it, we can make use of Declarative helpers within the top-most __tablename__() method, in this case a helper called has_inherited_table(). This function will return True if a superclass is already mapped to a Table. We may use this helper within the base-most __tablename__() classmethod so that we may conditionally return None for the table name, if a table is already present, thus indicating single-table inheritance for inheriting subclasses by default: from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import has_inherited_table from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class Tablename: @declared_attr.directive def __tablename__(cls): if has_inherited_table(cls): return None return cls.__name__.lower() class Person(Tablename, Base): id: Mapped[int] = mapped_column(primary_key=True) discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): @declared_attr.directive def __tablename__(cls): """override __tablename__ so that Engineer is joined-inheritance to Person""" return cls.__name__.lower() id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True) primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} class Manager(Person): __mapper_args__ = {"polymorphic_identity": "manager"} Copy to clipboard Using declared_attr() to generate table-specific inheriting columns¶ In contrast to how __tablename__ and other special names are handled when used with declared_attr, when we mix in columns and properties (e.g. relationships, column properties, etc.), the function is invoked for the base class only in the hierarchy, unless the declared_attr directive is used in combination with the declared_attr.cascading sub-directive. Below, only the Person class will receive a column called id; the mapping will fail on Engineer, which is not given a primary key: class HasId: id: Mapped[int] = mapped_column(primary_key=True) class Person(HasId, Base): __tablename__ = "person" discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} # this mapping will fail, as there's no primary key class Engineer(Person): __tablename__ = "engineer" primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} Copy to clipboard It is usually the case in joined-table inheritance that we want distinctly named columns on each subclass. However in this case, we may want to have an id column on every table, and have them refer to each other via foreign key. We can achieve this as a mixin by using the declared_attr.cascading modifier, which indicates that the function should be invoked for each class in the hierarchy, in almost (see warning below) the same way as it does for __tablename__: class HasIdMixin: @declared_attr.cascading def id(cls) -> Mapped[int]: if has_inherited_table(cls): return mapped_column(ForeignKey("person.id"), primary_key=True) else: return mapped_column(Integer, primary_key=True) class Person(HasIdMixin, Base): __tablename__ = "person" discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): __tablename__ = "engineer" primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} Copy to clipboard Warning The declared_attr.cascading feature currently does not allow for a subclass to override the attribute with a different function or value. This is a current limitation in the mechanics of how @declared_attr is resolved, and a warning is emitted if this condition is detected. This limitation only applies to ORM mapped columns, relationships, and other MapperProperty styles of attribute. It does not apply to Declarative directives such as __tablename__, __mapper_args__, etc., which resolve in a different way internally than that of declared_attr.cascading. Combining Table\/Mapper Arguments from Multiple Mixins¶ In the case of __table_args__ or __mapper_args__ specified with declarative mixins, you may want to combine some parameters from several mixins with those you wish to define on the class itself. The declared_attr decorator can be used here to create user-defined collation routines that pull from multiple collections: from sqlalchemy.orm import declared_attr class MySQLSettings: __table_args__ = {"mysql_engine": "InnoDB"} class MyOtherMixin: __table_args__ = {"info": "foo"} class MyModel(MySQLSettings, MyOtherMixin, Base): __tablename__ = "my_model" @declared_attr.directive def __table_args__(cls): args = dict() args.update(MySQLSettings.__table_args__) args.update(MyOtherMixin.__table_args__) return args id = mapped_column(Integer, primary_key=True) Copy to clipboard Creating Indexes and Constraints with Naming Conventions on Mixins¶ Using named constraints such as Index, UniqueConstraint, CheckConstraint, where each object is to be unique to a specific table descending from a mixin, requires that an individual instance of each object is created per actual mapped class. As a simple example, to define a named, potentially multicolumn Index that applies to all tables derived from a mixin, use the “inline” form of Index and establish it as part of __table_args__, using declared_attr to establish __table_args__() as a class method that will be invoked for each subclass: class MyMixin: a = mapped_column(Integer) b = mapped_column(Integer) @declared_attr.directive def __table_args__(cls): return (Index(f"test_idx_{cls.__tablename__}", "a", "b"),) class MyModelA(MyMixin, Base): __tablename__ = "table_a" id = mapped_column(Integer, primary_key=True) class MyModelB(MyMixin, Base): __tablename__ = "table_b" id = mapped_column(Integer, primary_key=True) Copy to clipboard The above example would generate two tables "table_a" and "table_b", with indexes "test_idx_table_a" and "test_idx_table_b" Typically, in modern SQLAlchemy we would use a naming convention, as documented at Configuring Constraint Naming Conventions. While naming conventions take place automatically using the MetaData.naming_convention as new Constraint objects are created, as this convention is applied at object construction time based on the parent Table for a particular Constraint, a distinct Constraint object needs to be created for each inheriting subclass with its own Table, again using declared_attr with __table_args__(), below illustrated using an abstract mapped base: from uuid import UUID from sqlalchemy import CheckConstraint from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy import UniqueConstraint from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column constraint_naming_conventions = { "ix": "ix_%(column_0_label)s", "uq": "uq_%(table_name)s_%(column_0_name)s", "ck": "ck_%(table_name)s_%(constraint_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "pk": "pk_%(table_name)s", } class Base(DeclarativeBase): metadata = MetaData(naming_convention=constraint_naming_conventions) class MyAbstractBase(Base): __abstract__ = True @declared_attr.directive def __table_args__(cls): return ( UniqueConstraint("uuid"), CheckConstraint("x > 0 OR y < 100", name="xy_chk"), ) id: Mapped[int] = mapped_column(primary_key=True) uuid: Mapped[UUID] x: Mapped[int] y: Mapped[int] class ModelAlpha(MyAbstractBase): __tablename__ = "alpha" class ModelBeta(MyAbstractBase): __tablename__ = "beta" Copy to clipboard The above mapping will generate DDL that includes table-specific names for all constraints, including primary key, CHECK constraint, unique constraint: CREATE TABLE alpha ( id INTEGER NOT NULL, uuid CHAR(32) NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, CONSTRAINT pk_alpha PRIMARY KEY (id), CONSTRAINT uq_alpha_uuid UNIQUE (uuid), CONSTRAINT ck_alpha_xy_chk CHECK (x > 0 OR y < 100) ) CREATE TABLE beta ( id INTEGER NOT NULL, uuid CHAR(32) NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, CONSTRAINT pk_beta PRIMARY KEY (id), CONSTRAINT uq_beta_uuid UNIQUE (uuid), CONSTRAINT ck_beta_xy_chk CHECK (x > 0 OR y < 100) ) Copy to clipboard Previous: Mapper Configuration with Declarative Next: Integration with dataclasses and attrs © Copyright 2007-2025, the SQLAlchemy authors and contributors. flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari. Created using Sphinx 9.1.0. Documentation last generated: Tue 13 Jan 2026 01:05:54 PM EST ``` column_property() ``` **Pattern 2:** Composing Mapped Hierarchies with Mixins¶ A common need when mapping classes using the Declarative style is to share common functionality, such as particular columns, table or mapper options, naming schemes, or other mapped properties, across many classes. When using declarative mappings, this idiom is supported via the use of mixin classes, as well as via augmenting the declarative base class itself. Tip In addition to mixin classes, common column options may also be shared among many classes using PEP 593 Annotated types; see Mapping Multiple Type Configurations to Python Types and Mapping Whole Column Declarations to Python Types for background on these SQLAlchemy 2.0 features. An example of some commonly mixed-in idioms is below: from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr 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 CommonMixin: """define a series of common elements that may be applied to mapped classes using this class as a mixin class.""" @declared_attr.directive def __tablename__(cls) -> str: return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id: Mapped[int] = mapped_column(primary_key=True) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self) -> Mapped["LogRecord"]: return relationship("LogRecord") class LogRecord(CommonMixin, Base): log_info: Mapped[str] class MyModel(CommonMixin, HasLogRecord, Base): name: Mapped[str] Copy to clipboard The above example illustrates a class MyModel which includes two mixins CommonMixin and HasLogRecord in its bases, as well as a supplementary class LogRecord which also includes CommonMixin, demonstrating a variety of constructs that are supported on mixins and base classes, including: columns declared using mapped_column(), Mapped or Column are copied from mixins or base classes onto the target class to be mapped; above this is illustrated via the column attributes CommonMixin.id and HasLogRecord.log_record_id. Declarative directives such as __table_args__ and __mapper_args__ can be assigned to a mixin or base class, where they will take effect automatically for any classes which inherit from the mixin or base. The above example illustrates this using the __table_args__ and __mapper_args__ attributes. All Declarative directives, including all of __tablename__, __table__, __table_args__ and __mapper_args__, may be implemented using user-defined class methods, which are decorated with the declared_attr decorator (specifically the declared_attr.directive sub-member, more on that in a moment). Above, this is illustrated using a def __tablename__(cls) classmethod that generates a Table name dynamically; when applied to the MyModel class, the table name will be generated as "mymodel", and when applied to the LogRecord class, the table name will be generated as "logrecord". Other ORM properties such as relationship() can be generated on the target class to be mapped using user-defined class methods also decorated with the declared_attr decorator. Above, this is illustrated by generating a many-to-one relationship() to a mapped object called LogRecord. The features above may all be demonstrated using a select() example: >>> from sqlalchemy import select >>> print(select(MyModel).join(MyModel.log_record)) SELECT mymodel.name, mymodel.id, mymodel.log_record_id FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id Copy to clipboard Tip The examples of declared_attr will attempt to illustrate the correct PEP 484 annotations for each method example. The use of annotations with declared_attr functions are completely optional, and are not consumed by Declarative; however, these annotations are required in order to pass Mypy --strict type checking. Additionally, the declared_attr.directive sub-member illustrated above is optional as well, and is only significant for PEP 484 typing tools, as it adjusts for the expected return type when creating methods to override Declarative directives such as __tablename__, __mapper_args__ and __table_args__. Added in version 2.0: As part of PEP 484 typing support for the SQLAlchemy ORM, added the declared_attr.directive to declared_attr to distinguish between Mapped attributes and Declarative configurational attributes There’s no fixed convention for the order of mixins and base classes. Normal Python method resolution rules apply, and the above example would work just as well with: class MyModel(Base, HasLogRecord, CommonMixin): name: Mapped[str] = mapped_column() Copy to clipboard This works because Base here doesn’t define any of the variables that CommonMixin or HasLogRecord defines, i.e. __tablename__, __table_args__, id, etc. If the Base did define an attribute of the same name, the class placed first in the inherits list would determine which attribute is used on the newly defined class. Tip While the above example is using Annotated Declarative Table form based on the Mapped annotation class, mixin classes also work perfectly well with non-annotated and legacy Declarative forms, such as when using Column directly instead of mapped_column(). Changed in version 2.0: For users coming from the 1.4 series of SQLAlchemy who may have been using the mypy plugin, the declarative_mixin() class decorator is no longer needed to mark declarative mixins, assuming the mypy plugin is no longer in use. Augmenting the Base¶ In addition to using a pure mixin, most of the techniques in this section can also be applied to the base class directly, for patterns that should apply to all classes derived from a particular base. The example below illustrates some of the previous section’s example in terms of the Base class: from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base(DeclarativeBase): """define a series of common elements that may be applied to mapped classes using this class as a base class.""" @declared_attr.directive def __tablename__(cls) -> str: return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id: Mapped[int] = mapped_column(primary_key=True) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self) -> Mapped["LogRecord"]: return relationship("LogRecord") class LogRecord(Base): log_info: Mapped[str] class MyModel(HasLogRecord, Base): name: Mapped[str] Copy to clipboard Where above, MyModel as well as LogRecord, in deriving from Base, will both have their table name derived from their class name, a primary key column named id, as well as the above table and mapper arguments defined by Base.__table_args__ and Base.__mapper_args__. When using legacy declarative_base() or registry.generate_base(), the declarative_base.cls parameter may be used as follows to generate an equivalent effect, as illustrated in the non-annotated example below: # legacy declarative_base() use from sqlalchemy import Integer, String from sqlalchemy import ForeignKey from sqlalchemy.orm import declared_attr from sqlalchemy.orm import declarative_base from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base: """define a series of common elements that may be applied to mapped classes using this class as a base class.""" @declared_attr.directive def __tablename__(cls): return cls.__name__.lower() __table_args__ = {"mysql_engine": "InnoDB"} __mapper_args__ = {"eager_defaults": True} id = mapped_column(Integer, primary_key=True) Base = declarative_base(cls=Base) class HasLogRecord: """mark classes that have a many-to-one relationship to the ``LogRecord`` class.""" log_record_id = mapped_column(ForeignKey("logrecord.id")) @declared_attr def log_record(self): return relationship("LogRecord") class LogRecord(Base): log_info = mapped_column(String) class MyModel(HasLogRecord, Base): name = mapped_column(String) Copy to clipboard Mixing in Columns¶ Columns can be indicated in mixins assuming the Declarative table style of configuration is in use (as opposed to imperative table configuration), so that columns declared on the mixin can then be copied to be part of the Table that the Declarative process generates. All three of the mapped_column(), Mapped, and Column constructs may be declared inline in a declarative mixin: class TimestampMixin: created_at: Mapped[datetime] = mapped_column(default=func.now()) updated_at: Mapped[datetime] class MyModel(TimestampMixin, Base): __tablename__ = "test" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] Copy to clipboard Where above, all declarative classes that include TimestampMixin in their class bases will automatically include a column created_at that applies a timestamp to all row insertions, as well as an updated_at column, which does not include a default for the purposes of the example (if it did, we would use the Column.onupdate parameter which is accepted by mapped_column()). These column constructs are always copied from the originating mixin or base class, so that the same mixin\/base class may be applied to any number of target classes which will each have their own column constructs. All Declarative column forms are supported by mixins, including: Annotated attributes - with or without mapped_column() present: class TimestampMixin: created_at: Mapped[datetime] = mapped_column(default=func.now()) updated_at: Mapped[datetime] Copy to clipboard mapped_column - with or without Mapped present: class TimestampMixin: created_at = mapped_column(default=func.now()) updated_at: Mapped[datetime] = mapped_column() Copy to clipboard Column - legacy Declarative form: class TimestampMixin: created_at = Column(DateTime, default=func.now()) updated_at = Column(DateTime) Copy to clipboard In each of the above forms, Declarative handles the column-based attributes on the mixin class by creating a copy of the construct, which is then applied to the target class. Changed in version 2.0: The declarative API can now accommodate Column objects as well as mapped_column() constructs of any form when using mixins without the need to use declared_attr(). Previous limitations which prevented columns with ForeignKey elements from being used directly in mixins have been removed. Mixing in Relationships¶ Relationships created by relationship() are provided with declarative mixin classes exclusively using the declared_attr approach, eliminating any ambiguity which could arise when copying a relationship and its possibly column-bound contents. Below is an example which combines a foreign key column and a relationship so that two classes Foo and Bar can both be configured to reference a common target class via many-to-one: from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column from sqlalchemy.orm import relationship class Base(DeclarativeBase): pass class RefTargetMixin: target_id: Mapped[int] = mapped_column(ForeignKey("target.id")) @declared_attr def target(cls) -> Mapped["Target"]: return relationship("Target") class Foo(RefTargetMixin, Base): __tablename__ = "foo" id: Mapped[int] = mapped_column(primary_key=True) class Bar(RefTargetMixin, Base): __tablename__ = "bar" id: Mapped[int] = mapped_column(primary_key=True) class Target(Base): __tablename__ = "target" id: Mapped[int] = mapped_column(primary_key=True) Copy to clipboard With the above mapping, each of Foo and Bar contain a relationship to Target accessed along the .target attribute: >>> from sqlalchemy import select >>> print(select(Foo).join(Foo.target)) SELECT foo.id, foo.target_id FROM foo JOIN target ON target.id = foo.target_id >>> print(select(Bar).join(Bar.target)) SELECT bar.id, bar.target_id FROM bar JOIN target ON target.id = bar.target_id Copy to clipboard Special arguments such as relationship.primaryjoin may also be used within mixed-in classmethods, which often need to refer to the class that’s being mapped. For schemes that need to refer to locally mapped columns, in ordinary cases these columns are made available by Declarative as attributes on the mapped class which is passed as the cls argument to the decorated classmethod. Using this feature, we could for example rewrite the RefTargetMixin.target method using an explicit primaryjoin which refers to pending mapped columns on both Target and cls: class Target(Base): __tablename__ = "target" id: Mapped[int] = mapped_column(primary_key=True) class RefTargetMixin: target_id: Mapped[int] = mapped_column(ForeignKey("target.id")) @declared_attr def target(cls) -> Mapped["Target"]: # illustrates explicit 'primaryjoin' argument return relationship("Target", primaryjoin=Target.id == cls.target_id) Copy to clipboard Mixing in column_property() and other MapperProperty classes¶ Like relationship(), other MapperProperty subclasses such as column_property() also need to have class-local copies generated when used by mixins, so are also declared within functions that are decorated by declared_attr. Within the function, other ordinary mapped columns that were declared with mapped_column(), Mapped, or Column will be made available from the cls argument so that they may be used to compose new attributes, as in the example below which adds two columns together: from sqlalchemy.orm import column_property from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class SomethingMixin: x: Mapped[int] y: Mapped[int] @declared_attr def x_plus_y(cls) -> Mapped[int]: return column_property(cls.x + cls.y) class Something(SomethingMixin, Base): __tablename__ = "something" id: Mapped[int] = mapped_column(primary_key=True) Copy to clipboard Above, we may make use of Something.x_plus_y in a statement where it produces the full expression: >>> from sqlalchemy import select >>> print(select(Something.x_plus_y)) SELECT something.x + something.y AS anon_1 FROM something Copy to clipboard Tip The declared_attr decorator causes the decorated callable to behave exactly as a classmethod. However, typing tools like Pylance may not be able to recognize this, which can sometimes cause it to complain about access to the cls variable inside the body of the function. To resolve this issue when it occurs, the @classmethod decorator may be combined directly with declared_attr as: class SomethingMixin: x: Mapped[int] y: Mapped[int] @declared_attr @classmethod def x_plus_y(cls) -> Mapped[int]: return column_property(cls.x + cls.y) Copy to clipboard Added in version 2.0: - declared_attr can accommodate a function decorated with @classmethod to help with PEP 484 integration where needed. Using Mixins and Base Classes with Mapped Inheritance Patterns¶ When dealing with mapper inheritance patterns as documented at Mapping Class Inheritance Hierarchies, some additional capabilities are present when using declared_attr either with mixin classes, or when augmenting both mapped and un-mapped superclasses in a class hierarchy. When defining functions decorated by declared_attr on mixins or base classes to be interpreted by subclasses in a mapped inheritance hierarchy, there is an important distinction made between functions that generate the special names used by Declarative such as __tablename__, __mapper_args__ vs. those that may generate ordinary mapped attributes such as mapped_column() and relationship(). Functions that define Declarative directives are invoked for each subclass in a hierarchy, whereas functions that generate mapped attributes are invoked only for the first mapped superclass in a hierarchy. The rationale for this difference in behavior is based on the fact that mapped properties are already inheritable by classes, such as a particular column on a superclass’ mapped table should not be duplicated to that of a subclass as well, whereas elements that are specific to a particular class or its mapped table are not inheritable, such as the name of the table that is locally mapped. The difference in behavior between these two use cases is demonstrated in the following two sections. Using declared_attr() with inheriting Table and Mapper arguments¶ A common recipe with mixins is to create a def __tablename__(cls) function that generates a name for the mapped Table dynamically. This recipe can be used to generate table names for an inheriting mapper hierarchy as in the example below which creates a mixin that gives every class a simple table name based on class name. The recipe is illustrated below where a table name is generated for the Person mapped class and the Engineer subclass of Person, but not for the Manager subclass of Person: from typing import Optional from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class Tablename: @declared_attr.directive def __tablename__(cls) -> Optional[str]: return cls.__name__.lower() class Person(Tablename, Base): id: Mapped[int] = mapped_column(primary_key=True) discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True) primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} class Manager(Person): @declared_attr.directive def __tablename__(cls) -> Optional[str]: """override __tablename__ so that Manager is single-inheritance to Person""" return None __mapper_args__ = {"polymorphic_identity": "manager"} Copy to clipboard In the above example, both the Person base class as well as the Engineer class, being subclasses of the Tablename mixin class which generates new table names, will have a generated __tablename__ attribute, which to Declarative indicates that each class should have its own Table generated to which it will be mapped. For the Engineer subclass, the style of inheritance applied is joined table inheritance, as it will be mapped to a table engineer that joins to the base person table. Any other subclasses that inherit from Person will also have this style of inheritance applied by default (and within this particular example, would need to each specify a primary key column; more on that in the next section). By contrast, the Manager subclass of Person overrides the __tablename__ classmethod to return None. This indicates to Declarative that this class should not have a Table generated, and will instead make use exclusively of the base Table to which Person is mapped. For the Manager subclass, the style of inheritance applied is single table inheritance. The example above illustrates that Declarative directives like __tablename__ are necessarily applied to each subclass individually, as each mapped class needs to state which Table it will be mapped towards, or if it will map itself to the inheriting superclass’ Table. If we instead wanted to reverse the default table scheme illustrated above, so that single table inheritance were the default and joined table inheritance could be defined only when a __tablename__ directive were supplied to override it, we can make use of Declarative helpers within the top-most __tablename__() method, in this case a helper called has_inherited_table(). This function will return True if a superclass is already mapped to a Table. We may use this helper within the base-most __tablename__() classmethod so that we may conditionally return None for the table name, if a table is already present, thus indicating single-table inheritance for inheriting subclasses by default: from sqlalchemy import ForeignKey from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import has_inherited_table from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column class Base(DeclarativeBase): pass class Tablename: @declared_attr.directive def __tablename__(cls): if has_inherited_table(cls): return None return cls.__name__.lower() class Person(Tablename, Base): id: Mapped[int] = mapped_column(primary_key=True) discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): @declared_attr.directive def __tablename__(cls): """override __tablename__ so that Engineer is joined-inheritance to Person""" return cls.__name__.lower() id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True) primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} class Manager(Person): __mapper_args__ = {"polymorphic_identity": "manager"} Copy to clipboard Using declared_attr() to generate table-specific inheriting columns¶ In contrast to how __tablename__ and other special names are handled when used with declared_attr, when we mix in columns and properties (e.g. relationships, column properties, etc.), the function is invoked for the base class only in the hierarchy, unless the declared_attr directive is used in combination with the declared_attr.cascading sub-directive. Below, only the Person class will receive a column called id; the mapping will fail on Engineer, which is not given a primary key: class HasId: id: Mapped[int] = mapped_column(primary_key=True) class Person(HasId, Base): __tablename__ = "person" discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} # this mapping will fail, as there's no primary key class Engineer(Person): __tablename__ = "engineer" primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} Copy to clipboard It is usually the case in joined-table inheritance that we want distinctly named columns on each subclass. However in this case, we may want to have an id column on every table, and have them refer to each other via foreign key. We can achieve this as a mixin by using the declared_attr.cascading modifier, which indicates that the function should be invoked for each class in the hierarchy, in almost (see warning below) the same way as it does for __tablename__: class HasIdMixin: @declared_attr.cascading def id(cls) -> Mapped[int]: if has_inherited_table(cls): return mapped_column(ForeignKey("person.id"), primary_key=True) else: return mapped_column(Integer, primary_key=True) class Person(HasIdMixin, Base): __tablename__ = "person" discriminator: Mapped[str] __mapper_args__ = {"polymorphic_on": "discriminator"} class Engineer(Person): __tablename__ = "engineer" primary_language: Mapped[str] __mapper_args__ = {"polymorphic_identity": "engineer"} Copy to clipboard Warning The declared_attr.cascading feature currently does not allow for a subclass to override the attribute with a different function or value. This is a current limitation in the mechanics of how @declared_attr is resolved, and a warning is emitted if this condition is detected. This limitation only applies to ORM mapped columns, relationships, and other MapperProperty styles of attribute. It does not apply to Declarative directives such as __tablename__, __mapper_args__, etc., which resolve in a different way internally than that of declared_attr.cascading. Combining Table\/Mapper Arguments from Multiple Mixins¶ In the case of __table_args__ or __mapper_args__ specified with declarative mixins, you may want to combine some parameters from several mixins with those you wish to define on the class itself. The declared_attr decorator can be used here to create user-defined collation routines that pull from multiple collections: from sqlalchemy.orm import declared_attr class MySQLSettings: __table_args__ = {"mysql_engine": "InnoDB"} class MyOtherMixin: __table_args__ = {"info": "foo"} class MyModel(MySQLSettings, MyOtherMixin, Base): __tablename__ = "my_model" @declared_attr.directive def __table_args__(cls): args = dict() args.update(MySQLSettings.__table_args__) args.update(MyOtherMixin.__table_args__) return args id = mapped_column(Integer, primary_key=True) Copy to clipboard Creating Indexes and Constraints with Naming Conventions on Mixins¶ Using named constraints such as Index, UniqueConstraint, CheckConstraint, where each object is to be unique to a specific table descending from a mixin, requires that an individual instance of each object is created per actual mapped class. As a simple example, to define a named, potentially multicolumn Index that applies to all tables derived from a mixin, use the “inline” form of Index and establish it as part of __table_args__, using declared_attr to establish __table_args__() as a class method that will be invoked for each subclass: class MyMixin: a = mapped_column(Integer) b = mapped_column(Integer) @declared_attr.directive def __table_args__(cls): return (Index(f"test_idx_{cls.__tablename__}", "a", "b"),) class MyModelA(MyMixin, Base): __tablename__ = "table_a" id = mapped_column(Integer, primary_key=True) class MyModelB(MyMixin, Base): __tablename__ = "table_b" id = mapped_column(Integer, primary_key=True) Copy to clipboard The above example would generate two tables "table_a" and "table_b", with indexes "test_idx_table_a" and "test_idx_table_b" Typically, in modern SQLAlchemy we would use a naming convention, as documented at Configuring Constraint Naming Conventions. While naming conventions take place automatically using the MetaData.naming_convention as new Constraint objects are created, as this convention is applied at object construction time based on the parent Table for a particular Constraint, a distinct Constraint object needs to be created for each inheriting subclass with its own Table, again using declared_attr with __table_args__(), below illustrated using an abstract mapped base: from uuid import UUID from sqlalchemy import CheckConstraint from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy import UniqueConstraint from sqlalchemy.orm import DeclarativeBase from sqlalchemy.orm import declared_attr from sqlalchemy.orm import Mapped from sqlalchemy.orm import mapped_column constraint_naming_conventions = { "ix": "ix_%(column_0_label)s", "uq": "uq_%(table_name)s_%(column_0_name)s", "ck": "ck_%(table_name)s_%(constraint_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "pk": "pk_%(table_name)s", } class Base(DeclarativeBase): metadata = MetaData(naming_convention=constraint_naming_conventions) class MyAbstractBase(Base): __abstract__ = True @declared_attr.directive def __table_args__(cls): return ( UniqueConstraint("uuid"), CheckConstraint("x > 0 OR y < 100", name="xy_chk"), ) id: Mapped[int] = mapped_column(primary_key=True) uuid: Mapped[UUID] x: Mapped[int] y: Mapped[int] class ModelAlpha(MyAbstractBase): __tablename__ = "alpha" class ModelBeta(MyAbstractBase): __tablename__ = "beta" Copy to clipboard The above mapping will generate DDL that includes table-specific names for all constraints, including primary key, CHECK constraint, unique constraint: CREATE TABLE alpha ( id INTEGER NOT NULL, uuid CHAR(32) NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, CONSTRAINT pk_alpha PRIMARY KEY (id), CONSTRAINT uq_alpha_uuid UNIQUE (uuid), CONSTRAINT ck_alpha_xy_chk CHECK (x > 0 OR y < 100) ) CREATE TABLE beta ( id INTEGER NOT NULL, uuid CHAR(32) NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, CONSTRAINT pk_beta PRIMARY KEY (id), CONSTRAINT uq_beta_uuid UNIQUE (uuid), CONSTRAINT ck_beta_xy_chk CHECK (x > 0 OR y < 100) ) Copy to clipboard ``` Annotated ``` **Pattern 3:** The features above may all be demonstrated using a select() example: ``` select() ``` **Pattern 4:** Release: 2.0.45 current release | Release Date: December 9, 2025 SQLAlchemy 2.0 Documentation SQLAlchemy 2.0 Documentation current release Home | Download this Documentation Search terms: Connect your ecommerce to AI. Start selling with Checkout in ChatGPT - no replatforming Join Beta Now!saleor.ioAds by EthicalAds SQLAlchemy Core SQL Statements and Expressions API▼ Column Elements and Expressions Operator Reference SELECT and Related Constructs Insert, Updates, Deletes SQL and Generic Functions Custom SQL Constructs and Compilation Extension¶▼ Synopsis Dialect-specific compilation rules Compiling sub-elements of a custom expression construct▶ Cross Compiling between SQL and DDL compilers Changing the default compilation of existing constructs Changing Compilation of Types Subclassing Guidelines Enabling Caching Support for Custom Constructs Further Examples▶ “UTC timestamp” function “GREATEST” function “false” expression compiles() deregister() Expression Serializer Extension SQL Expression Language Foundational Constructs Visitor and Traversal Utilities Schema Definition Language SQL Datatype Objects Engine and Connection Use Core API Basics Project Versions Version 2.1 (development)Version 2.0Version 1.4Version 1.3 Search terms: Home | Download this Documentation Previous: SQL and Generic Functions Next: Expression Serializer Extension Up: Home SQLAlchemy Core SQL Statements and Expressions API On this page: Custom SQL Constructs and Compilation Extension Synopsis Dialect-specific compilation rules Compiling sub-elements of a custom expression construct Cross Compiling between SQL and DDL compilers Changing the default compilation of existing constructs Changing Compilation of Types Subclassing Guidelines Enabling Caching Support for Custom Constructs Further Examples “UTC timestamp” function “GREATEST” function “false” expression compiles() deregister() Custom SQL Constructs and Compilation Extension¶ Provides an API for creation of custom ClauseElements and compilers. Synopsis¶ Usage involves the creation of one or more ClauseElement subclasses and one or more callables defining its compilation: from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import ColumnClause class MyColumn(ColumnClause): inherit_cache = True @compiles(MyColumn) def compile_mycolumn(element, compiler, **kw): return "[%s]" % element.name Copy to clipboard Above, MyColumn extends ColumnClause, the base expression element for named column objects. The compiles decorator registers itself with the MyColumn class so that it is invoked when the object is compiled to a string: from sqlalchemy import select s = select(MyColumn("x"), MyColumn("y")) print(str(s)) Copy to clipboard Produces: SELECT [x], [y] Copy to clipboard Dialect-specific compilation rules¶ Compilers can also be made dialect-specific. The appropriate compiler will be invoked for the dialect in use: from sqlalchemy.schema import DDLElement class AlterColumn(DDLElement): inherit_cache = False def __init__(self, column, cmd): self.column = column self.cmd = cmd @compiles(AlterColumn) def visit_alter_column(element, compiler, **kw): return "ALTER COLUMN %s ..." % element.column.name @compiles(AlterColumn, "postgresql") def visit_alter_column(element, compiler, **kw): return "ALTER TABLE %s ALTER COLUMN %s ..." % ( element.table.name, element.column.name, ) Copy to clipboard The second visit_alter_table will be invoked when any postgresql dialect is used. Compiling sub-elements of a custom expression construct¶ The compiler argument is the Compiled object in use. This object can be inspected for any information about the in-progress compilation, including compiler.dialect, compiler.statement etc. The SQLCompiler and DDLCompiler both include a process() method which can be used for compilation of embedded attributes: from sqlalchemy.sql.expression import Executable, ClauseElement class InsertFromSelect(Executable, ClauseElement): inherit_cache = False def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return "INSERT INTO %s (%s)" % ( compiler.process(element.table, asfrom=True, **kw), compiler.process(element.select, **kw), ) insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5)) print(insert) Copy to clipboard Produces (formatted for readability): INSERT INTO mytable ( SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x > :x_1 ) Copy to clipboard Note The above InsertFromSelect construct is only an example, this actual functionality is already available using the Insert.from_select() method. Cross Compiling between SQL and DDL compilers¶ SQL and DDL constructs are each compiled using different base compilers - SQLCompiler and DDLCompiler. A common need is to access the compilation rules of SQL expressions from within a DDL expression. The DDLCompiler includes an accessor sql_compiler for this reason, such as below where we generate a CHECK constraint that embeds a SQL expression: @compiles(MyConstraint) def compile_my_constraint(constraint, ddlcompiler, **kw): kw["literal_binds"] = True return "CONSTRAINT %s CHECK (%s)" % ( constraint.name, ddlcompiler.sql_compiler.process(constraint.expression, **kw), ) Copy to clipboard Above, we add an additional flag to the process step as called by SQLCompiler.process(), which is the literal_binds flag. This indicates that any SQL expression which refers to a BindParameter object or other “literal” object such as those which refer to strings or integers should be rendered in-place, rather than being referred to as a bound parameter; when emitting DDL, bound parameters are typically not supported. Changing the default compilation of existing constructs¶ The compiler extension applies just as well to the existing constructs. When overriding the compilation of a built in SQL construct, the @compiles decorator is invoked upon the appropriate class (be sure to use the class, i.e. Insert or Select, instead of the creation function such as insert() or select()). Within the new compilation function, to get at the “original” compilation routine, use the appropriate visit_XXX method - this because compiler.process() will call upon the overriding routine and cause an endless loop. Such as, to add “prefix” to all insert statements: from sqlalchemy.sql.expression import Insert @compiles(Insert) def prefix_inserts(insert, compiler, **kw): return compiler.visit_insert(insert.prefix_with("some prefix"), **kw) Copy to clipboard The above compiler will prefix all INSERT statements with “some prefix” when compiled. Changing Compilation of Types¶ compiler works for types, too, such as below where we implement the MS-SQL specific ‘max’ keyword for String\/VARCHAR: @compiles(String, "mssql") @compiles(VARCHAR, "mssql") def compile_varchar(element, compiler, **kw): if element.length == "max": return "VARCHAR('max')" else: return compiler.visit_VARCHAR(element, **kw) foo = Table("foo", metadata, Column("data", VARCHAR("max"))) Copy to clipboard Subclassing Guidelines¶ A big part of using the compiler extension is subclassing SQLAlchemy expression constructs. To make this easier, the expression and schema packages feature a set of “bases” intended for common tasks. A synopsis is as follows: ClauseElement - This is the root expression class. Any SQL expression can be derived from this base, and is probably the best choice for longer constructs such as specialized INSERT statements. ColumnElement - The root of all “column-like” elements. Anything that you’d place in the “columns” clause of a SELECT statement (as well as order by and group by) can derive from this - the object will automatically have Python “comparison” behavior. ColumnElement classes want to have a type member which is expression’s return type. This can be established at the instance level in the constructor, or at the class level if its generally constant: class timestamp(ColumnElement): type = TIMESTAMP() inherit_cache = True Copy to clipboard FunctionElement - This is a hybrid of a ColumnElement and a “from clause” like object, and represents a SQL function or stored procedure type of call. Since most databases support statements along the line of “SELECT FROM ” FunctionElement adds in the ability to be used in the FROM clause of a select() construct: from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = "coalesce" inherit_cache = True @compiles(coalesce) def compile(element, compiler, **kw): return "coalesce(%s)" % compiler.process(element.clauses, **kw) @compiles(coalesce, "oracle") def compile(element, compiler, **kw): if len(element.clauses) > 2: raise TypeError( "coalesce only supports two arguments on " "Oracle Database" ) return "nvl(%s)" % compiler.process(element.clauses, **kw) Copy to clipboard ExecutableDDLElement - The root of all DDL expressions, like CREATE TABLE, ALTER TABLE, etc. Compilation of ExecutableDDLElement subclasses is issued by a DDLCompiler instead of a SQLCompiler. ExecutableDDLElement can also be used as an event hook in conjunction with event hooks like DDLEvents.before_create() and DDLEvents.after_create(), allowing the construct to be invoked automatically during CREATE TABLE and DROP TABLE sequences. See also Customizing DDL - contains examples of associating DDL objects (which are themselves ExecutableDDLElement instances) with DDLEvents event hooks. Executable - This is a mixin which should be used with any expression class that represents a “standalone” SQL statement that can be passed directly to an execute() method. It is already implicit within DDLElement and FunctionElement. Most of the above constructs also respond to SQL statement caching. A subclassed construct will want to define the caching behavior for the object, which usually means setting the flag inherit_cache to the value of False or True. See the next section Enabling Caching Support for Custom Constructs for background. Enabling Caching Support for Custom Constructs¶ SQLAlchemy as of version 1.4 includes a SQL compilation caching facility which will allow equivalent SQL constructs to cache their stringified form, along with other structural information used to fetch results from the statement. For reasons discussed at Object will not produce a cache key, Performance Implications, the implementation of this caching system takes a conservative approach towards including custom SQL constructs and\/or subclasses within the caching system. This includes that any user-defined SQL constructs, including all the examples for this extension, will not participate in caching by default unless they positively assert that they are able to do so. The HasCacheKey.inherit_cache attribute when set to True at the class level of a specific subclass will indicate that instances of this class may be safely cached, using the cache key generation scheme of the immediate superclass. This applies for example to the “synopsis” example indicated previously: class MyColumn(ColumnClause): inherit_cache = True @compiles(MyColumn) def compile_mycolumn(element, compiler, **kw): return "[%s]" % element.name Copy to clipboard Above, the MyColumn class does not include any new state that affects its SQL compilation; the cache key of MyColumn instances will make use of that of the ColumnClause superclass, meaning it will take into account the class of the object (MyColumn), the string name and datatype of the object: >>> MyColumn("some_name", String())._generate_cache_key() CacheKey( key=('0', , 'name', 'some_name', 'type', (, ('length', None), ('collation', None)) ), bindparams=[]) Copy to clipboard For objects that are likely to be used liberally as components within many larger statements, such as Column subclasses and custom SQL datatypes, it’s important that caching be enabled as much as possible, as this may otherwise negatively affect performance. An example of an object that does contain state which affects its SQL compilation is the one illustrated at Compiling sub-elements of a custom expression construct; this is an “INSERT FROM SELECT” construct that combines together a Table as well as a Select construct, each of which independently affect the SQL string generation of the construct. For this class, the example illustrates that it simply does not participate in caching: class InsertFromSelect(Executable, ClauseElement): inherit_cache = False def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return "INSERT INTO %s (%s)" % ( compiler.process(element.table, asfrom=True, **kw), compiler.process(element.select, **kw), ) Copy to clipboard While it is also possible that the above InsertFromSelect could be made to produce a cache key that is composed of that of the Table and Select components together, the API for this is not at the moment fully public. However, for an “INSERT FROM SELECT” construct, which is only used by itself for specific operations, caching is not as critical as in the previous example. For objects that are used in relative isolation and are generally standalone, such as custom DML constructs like an “INSERT FROM SELECT”, caching is generally less critical as the lack of caching for such a construct will have only localized implications for that specific operation. Further Examples¶ “UTC timestamp” function¶ A function that works like “CURRENT_TIMESTAMP” except applies the appropriate conversions so that the time is in UTC time. Timestamps are best stored in relational databases as UTC, without time zones. UTC so that your database doesn’t think time has gone backwards in the hour when daylight savings ends, without timezones because timezones are like character encodings - they’re best applied only at the endpoints of an application (i.e. convert to UTC upon user input, re-apply desired timezone upon display). For PostgreSQL and Microsoft SQL Server: from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime class utcnow(expression.FunctionElement): type = DateTime() inherit_cache = True @compiles(utcnow, "postgresql") def pg_utcnow(element, compiler, **kw): return "TIMEZONE('utc', CURRENT_TIMESTAMP)" @compiles(utcnow, "mssql") def ms_utcnow(element, compiler, **kw): return "GETUTCDATE()" Copy to clipboard Example usage: from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData metadata = MetaData() event = Table( "event", metadata, Column("id", Integer, primary_key=True), Column("description", String(50), nullable=False), Column("timestamp", DateTime, server_default=utcnow()), ) Copy to clipboard “GREATEST” function¶ The “GREATEST” function is given any number of arguments and returns the one that is of the highest value - its equivalent to Python’s max function. A SQL standard version versus a CASE based version which only accommodates two arguments: from sqlalchemy.sql import expression, case from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import Numeric class greatest(expression.FunctionElement): type = Numeric() name = "greatest" inherit_cache = True @compiles(greatest) def default_greatest(element, compiler, **kw): return compiler.visit_function(element) @compiles(greatest, "sqlite") @compiles(greatest, "mssql") @compiles(greatest, "oracle") def case_greatest(element, compiler, **kw): arg1, arg2 = list(element.clauses) return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw) Copy to clipboard Example usage: Session.query(Account).filter( greatest(Account.checking_balance, Account.savings_balance) > 10000 ) Copy to clipboard “false” expression¶ Render a “false” constant expression, rendering as “0” on platforms that don’t have a “false” constant: from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles class sql_false(expression.ColumnElement): inherit_cache = True @compiles(sql_false) def default_false(element, compiler, **kw): return "false" @compiles(sql_false, "mssql") @compiles(sql_false, "mysql") @compiles(sql_false, "oracle") def int_false(element, compiler, **kw): return "0" Copy to clipboard Example usage: from sqlalchemy import select, union_all exp = union_all( select(users.c.name, sql_false().label("enrolled")), select(customers.c.name, customers.c.enrolled), ) Copy to clipboard Object Name Description compiles(class_, *specs) Register a function as a compiler for a given ClauseElement type. deregister(class_) Remove all custom compilers associated with a given ClauseElement type. function sqlalchemy.ext.compiler.compiles(class_: Type[Any], *specs: str) → Callable[[_F], _F]¶ Register a function as a compiler for a given ClauseElement type. function sqlalchemy.ext.compiler.deregister(class_: Type[Any]) → None¶ Remove all custom compilers associated with a given ClauseElement type. Previous: SQL and Generic Functions Next: Expression Serializer Extension © Copyright 2007-2025, the SQLAlchemy authors and contributors. flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari. Created using Sphinx 9.1.0. Documentation last generated: Tue 13 Jan 2026 01:05:54 PM EST ``` compiles() ``` **Pattern 5:** Custom SQL Constructs and Compilation Extension¶ Provides an API for creation of custom ClauseElements and compilers. Synopsis¶ Usage involves the creation of one or more ClauseElement subclasses and one or more callables defining its compilation: from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import ColumnClause class MyColumn(ColumnClause): inherit_cache = True @compiles(MyColumn) def compile_mycolumn(element, compiler, **kw): return "[%s]" % element.name Copy to clipboard Above, MyColumn extends ColumnClause, the base expression element for named column objects. The compiles decorator registers itself with the MyColumn class so that it is invoked when the object is compiled to a string: from sqlalchemy import select s = select(MyColumn("x"), MyColumn("y")) print(str(s)) Copy to clipboard Produces: SELECT [x], [y] Copy to clipboard Dialect-specific compilation rules¶ Compilers can also be made dialect-specific. The appropriate compiler will be invoked for the dialect in use: from sqlalchemy.schema import DDLElement class AlterColumn(DDLElement): inherit_cache = False def __init__(self, column, cmd): self.column = column self.cmd = cmd @compiles(AlterColumn) def visit_alter_column(element, compiler, **kw): return "ALTER COLUMN %s ..." % element.column.name @compiles(AlterColumn, "postgresql") def visit_alter_column(element, compiler, **kw): return "ALTER TABLE %s ALTER COLUMN %s ..." % ( element.table.name, element.column.name, ) Copy to clipboard The second visit_alter_table will be invoked when any postgresql dialect is used. Compiling sub-elements of a custom expression construct¶ The compiler argument is the Compiled object in use. This object can be inspected for any information about the in-progress compilation, including compiler.dialect, compiler.statement etc. The SQLCompiler and DDLCompiler both include a process() method which can be used for compilation of embedded attributes: from sqlalchemy.sql.expression import Executable, ClauseElement class InsertFromSelect(Executable, ClauseElement): inherit_cache = False def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return "INSERT INTO %s (%s)" % ( compiler.process(element.table, asfrom=True, **kw), compiler.process(element.select, **kw), ) insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5)) print(insert) Copy to clipboard Produces (formatted for readability): INSERT INTO mytable ( SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x > :x_1 ) Copy to clipboard Note The above InsertFromSelect construct is only an example, this actual functionality is already available using the Insert.from_select() method. Cross Compiling between SQL and DDL compilers¶ SQL and DDL constructs are each compiled using different base compilers - SQLCompiler and DDLCompiler. A common need is to access the compilation rules of SQL expressions from within a DDL expression. The DDLCompiler includes an accessor sql_compiler for this reason, such as below where we generate a CHECK constraint that embeds a SQL expression: @compiles(MyConstraint) def compile_my_constraint(constraint, ddlcompiler, **kw): kw["literal_binds"] = True return "CONSTRAINT %s CHECK (%s)" % ( constraint.name, ddlcompiler.sql_compiler.process(constraint.expression, **kw), ) Copy to clipboard Above, we add an additional flag to the process step as called by SQLCompiler.process(), which is the literal_binds flag. This indicates that any SQL expression which refers to a BindParameter object or other “literal” object such as those which refer to strings or integers should be rendered in-place, rather than being referred to as a bound parameter; when emitting DDL, bound parameters are typically not supported. Changing the default compilation of existing constructs¶ The compiler extension applies just as well to the existing constructs. When overriding the compilation of a built in SQL construct, the @compiles decorator is invoked upon the appropriate class (be sure to use the class, i.e. Insert or Select, instead of the creation function such as insert() or select()). Within the new compilation function, to get at the “original” compilation routine, use the appropriate visit_XXX method - this because compiler.process() will call upon the overriding routine and cause an endless loop. Such as, to add “prefix” to all insert statements: from sqlalchemy.sql.expression import Insert @compiles(Insert) def prefix_inserts(insert, compiler, **kw): return compiler.visit_insert(insert.prefix_with("some prefix"), **kw) Copy to clipboard The above compiler will prefix all INSERT statements with “some prefix” when compiled. Changing Compilation of Types¶ compiler works for types, too, such as below where we implement the MS-SQL specific ‘max’ keyword for String\/VARCHAR: @compiles(String, "mssql") @compiles(VARCHAR, "mssql") def compile_varchar(element, compiler, **kw): if element.length == "max": return "VARCHAR('max')" else: return compiler.visit_VARCHAR(element, **kw) foo = Table("foo", metadata, Column("data", VARCHAR("max"))) Copy to clipboard Subclassing Guidelines¶ A big part of using the compiler extension is subclassing SQLAlchemy expression constructs. To make this easier, the expression and schema packages feature a set of “bases” intended for common tasks. A synopsis is as follows: ClauseElement - This is the root expression class. Any SQL expression can be derived from this base, and is probably the best choice for longer constructs such as specialized INSERT statements. ColumnElement - The root of all “column-like” elements. Anything that you’d place in the “columns” clause of a SELECT statement (as well as order by and group by) can derive from this - the object will automatically have Python “comparison” behavior. ColumnElement classes want to have a type member which is expression’s return type. This can be established at the instance level in the constructor, or at the class level if its generally constant: class timestamp(ColumnElement): type = TIMESTAMP() inherit_cache = True Copy to clipboard FunctionElement - This is a hybrid of a ColumnElement and a “from clause” like object, and represents a SQL function or stored procedure type of call. Since most databases support statements along the line of “SELECT FROM ” FunctionElement adds in the ability to be used in the FROM clause of a select() construct: from sqlalchemy.sql.expression import FunctionElement class coalesce(FunctionElement): name = "coalesce" inherit_cache = True @compiles(coalesce) def compile(element, compiler, **kw): return "coalesce(%s)" % compiler.process(element.clauses, **kw) @compiles(coalesce, "oracle") def compile(element, compiler, **kw): if len(element.clauses) > 2: raise TypeError( "coalesce only supports two arguments on " "Oracle Database" ) return "nvl(%s)" % compiler.process(element.clauses, **kw) Copy to clipboard ExecutableDDLElement - The root of all DDL expressions, like CREATE TABLE, ALTER TABLE, etc. Compilation of ExecutableDDLElement subclasses is issued by a DDLCompiler instead of a SQLCompiler. ExecutableDDLElement can also be used as an event hook in conjunction with event hooks like DDLEvents.before_create() and DDLEvents.after_create(), allowing the construct to be invoked automatically during CREATE TABLE and DROP TABLE sequences. See also Customizing DDL - contains examples of associating DDL objects (which are themselves ExecutableDDLElement instances) with DDLEvents event hooks. Executable - This is a mixin which should be used with any expression class that represents a “standalone” SQL statement that can be passed directly to an execute() method. It is already implicit within DDLElement and FunctionElement. Most of the above constructs also respond to SQL statement caching. A subclassed construct will want to define the caching behavior for the object, which usually means setting the flag inherit_cache to the value of False or True. See the next section Enabling Caching Support for Custom Constructs for background. Enabling Caching Support for Custom Constructs¶ SQLAlchemy as of version 1.4 includes a SQL compilation caching facility which will allow equivalent SQL constructs to cache their stringified form, along with other structural information used to fetch results from the statement. For reasons discussed at Object will not produce a cache key, Performance Implications, the implementation of this caching system takes a conservative approach towards including custom SQL constructs and\/or subclasses within the caching system. This includes that any user-defined SQL constructs, including all the examples for this extension, will not participate in caching by default unless they positively assert that they are able to do so. The HasCacheKey.inherit_cache attribute when set to True at the class level of a specific subclass will indicate that instances of this class may be safely cached, using the cache key generation scheme of the immediate superclass. This applies for example to the “synopsis” example indicated previously: class MyColumn(ColumnClause): inherit_cache = True @compiles(MyColumn) def compile_mycolumn(element, compiler, **kw): return "[%s]" % element.name Copy to clipboard Above, the MyColumn class does not include any new state that affects its SQL compilation; the cache key of MyColumn instances will make use of that of the ColumnClause superclass, meaning it will take into account the class of the object (MyColumn), the string name and datatype of the object: >>> MyColumn("some_name", String())._generate_cache_key() CacheKey( key=('0', , 'name', 'some_name', 'type', (, ('length', None), ('collation', None)) ), bindparams=[]) Copy to clipboard For objects that are likely to be used liberally as components within many larger statements, such as Column subclasses and custom SQL datatypes, it’s important that caching be enabled as much as possible, as this may otherwise negatively affect performance. An example of an object that does contain state which affects its SQL compilation is the one illustrated at Compiling sub-elements of a custom expression construct; this is an “INSERT FROM SELECT” construct that combines together a Table as well as a Select construct, each of which independently affect the SQL string generation of the construct. For this class, the example illustrates that it simply does not participate in caching: class InsertFromSelect(Executable, ClauseElement): inherit_cache = False def __init__(self, table, select): self.table = table self.select = select @compiles(InsertFromSelect) def visit_insert_from_select(element, compiler, **kw): return "INSERT INTO %s (%s)" % ( compiler.process(element.table, asfrom=True, **kw), compiler.process(element.select, **kw), ) Copy to clipboard While it is also possible that the above InsertFromSelect could be made to produce a cache key that is composed of that of the Table and Select components together, the API for this is not at the moment fully public. However, for an “INSERT FROM SELECT” construct, which is only used by itself for specific operations, caching is not as critical as in the previous example. For objects that are used in relative isolation and are generally standalone, such as custom DML constructs like an “INSERT FROM SELECT”, caching is generally less critical as the lack of caching for such a construct will have only localized implications for that specific operation. Further Examples¶ “UTC timestamp” function¶ A function that works like “CURRENT_TIMESTAMP” except applies the appropriate conversions so that the time is in UTC time. Timestamps are best stored in relational databases as UTC, without time zones. UTC so that your database doesn’t think time has gone backwards in the hour when daylight savings ends, without timezones because timezones are like character encodings - they’re best applied only at the endpoints of an application (i.e. convert to UTC upon user input, re-apply desired timezone upon display). For PostgreSQL and Microsoft SQL Server: from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import DateTime class utcnow(expression.FunctionElement): type = DateTime() inherit_cache = True @compiles(utcnow, "postgresql") def pg_utcnow(element, compiler, **kw): return "TIMEZONE('utc', CURRENT_TIMESTAMP)" @compiles(utcnow, "mssql") def ms_utcnow(element, compiler, **kw): return "GETUTCDATE()" Copy to clipboard Example usage: from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData metadata = MetaData() event = Table( "event", metadata, Column("id", Integer, primary_key=True), Column("description", String(50), nullable=False), Column("timestamp", DateTime, server_default=utcnow()), ) Copy to clipboard “GREATEST” function¶ The “GREATEST” function is given any number of arguments and returns the one that is of the highest value - its equivalent to Python’s max function. A SQL standard version versus a CASE based version which only accommodates two arguments: from sqlalchemy.sql import expression, case from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import Numeric class greatest(expression.FunctionElement): type = Numeric() name = "greatest" inherit_cache = True @compiles(greatest) def default_greatest(element, compiler, **kw): return compiler.visit_function(element) @compiles(greatest, "sqlite") @compiles(greatest, "mssql") @compiles(greatest, "oracle") def case_greatest(element, compiler, **kw): arg1, arg2 = list(element.clauses) return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw) Copy to clipboard Example usage: Session.query(Account).filter( greatest(Account.checking_balance, Account.savings_balance) > 10000 ) Copy to clipboard “false” expression¶ Render a “false” constant expression, rendering as “0” on platforms that don’t have a “false” constant: from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles class sql_false(expression.ColumnElement): inherit_cache = True @compiles(sql_false) def default_false(element, compiler, **kw): return "false" @compiles(sql_false, "mssql") @compiles(sql_false, "mysql") @compiles(sql_false, "oracle") def int_false(element, compiler, **kw): return "0" Copy to clipboard Example usage: from sqlalchemy import select, union_all exp = union_all( select(users.c.name, sql_false().label("enrolled")), select(customers.c.name, customers.c.enrolled), ) Copy to clipboard Object Name Description compiles(class_, *specs) Register a function as a compiler for a given ClauseElement type. deregister(class_) Remove all custom compilers associated with a given ClauseElement type. function sqlalchemy.ext.compiler.compiles(class_: Type[Any], *specs: str) → Callable[[_F], _F]¶ Register a function as a compiler for a given ClauseElement type. function sqlalchemy.ext.compiler.deregister(class_: Type[Any]) → None¶ Remove all custom compilers associated with a given ClauseElement type. ``` ClauseElement ``` **Pattern 6:** Example usage: ``` Session.query(Account).filter( greatest(Account.checking_balance, Account.savings_balance) > 10000 ) ``` **Pattern 7:** Release: 2.0.45 current release | Release Date: December 9, 2025 SQLAlchemy 2.0 Documentation SQLAlchemy 2.0 Documentation current release Home | Download this Documentation Search terms: AI and large language models (LLMs) are revolutionizing the way businesses use and process data.www.mongodb.comAds by EthicalAds SQLAlchemy ORM ORM Quick Start ORM Mapped Class Configuration Relationship Configuration ORM Querying Guide Using the Session▼ Session Basics State Management Cascades¶▼ save-update▶ Behavior of save-update cascade with bi-directional relationships delete▶ Using delete cascade with many-to-many relationships Using foreign key ON DELETE cascade with ORM relationships Using foreign key ON DELETE with many-to-many relationships delete-orphan merge refresh-expire expunge Notes on Delete - Deleting Objects Referenced from Collections and Scalar Relationships Transactions and Connection Management Additional Persistence Techniques Contextual\/Thread-local Sessions Tracking queries, object and Session Changes with Events Session API Events and Internals ORM Extensions ORM Examples Project Versions Version 2.1 (development)Version 2.0Version 1.4Version 1.3 Search terms: Home | Download this Documentation Previous: State Management Next: Transactions and Connection Management Up: Home SQLAlchemy ORM Using the Session On this page: Cascades save-update Behavior of save-update cascade with bi-directional relationships delete Using delete cascade with many-to-many relationships Using foreign key ON DELETE cascade with ORM relationships Using foreign key ON DELETE with many-to-many relationships delete-orphan merge refresh-expire expunge Notes on Delete - Deleting Objects Referenced from Collections and Scalar Relationships Cascades¶ Mappers support the concept of configurable cascade behavior on relationship() constructs. This refers to how operations performed on a “parent” object relative to a particular Session should be propagated to items referred to by that relationship (e.g. “child” objects), and is affected by the relationship.cascade option. The default behavior of cascade is limited to cascades of the so-called save-update and merge settings. The typical “alternative” setting for cascade is to add the delete and delete-orphan options; these settings are appropriate for related objects which only exist as long as they are attached to their parent, and are otherwise deleted. Cascade behavior is configured using the relationship.cascade option on relationship(): class Order(Base): __tablename__ = "order" items = relationship("Item", cascade="all, delete-orphan") customer = relationship("User", cascade="save-update") Copy to clipboard To set cascades on a backref, the same flag can be used with the backref() function, which ultimately feeds its arguments back into relationship(): class Item(Base): __tablename__ = "item" order = relationship( "Order", backref=backref("items", cascade="all, delete-orphan") ) Copy to clipboard The Origins of Cascade SQLAlchemy’s notion of cascading behavior on relationships, as well as the options to configure them, are primarily derived from the similar feature in the Hibernate ORM; Hibernate refers to “cascade” in a few places such as in Example: Parent\/Child. If cascades are confusing, we’ll refer to their conclusion, stating “The sections we have just covered can be a bit confusing. However, in practice, it all works out nicely.” The default value of relationship.cascade is save-update, merge. The typical alternative setting for this parameter is either all or more commonly all, delete-orphan. The all symbol is a synonym for save-update, merge, refresh-expire, expunge, delete, and using it in conjunction with delete-orphan indicates that the child object should follow along with its parent in all cases, and be deleted once it is no longer associated with that parent. Warning The all cascade option implies the refresh-expire cascade setting which may not be desirable when using the Asynchronous I\/O (asyncio) extension, as it will expire related objects more aggressively than is typically appropriate in an explicit IO context. See the notes at Preventing Implicit IO when Using AsyncSession for further background. The list of available values which can be specified for the relationship.cascade parameter are described in the following subsections. save-update¶ save-update cascade indicates that when an object is placed into a Session via Session.add(), all the objects associated with it via this relationship() should also be added to that same Session. Suppose we have an object user1 with two related objects address1, address2: >>> user1 = User() >>> address1, address2 = Address(), Address() >>> user1.addresses = [address1, address2] Copy to clipboard If we add user1 to a Session, it will also add address1, address2 implicitly: >>> sess = Session() >>> sess.add(user1) >>> address1 in sess True Copy to clipboard save-update cascade also affects attribute operations for objects that are already present in a Session. If we add a third object, address3 to the user1.addresses collection, it becomes part of the state of that Session: >>> address3 = Address() >>> user1.addresses.append(address3) >>> address3 in sess True Copy to clipboard A save-update cascade can exhibit surprising behavior when removing an item from a collection or de-associating an object from a scalar attribute. In some cases, the orphaned objects may still be pulled into the ex-parent’s Session; this is so that the flush process may handle that related object appropriately. This case usually only arises if an object is removed from one Session and added to another: >>> user1 = sess1.scalars(select(User).filter_by(id=1)).first() >>> address1 = user1.addresses[0] >>> sess1.close() # user1, address1 no longer associated with sess1 >>> user1.addresses.remove(address1) # address1 no longer associated with user1 >>> sess2 = Session() >>> sess2.add(user1) # ... but it still gets added to the new session, >>> address1 in sess2 # because it's still "pending" for flush True Copy to clipboard The save-update cascade is on by default, and is typically taken for granted; it simplifies code by allowing a single call to Session.add() to register an entire structure of objects within that Session at once. While it can be disabled, there is usually not a need to do so. Behavior of save-update cascade with bi-directional relationships¶ The save-update cascade takes place uni-directionally in the context of a bi-directional relationship, i.e. when using the relationship.back_populates or relationship.backref parameters to create two separate relationship() objects which refer to each other. An object that’s not associated with a Session, when assigned to an attribute or collection on a parent object that is associated with a Session, will be automatically added to that same Session. However, the same operation in reverse will not have this effect; an object that’s not associated with a Session, upon which a child object that is associated with a Session is assigned, will not result in an automatic addition of that parent object to the Session. The overall subject of this behavior is known as “cascade backrefs”, and represents a change in behavior that was standardized as of SQLAlchemy 2.0. To illustrate, given a mapping of Order objects which relate bi-directionally to a series of Item objects via relationships Order.items and Item.order: mapper_registry.map_imperatively( Order, order_table, properties={"items": relationship(Item, back_populates="order")}, ) mapper_registry.map_imperatively( Item, item_table, properties={"order": relationship(Order, back_populates="items")}, ) Copy to clipboard If an Order is already associated with a Session, and an Item object is then created and appended to the Order.items collection of that Order, the Item will be automatically cascaded into that same Session: >>> o1 = Order() >>> session.add(o1) >>> o1 in session True >>> i1 = Item() >>> o1.items.append(i1) >>> o1 is i1.order True >>> i1 in session True Copy to clipboard Above, the bidirectional nature of Order.items and Item.order means that appending to Order.items also assigns to Item.order. At the same time, the save-update cascade allowed for the Item object to be added to the same Session which the parent Order was already associated. However, if the operation above is performed in the reverse direction, where Item.order is assigned rather than appending directly to Order.item, the cascade operation into the Session will not take place automatically, even though the object assignments Order.items and Item.order will be in the same state as in the previous example: >>> o1 = Order() >>> session.add(o1) >>> o1 in session True >>> i1 = Item() >>> i1.order = o1 >>> i1 in order.items True >>> i1 in session False Copy to clipboard In the above case, after the Item object is created and all the desired state is set upon it, it should then be added to the Session explicitly: >>> session.add(i1) Copy to clipboard In older versions of SQLAlchemy, the save-update cascade would occur bidirectionally in all cases. It was then made optional using an option known as cascade_backrefs. Finally, in SQLAlchemy 1.4 the old behavior was deprecated and the cascade_backrefs option was removed in SQLAlchemy 2.0. The rationale is that users generally do not find it intuitive that assigning to an attribute on an object, illustrated above as the assignment of i1.order = o1, would alter the persistence state of that object i1 such that it’s now pending within a Session, and there would frequently be subsequent issues where autoflush would prematurely flush the object and cause errors, in those cases where the given object was still being constructed and wasn’t in a ready state to be flushed. The option to select between uni-directional and bi-directional behaviors was also removed, as this option created two slightly different ways of working, adding to the overall learning curve of the ORM as well as to the documentation and user support burden. See also cascade_backrefs behavior deprecated for removal in 2.0 - background on the change in behavior for “cascade backrefs” delete¶ The delete cascade indicates that when a “parent” object is marked for deletion, its related “child” objects should also be marked for deletion. If for example we have a relationship User.addresses with delete cascade configured: class User(Base): # ... addresses = relationship("Address", cascade="all, delete") Copy to clipboard If using the above mapping, we have a User object and two related Address objects: >>> user1 = sess1.scalars(select(User).filter_by(id=1)).first() >>> address1, address2 = user1.addresses Copy to clipboard If we mark user1 for deletion, after the flush operation proceeds, address1 and address2 will also be deleted: >>> sess.delete(user1) >>> sess.commit() DELETE FROM address WHERE address.id = ? ((1,), (2,)) DELETE FROM user WHERE user.id = ? (1,) COMMIT Copy to clipboard Alternatively, if our User.addresses relationship does not have delete cascade, SQLAlchemy’s default behavior is to instead de-associate address1 and address2 from user1 by setting their foreign key reference to NULL. Using a mapping as follows: class User(Base): # ... addresses = relationship("Address") Copy to clipboard Upon deletion of a parent User object, the rows in address are not deleted, but are instead de-associated: >>> sess.delete(user1) >>> sess.commit() UPDATE address SET user_id=? WHERE address.id = ? (None, 1) UPDATE address SET user_id=? WHERE address.id = ? (None, 2) DELETE FROM user WHERE user.id = ? (1,) COMMIT Copy to clipboard delete cascade on one-to-many relationships is often combined with delete-orphan cascade, which will emit a DELETE for the related row if the “child” object is deassociated from the parent. The combination of delete and delete-orphan cascade covers both situations where SQLAlchemy has to decide between setting a foreign key column to NULL versus deleting the row entirely. The feature by default works completely independently of database-configured FOREIGN KEY constraints that may themselves configure CASCADE behavior. In order to integrate more efficiently with this configuration, additional directives described at Using foreign key ON DELETE cascade with ORM relationships should be used. Warning Note that the ORM’s “delete” and “delete-orphan” behavior applies only to the use of the Session.delete() method to mark individual ORM instances for deletion within the unit of work process. It does not apply to “bulk” deletes, which would be emitted using the delete() construct as illustrated at ORM UPDATE and DELETE with Custom WHERE Criteria. See Important Notes and Caveats for ORM-Enabled Update and Delete for additional background. See also Using foreign key ON DELETE cascade with ORM relationships Using delete cascade with many-to-many relationships delete-orphan Using delete cascade with many-to-many relationships¶ The cascade="all, delete" option works equally well with a many-to-many relationship, one that uses relationship.secondary to indicate an association table. When a parent object is deleted, and therefore de-associated with its related objects, the unit of work process will normally delete rows from the association table, but leave the related objects intact. When combined with cascade="all, delete", additional DELETE statements will take place for the child rows themselves. The following example adapts that of Many To Many to illustrate the cascade="all, delete" setting on one side of the association: association_table = Table( "association", Base.metadata, Column("left_id", Integer, ForeignKey("left.id")), Column("right_id", Integer, ForeignKey("right.id")), ) class Parent(Base): __tablename__ = "left" id = mapped_column(Integer, primary_key=True) children = relationship( "Child", secondary=association_table, back_populates="parents", cascade="all, delete", ) class Child(Base): __tablename__ = "right" id = mapped_column(Integer, primary_key=True) parents = relationship( "Parent", secondary=association_table, back_populates="children", ) Copy to clipboard Above, when a Parent object is marked for deletion using Session.delete(), the flush process will as usual delete the associated rows from the association table, however per cascade rules it will also delete all related Child rows. Warning If the above cascade="all, delete" setting were configured on both relationships, then the cascade action would continue cascading through all Parent and Child objects, loading each children and parents collection encountered and deleting everything that’s connected. It is typically not desirable for “delete” cascade to be configured bidirectionally. See also Deleting Rows from the Many to Many Table Using foreign key ON DELETE with many-to-many relationships Using foreign key ON DELETE cascade with ORM relationships¶ The behavior of SQLAlchemy’s “delete” cascade overlaps with the ON DELETE feature of a database FOREIGN KEY constraint. SQLAlchemy allows configuration of these schema-level DDL behaviors using the ForeignKey and ForeignKeyConstraint constructs; usage of these objects in conjunction with Table metadata is described at ON UPDATE and ON DELETE. In order to use ON DELETE foreign key cascades in conjunction with relationship(), it’s important to note first and foremost that the relationship.cascade setting must still be configured to match the desired “delete” or “set null” behavior (using delete cascade or leaving it omitted), so that whether the ORM or the database level constraints will handle the task of actually modifying the data in the database, the ORM will still be able to appropriately track the state of locally present objects that may be affected. There is then an additional option on relationship() which indicates the degree to which the ORM should try to run DELETE\/UPDATE operations on related rows itself, vs. how much it should rely upon expecting the database-side FOREIGN KEY constraint cascade to handle the task; this is the relationship.passive_deletes parameter and it accepts options False (the default), True and "all". The most typical example is that where child rows are to be deleted when parent rows are deleted, and that ON DELETE CASCADE is configured on the relevant FOREIGN KEY constraint as well: class Parent(Base): __tablename__ = "parent" id = mapped_column(Integer, primary_key=True) children = relationship( "Child", back_populates="parent", cascade="all, delete", passive_deletes=True, ) class Child(Base): __tablename__ = "child" id = mapped_column(Integer, primary_key=True) parent_id = mapped_column(Integer, ForeignKey("parent.id", ondelete="CASCADE")) parent = relationship("Parent", back_populates="children") Copy to clipboard The behavior of the above configuration when a parent row is deleted is as follows: The application calls session.delete(my_parent), where my_parent is an instance of Parent. When the Session next flushes changes to the database, all of the currently loaded items within the my_parent.children collection are deleted by the ORM, meaning a DELETE statement is emitted for each record. If the my_parent.children collection is unloaded, then no DELETE statements are emitted. If the relationship.passive_deletes flag were not set on this relationship(), then a SELECT statement for unloaded Child objects would have been emitted. A DELETE statement is then emitted for the my_parent row itself. The database-level ON DELETE CASCADE setting ensures that all rows in child which refer to the affected row in parent are also deleted. The Parent instance referred to by my_parent, as well as all instances of Child that were related to this object and were loaded (i.e. step 2 above took place), are de-associated from the Session. Note To use “ON DELETE CASCADE”, the underlying database engine must support FOREIGN KEY constraints and they must be enforcing: When using MySQL, an appropriate storage engine must be selected. See CREATE TABLE arguments including Storage Engines for details. When using SQLite, foreign key support must be enabled explicitly. See Foreign Key Support for details. Notes on Passive Deletes It is important to note the differences between the ORM and the relational database’s notion of “cascade” as well as how they integrate: A database level ON DELETE cascade is configured effectively on the many-to-one side of the relationship; that is, we configure it relative to the FOREIGN KEY constraint that is the “many” side of a relationship. At the ORM level, this direction is reversed. SQLAlchemy handles the deletion of “child” objects relative to a “parent” from the “parent” side, which means that delete and delete-orphan cascade are configured on the one-to-many side. Database level foreign keys with no ON DELETE setting are often used to prevent a parent row from being removed, as it would necessarily leave an unhandled related row present. If this behavior is desired in a one-to-many relationship, SQLAlchemy’s default behavior of setting a foreign key to NULL can be caught in one of two ways: The easiest and most common is just to set the foreign-key-holding column to NOT NULL at the database schema level. An attempt by SQLAlchemy to set the column to NULL will fail with a simple NOT NULL constraint exception. The other, more special case way is to set the relationship.passive_deletes flag to the string "all". This has the effect of entirely disabling SQLAlchemy’s behavior of setting the foreign key column to NULL, and a DELETE will be emitted for the parent row without any affect on the child row, even if the child row is present in memory. This may be desirable in the case when database-level foreign key triggers, either special ON DELETE settings or otherwise, need to be activated in all cases when a parent row is deleted. Database level ON DELETE cascade is generally much more efficient than relying upon the “cascade” delete feature of SQLAlchemy. The database can chain a series of cascade operations across many relationships at once; e.g. if row A is deleted, all the related rows in table B can be deleted, and all the C rows related to each of those B rows, and on and on, all within the scope of a single DELETE statement. SQLAlchemy on the other hand, in order to support the cascading delete operation fully, has to individually load each related collection in order to target all rows that then may have further related collections. That is, SQLAlchemy isn’t sophisticated enough to emit a DELETE for all those related rows at once within this context. SQLAlchemy doesn’t need to be this sophisticated, as we instead provide smooth integration with the database’s own ON DELETE functionality, by using the relationship.passive_deletes option in conjunction with properly configured foreign key constraints. Under this behavior, SQLAlchemy only emits DELETE for those rows that are already locally present in the Session; for any collections that are unloaded, it leaves them to the database to handle, rather than emitting a SELECT for them. The section Using foreign key ON DELETE cascade with ORM relationships provides an example of this use. While database-level ON DELETE functionality works only on the “many” side of a relationship, SQLAlchemy’s “delete” cascade has limited ability to operate in the reverse direction as well, meaning it can be configured on the “many” side to delete an object on the “one” side when the reference on the “many” side is deleted. However this can easily result in constraint violations if there are other objects referring to this “one” side from the “many”, so it typically is only useful when a relationship is in fact a “one to one”. The relationship.single_parent flag should be used to establish an in-Python assertion for this case. Using foreign key ON DELETE with many-to-many relationships¶ As described at Using delete cascade with many-to-many relationships, “delete” cascade works for many-to-many relationships as well. To make use of ON DELETE CASCADE foreign keys in conjunction with many to many, FOREIGN KEY directives are configured on the association table. These directives can handle the task of automatically deleting from the association table, but cannot accommodate the automatic deletion of the related objects themselves. In this case, the relationship.passive_deletes directive can save us some additional SELECT statements during a delete operation but there are still some collections that the ORM will continue to load, in order to locate affected child objects and handle them correctly. Note Hypothetical optimizations to this could include a single DELETE statement against all parent-associated rows of the association table at once, then use RETURNING to locate affected related child rows, however this is not currently part of the ORM unit of work implementation. In this configuration, we configure ON DELETE CASCADE on both foreign key constraints of the association table. We configure cascade="all, delete" on the parent->child side of the relationship, and we can then configure passive_deletes=True on the other side of the bidirectional relationship as illustrated below: association_table = Table( "association", Base.metadata, Column("left_id", Integer, ForeignKey("left.id", ondelete="CASCADE")), Column("right_id", Integer, ForeignKey("right.id", ondelete="CASCADE")), ) class Parent(Base): __tablename__ = "left" id = mapped_column(Integer, primary_key=True) children = relationship( "Child", secondary=association_table, back_populates="parents", cascade="all, delete", ) class Child(Base): __tablename__ = "right" id = mapped_column(Integer, primary_key=True) parents = relationship( "Parent", secondary=association_table, back_populates="children", passive_deletes=True, ) Copy to clipboard Using the above configuration, the deletion of a Parent object proceeds as follows: A Parent object is marked for deletion using Session.delete(). When the flush occurs, if the Parent.children collection is not loaded, the ORM will first emit a SELECT statement in order to load the Child objects that correspond to Parent.children. It will then then emit DELETE statements for the rows in association which correspond to that parent row. for each Child object affected by this immediate deletion, because passive_deletes=True is configured, the unit of work will not need to try to emit SELECT statements for each Child.parents collection as it is assumed the corresponding rows in association will be deleted. DELETE statements are then emitted for each Child object that was loaded from Parent.children. delete-orphan¶ delete-orphan cascade adds behavior to the delete cascade, such that a child object will be marked for deletion when it is de-associated from the parent, not just when the parent is marked for deletion. This is a common feature when dealing with a related object that is “owned” by its parent, with a NOT NULL foreign key, so that removal of the item from the parent collection results in its deletion. delete-orphan cascade implies that each child object can only have one parent at a time, and in the vast majority of cases is configured only on a one-to-many relationship. For the much less common case of setting it on a many-to-one or many-to-many relationship, the “many” side can be forced to allow only a single object at a time by configuring the relationship.single_parent argument, which establishes Python-side validation that ensures the object is associated with only one parent at a time, however this greatly limits the functionality of the “many” relationship and is usually not what’s desired. See also For relationship , delete-orphan cascade is normally configured only on the “one” side of a one-to-many relationship, and not on the “many” side of a many-to-one or many-to-many relationship. - background on a common error scenario involving delete-orphan cascade. merge¶ merge cascade indicates that the Session.merge() operation should be propagated from a parent that’s the subject of the Session.merge() call down to referred objects. This cascade is also on by default. refresh-expire¶ refresh-expire is an uncommon option, indicating that the Session.expire() operation should be propagated from a parent down to referred objects. When using Session.refresh(), the referred objects are expired only, but not actually refreshed. expunge¶ expunge cascade indicates that when the parent object is removed from the Session using Session.expunge(), the operation should be propagated down to referred objects. Notes on Delete - Deleting Objects Referenced from Collections and Scalar Relationships¶ The ORM in general never modifies the contents of a collection or scalar relationship during the flush process. This means, if your class has a relationship() that refers to a collection of objects, or a reference to a single object such as many-to-one, the contents of this attribute will not be modified when the flush process occurs. Instead, it is expected that the Session would eventually be expired, either through the expire-on-commit behavior of Session.commit() or through explicit use of Session.expire(). At that point, any referenced object or collection associated with that Session will be cleared and will re-load itself upon next access. A common confusion that arises regarding this behavior involves the use of the Session.delete() method. When Session.delete() is invoked upon an object and the Session is flushed, the row is deleted from the database. Rows that refer to the target row via foreign key, assuming they are tracked using a relationship() between the two mapped object types, will also see their foreign key attributes UPDATED to null, or if delete cascade is set up, the related rows will be deleted as well. However, even though rows related to the deleted object might be themselves modified as well, no changes occur to relationship-bound collections or object references on the objects involved in the operation within the scope of the flush itself. This means if the object was a member of a related collection, it will still be present on the Python side until that collection is expired. Similarly, if the object were referenced via many-to-one or one-to-one from another object, that reference will remain present on that object until the object is expired as well. Below, we illustrate that after an Address object is marked for deletion, it’s still present in the collection associated with the parent User, even after a flush: >>> address = user.addresses[1] >>> session.delete(address) >>> session.flush() >>> address in user.addresses True Copy to clipboard When the above session is committed, all attributes are expired. The next access of user.addresses will re-load the collection, revealing the desired state: >>> session.commit() >>> address in user.addresses False Copy to clipboard There is a recipe for intercepting Session.delete() and invoking this expiration automatically; see ExpireRelationshipOnFKChange for this. However, the usual practice of deleting items within collections is to forego the usage of Session.delete() directly, and instead use cascade behavior to automatically invoke the deletion as a result of removing the object from the parent collection. The delete-orphan cascade accomplishes this, as illustrated in the example below: class User(Base): __tablename__ = "user" # ... addresses = relationship("Address", cascade="all, delete-orphan") # ... del user.addresses[1] session.flush() Copy to clipboard Where above, upon removing the Address object from the User.addresses collection, the delete-orphan cascade has the effect of marking the Address object for deletion in the same way as passing it to Session.delete(). The delete-orphan cascade can also be applied to a many-to-one or one-to-one relationship, so that when an object is de-associated from its parent, it is also automatically marked for deletion. Using delete-orphan cascade on a many-to-one or one-to-one requires an additional flag relationship.single_parent which invokes an assertion that this related object is not to shared with any other parent simultaneously: class User(Base): # ... preference = relationship( "Preference", cascade="all, delete-orphan", single_parent=True ) Copy to clipboard Above, if a hypothetical Preference object is removed from a User, it will be deleted on flush: some_user.preference = None session.flush() # will delete the Preference object Copy to clipboard See also Cascades for detail on cascades. Previous: State Management Next: Transactions and Connection Management © Copyright 2007-2025, the SQLAlchemy authors and contributors. flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari. Created using Sphinx 9.1.0. Documentation last generated: Tue 13 Jan 2026 01:05:54 PM EST ``` relationship() ``` **Pattern 8:** SQLAlchemy’s notion of cascading behavior on relationships, as well as the options to configure them, are primarily derived from the similar feature in the Hibernate ORM; Hibernate refers to “cascade” in a few places such as in Example: Parent\/Child. If cascades are confusing, we’ll refer to their conclusion, stating “The sections we have just covered can be a bit confusing. However, in practice, it all works out nicely.” ``` relationship.cascade ``` ### Example Code Patterns **Example 1** (python): ```python >>> from sqlalchemy import create_engine >>> engine = create_engine("sqlite:\/\/", echo=True) ``` **Example 2** (json): ```json >>> u1 = User(name="pkrabs", fullname="Pearl Krabs") >>> u1.addresses [] ``` **Example 3** (csharp): ```csharp exists_criteria = exists().where(table1.c.col1 == table2.c.col2) ``` **Example 4** (csharp): ```csharp exists_criteria = ( select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists() ) ``` **Example 5** (sql): ```sql >>> from sqlalchemy import select >>> stmt = select(User).where(User.name == "spongebob") ``` ## Reference Files This skill includes comprehensive documentation in `references\/`: - **async.md** - Async documentation - **core.md** - Core documentation - **dialects.md** - Dialects documentation - **events.md** - Events documentation - **extensions.md** - Extensions documentation - **getting_started.md** - Getting Started documentation - **orm_mapping.md** - Orm Mapping documentation - **other.md** - Other documentation - **querying.md** - Querying documentation - **relationships.md** - Relationships documentation - **session.md** - Session documentation - **sql.md** - Sql documentation - **tutorial.md** - Tutorial documentation - **types.md** - Types documentation Use `view` to read specific reference files when detailed information is needed. ## Working with This Skill ### For Beginners Start with the getting_started or tutorials reference files for foundational concepts. ### For Specific Features Use the appropriate category reference file (api, guides, etc.) for detailed information. ### For Code Examples The quick reference section above contains common patterns extracted from the official docs. ## Resources ### references\/ Organized documentation extracted from official sources. These files contain: - Detailed explanations - Code examples with language annotations - Links to original documentation - Table of contents for quick navigation ### scripts\/ Add helper scripts here for common automation tasks. ### assets\/ Add templates, boilerplate, or example projects here. ## Notes - This skill was automatically generated from official documentation - Reference files preserve the structure and examples from source docs - Code examples include language detection for better syntax highlighting - Quick reference patterns are extracted from common usage examples in the docs ## Updating To refresh this skill with updated documentation: 1. Re-run the scraper with the same configuration 2. The skill will be rebuilt with the latest information