CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyalembic
SivolcC
SivolcC

Reputation: 3608

Alembic's server_default and Postgres

I would like to run a migration using alembic from a sqlalchemy model to another. The initial model looks like this:

from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy_utils import UUIDType

class Foo(db.Model):
    __tablename__ = 'foo'
    id = db.Column(UUIDType(binary=True), primary_key=True)
    foo_field = db.Column(JSONB)

and I want to make foo_field not nullable:

class Foo(db.Model):
    __tablename__ = 'foo'
    id = db.Column(UUIDType(binary=True), primary_key=True)
    foo_field = db.Column(JSONB, nullable=False)

I want to run an alembic script to alter the column, but since some of my existing foo_field are null, I want to use server_default to apply a default value (an empty dict) when altering

op.alter_column('foo',
         'foo_field',
         existing_type=postgresql.JSONB(astext_type=sa.Text()),
         nullable=False,
         server_default="{}")

I tried different options to pass to this server_default, such as text("{}"), lambda: {}, {}, text("SELECT '{}'"). But it seems to be just ignored and all I get is an IntegrityError when running the upgrade : 

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "foo_field" contains null values [SQL: 'ALTER TABLE foo ALTER COLUMN foo_field SET NOT NULL'] (Background on this error at: http://sqlalche.me/e/gkpj)

Does a string representing an empty dict is considered as not-null? What should be passed to this server_default? Should I fraction the code in multiple steps to fill the default value before setting the field not nullable (not the prefered option, I have a bunch of columns that I want to apply this to)?

I am using postgres 10.5, I read that postgres 11 will kinda handle better this sort of operation, should I switch to it now?

Thanks in advance for help/advices

Upvotes: 5

Views: 11024

Answers (2)

Vishnu Sajeev
Vishnu Sajeev

Reputation: 974

If anyone comes here again, this is what I use

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("ALTER TABLE {table_name} ALTER COlUMN {column_name} set DEFAULT {new_default_value};")
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("ALTER TABLE {table_name} ALTER COlUMN {column_name} set DEFAULT {old_default_value};")
    # ### end Alembic commands ###

Upvotes: 5

moshevi
moshevi

Reputation: 5973

you can use a Rewriter and do so yourself automatically.

override the ops.AlterColumnOp operation and return two operations first one being an update column operation with the server default value and the second one is the original op.

Upvotes: 0

Related Questions