-
-
Notifications
You must be signed in to change notification settings - Fork 224
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
ALTER table - Flask-migrate and sqlite #61
Comments
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. |
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. |
Following this link: https://bitbucket.org/zzzeek/alembic/issues/287
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 |
Just a note for anyone coming across this issue: I enabled batch mode for auto-generated migrations by adding a 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. |
@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 ? |
@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. |
@miguelgrinberg Ok thanks. Do you control the env.py file ? |
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. |
Ok, what do you think about adding the snippet from @Vlad003 in the /* On Thu, Dec 17, 2015 at 4:34 PM, Miguel Grinberg [email protected]
|
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. |
Great, keep me posted if I can be of any help ! /* On Thu, Dec 17, 2015 at 7:19 PM, Miguel Grinberg [email protected]
|
I've hit against this tonight, and did the following:
Which is possible in Flask-SQLAlchemy 2.1 thanks to pallets-eco/flask-sqlalchemy#255 Then:
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? |
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:
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 ? |
To be honest, I'm not sure I want to encourage the use of this feature until it becomes rock solid. |
The fact is that any migration of sqlite need this feature because the On Sat, 16 Apr 2016 00:28 Miguel Grinberg, [email protected] wrote:
|
The naming convention allows us to properly name constraints which some database engines (such as SQLite) don't natively do by default. The fact that SQLite doesn't name constraints ends up being a huge pain to manage if you don't name them first [1][2] so let's do that now. [1]: miguelgrinberg/Flask-Migrate#61 [2]: http://alembic.zzzcomputing.com/en/latest/batch.html#dealing-with-constraints Change-Id: Ib4bc7b73f4c18f8d4f666f526be51f81d7b2bed4
NotImplementedError: No support for ALTER of constraints in SQLite dialect |
@desmond75 There is nothing I can do. SQLite has limited support for modifying existing columns. If you need to make these type of changes without complications you may want to consider using MySQL or Postgres. Alembic has an experimental support for applying changes by moving all the data to a new table, but I haven't had much experience with that. See above in this issue for people who have tried that option. |
Mark Steward's solution worked perfectly for me for the case of adding a simple foreignkey column. |
Can somebody post full env.py file with Mark Steward's solution merged please? |
@BrokeRU This is the code (maybe old now) I was referring to in my above comment - This is the file you are looking for env.py, however, this is the commit fix which doesn't touch the env.py |
This is a workaround for some unimplemented features (ALTER) in SQLite, e.g. the ability to modify constraints on a column after it has been created, see: https://stackoverflow.com/questions/30378233/sqlite-lack-of-alter-support-alembic-migration-failing-because-of-this-solutio http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate miguelgrinberg/Flask-Migrate#61
This is a workaround for some unimplemented features (ALTER) in SQLite, e.g. the ability to modify constraints on a column after it has been created, see: https://stackoverflow.com/questions/30378233/sqlite-lack-of-alter-support-alembic-migration-failing-because-of-this-solutio http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate miguelgrinberg/Flask-Migrate#61
This is a workaround for some unimplemented features (ALTER) in SQLite, e.g. the ability to modify constraints on a column after it has been created, see: https://stackoverflow.com/questions/30378233/sqlite-lack-of-alter-support-alembic-migration-failing-because-of-this-solutio http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate miguelgrinberg/Flask-Migrate#61
For those who struggle to put this all together, here's an explanation video I found very helpful. |
Sqlite doesn't support dropping columns of existing tables, so we need to use batch processing, which creates a new table, migrates the data, deletes the old table, and renames the new table to the original's name: miguelgrinberg/Flask-Migrate#61 (comment) The existing migration version files also needed to be edited: https://www.youtube.com/watch?v=CxCK1DkikgA
On StackOverflow, the user yarlen has found a way more elegant solution to this. Check the last answer here: https://stackoverflow.com/questions/30394222/why-flask-migrate-cannot-upgrade-when-drop-column/54322445#54322445
By adding the code above on initialisation of the Flask app / migrate module, you automatically enable batch rendering of the migrations ONLY when the database in use is SQLite. No mess around with env.py files, or manual edits to migrations files. This is the safest solution available. This answer on stack overflow should be upvoted and made the correct answer. |
@sgabello This definitely seems to be the most elegant solution out there, but unfortunately it does not work for me. I continue to get the dreaded I added the code to my app file, restarted the app and added some debug print statements to make sure SQLite is being identified correctly and it is but still the error... Any tips? |
Did you re-build the migrations files from scratch? The error message is pretty clear... somewhere in your code you are still using ALTER... potentially you could find where with a simple search in your project folder. |
@sgabello that's a copy of my comment at #61 (comment). You probably also want the other bits because the default naming convention is slightly off. |
Totally missed your piece of code. Probably my eyes skipped your comment because of the naming convention tweaking code that in my case was not necessary. Didn't mean to steal any merit... Apologies! 🤦 And btw... I made it pretty clear that the code in my comment was not my fix but rather someone's on SO anyway 😄 |
Thanks. That was it. Over that hurdle and now on to the next battle: I wonder if this is all worth it on a dev environment. I could just blow it away and start over but was trying to "do it right". |
@pfabri , many thanks, video was very helpful. I tried to add new column to a table but also add foreign key. Add column works create but I had issue with foreign key, I applied some logic described in the video and finally I have this upgrade function:
before it was (pay attention, when you use
Hope it helps to some one 😃 |
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.
The text was updated successfully, but these errors were encountered: