User defined function creation in SQLAlchemy

Question

SQLAlchemy provides a very clean interface for defining database tables:

engine = create_engine('sqlite:///:memory:')

metadata = MetaData()

user = Table('user', metadata,
    Column('user_id', Integer, primary_key = True),
    Column('user_name', String(16), nullable = False),
    Column('email_address', String(60), key='email'),
    Column('password', String(20), nullable = False)
)

user_prefs = Table('user_prefs', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

And once these tables have been defined, it is very easy to create these tables with the metadata.create_all(engine) function. This is especially nice for testing, when you want to create tables that will not interfere with the existing tables being used in production.

A project I am currently working on relies heavily on user defined functions in postgres. Is there a clean way to define these functions using SQLAlchemy in order for the metadata.create_all(engine) to properly create the functions along with the appropriate tables?

Answer 1

I've been working on something similar today. So far the best way I've found to do it is using sqlalchemy before_create event listeners. For general functions, you can bind the events to the metadata, but for table specific functions you could bind to the tables. For example:

import sqlalchemy
from sqlalchemy.schema import DDL

sqlalchemy.event.listen(
    metadata,
    'before_create',
    DDL('CREATE OR REPLACE FUNCTION myfunc() ...')
)

You can just replace metadata with your table if you wanted to create the function before a table was created.

This DDL seems to run every time you call metadata.create_all so it's important to use CREATE OR REPLACE. If you wanted a bit more control over when functions are created, you might be better looking into migrations with alembic or similar.

Some uses of DDL are described in the sqlalchemy docs here: http://docs.sqlalchemy.org/en/latest/core/ddl.html#custom-ddl