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
Is Zig's New Writer Unsafe?
If we wanted to write a function that takes one of Zig's new *std.Io.Reader and write it to stdout, we might start with something like: fn output(r: *std.Io.Reader) !void { const stdout = std.fs.File.stdout(); var buffer: [???]u8 = undefined; …
via openmymind.net September 20, 2025Are you an experienced software buyer? I could use some help.
If it seems like I’ve been relatively quiet lately on social media and my blog, that’s because I have. Liz, Austin, George and I have been busy toiling away on the second edition of “Observability Engineering” ever since April or May. I personally have be…
via charity.wtf September 19, 2025Class Warfare! Can I eliminate CSS classes from my HTML?
I recently read a brilliantly provocative blog post called "This website has no class". In it, Adam Stoddard makes the case that you might not need CSS classes on a modern website: I think constraints lead to interesting, creative solutions […]. …
via Terence Eden’s Blog September 19, 2025Generated by openring