Introducing Alembic for FastAPI Database Migrations
In this video, we're going to add the Alembic package to our FastAPI project. And Alembic is a package that allows you to create and manage migrations within a Python application. And we'll see how nicely Alembic plays with the SQLModel package that we installed in the last video. And whenever we make changes to our model classes, we're going to set up Alembic in a way that it detects those changes and automatically generates the appropriate revision file in response to those changes.
And having a proper migration setup using something like Alembic is a really important part of application development. You need a way to manage these changes that you're making to the underlying database and a way to track the historical revisions and changes that have been made to that database.
So if you're enjoying this content, give the video a thumbs up, and it would be greatly appreciated if you'd subscribe to the channel if you've not already done so. And let's dive in.
So let's start with a quick look at Alembic documentation. You can see that it's a lightweight database migration tool for usage with SQLAlchemy, and that includes the SQLModel package that we're using with FastAPI that builds on top of SQLAlchemy, so we can use Alembic with that as well.
Now, a note about pronunciation. I don't know what the best way to pronounce these tools is. Is it SQL Alchemy or SQLAlchemy? If you have an opinion on this, let me know in the comments. I'm going to keep using SQLAlchemy and SQLModel because it rolls off the tongue a little bit better in my opinion.
Now, we did a video on Alembic previously. It should be appearing on the screen now. And if you're not familiar at all with Alembic, that would be a good introduction to the package. What we're going to do to get started in this video is go to the installation section, and we can install Alembic in a Python virtual environment by using pip. So let's copy the command and go over to VS Code where we have the project open.
Initializing the Alembic Migration Environment
Now, this is an existing FastAPI project with database connectivity already implemented, and we're using SQLModel here to create all of the tables in the database. And the database that we're using at the moment is SQLite. Now, ideally, we want to manage the database and manage any changes to that schema using Alembic or any other migration tool.
So what we're going to do in the virtual environment is paste that command that we've copied in. It's the pip install alembic command. We're going to run that and it's going to install Alembic. Now, once that's completed, we can clear the terminal, and we have another command that we can run to start the migrations in this project. And that's the alembic init command. And we can give the Alembic migration environment a name. I like to call it migrations. Once we execute that, it's going to create a directory on the left-hand side in the root of this project called migrations.
So the alembic init command is going to generate that folder and some files within that folder, and it's also going to create this file at the root of the project, and that's the alembic.ini file. Now, if you want to learn more about the contents of this file and the folder that was created, check out that video on Alembic that we did.
Configuring Alembic for SQLModel
What we're going to do now in terms of this FastAPI application is we're going to use the models that are defined in this application. So these models that have table=True, these are SQLModel classes and we want to manage any changes to these classes using Alembic.
So for example, if we look at the album class here, this represents album data and there will be an album database table under the hood. And at the moment, the album has an ID and it has a relationship or a foreign key to a band. And as well as that, because of the structure of these, it's inheriting a couple of extra properties. Now, let's say we wanted to add a new field to this class and to the underlying database table, we want to manage that change using Alembic. So when we run Alembic commands, we want to autogenerate a migration file, and when we apply that migration, it's going to change the underlying database. So let's see how to do that.
Now, if you look at the migrations directory on the left-hand side, it's generated a file called script.py.mako. And again, I don't know how that's pronounced. Now I'm going to pronounce this Mako. And what we're going to do in this Mako file is import SQLModel at the top. Now you can see that when you run alembic init, there's a couple of imports at the top from the Alembic package. We import the op and that stands for operation. And we also import sqlalchemy as sa. Now, in order to autogenerate migrations from SQLModel classes, we're also going to import SQLModel at the top of this Mako file. And if you're wondering what Mako is, this is a template library for Python, and the contents of this template are going to be injected into the revision files that are generated by Alembic. So we're defining what is essentially a schema for those revision files, and we also want to import SQLModel as part of each migration.
The next thing we need to do is go to the env.py file, and this is a Python file. And what we're also going to do at the top is from the SQLModel package, import the SQLModel base class. And underneath the Alembic import, from our models package, we're going to import the two models that are defined with the table=True keyword argument, and that's the Album and Band tables. And I say tables, these are actually model classes, and they're defined in models.py. This is one of them, and the Band model is here at the bottom. So we're importing them into Alembic's environment file and essentially telling Alembic about these models.
What we're now going to do is we're going to find a property in this file called target_metadata. Now, let's get down to this property. What we can do here is add the model metadata object. So for example, if we had a model called MyModel in the application, we could reference the metadata here and set target_metadata to that. Now, we've imported the SQLModel base class and we can reference a metadata property on this. And what it's going to do is add the metadata for all tables, or all models, sorry, that have table=True. So again, telling Alembic about those model classes in the target_metadata property.
Setting the Database URL in Alembic
Now there's one more thing we need to do here before we can actually generate a revision or a migration file. We need to tell Alembic what the SQLAlchemy URL is. In other words, we need to tell Alembic, what is the URL that points to our database. Now, to do that, we can go to the alembic.ini file, and we're going to search for a property here called sqlalchemy.url. Now, the value of the SQLAlchemy URL should point to the database that we're actually connecting to. So for example, if we have a local SQLite database, this should point to that. Or if we have a database on a remote server, for example, it could be Postgres or MySQL, we can provide the URL to that database in this property.
Now, what we're going to do in order to point to the SQLAlchemy URL is we're going to define this in the env.py file. So let's go back to that. And at the top of this file, from the pathlib module in Python, let's import the Path object. And we can use the config that we have here in order to set properties in the Alembic configuration. And one of the properties we're going to set is the SQLAlchemy URL. So let's go back to env.py and below this config, I'm going to point to our database. So from the env.py file, we take the current directory and we look at the parent directory, which is the project root, and then we're looking at the database.sqlite file within that directory. And I'm going to change this, doesn't have a three at the end. Once we have that path, we can resolve the full absolute path on the file system, and that's going to be the path to our SQLite file.
And after we've got that path, we can use the config object. And in order to set some configuration, there's a function called set_main_option. We're going to call that function. The first argument is the name of the property that we want to set. So let's set that to sqlalchemy.url. And the value that we want to set for that property, we're going to define that using an f-string. And we're connecting to SQLite, so we're going to use the sqlite protocol, and that has three forward slashes after the name. And we're referencing the value on the line above using that f-string syntax. So let's save the env.py file. And what we've done on these two lines of code is tell Alembic where our database lives by setting that sqlalchemy.url property.
Now, if you have a simple configuration, again, we can go back to alembic.ini. If your database has a remote URL, you can just set this directly here without having to call set_main_option, but because I'm doing this dynamically in Python using file system operations, we're doing this using the config.set_main_option function in the env.py file. And with that, we're now ready to generate the initial revision for this application.
Generating the Initial Migration Script
Now inorder to generate a migration, we can go back to the command line, and we're going to run a command, and that's the alembic revision command. And because we want to autogenerate these revisions from the models that we have, and these are the models that we pointed Alembic to using the SQLModel metadata, we can pass a flag for this, and that's --autogenerate. And when we generate a revision file, we can also generate a message for that. And this -m and then a message, it's very similar to what you do when you create a commit using git. Each commit that you generate should have a message, and it's the same for Alembic revisions. We use the -m option. Now, I'm going to call this initial migration. So let's execute that command.
And what happens when we do that, if we go to the versions directory within this migrations folder, the command has generated this initial_migration.py file. So we can go to that file. And what we're going to do is take a look at some of the properties and the functions in this file. Now, you can see the revision has an identifier and the down revision identifier is set to None because this is the initial migration. If we generate a second migration, it's going to point to the previous one in this down_revision property.
Now, one thing to note is that the upgrade function here is empty, and so is the downgrade function. So I'm going to explain why that is in a second, but the upgrade function here, the code in this function defines what change you actually want to apply to the database when you run the migration against that database. And conversely, the downgrade function here will define what happens when you want to reverse those changes.
Now, the reason that these functions are empty is because we already have this database file on the left-hand side and the tables within that already exist. And the reason for that, if we go to database.py, is because we called this init_db function in the last video, and that is going to use this helper function from SQLModel to actually create the tables in the database for the SQLModel classes.
Now what I'm going to do is delete the database.sqlite file, and then if we go back to the command line, I'm going to remove the initial migration file as well. Now after we've done that, we can rerun the alembic revision command at the bottom. And again, that's going to generate a migration file, and we can look at that now and see what is in this file.
Applying the First Migration with alembic upgrade
Now the upgrade command, you can see that it actually contains some code. So what's been generated here in this upgrade command? Now I'm going to tab some of these operations over so that we can see this a little bit better. And let's start with the one at the top. It creates a table called band and it's using the schema that's defined here. So we have a SQLAlchemy column for the name, the genre, and the ID. So we use this op object, which is imported at the top from Alembic, to call the create_table function. And we're doing that for both the band and the album.
Now, each SQLAlchemy column has a data type, for example, a string or an enum type or an integer. And these types coming directly from the model classes. So if we look at the Band model here, we have an ID which is of type integer, and that is also the primary key. Now, let's go back to the migration file, and you can see that the primary key constraint has also been added when we generated that revision. And we also get some constraints. For example, some fields here in the Album model are nullable and others are not. So when we run the Alembic revision command with that autogenerate flag, what Alembic is doing is it's looking at the models.py file, and it's looking at the SQLModel classes, and if you're using SQLAlchemy, it's going to be exactly the same. And it's creating the table by introspecting those classes and looking at the properties and the data types on each one of those classes.
Now, if we look at the database.sqlite file at the moment, and I'm using the SQLite extension for VS Code here to do this, let's look at the database now. And you can see we only have one table. It's called alembic_version. So let's go back to the migration file. We have a couple of create table statements. How do we actually apply those to the database? Before we do that, let's just go down to the downgrade command and very quickly look at this. This is for reversing a migration, and for this particular migration, in order to reverse it, it's very simple. We call the drop_table function on the Alembic op object. So again, let's go back to the upgrade function. How do we apply these changes and actually create the tables in the database? Let's go back to the command line and we're going to use the Alembic command line tool. Now we use the revision command to generate the file. What we're going to use now is a command called upgrade, and if we want to upgrade the database based on the most recent file that was generated, we can use alembic upgrade head. Now, let's run that command and we're going to look again at the database. So let's refresh this SQLAlchemy or SQLite database, sorry. And we can now see we have an album and a band table in this database.
Refactoring FastAPI to Use Alembic Exclusively
Now, what I'm going to do in the FastAPI code is very quickly remove one of the functions that we defined. So let's go back to main.py. We defined this lifespan event that called init_db. We no longer want to manage this from FastAPI. We want to manage the database completely using Alembic migrations. So let's remove this function now. And we can remove the lifespan event that's passed into the FastAPI object. And we can now remove the import of the async context manager at the top, and everything should still work as expected. But we're now managing the database using the migrations.
Now I want to very quickly test one of the endpoints. So let's go back here and I'm going to send a POST request to create a new band in the database. And in order to do that, we need to actually start the server. So let's run uvicorn and point to our main app. When we run that, we will get the server running. And we can actually send this post request. And when we send that, at the right-hand side, we get back the data for the newly created band. So everything is still working. We're going to stop the server at the bottom. And I'm going to close this response page.
Let's now demonstrate an example of why these migrations are so useful when you're working with model classes. So I'm going to clean this up a bit. I'm going to close all of the files at the top and we're going to go back to models.py.
Auto-Generating a Migration from Model Changes
Now, the typical workflow when you're developing an application is that you have a model and you realize that something has to change. You need to add a new field or you need to remove a field or you need to add a new relationship from one model to another. Now, remember the revision files, the revisions themselves are autogenerated from our SQLModel class definitions. So when we change a model definition, we want to generate a migration file that encapsulates those changes to our database schema. So let's do that now.
We have a band model here, and let's imagine I wanted to add a field to this model. And let's say we want to add a field that tracks which year the band was formed. So we're going to add a new field here called date_formed, and that's going to be of type date. But we also want to make this nullable, so let's give it a union type of date or None. And I think we have date imported at the top here from Python's datetime module. And that Python date object is going to be mapped to the underlying date type in the database.
Now, what we need to do with Alembic when we change a model like this is we need to generate the migration. So let's go back to the command line. And again, we're going to run the alembic revision command. And to that, we're going to pass the --autogenerate flag. And we can also pass a message to that as well. So let's add the following message: we've added the date_formed field to the band model. And we can now execute this command.
Let's go back to the migrations folder and the versions folder within that. And we can look at these files and we can see the new revision file that's been generated by that command. So if we inspect that file, what we're going to see here is that it points in the down_revision to the previously generated revision file. And of course, this new revision has its own identifier. And if we look at the upgrade function here, we have a new operation, and it's calling the add_column function. And the table that we're adding the column to is the band table. That was the model that we changed. And the column that we're actually adding is a column called date_formed. And that was the name of the field that we added to the model. And the type for that in the underlying database, we're using the SQLAlchemy.Date type. And that's going to set the data type in the new column in this table to the date type. And finally, because we used the union of date and None, it's going to set nullable=True.
Applying the Schema Change and Verifying the Result
If you look at the downgrade command to reverse that change, it's a very simple one. We call the drop_column function, and we're dropping the date_formed column from the band table. Now, one thing to note, if we need to change anything about this autogenerated revision, we can actually do that here in the two functions that we have. But if you're happy with the code that's been generated, what we can do is go back to the command line. And I'm going to clear the terminal just now. And we can run the alembic upgrade command. Now I need to spell alembic correctly here. It's alembic upgrade. And again, we want to apply the most recently generated revision file, so we use the head command. And when we run this, it's going to add that new column, hopefully to the band table.
Now, we can verify that. So let's minimize the terminal and we're going to bring back the sidebar, and we're going to look at the SQLite Explorer here. And I'm going to refresh this. And if you look at the band table here, we have a single record in this table where it now has a new column, and that's the date_formed column that we added to the SQLModel class. And we then generated the revision. And when we apply that revision, it's going to add this column, as you can see here, to the band table.
So the crux of this is that anytime we change our models in the application, after we've done the initial setup with Alembic, we can then just run the alembic revision command and the alembic upgrade command to apply those changes to the underlying database. And that allows us to manage the database from our application using these migrations. And that's a lot easier and a lot more scalable than trying to manage these outside of the application. We can very easily keep the database structure in sync with our models by using Alembic with FastAPI and SQLModel.
Conclusion and Next Steps
And if you're using a different Python backend package, for example, that might be Flask or Lightstar, you can do this in a very similar way with those packages.
So that's all for this video. We've seen how to use Alembic with FastAPI and we've seen how we can manage databases using changes to our model classes. If you've enjoyed this, give it a thumbs up and subscribe to the channel if you've not already done so. And what we want to do next in this series is actually build an application using FastAPI. So if you have any ideas for what you want to see as that application, drop them in the comments and we can consider those for the next step of this series. So thanks again for watching and we'll see you in the next video.
