Customizing DDL — SQLAlchemy 2.0.0b1 documentation

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 _schema.DDL construct introduced previously also has the ability to be invoked conditionally based on inspection of the database. This feature is available using the DDLElement.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 :paramref:`.DDLElement.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 DDLElement.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()
    return not bool(row)

def should_drop(ddl, target, connection, **kw):
    return not should_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)
)

{sql}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){stop}

{sql}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{stop}

Using the built-in DDLElement Classes

The sqlalchemy.schema package contains SQL expression constructs that provide DDL expressions. For example, to produce a CREATE TABLE statement:

from sqlalchemy.schema import CreateTable
with engine.connect() as conn:
{sql}    conn.execute(CreateTable(mytable))
CREATE TABLE mytable (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER,
    col5 INTEGER,
    col6 INTEGER
){stop}

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 DDLElement 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 DDLElement itself. The documentation in Custom SQL Constructs and Compilation Extension has several examples of this.

The event-driven DDL system described in the previous section Controlling DDL Sequences is available with other DDLElement objects as well. However, when dealing with the built-in constructs such as CreateIndex, CreateSequence, etc, the event system is of limited use, as methods like _schema.Table.create() and _schema.MetaData.create_all() will invoke these constructs unconditionally. In a future SQLAlchemy release, the DDL event system including conditional execution will taken into account for built-in constructs that currently invoke in all cases.

We can illustrate an event-driven example with the AddConstraint and DropConstraint constructs, as the event-driven system will work for CHECK and UNIQUE constraints, using these as we did in our previous example of DDLElement.execute_if():

def should_create(ddl, target, connection, **kw):
    row = connection.execute(
        "select conname from pg_constraint where conname='%s'" %
        ddl.element.name).scalar()
    return not bool(row)

def should_drop(ddl, target, connection, **kw):
    return not should_create(ddl, target, connection, **kw)

event.listen(
    users,
    "after_create",
    AddConstraint(constraint).execute_if(callable_=should_create)
)
event.listen(
    users,
    "before_drop",
    DropConstraint(constraint).execute_if(callable_=should_drop)
)

{sql}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){stop}

{sql}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{stop}

While the above example is against the built-in AddConstraint and DropConstraint objects, the main usefulness of DDL events for now remains focused on the use of the DDL construct itself, as well as with user-defined subclasses of DDLElement that aren’t already part of the _schema.MetaData.create_all(), _schema.Table.create(), and corresponding “drop” processes.