ALTER table - Flask-migrate and sqlite

[SylvainMartel]

I know this is more a SQLlite issue, but I was wondering how you dealt with the many "No support for ALTER of constraints in SQLite dialect" that arise with flask-migrate and alembic. I am stuck right now because db upgrade cannot update the database correctly due to such an issue.

[miguelgrinberg]

This is really between Alembic and SQLite. The latest version of Alembic is able to do some of these unsupported migrations by moving the data to a new table, so maybe you can address your problem.

[italomaia]

I believe this is what miguel is referring to http://alembic.readthedocs.org/en/latest/batch.html#running-batch-migrations-for-sqlite-and-other-databases

It would be nice if Flask-Migrate could have native support to batch when sqlite is the database.

[italomaia]

Following this link: https://bitbucket.org/zzzeek/alembic/issues/287
I've found that this:

DATABASE_URI = getattr(app.config, 'SQLALCHEMY_DATABASE_URI', '')
is_sqlite = DATABASE_URI.startswith('sqlite:')
migrate.init_app(app, db, render_as_batch=is_sqlite)

Is partially required to use batch. The second step would be to alter the upgrade command to use batch operation. Not quite sure where to look further into the matter ...

[EDIT] Actually, I also have to update the script.py.mako; let's see how to do this ... no idea how to populate the script template context with a is_sqlite variable

[avacariu]

Just a note for anyone coming across this issue:

I enabled batch mode for auto-generated migrations by adding a render_as_batch=config.get_main_option('sqlalchemy.url').startswith('sqlite:') argument to context.configure() in run_migrations_online() in env.py.

So my full function looks like:

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_as_batch=config.get_main_option('sqlalchemy.url').startswith('sqlite:///')
                )

    try:
        with context.begin_transaction():
            context.run_migrations()
    finally:
        connection.close()

And it seems to work fine for me.

[nMustaki]

@Vlad003 : thanks for your snippet, it helped me get started !

A full Sqlite support should include something about naming constraints, and the migrations for sqlite should look something like this from Alembic doc :

naming_convention = {
    "fk":
    "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
}
with self.op.batch_alter_table(
        "bar", naming_convention=naming_convention) as batch_op:
    batch_op.drop_constraint(
        "fk_bar_foo_id_foo", type_="foreignkey")

@miguelgrinberg is extending support for sqlite in your roadmap, or would you prefer pull requests ?

[miguelgrinberg]

@nMustaki there is absolutely nothing in this project that performs migrations. I just facilitate the use of Alembic. Any migration improvements you make should go directly into Alembic.

[nMustaki]

@miguelgrinberg Ok thanks. Do you control the env.py file ?

[miguelgrinberg]

This project provides a generic env.py file template. Once you initialize a project, a copy is placed in the migrations directory. From that point on, you can customize it if you need something that isn't provided in the generic version.

[nMustaki]

Ok,

what do you think about adding the snippet from @Vlad003 in the
documentation in a section about sqlite ?

/*
** "What do you despise? By this you are truly known."
** from Manual of Muad'Dib by the Princess Irulan.
*/

On Thu, Dec 17, 2015 at 4:34 PM, Miguel Grinberg notifications@github.com
wrote:

This project provides a generic env.py file template. Once you initialize
a project, a copy is placed in the migrations directory. From that point
on, you can customize it if you need something that isn't provided in the
generic version.

—
Reply to this email directly or view it on GitHub
#61 (comment)
.

[miguelgrinberg]

Oh okay, I see what you are getting at now. For some reason I assumed this snippet needed to go in the migration script, that's why I sent you to Alembic.

Let me think about how to best handle this. I could, for example, provide an alternate env.py, in the same way I provide one for multidb support. What's unclear to me is if these changes are okay to have when you work with other databases, or if they will prevent you from using anything other than sqlite. I guess I need to experiment with this a bit to know what to do.

[nMustaki]

Great, keep me posted if I can be of any help !

/*
** "What do you despise? By this you are truly known."
** from Manual of Muad'Dib by the Princess Irulan.
*/

On Thu, Dec 17, 2015 at 7:19 PM, Miguel Grinberg notifications@github.com
wrote:

Oh okay, I see what you are getting at now. For some reason I assumed this
snippet needed to go in the migration script, that's why I sent you to
Alembic.

Let me think about how to best handle this. I could, for example, provide
an alternate env.py, in the same way I provide one for multidb support.
What's unclear to me is if these changes are okay to have when you work
with other databases, or if they will prevent you from using anything other
than sqlite. I guess I need to experiment with this a bit to know what to
do.

—
Reply to this email directly or view it on GitHub
#61 (comment)
.

[marksteward]

I've hit against this tonight, and did the following:

naming_convention = {
    "ix": 'ix_%(column_0_label)s',
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(column_0_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s"
}
db = SQLAlchemy(metadata=MetaData(naming_convention=naming_convention))

Which is possible in Flask-SQLAlchemy 2.1 thanks to pallets-eco/flask-sqlalchemy#255

Then:

with app.app_context():
    if db.engine.url.drivername == 'sqlite':
        migrate.init_app(app, db, render_as_batch=True)
    else:
        migrate.init_app(app, db)

and suddenly realised the multidb problem. You need to use render_as_batch from the start and on all migrations for a particular DB or you still risk getting unnamed constraint issues later down the line. This means sqlite-specific migrations, or always using render_as_batch.

Did you get to do much experimenting with this? I'm wondering if there's any reason not to always set render_as_batch=True?

[nMustaki]

I stopped playing with it because it messed with the db constraints (they disappeared after migration) and I had no time to debug it (sadly).

Reading the doc, render_as_batch only purpose is to handle sqlite while being compatible with others db:

On other backends, we’d see the usual ALTER statements done as though there were no batch directive - the batch context by default only does the “move and copy” process if SQLite is in use
https://alembic.readthedocs.org/en/latest/batch.html#batch-migrations

Docs still warn that is beta code, so I would be wary to enable it by default for now if it is not needed. Sqlite users have no choice though, so we could provide a working env.py and docs ?