Skip to content
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

Column renames not supported on SQLite #21

Closed
sqlalchemy-bot opened this issue Jan 3, 2012 · 31 comments
Closed

Column renames not supported on SQLite #21

sqlalchemy-bot opened this issue Jan 3, 2012 · 31 comments

Comments

@sqlalchemy-bot
Copy link

@sqlalchemy-bot sqlalchemy-bot commented Jan 3, 2012

Migrated issue, originally created by Wichert Akkerman (@wichert)

SQLite does not support renaming a column directly, so you need a magic dance: create a new column, copy the right data to it and then remove the old column, taking care to only add constraints after copying the data.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 3, 2012

Michael Bayer (@zzzeek) wrote:

Yeah this is a feature I specially don't want to add to Alembic (Edit: please see below where I have softened my stance here). I'd rather SQLite get on board with basic alteration capability.

I've mentioned this in the README and CHANGES. Feel free to try changing my mind again but I don't think SQLite is appropriate for a long-term data store. An app with a new schema should just read the SQLite DB in, write it out to a new one, or just use Postgresql.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 3, 2012

Wichert Akkerman (@wichert) wrote:

I agree with the basic sentiment; I ran into it when writing tests for upgrade logic which does use SQLite as backend. I worked around it by mocking out alter_table and testing how that is invoked instead.

Could you add a note to the alter_table docstring that it does not implement this for SQLite? It already has a lot of MySQL and MS-SQL specific notes, so I was expecting to find SQLite-related remarks there as well.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Apr 17, 2012

Marc Schlaich (@schlamar) wrote:

Can you please make clear which operations are supported for sqlite and which one not. Just found out that drop_column isn't supported, too:

#!python

sqlalchemy.exc.OperationalError: (OperationalError) near "DROP": syntax error u'ALTER TABLE jobs DROP COLUMN actual' ()

I really would like to see full support for sqlite and would help to implement it. This could either be a direct contribution or via a plugin, if you don't want to have the workarounds in you core codebase.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Apr 17, 2012

Michael Bayer (@zzzeek) wrote:

SQLite's ALTER functionality is documented at http://sqlite.org/lang_altertable.html.

Why not petition the SQLite project directly to support ALTER ? why is all the energy always focused at development tools and not the source of the problem itself ?

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Apr 20, 2012

Michael Bayer (@zzzeek) wrote:

question here, if we need to entirely recreate the table, is the usual routine here to reflect the existing table behind the scenes so that this drop and re-create technique is transparent?

if you want to contribute a patch, if it looks good and doesnt complicate life for the other backends I will accept it.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 3, 2012

Barry Warsaw (@warsaw) wrote:

I'm hitting the DROP COLUMN case too. I completely understand your position Michael, and I'd probably have the same myself if I was the author. OTOH, it sure would be nice to have alembic paper over the deficiencies of SQLite. ;)

In my use case, I'm trying to migrate a database which might be in SQLite, PostgreSQL, or MySQL. Because I use an ORM, I have no idea which db end users will be using. It's much easier for me to do most of my development with SQLite and then use other CI tools to ensure continued compatibility with the others. Having Alembic work around these missing features allows me to write and distribute a single migration script that end users can use regardless of which actual db they're using.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 3, 2012

Michael Bayer (@zzzeek) wrote:

Hey Barry -

I'm all for this feature but I would prefer if someone could volunteer to get most of the legwork done. Alembic's architecture should support the feature but it requires that the sqlite implementation go through some much bigger hoops for these operations, namely that it has to rename the table and then fully recreate it minus the one column.

Getting at the full table definition is the most tedious part. drop_column() should, by default, use table reflection when invoked to get at the existing table def, so that it can be recreated. This would be within the sqlite impl only. drop_column() could also accept a fully formed Table object as an option, which the sqlite impl will make use of in lieu of reflection, in case the user wants to do it that way. In particular the latter option would be needed if the user is running the migrations in offline mode as interpretation of reflection is not an offline concept. It would then need to render a "INSERT .. FROM SELECT" to transfer data between the two tables, then finally drop the old table.

SQLite's description of this operation is here: http://sqlite.org/faq.html#q11.

What is really ridiculous about the whole situation is that SQLite could easily implement this operation as a single command, and handle the details of copying the table's schema with no inaccuracies and no need to pull data into memory for re-interpretation.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 7, 2013

Changes by Michael Bayer (@zzzeek):

  • added labels: op directives
@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 4, 2014

Elrond (@elvenlord) wrote:

If it comes to just renaming a column on sqlite, this can be done using a trick: See recipie on StackOverflow.

There are two important downsides:

  • This IS using dangerous internals of sqlite.
  • If the column name is used in contraints/indexes, those need to be fixed as well (or rather don't use the described method).

p.s.: ${Sqlite-should-fix-this-boilerplate}

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 9, 2014

Nicolas Dietrich (@nidi) wrote:

After reading a discussion thread on a blog post petitioning for an implementation of DROP COLUMN in SQLite upstream, it seems unlikely that this will happen anytime soon, unfortunately.

Therefore introducing a workaround in Alembic (as is done in sqlalchemy-migrate) would be very welcome.

Our use case is similar to Barry's: on production machines, PostgreSQL is used, but developers often use SQLite in order to keep the development environment setup hurdle low. It'd be nice if the migration scripts wouldn't break (and could be tested) in these setups.

Nothing new really though...

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 7, 2014

Michael Bayer (@zzzeek) wrote:

proposed API:

# will autoload my_table at migration time in order to know how to 
# recreate the table - does not support --sql mode
op.drop_column("my_table", "some_column", sqlite_autoload=True)

# will use the Table object that you pass as the "existing" table.
# supports --sql mode
my_table = Table("my_table", some_metadata, 
            Column(...), 
            # ...
)
op.drop_column("my_table", "some_column", sqlite_existing=my_table)

the sqlite_autoload and sqlite_existing options would be added to: drop_column(), rename_column(), add_constraint(), drop_constraint(), alter_column() (any others??).

Also @wichert can you confirm that SQLite has no "drop column", the case you describe at the top here requires that the whole table is recreated and copied, right?

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 7, 2014

Wichert Akkerman (@wichert) wrote:

SQLite has no drop column:

sqlite> create table foo(bar, buz);
sqlite> alter table foo drop column bar;
Error: near "drop": syntax error

The only table-altering statements SQLite supports are renaming the table and adding a new column. See the SQLite DDL documentation for details.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jun 27, 2014

Michael Bayer (@zzzeek) wrote:

it was just pointed out on irc that the idea of making a new table, copying data and renaming might be desirable on other platforms as well, as a way to work around locking issues with ALTER. we might want to see if there's a way to generalize this feature, and additionally see if manual intervention can allow optimizations of multiple columns:

with op.batch_alter("mytable", recreate=True) as batch_op:
    batch_op.drop_column("some_column")
    batch_op.alter_column("q", type=Integer)
    batch_op.add_column(Column('y', String(50), nullable=False))

Perhaps autogenerate can group column alterations in this way, and it can do a "recreate" only for sqlite, normal ops for others:

with op.batch_alter("mytable", recreate=('sqlite',)) as batch_op:
    batch_op.drop_column("some_column")
    batch_op.alter_column("q", type=Integer)
    batch_op.add_column(Column('y', String(50), nullable=False))

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Oct 7, 2014

Changes by Michael Bayer (@zzzeek):

  • set milestone to "tier 1"
@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Oct 22, 2014

Sven-Hendrik Haase (@svenstaro) wrote:

I'm interested in contributing to this as per IRC.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Oct 22, 2014

Michael Bayer (@zzzeek) wrote:

OK a sketch of the rough idea is at: c2a91ac. It's pretty easy. I want to actually implement the reflection and data transfer here and get a real round trip working.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Nov 7, 2014

Michael Bayer (@zzzeek) wrote:

hi @svenstaro and others....I got a "hello world" version of this working as of 7eee803. The structure of operations is in place and some docstrings, as well as autogenerate support.

The demo I've run is as follows:

from alembic.migration import MigrationContext
from alembic.operations import Operations

from sqlalchemy import create_engine, Column, Integer, String

eng = create_engine("sqlite://", echo=True)

conn = eng.connect()
ctx = MigrationContext.configure(conn)
op = Operations(ctx)

op.create_table(
    "table1",
    Column('x', Integer, primary_key=True),
    Column('y', String(50)),
    Column('z', Integer)
)

with op.batch_alter_table("table1") as batch_op:
    batch_op.alter_column('y', type_=Integer)
    batch_op.drop_column('z')
    batch_op.alter_column('x', new_column_name='q')


an abbreviated form of its output:

CREATE TABLE table1 (
	x INTEGER NOT NULL, 
	y VARCHAR(50), 
	z INTEGER, 
	PRIMARY KEY (x)
)


SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'table1' AND type = 'table'

CREATE TABLE _alembic_batch_temp (
	x INTEGER NOT NULL, 
	y INTEGER, 
	PRIMARY KEY (x)
)


INSERT INTO _alembic_batch_temp (y, x) SELECT table1.y, table1.x 
FROM table1

DROP TABLE table1

ALTER TABLE _alembic_batch_temp RENAME TO table1

I'd like to get this feature out in 0.7.0 so I'll continue with tests.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Nov 9, 2014

Michael Bayer (@zzzeek) wrote:

  • changelog
    fixes #21

713b128

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Nov 9, 2014

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Nov 13, 2014

Sven-Hendrik Haase (@svenstaro) wrote:

Wow, Mike. In the end, this was implemented much faster than anticipated. Many thanks for that! Is there still anything that needs doing?

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Nov 13, 2014

Michael Bayer (@zzzeek) wrote:

well if you want to give it a spin and make sure the basic idea works OK for you, unlike sqlalchemy-migrate there is an explicit step here, which I think is a good thing, but could use more feedback on that.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Jan 6, 2016

Jesse Peterson (@jessepeterson) wrote:

Just like to add that the documentation for this feature has been moved here: http://alembic.readthedocs.org/en/latest/batch.html

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Sean Behan (@seanbehan) wrote:

People want software to just work. And while SQLite may be at fault, nobody cares. I come from a Rails background, where ActiveRecord migrations just work... even with SQLite. It's so disappointing that there is nothing comparable to Rails' ActiveRecord in the Python world. I was hoping SQLAlchemy and Alembic would
be a general purpose ORM for working with databases in Python. But it clearly isn't when you can't even drop a column without an exception being raised.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

David Reaver (@jdreaver) wrote:

@seanbehan Why did you resurrect an old issue just to complain? Attitudes like yours make maintaining open source software a pain. You are complaining to folks who spend their free time making something free for you to use, and you aren't even being constructive when doing so. I'm not a maintainer on this project but I was pinged when you commented, and I just came to let you know you sound like a jerk.

More importantly, do you understand this was implemented and released over two years ago?

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Michael Bayer (@zzzeek) wrote:

@seanbehan Python has Django for those who prefer a strongly opinionated approach (which is what "just works" actually is). What's wrong with Django?

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Michael Bayer (@zzzeek) wrote:

And while SQLite may be at fault, nobody cares.

because all the framework authors bend over backwards to work around its awful design. They care. Also, nobody cares that you don't like Alembic, so there's that :)

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Sean Behan (@seanbehan) wrote:

@jdreaver Sorry for sounding like a jerk. I was just trying to emphasize why it should be a supported feature.

I randomly found the thread searching for a solution to downgrade a migration w/ Alembic and SQLite. It was a top hit so I assumed still relevant. I also just installed Alembic v0.9.1 and I ran into the issue. So I'm not sure that it is fixed or if it is I'm not sure how to take advantage of it. The documentation is not very easy to use when just getting started... and a lot of the links in this thread are broken.

Anyway, as a new comer to Alembic its configuration is overwhelming and a very frustrating experience when all you want to do is make and apply a migration.

It's not meant to be an attack. It was just honest criticism of the top open source database migration tool for Python.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Sean Behan (@seanbehan) wrote:

@zzzeek Django was actually my first choice but the Django ORM is very tightly coupled to Django. If you want to use the Django ORM on its own it is very difficult and have to jump through a lot of hoops.

Listen it's not a personal attack. I would love to use Alembic. I was super hopeful when I found it. But then found it was very difficult to get started with and then ran into the SQLite issue. So I was equally as disappointed.

Also, I never called this project awful and when I said "nobody cares" I didn't mean about this project. I meant to say that nobody cares the reasons for why a feature is or is not supported. They just want things to work.

I apologize for coming across as a jerk.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 22, 2017

Michael Bayer (@zzzeek) wrote:

IMO people should really care that sqlite3 does not make this possible. The upstream argument is that it would not be an efficient operation since sqlite stores the essentially the structure of a table in every row. But we are rewriting the whole table anyway. It baffles me that library/framework authors get hit over the head with this constantly but nobody feels they should be bothering sqlite3's developers. I have no idea why they get a pass on this.

@sqlalchemy-bot
Copy link
Author

@sqlalchemy-bot sqlalchemy-bot commented Mar 23, 2017

Berker Peksag (@berkerpeksag) wrote:

Note that I don't have any problems with using batch mode in Alembic 0.9. Perhaps you might find http://alembic.zzzcomputing.com/en/latest/batch.html#batch-mode-with-autogenerate helpful if you are using the autogenerate feature of Alembic.

Anyway, thank you for implementing this Michael!

@sqlalchemy-bot sqlalchemy-bot added this to the tier 1 milestone Nov 27, 2018
leo-at-crefeld pushed a commit to leo-at-crefeld/seniorwork_app that referenced this issue Jun 11, 2020
SQLite doesn't support dropping columns, or the ALTER TABLE commands.
So the most recent migration wouldn't work for us. However, there's a
"batch mode" that accomplishes things the long way: it creates a table
with the new schema, copies all the data over, drops the old table, and
then renames the new table to replace the old one.

If you've got a db that attempted the 2b1421e46110 migration, and you
want to preserve that data, you probably need to edit it manually.
If you don't need that data, then just delete the app.db and run "flask
db upgrade" to generate one from scratch. Everyone else, you won't even
know that there was a hiccup, unless you find this commit message. ;)

- https://alembic.sqlalchemy.org/en/latest/batch.html#batch-mode-with-autogenerate
- sqlalchemy/alembic#21
- https://code.i-harness.com/en/q/1cfc76e

There might have also been complications from the circular reference
between Project and Exhibition.

- https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#foreign-key-support
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.