Alembic Database Migrations

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 1 of 8. You run a manual database command directly on your production server to add a single column, and suddenly the application locks up. Your deployed code expected one structure, the database now has another, and there is no simple undo button. This is why we are looking at The Case for Migrations. When a software project first starts, changing the database schema is easy. You drop the tables and recreate them. Once you have real users and real data, that is no longer an option. Making manual changes across different environments like development, staging, and production eventually leads to a mismatch. Your application code relies on a specific database state. When that state drifts, the application fails. Alembic solves this by acting as version control for your database schema. Just as you track the history of your source code, Alembic tracks the history of your database structure. To use Alembic, you initialize a migration environment. This is a dedicated directory structure that you commit to your source control repository alongside your application code. It contains the instructions and configuration required to modify your database over time. The environment consists of three main pieces. First is the root configuration file, named alembic dot ini. This file lives at the base of your project. It stores basic settings, primarily the database connection URL, telling Alembic where the target database actually is. Next is the versions directory. This is where the migration scripts are stored. Every time you need to change the database schema, a new Python script is created in this folder. Each script defines two actions: an upgrade function to apply the change, and a downgrade function to revert it. If you need to add a table for user profiles, the exact instruction for that change lives in a script right here. The final piece is a file called env dot py. It is easy to confuse this with a general application configuration file or a place to store system variables, but that is not its purpose. Here is the key insight. The env dot py file specifically bridges your application models with the Alembic migration engine. It sets up the database engine, manages the connection lifecycle, and most importantly, it loads your SQLAlchemy metadata. This tells Alembic exactly what your models look like in code so it knows what the database schema should eventually match. Whenever you invoke an Alembic command, it runs this env dot py script first to establish the context it needs to operate. Instead of relying on a fragile memory of manual database commands, you have a structured, repeatable process. The true value of the Alembic environment is not just that it runs commands safely, but that it creates a definitive, versioned history of exactly how your data structures evolved from day one. If you enjoy the podcast and want to support the show, you can find us by searching for DevStoriesEU on Patreon. I would like to take a moment to thank you for listening — it helps us a lot. Have a great one!

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 2 of 8. Have you ever wondered how a database actually knows what schema version it is currently running? It is not inspecting your tables or guessing based on what columns exist. The secret lies in a single hidden table, and understanding how it connects to your code is the core of the Anatomy of a Revision. To change a database schema, you first create a migration script. You do this by running the Alembic revision command along with a short descriptive message, like create account table. Alembic generates a new Python file in your versions directory. The filename starts with a random string of characters, followed by your message. That string is a partial GUID, a globally unique identifier. Alembic uses these identifiers instead of sequential integers to avoid merge conflicts when multiple developers are creating migrations on different branches at the same time. If you open that new Python file, you will see two variables near the top: revision and down revision. The revision variable holds the GUID for this specific script. The down revision variable holds the GUID of the script that came right before it. Here is the key insight. Developers often think migrations are applied in order based on file creation timestamps or alphabetical filenames. They are not. Alembic strictly relies on the down revision chain. It reads these variables inside the files to build a linked list of your schema history. If a script does not point to a valid prior revision, the chain breaks. Below these routing variables, you will find two empty functions: upgrade and downgrade. This is where you manually write your schema changes. For our scenario, we are adding an account table. In the upgrade function, you write the logic to create the table, defining your columns, like an integer primary key and a string for the account name. The downgrade function must do the exact opposite. If upgrade creates the account table, downgrade must drop it. Every step forward must have a corresponding, reliable step backward. Once your script is written, you apply it by running the Alembic upgrade command, pointing it to head, which means the most recent revision in your chain. Here is what happens behind the scenes. Alembic connects to your database and looks for a table called alembic version. If this is your first migration, the table does not exist yet, so Alembic creates it. This table has exactly one row and one column, storing the GUID of the currently applied revision. Alembic checks this table, sees where the database is currently sitting, and runs the upgrade functions of every script needed to reach the target revision. Finally, it updates the version table with your new GUID. If you test your new account feature and realize something is wrong, you can roll it back cleanly. You run the Alembic downgrade command, passing a relative identifier like minus one to step back a single revision. Alembic looks at the current version in the database, finds the corresponding script, and runs its downgrade function. It drops the account table and updates the version table with the previous GUID. The most important takeaway is that a migration script is not just a loose collection of database commands. It is a self-contained node in a linked list that gives your database a precise path to move both forward and backward through time. Thanks for spending a few minutes with me. Until next time, take it easy.

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 3 of 8. Blindly trusting an automated tool to write your database migrations is a fast track to accidentally deleting your production tables. The problem lies in understanding what the tool actually sees when it compares your code to reality, and that is exactly what we cover today with The Magic and Limits of Autogenerate. Alembic has a core feature called autogenerate. When you run a revision command with the autogenerate flag, Alembic performs a comparison. First, it connects to your live database and inspects the current schema. Second, it looks at the target state defined by the SQLAlchemy models in your application code. It compares these two states and figures out the differences. There is a common misconception about this step. Autogenerate does not magically apply these changes to your database. It simply writes a draft Python script containing the migration operations it thinks you need to make the database match your models. You are required to review this candidate script before actually executing it against your database. When it compares your database to your models, autogenerate reliably detects basic structural changes. If you add a new model class to your code, Alembic drafts an instruction to create a table. If you remove a model class, it drafts an instruction to drop that table. It correctly catches when you add or remove columns, when you change a column to allow null values, or when you add basic indexes and unique constraints. For these standard operations, the feature saves you a massive amount of manual typing. Here is the part that matters. Autogenerate has blind spots because it cannot read your mind. Suppose you decide to rename an existing table in your SQLAlchemy models. You update the code and run the autogenerate command, expecting Alembic to draft a safe command to alter the table name. Instead, it proposes something highly dangerous. It drafts a command to drop the old table completely, destroying all the data inside, and then drafts a second command to create a brand new table with the new name. Alembic does this because it only sees that the old table name exists in the database but is missing from your models, and a new table name exists in your models but is missing from the database. It has no way to link the two together as a simple rename. You have to manually edit the drafted script to use a rename table operation instead. The exact same limitation applies to renaming columns. Autogenerate will interpret a renamed column as an instruction to drop the old one and add a new one. Beyond renames, there are changes autogenerate will ignore entirely by default. If you change a column data type, or if you modify a server default value, Alembic will skip over those differences. You can configure the tool to detect type and default changes, but you have to explicitly turn those settings on in your environment configuration. Even with those settings enabled, it will never detect changes to sequence objects or constraint names. The safest way to treat autogenerate is as a high-speed dictation tool that handles the boilerplate for you, rather than an intelligent system that understands the intent behind your code changes. Thanks for hanging out. Hope you picked up something new.

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 4 of 8. The easiest way to ruin a deployment is trying to drop a database constraint you never actually gave a name to. Your migration script works perfectly on your local machine, but the staging environment just crashed, complaining about a missing foreign key called something unpredictable like SYS C 0 0 2 9 3 3 4. The culprit is relying on database-generated identifiers. This episode covers the importance of naming constraints and how to automate them. Many developers assume the object-relational mapper handles dropping columns and their associated rules seamlessly. They define a unique constraint or a foreign key in their model, omit the name to save time, and move on. When you do this, the database engine takes over. Systems like Postgres or Oracle will auto-assign an arbitrary, system-generated name to enforce that rule. This creates a ticking time bomb for future migrations. When you eventually need to alter or drop that table or column, Alembic uses the drop constraint operation. That operation strictly requires the target constraint's exact name. If you let the database generate the name, it will almost certainly be different in development than it is in staging or production. You end up hardcoding a local constraint name into your migration script, which immediately fails when run against a different environment where that random string does not exist. To fix this, every single constraint in your database must have an explicit, deterministic name. Doing this manually across hundreds of models is tedious and easily forgotten. The better approach is to configure a naming convention dictionary on your SQLAlchemy MetaData object. This dictionary serves as a global template for your application. You define rules for each constraint type. For instance, you can specify that every index should be named using the prefix i x, followed by the table name, followed by the column name. You set similar templates for unique constraints, check constraints, and foreign keys. You then attach this configured MetaData object to your declarative base class. Here is the part that matters. Once this dictionary is in place, Alembic automatically integrates your naming conventions into both its autogenerate feature and its manual operations. When you run a command to autogenerate a new migration, Alembic looks at your models, sees a new constraint, and checks the MetaData dictionary. It applies your template, calculates the explicit name, and writes that exact string into the generated python script. Because the generated script explicitly commands the database to use that specific name, the constraint will be identical across every single environment. This integration extends to Alembic operations run during the upgrade process itself. If a migration script includes a create table or add column operation with inline constraints that lack explicit names, Alembic does not just pass them blindly to the database. It intercepts them, consults the naming convention template, and assigns the correct deterministic name before executing the database commands. A deterministic naming convention guarantees that a rule created on your local machine will share the exact same identifier when it reaches your production servers, entirely eliminating the risk of un-droppable constraints. Thanks for listening. Take care, everyone.

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 5 of 8. In highly regulated enterprise environments, developers almost never get direct access to run Python against production databases. When deployment day arrives, database administrators will usually lock you out and demand a raw, reviewable script instead. Bridging that gap between your Python codebase and their strict deployment process is handled by Offline Migrations and SQL Generation. Normally, Alembic connects to a live database and executes schema changes directly over that connection. But when you need to hand over a plain text file to a DBA team, you use offline mode. By appending the dash dash sql flag to your upgrade or downgrade commands in the terminal, Alembic completely changes its execution behavior. Instead of running the statements against a database engine, it renders them as a continuous string of standard SQL and dumps them directly to standard output. You can easily redirect this terminal output into a text file. This dual behavior is not magic, it is explicitly defined in your project environment file, typically named env dot py. If you look inside that file, you will find two distinct routing functions. The first is run migrations online. This function creates a live database engine, binds an active connection to the Alembic context, and runs your migration scripts step by step. The second function is run migrations offline, and this is where the translation happens. When you pass the sql flag, Alembic detects the flag and triggers this offline function instead. It configures the context using only a database URL. No network connection is made, and no engine is instantiated. It then takes your Python migration structures and generates the exact CREATE, ALTER, or DROP statements, wraps them in standard BEGIN and COMMIT transaction blocks, and formats everything for your specific database dialect. Here is the key insight. Because offline mode never actually connects to the database, your migration scripts cannot rely on active database state. You cannot execute a SELECT statement inside an offline migration to check if a row exists, and you cannot inspect the current state of a table before making a change. If your Python code expects a database cursor to return data to decide what schema change to make, the offline generation will fail. The script must be purely declarative. It simply tells Alembic what structures to generate. Consider a developer finishing a local feature branch. They have run the migrations locally in online mode to verify everything works against their testing database. For the production release, they run the upgrade command with a specific start revision and end revision, add the sql flag, and route the output to a text file. The result is a clean, sequential SQL script. The developer hands this file over to the DBA team. The DBAs can read it, verify it against their strict security policies, and apply it during the maintenance window using standard database administration tools. You also have control over how this offline output is generated. Inside the run migrations offline function, the context configure call accepts parameters that tweak the rendered SQL. A common requirement is converting variables into literal values. By enabling literal binds in the configuration, you ensure that any data inserted during the migration includes the actual values directly in the SQL string, rather than outputting generic parameter markers. This ensures the output is a fully self-contained script ready for execution. The true value of offline generation is predictability; it turns dynamic Python state changes into static, auditable SQL that any deployment pipeline or security team can verify before a single table is modified. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 6 of 8. You are building a local app, testing your migration, and you try to drop a single column. The database throws an error, and you discover a surprising fact: a simple drop column command fundamentally does not exist in your database engine. This is the reality of working with SQLite, and it is exactly why Alembic provides Batch Migrations. SQLite has a lightweight architecture with very limited support for altering existing tables. You can add a column to a table, but if you want to drop a column, change a column type, or rename a column, the database engine simply does not support it. Many developers run into this when they try to run a standard drop column operation in their Alembic script. It works perfectly on PostgreSQL, but on SQLite, it crashes. Alembic solves this limitation using a pattern called the move and copy workflow. Since the database cannot modify the table structure in place, Alembic rebuilds the entire table from scratch behind the scenes. To use this feature, you do not call the standard operation methods directly. Instead, you use a context manager called batch alter table. You pass the name of your table to this context manager, and then you define all your structural changes inside that block. When the block finishes executing, Alembic takes over and orchestrates the table replacement. Let us look at a specific scenario. You have a table named user_data, and you need to drop a column called bar. Inside your script, you open the batch alter table context manager for the user_data table. Inside the block, you instruct it to drop the column named bar. That is all the Python code you write. The moment the context manager exits, Alembic generates a sequence of precise SQL commands to execute the move and copy workflow. First, Alembic reads the current structure of your table. It generates a create table statement for a brand new, temporary table. This new table has the exact same schema as the original, except the bar column is missing. Next, Alembic copies your data. It runs an insert statement that selects all the existing rows from the original table and pushes them into the new temporary table. Because the bar column no longer exists in the new schema, that specific data is simply left behind. Once the data is safely copied over, Alembic drops the original user_data table entirely. Finally, it renames the temporary table back to user_data. The database ends up in the exact state you wanted, and your application never knows the table was completely rebuilt. Here is the key insight. The batch alter table context manager batches your operations together for performance. If you need to drop two columns, add a new one, and change a data type, you put all of those instructions inside the same context block. Alembic will compile all of those changes and perform the move and copy workflow exactly once. Rebuilding a large table is an expensive operation involving heavy disk reads and writes, so doing it in a single pass is crucial. Batch operations turn a severe engine limitation into a completely invisible implementation detail, letting you write clean, database-agnostic migration scripts while Alembic handles the heavy lifting of table recreation safely in the background. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 7 of 8. A messy code merge is annoying, but it usually just fails a local test. Two conflicting database schemas, on the other hand, can bring your entire deployment pipeline to a halt. You merge your code to main, but suddenly your database migration tool complains about multiple heads. This is the reality of working with branches in Alembic, and resolving it requires understanding how your database timeline forks. Branches occur naturally in any team environment. Developer A works on a feature and generates a migration script to add a shopping cart table. This new script points to the current database state, let us call it revision 100, as its base. Meanwhile, Developer B works on a different branch and generates a script to add an account column. Their script also points to revision 100 as its base. Both developers test locally, everything works fine, and both pull requests are merged into the main repository. You now have two separate migration scripts in your project that both claim to be the immediate successor to revision 100. The migration timeline has split into two parallel paths. If you run the command to upgrade the database to the head revision, Alembic will immediately stop. It will throw an error stating there are multiple heads. The tool refuses to guess which migration should be applied first, because applying database changes in an unpredictable order is dangerous. To resolve this, you must reconcile the divergent streams using the Alembic merge command. Here is the key insight. Merging in Alembic is not like a Git merge. It does not look inside the Python files and try to automatically combine your schema changes into a single file. Instead, the merge command creates a completely new, empty migration script. This new script contains no database operations. It does not alter tables or add columns. Its entire purpose is structural. Inside the generated Python file, the down revision variable is set to a tuple containing the revision IDs of both divergent scripts, rather than a single string. This single action ties the two parallel branches back together. It creates a new, unified head for the timeline. When you trigger the command, you typically pass it the word heads, which tells Alembic to find all the current endpoints in your migration history and merge them. You can also attach a message string to document the synchronization, much like a commit message. Once this merge script is generated and committed to your repository, your timeline is linear again. The next time you run the upgrade command, Alembic will execute both parent scripts in a safe sequence and then stamp the database with the new merged revision ID. The structural integrity of your database history depends on this synchronization. An Alembic branch is just a fork in your migration history, and fixing it means generating a dedicated script that acts as a physical knot tying those divergent paths back into one clear sequence. If you find these episodes helpful and want to support the show, you can search for DevStoriesEU on Patreon. That is all for this one. Thanks for listening, and keep building!

Hi, this is Alex from DEV STORIES DOT EU. Alembic Database Migrations, episode 8 of 8. For modern containerized applications, relying on manual command-line scripts to prepare your database before your app starts is an operational headache. Your application should be smart enough to verify and update its own schema on startup. To do that safely, you need Production Power-Ups. Most developers only know Alembic as a terminal tool. You type an upgrade command, and it modifies the database. But the command-line interface is just a thin wrapper. Underneath is Alembic's programmatic API. You can trigger migrations directly from your Python application code. This allows modern backend frameworks to execute schema updates automatically during their startup routine, ensuring the code and the database are always perfectly synchronized. However, doing this naively introduces a subtle problem. When you run a migration programmatically, Alembic defaults to its standard behavior. It reads your configuration file, creates a brand new database engine, opens a connection, runs the migration, and closes it. But your application just started up. It already created an engine and initialized a connection pool. Letting Alembic spin up an entirely separate connection during startup is inefficient. More importantly, it can be dangerous. If your application startup sequence holds a lock on a table, Alembic's separate connection will hang waiting for that lock, causing a deadlock that crashes your container. This is also a major issue if you run automated tests against an in-memory database. In that scenario, a brand new connection points to a completely empty database, meaning your migrations will fail to apply to the data you are actually testing. You solve this by handing your application's active database connection directly to Alembic. This is done using the Alembic configuration object. First, your application code instantiates a configuration object, pointing it to your main initialization file. Here is the key insight. The configuration object has an attributes dictionary. This acts as a bridge for passing live Python objects into the migration environment. You acquire an active connection from your application engine, and you assign it to a key called connection inside that attributes dictionary. Next, you call the Alembic programmatic API, specifically the upgrade command, passing it your modified configuration object and telling it to upgrade to the head revision. But Alembic does not automatically know what to do with that injected connection. You must modify your migration environment file to complete the circuit. In the section of your environment file that handles online migrations, you add a simple check before the setup happens. You instruct the script to look inside the configuration attributes. If it finds a connection object there, it skips creating a new engine. Instead, it configures the migration context to use the connection you provided. If it does not find a connection in the attributes, it safely falls back to normal behavior, creating a new engine from the database URL. This fallback ensures your command-line tools still work exactly as they did before when you run them locally. By designing your system this way, you transform migrations from an external deployment chore into a native, predictable part of the application lifecycle. When a new instance spins up, it requests a connection, handles its own upgrades within that session, and seamlessly transitions to serving traffic. That wraps up our series on database migrations. I highly encourage you to explore the official Alembic documentation and try these programmatic configurations hands-on. If you have ideas for entirely new topics you would like to hear, visit devstories dot eu and let us know. That is all for this one. Thanks for listening, and keep building!