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.
- Give Your Spouse the Gift of a Couple's Email Domain from mtlynch.io
- Skip the Next iPhone from Articles on Jose M.
- Have smart glasses finally hit an inflection point? from The Torment Nexus
- The McPhee method from the jsomers.net blog
- Pluralistic: LLMs are slot-machines (16 Aug 2025) from Pluralistic: Daily links from Cory Doctorow
- Pluralistic: Bluesky creates the world's weirdest, hardest-to-understand binding arbitration clause (15 Aug 2025) from Pluralistic: Daily links from Cory Doctorow
- Just a Little More Context Bro, I Promise, and It’ll Fix Everything from Jim Nielsen’s Blog
- The Futzing Fraction from Deciphering Glyph
Articles from blogs I follow around the net
If you’re like me; you like files, you like web technologies like HTML, CSS, and JavaScript, you like markdown, you like kanban, you like pomodoro, and you like apps. If this sounds like you reach out. I’ll be open sourcing something in the coming weeks a…
via Colin Devroe September 3, 2025Pluralistic: The worst possible antitrust outcome (03 Sep 2025)
Today's links The worst possible antitrust outcome: Hope you like enshittification. Hey look at this: Delights to delectate. Object permanence: Amazon drivers hang phones from trees; DVD Jon v Windows DRM; Chevron's dirty tricks. Upcoming appearan…
via Pluralistic: Daily links from Cory Doctorow September 3, 2025Give a Problem. Grow a Programmer.
In 2009, I kicked off my senior year in college with a class that ultimately changed the way I thought about my degree—and my future.
via flower.codes September 3, 2025Generated by openring