Customizing DDL¶

In the preceding sections we’ve discussed a variety of schema constructs including Table, ForeignKeyConstraint, CheckConstraint, and Sequence. Throughout, we’ve relied upon the create() and create_all() methods of Table and MetaData in order to issue data definition language (DDL) for all constructs. When issued, a pre-determined order of operations is invoked, and DDL to create each table is created unconditionally including all constraints and other objects associated with it. For more complex scenarios where database-specific DDL is required, SQLAlchemy offers two techniques which can be used to add any DDL based on any condition, either accompanying the standard generation of tables or by itself.

Custom DDL¶

Custom DDL phrases are most easily achieved using the DDL construct. This construct works like all the other DDL elements except it accepts a string which is the text to be emitted:

event.listen(
    metadata,
    "after_create",
    DDL(
        "ALTER TABLE users ADD CONSTRAINT "
        "cst_user_name_length "
        " CHECK (length(user_name) >= 8)"
    ),
)

A more comprehensive method of creating libraries of DDL constructs is to use custom compilation - see Custom SQL Constructs and Compilation Extension for details.

Controlling DDL Sequences¶

The DDL construct introduced previously also has the ability to be invoked conditionally based on inspection of the database. This feature is available using the ExecutableDDLElement.execute_if() method. For example, if we wanted to create a trigger but only on the PostgreSQL backend, we could invoke this as:

mytable = Table(
    "mytable",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", String(50)),
)

func = DDL(
    "CREATE FUNCTION my_func() "
    "RETURNS TRIGGER AS $$ "
    "BEGIN "
    "NEW.data := 'ins'; "
    "RETURN NEW; "
    "END; $$ LANGUAGE PLPGSQL"
)

trigger = DDL(
    "CREATE TRIGGER dt_ins BEFORE INSERT ON mytable "
    "FOR EACH ROW EXECUTE PROCEDURE my_func();"
)

event.listen(mytable, "after_create", func.execute_if(dialect="postgresql"))

event.listen(mytable, "after_create", trigger.execute_if(dialect="postgresql"))

The ExecutableDDLElement.execute_if.dialect keyword also accepts a tuple of string dialect names:

event.listen(
    mytable, "after_create", trigger.execute_if(dialect=("postgresql", "mysql"))
)
event.listen(
    mytable, "before_drop", trigger.execute_if(dialect=("postgresql", "mysql"))
)

The ExecutableDDLElement.execute_if() method can also work against a callable function that will receive the database connection in use. In the example below, we use this to conditionally create a CHECK constraint, first looking within the PostgreSQL catalogs to see if it exists:

def should_create(ddl,target,connection,**kw):row=connection.execute("select conname from pg_constraint where conname='%s'"%ddl.element.name).scalar()returnnotbool(row)def should_drop(ddl,target,connection,**kw):returnnotshould_create(ddl,target,connection,**kw)event.listen(users,"after_create",DDL("ALTER TABLE users ADD CONSTRAINT ""cst_user_name_length CHECK (length(user_name) >= 8)").execute_if(callable_=should_create),)event.listen(users,"before_drop",DDL("ALTER TABLE users DROP CONSTRAINT cst_user_name_length").execute_if(callable_=should_drop),)users.create(engine)CREATE TABLE users (
    user_id SERIAL NOT NULL,
    user_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (user_id)
)

SELECT conname FROM pg_constraint WHERE conname='cst_user_name_length'
ALTER TABLE users ADD CONSTRAINT cst_user_name_length  CHECK (length(user_name) >= 8)
users.drop(engine)SELECT conname FROM pg_constraint WHERE conname='cst_user_name_length'
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
DROP TABLE users

Using the built-in DDLElement Classes¶

The sqlalchemy.schema package contains SQL expression constructs that provide DDL expressions, all of which extend from the common base ExecutableDDLElement. For example, to produce a CREATE TABLE statement, one can use the CreateTable construct:

from sqlalchemy.schema importCreateTablewithengine.connect()asconn:conn.execute(CreateTable(mytable))CREATE TABLE mytable (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER,
    col5 INTEGER,
    col6 INTEGER
)

Above, the CreateTable construct works like any other expression construct (such as select(), table.insert(), etc.). All of SQLAlchemy’s DDL oriented constructs are subclasses of the ExecutableDDLElement base class; this is the base of all the objects corresponding to CREATE and DROP as well as ALTER, not only in SQLAlchemy but in Alembic Migrations as well. A full reference of available constructs is in DDL Expression Constructs API.

User-defined DDL constructs may also be created as subclasses of ExecutableDDLElement itself. The documentation in Custom SQL Constructs and Compilation Extension has several examples of this.

Controlling DDL Generation of Constraints and Indexes¶

Added in version 2.0.

While the previously mentioned ExecutableDDLElement.execute_if() method is useful for custom DDL classes which need to invoke conditionally, there is also a common need for elements that are typically related to a particular Table, namely constraints and indexes, to also be subject to “conditional” rules, such as an index that includes features that are specific to a particular backend such as PostgreSQL or SQL Server. For this use case, the Constraint.ddl_if() and Index.ddl_if() methods may be used against constructs such as CheckConstraint, UniqueConstraint and Index, accepting the same arguments as the ExecutableDDLElement.execute_if() method in order to control whether or not their DDL will be emitted in terms of their parent Table object. These methods may be used inline when creating the definition for a Table (or similarly, when using the __table_args__ collection in an ORM declarative mapping), such as:

from sqlalchemy import CheckConstraint, Index
from sqlalchemy import MetaData, Table, Column
from sqlalchemy import Integer, String

meta = MetaData()

my_table = Table(
    "my_table",
    meta,
    Column("id", Integer, primary_key=True),
    Column("num", Integer),
    Column("data", String),
    Index("my_pg_index", "data").ddl_if(dialect="postgresql"),
    CheckConstraint("num > 5").ddl_if(dialect="postgresql"),
)

In the above example, the Table construct refers to both an Index and a CheckConstraint construct, both which indicate .ddl_if(dialect="postgresql"), which indicates that these elements will be included in the CREATE TABLE sequence only against the PostgreSQL dialect. If we run meta.create_all() against the SQLite dialect, for example, neither construct will be included:

>>> from sqlalchemy importcreate_engine>>> sqlite_engine=create_engine("sqlite+pysqlite://",echo=True)>>> meta.create_all(sqlite_engine)BEGIN (implicit)
PRAGMA main.table_info("my_table")
[raw sql] ()
PRAGMA temp.table_info("my_table")
[raw sql] ()

CREATE TABLE my_table (
    id INTEGER NOT NULL,
    num INTEGER,
    data VARCHAR,
    PRIMARY KEY (id)
)

However, if we run the same commands against a PostgreSQL database, we will see inline DDL for the CHECK constraint as well as a separate CREATE statement emitted for the index:

>>> from sqlalchemy importcreate_engine>>> postgresql_engine=create_engine(...     "postgresql+psycopg2://scott:tiger@localhost/test", echo=True... )>>> meta.create_all(postgresql_engine)BEGIN (implicit)
select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
[generated in 0.00009s] {'name': 'my_table'}

CREATE TABLE my_table (
    id SERIAL NOT NULL,
    num INTEGER,
    data VARCHAR,
    PRIMARY KEY (id),
    CHECK (num > 5)
)
[no key 0.00007s] {}
CREATE INDEX my_pg_index ON my_table (data)
[no key 0.00013s] {}
COMMIT

The Constraint.ddl_if() and Index.ddl_if() methods create an event hook that may be consulted not just at DDL execution time, as is the behavior with ExecutableDDLElement.execute_if(), but also within the SQL compilation phase of the CreateTable object, which is responsible for rendering the CHECK (num > 5) DDL inline within the CREATE TABLE statement. As such, the event hook that is received by the ddl_if.callable_() parameter has a richer argument set present, including that there is a dialect keyword argument passed, as well as an instance of DDLCompiler via the compiler keyword argument for the “inline rendering” portion of the sequence. The bind argument is not present when the event is triggered within the DDLCompiler sequence, so a modern event hook that wishes to inspect the database versioning information would best use the given Dialect object, such as to test PostgreSQL versioning:

def only_pg_14(ddl_element, target, bind, dialect, **kw):
    return dialect.name == "postgresql" and dialect.server_version_info >= (14,)


my_table = Table(
    "my_table",
    meta,
    Column("id", Integer, primary_key=True),
    Column("num", Integer),
    Column("data", String),
    Index("my_pg_index", "data").ddl_if(callable_=only_pg_14),
)

SQLAlchemy 2.1 Documentation