correct pattern for adding a column with a default value, without setting a server_default ?

[jvanasco]

I need to add a new INT column that is NOT NULL to an existing table in sqlite. The existing records should show an initial value of 1 during the migration, but the column should not have a default value when the migration is done - the default is to only be managed on the python side. I can think of a few ways to do this using consecutive batch_ops, but I wanted to know if there was a preferred way (readability? speed? another reason?)

• Alter table as nullable, set default, make not null
• Alter table as not-null with a server default, then drop server default
• other ways?

[zzzeek]

well it's SQLite, so we dont exactly have ALTER COLUMN (it looks like ALTER TABLE/ADD/DROP COLUMN is coming along though). I guess how it could work is, do the ADD COLUMN including the default value of 1, then do an alembic batch migrate to remove the default (which will create a new table and copy the old one over). if this were any other database, then yes you could just drop the server default directly.

[jvanasco]

That's only 2 operations! great thinking! Thanks, Mike.

I implemented working versions of the 3 step operations I mentioned above, but will switch to this.

One of them was:

with op.batch_alter_table:
    batch_op.add_column # nullable=True
op.execute(
    "UPDATE table SET column = 1 ;"
with op.batch_alter_table:
    batch_op.alter_column # nullable=False