Issue #136 new

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

Mattias Gyllsdorff avatarMattias 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

y DATETIME DEFAULT NOW()

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

    op.create_table('foo',
    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

    context.configure(
                connection=connection,
                target_metadata=target_metadata,
                compare_server_default=True,
                )

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 (7)

  1. Mattias Gyllsdorff

    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)
        else:
            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(
            config.get_section(config.config_ini_section),
            prefix = 'sqlalchemy.',
            poolclass = pool.NullPool)
        connection = engine.connect()
    
        context.configure(
            connection = connection,
            target_metadata = target_metadata,
            render_item = render_server_default_values
        )
    
  2. Mike Bayer

    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. Dimitris 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)
                else:
                    return self.sql_compiler.process(column.server_default.arg)
            else:
                return None
    

    Otherwise manually remove the quotes from the alemic code

  4. Mike Bayer

    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
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.