That Dreaded Alembic NotNullViolation Error (and How to Survive It)

Source: Dev.to

What Happened?

Imagine you have a table called organizations full of precious data. You decide to add a new column called plan.

If you add it as NOT NULL, every row must have a value. But the table already contains rows, so PostgreSQL refuses the change:

Adding a NOT NULL column to existing data without a default is basically like asking someone to fill out a survey you didn’t give them in advance. They don’t know what to answer, so they freak out.

Quick fix: add a default

op.add_column(
    'organizations',
    sa.Column('plan', sa.String(), nullable=False, server_default='free')
)

• Existing rows get the value 'free' automatically.
• After the migration you can remove the default if you prefer.

More controlled approach: add as nullable, then populate

# 1. Add the column as nullable
op.add_column(
    'organizations',
    sa.Column('plan', sa.String(), nullable=True)
)

UPDATE organizations SET plan = 'free' WHERE plan IS NULL;

# 3. Make the column NOT NULL
op.alter_column('organizations', 'plan', nullable=False)

• This gives you a chance to apply custom logic per row before enforcing the NOT NULL constraint.

Manual preparation before the migration

1. Update the table manually (e.g., with an UPDATE statement).
2. Run the Alembic migration that adds the column as NOT NULL.

Old rows are ready, and PostgreSQL won’t raise an error.

---

Adding a NOT NULL column to a table with existing rows is like adding a new rule to a crowded party: someone will get upset if you don’t provide a plan. Always consider existing data—give PostgreSQL a default, update the old rows, or do both.

Alembic errors might look scary, but they’re really just your database’s way of saying:
“Hey, I love your data too much to let it break. Let’s do this right.”