SQLite batch migrations: an option for always using recreate style on SQLite

[hiyoko3m]

Hi! I want a new option of batch_alter_table's recreate argument; always use the recreate style on SQLite backend. Here is a use case:

from alembic import op
from sqlalchemy import DateTime
from sqlalchemy.sql.functions import current_timestamp

with op.batch_alter_table("some_table") as batch_op:
    batch_op.add_column(
        "some_datetime",
        DateTime, 
        server_default=current_timestamp()
    )

The above code raises an error on SQLite, since alembic does not use the recreate style for a batch only having add_columns, but SQLite does not support ADD COLUMN with a default value of CURRENT_TIMESTAMP.

The option recreate=always at batch_alter_table can be used for avoiding this error, but then alembic uses recreate style also on other backends. Such a behavior is not desirable in some cases, e.g., using MySQL for the production environment (with a large data) and using SQLite for the development environment (with fewer data).

So I think the flag for always using the recreate style on SQLite backend is useful. Or are there any methods which alternates this?

[zzzeek]

I think a better option here would be that the SQLite batch op feature detects those cases where "add column" requires the batch version of things and then does the right thing automatically.

can you add some detail on the sets of circumstances under which SQLite does not allow additions of columns? is it with any kind of server default ?

[hiyoko3m]

That sounds great!
According to this SQLite's official document, "add column" with a primary key or unique key constraint, or having some kinds of server default needs the recreate style.

(I'm not sure that other circumstances described in the page should be treated in this context.)

[zzzeek]

there is a patch in review at #883. we have a little bit of a delayed timeline for alembic 0.7 though, so you might want to use this patch locally in the interim.

[hiyoko3m]

Thanks a lot! I will try the patch locally in my code :)