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.
- workflows for ai coding from /* 🤖🛠️ */
- Reinvent the Wheel from Matthias Endler
- CAPTCHAs are over (in ticketing) from pretix – behind the scenes
- You Can Choose Tools That Make You Happy from Fernando Borretti
- Using Git as S3 from Kris Tun
- Future Fonts from Blog – Brad Frost
- 21st Century C++ from Communications of the ACM
- Submarines DevCon 2025 Keynote Speech from JoshHaines.com
Articles from blogs I follow around the net
Tradeoffs to Continuous Software?
I came across this post from the tech collective crftd. about how software is in a process of “continuous disintegration”: One of the uncomfortable truths we sometimes have to break to people is that software isn't just never “done”. Worse even, it rot…
via Jim Nielsen’s Blog May 28, 2025Pluralistic: America is a scam (28 May 2025)
Today's links America is a scam: Welcome to the age of cheating. Hey look at this: Delights to delectate. Object permanence: 2010, 2015, 2020 Upcoming appearances: Where to find me. Recent appearances: Where I've been. Latest books: You keep readi…
via Pluralistic: Daily links from Cory Doctorow May 28, 2025How I taught my 3-year-old to read like a 9-year-old
Tripling reading level through tutoring
via The Intrinsic Perspective May 28, 2025Generated by openring