Recreating Foreign Keys with Alembic
Alembic is a great tool for keeping track of schema changes in python applications. I am using it to manage DB migrations for braindump along with Flask SQL Alchemy as my ORM. One challenge is managing proper foreign key constraints. By default if you define a foreign key relationship in your schema definition it will not generate the proper migration code. For example, in braindump we have a one to many relationship between users and notes.
class User(UserMixin, db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(254), unique=True, index=True) password_hash = db.Column(db.String(256)) confirmed = db.Column(db.Boolean, default=False) avatar_hash = db.Column(db.String(32)) created_date = db.Column(db.DateTime(), default=datetime.utcnow) updated_date = db.Column(db.DateTime(), default=datetime.utcnow)<span class="nx">notes</span> <span class="o">=</span> <span class="nx">db</span><span class="p">.</span><span class="nx">relationship</span><span class="p">(</span> <span class="s1">'Note'</span><span class="p">,</span> <span class="nx">backref</span><span class="o">=</span><span class="s1">'author'</span><span class="p">,</span> <span class="nx">lazy</span><span class="o">=</span><span class="s1">'dynamic'</span><span class="p">,</span> <span class="nx">cascade</span><span class="o">=</span><span class="s2">"all, delete-orphan"</span><span class="p">)</span>
sa.ForeignKeyConstraint(['author_id'], ['users.id'], ),
ondelete
action. What we actually want is something like this:
sa.ForeignKeyConstraint(['author_id'], ['users.id'], ondelete='CASCADE')
ondelete='CASCADE'
will create the proper relationship. If you are like me, and do not catch this, then you will need to run a second migration to remove and recreate these keys. The migration code to do this is shown below:
from alembic import op import sqlalchemy as sadef upgrade(): with op.batch_alter_table(“notes”) as batch_op: batch_op.drop_constraint( “notes_author_id_fkey”, type_=“foreignkey”) op.create_foreign_key( “notes_author_id_fkey”, “notes”, “users”, [“author_id”], [“id”], ondelete=“CASCADE”)
Thank you for reading! Share your thoughts with me on bluesky, mastodon, or via email.
Check out some more stuff to read down below.
Most popular posts this month
- 2024
- Reinstalling Windows at 1am
- SQLite DB Migrations with PRAGMA user_version
- My Custom Miniflux CSS Theme
- How to Disable Wayland in Debian Testing
Recent Favorite Blog Posts
This is a collection of the last 8 posts that I bookmarked.
- The AGI economy is coming faster than you think from Freethink
- Rolling the ladder up behind us from Xe Iaso's blog
- In Praise of “Normal” Engineers from charity.wtf
- Reports of Bluesky's death have been greatly exaggerated from The Torment Nexus
- What Would a Kubernetes 2.0 Look Like from matduggan.com
- We Can Just Measure Things from Armin Ronacher's Thoughts and Writings
- The Gentle Singularity from Sam Altman
- Whale Watching from https://popagandhi.com/
Articles from blogs I follow around the net
Pluralistic: Daniel de Visé's 'The Blues Brothers' (21 Jun 2025)
Today's links Daniel de Visé's 'The Blues Brothers': Far more than production gossip – an unmissable portrait of a turning point in American comedy and music. Hey look at this: Delights to delectate. Object permanence: 2005, 2010, 2015, 20…
via Pluralistic: Daily links from Cory Doctorow June 21, 2025Hiding metrics from the web
In 2012, artist Ben Grosser released a browser extension called Facebook Demetricator. Once installed, it hid all metrics from Facebook’s interface: likes, comments, notifications, unread messages, and so on. “What’s going on here is that these quantifica…
via Manual do Usuário June 21, 2025It's like surfing
The weird thing about engineering management is that you feel kinda useless. Yet if you stop, projects stop.
via swizec.com RSS Feed June 21, 2025Generated by openring