1. Michael Bayer
  2. alembic
  3. Issues

Issues

Issue #21 resolved

Column renames not supported on SQLite

Wichert Akkerman
created an issue

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.

Comments (30)

  1. Michael Bayer repo owner

    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.

  2. Wichert Akkerman reporter

    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.

  3. Marc Schlaich

    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:

    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.

  4. Michael Bayer repo owner

    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.

  5. Barry Warsaw

    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.

  6. Michael Bayer repo owner

    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.

  7. elvenlord

    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}

  8. Nicolas Dietrich

    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...

  9. Michael Bayer repo owner

    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 Akkerman 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?

  10. Michael Bayer repo owner

    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))
    
  11. Michael Bayer repo owner

    hi Sven-Hendrik Haase and others....I got a "hello world" version of this working as of 7eee8033c2c73a9368462ed73770d9932c572f32. 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.

  12. Michael Bayer repo owner

    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.

  13. Sean Behan

    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.

  14. John David Reaver

    Sean Behan 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?

  15. Michael Bayer repo owner

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

  16. Sean Behan

    John David Reaver 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.

  17. Sean Behan

    Michael Bayer 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.

  18. Michael Bayer repo owner

    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.

  19. Log in to comment