--autogenerate ignores text on server_default = sqlalchemy.sql.expression.text('uuid_generate_v4()')

Issue #136 new
Mattias Gyllsdorff
created an issue

I have a model defined as

class Foo(Base):
    __tablename__ = 'foo'

    salt = Column(UUID, nullable=False, server_default = sqlalchemy.sql.expression.text('uuid_generate_v4()'))

According to the SqlAlchemy doc a text variable means

A text() expression will be rendered as-is, without quotes:

Column('y', DateTime, server_default=text('NOW()'))0


But the output of 'alembic -c development.ini --autogenerate -m "test"' is

    sa.Column('salt', postgresql.UUID(), server_default='uuid_generate_v4()', nullable=False)

I tried to add compare_server_default to the configure call like this


but nothing changed.

This is especially annoying since it means that integer columns can't have a server_default defined in the model.

It is easy to fix manually by adding sa.text() around the generated server_default value in the version file but I know that we will forget that sometimes.

Am I doing something wrong or is this a bug?

Except for this everything have worked exactly as documented without any problems.

Comments (8)

  1. Mattias Gyllsdorff reporter

    I "fixed" it by doing

    def render_server_default_values(type_, col, autogen_context):
        if type_ == "server_default" and isinstance(col.arg, sqlalchemy.sql.expression.TextClause):
            return "sa.text('{0}')".format(col.arg.text)
            return False
    def run_migrations_online():
        """Run migrations in 'online' mode.
        In this scenario we need to create an Engine
        and associate a connection with the context.
        engine = engine_from_config(
            prefix = 'sqlalchemy.',
            poolclass = pool.NullPool)
        connection = engine.connect()
            connection = connection,
            target_metadata = target_metadata,
            render_item = render_server_default_values
  2. Michael Bayer repo owner

    the server_default mechanism applies a text() wrapper around the string I think in a reflection context, so there is probably some wrapping/unwrapping of text() happening there.

    a simple workaround is just to use func, func.uuid_generate_v4().

  3. Dimitrios Theodorou

    The mistake happens at alembic.ddl.base line 120. What should be returned as DEFAULT NOW() is instead DEFAULT 'NOW()'

    format_server_default() at alembic.ddl.base.py 143 will call get_column_default_string() at sqlalchemy.sql.compiler 2120 which will add additional quotes around the string.

    I have no idea why the sqlalchemy function adds the quotes, but there you have it.

    Maybe it is SQL ALchemy that should be fixed.

        def get_column_default_string(self, column, quote=True):
            str_format =  "'{}'" if quote else "{}"
            if isinstance(column.server_default, schema.DefaultClause):
                if isinstance(column.server_default.arg, basestring):
                    return str_format.format(column.server_default.arg)
                    return self.sql_compiler.process(column.server_default.arg)
                return None

    Otherwise manually remove the quotes from the alemic code

  4. Michael Bayer repo owner

    its because there's a difference between "ABC INT DEFAULT 'XYZ'" and "ABC DATETIME DEFAULT NOW()". there's not enough information here for the components to know whether or not the element is a literal or a SQL keyword, which is usually because the backend's information tables give us ambiguous information.

    I'm not seeing what backend this is here? MySQL / Postgresql?

  5. Log in to comment