Alembic default value to be used in add_column

Question

How do I get alembic to use a specified default value for a new column, without making it the server_default?

Only the existing rows should receive this default value. New rows inserted after should still get server_default.

Answer 1

You have to use server_default, but you need to pass the value as string in the same format your database deals with it.

https://docs.sqlalchemy.org/en/13/core/metadata.html#:~:text=server_default%20%E2%80%93,server%20side%20defaults

For example:

op.add_column('users', sa.Column('created_at', sa.DateTime(), nullable=False, server_default=str(datetime.now())))

Check what miguelgrinberg said here:

https://github.com/miguelgrinberg/Flask-Migrate/issues/265#:~:text=The%20server_default%20should%20be%20given%20as%20text%20in%20the%20native%20format%20of%20the%20database%2C%20not%20as%20a%20Python%20type.%20See%20https%3A//docs.sqlalchemy.org/en/13/core/metadata.html%23sqlalchemy.schema.Column.params.server_default.

Answer 2

I had a similar problem, I wanted a new column to be not nullable, which does not work for existing rows, of course. So I created the row without not null constraint first, filled the column with some custom python code and than altered the column (in the same migration) to have the constraint.

So you'd iterate over the existing objects, set the values according to the transient default.