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

  1. Mike 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. Mike 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. Mike 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. Mike 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. Wichert Akkerman reporter

    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.

  11. Mike 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))
    
  12. Mike 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.

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

  14. Log in to comment