SQL databases operate by defining specific fields, data types, and relationships across various models. These characteristics make SQL databases ideal for tasks like data science and record management. In Python-based web applications, SQL databases are commonly chosen, especially with Object Relational Manager (ORM) tools like SQLAlchemy. These tools facilitate the creation, querying, and modification of SQL models using Python.

However, altering a production database after it’s been defined can be difficult. Frameworks such as Django provide their own database migration systems to simplify and streamline this process. It introduces various checks to ensure database updates are performed smoothly. But, Python Frameworks such as Flask and FastAPI do not come with their own database migration system, this is where Alembic comes into play. Alembic is a database Migration system for usage with the SQLAlchemy Database Toolkit.

It allows you to make changes to your databases structure in a secure way using only Python without writing a single SQL statement. Additionally, with Alembic you can rollback to the previous version of your database definition with just a few commands. Alembic make migrations in Database really easy with intuitive syntax. It make easier to evolve our database with our application.

In this tutorial, you will learn to use Alembic in a FastAPI application. We will cover most important features like upgrade, downgrade, addcolumn and dropcolumn to enhance our FastAPI application. For this tutorial, we have already created a sample application in FastAPI which has a CRUD functionality on a book table having the following fields.

id: int  
title: string  
rating: integer  
price: float  
published: boolean

Setting Up the starter code

Cloning Application from GitHub

In order to setup the starter application, you have to first clone the application in your local directory using this command:

git clone https://github.com/vivekthedev/blog-tuts.git

Creating virtual environment

Once you have the application on your system, create a python virtual environment in order to isolote the dependencies from the system site packages. You can create a virtual environment using the following command:

python -m venv env

After running the above command, run env\Scripts\activate if you are on Windows else run source env/bin/activate if you are on Mac/Linux in order to activate your virtual environment.

Installing Dependencies

Once you activate the virtual envrironment you can install dependencies present in requirements.txt file by running pip install -r requirements.txt command.

Running and Testing Starter Application

At this point you are ready to run and test the application. In the same terminal, run uvicorn main:app --reload to start the application on the default address of http://127.0.0.1:8000/ , visit http://127.0.0.1:8000/docs to test your application on the Swagger UI.

What are Database Migrations?

As mentioned earlier, SQL databases have a strict structure with predefined rows and columns, which makes altering the database more challenging. For application developers, this process may require writing SQL statements, ensuring constraints like NOT NULL or UNIQUE are met, and specifying DEFAULT values. Moreover, if issues arise during the update process, reverting to the previous database state can be difficult, especially if TRANSACTION command aren’t explicitly defined when executing SQL statements.

Alembic solves this problem by providing an easier and intuitive way to evolve your database without writing any line of SQL code. Additionally, working with Alembic is much easier compared to manually changing database structure through SQL or via GUI application.

Each database update is stored in a Python file, providing flexibility to customize as needed. Reverting to a specific database state is as easy as executing a simple command, making the process more manageable and efficient.

Working with Alembic

To start working with Alembic, you first need to install Alembic using the following command. Also make sure to run the command in the same virtual environment as you FastAPI starter application:

pip install alembic

Once you install Alembic, you now have a alembic Command Line Tool to configure your database. To integrate Alembic with your project run the following command in the same terminal:

alembic init alembic-conf

The above command creates a directory named alembic-conf in your project directory and generates the following files:

• alembic-conf/versions/ : This directory stores various versions of your database, you will use this directory in order to examine or update various states of your database.
• alembic-conf/env.py : This file is used by alembic to run migrations, it defines how the migrations should be made on the database.
• alembic-conf/script.py.mako : It is a template file which is used to create new database versions in the versions directory. In other words every file/migration in versions directory is created using this template file.
• alembic-conf/README : Contains information about the projects or the alembic. You can use it as changelog or writing something informative about the project.
• alembic.ini : This is the configuration file where you define you various setting in order to configure your migration process.

Configuring Database with Alembic

To enable Alembic to make changes to your Database, it requires knowledge of the location of your database service, which is defined by the SQLALCHEMY_DATABASE_URL. To do this, locate the database.pyfile within your project and copy the value sqlite:///books.sqlite3 . Then, navigate to Line 63 of the alembic.ini file and paste this value just after the variable sqlalchemy.url. Make sure to NOT enclose it in quotes .

Now open alembic-conf/env.py file, and populate it with the following code:

import sys
import models
sys.path.append("..")

After the above imports find the target_metadata variable and set it to models.Base.metadata .

Once you set up the above two configurations successfully, you are now ready to create you alembic revisions in order to make changes in your database.

Creating Alembic Revisions

For your first Alembic revision, you will create a new column in your Books table named Description which should be of type String. In order to create a revision file, open the terminal and type the following command.

alembic revision -m "create book description"

After running the above command, you will see that a new file with the name “<hash_value>_create_book_description.py” has been created. This file will contain our revision logic which we will define in upgrade() function in the file.

Open the newly created file and in the upgrade() function type the following code:

def upgrade() -> None:  
    op.add_column('books', sa.Column('description', sa.String(), server_default="A short Description"), nullable=False)

In the provided code, you’ve created a new revision that, when applied, adds a new column named ‘description’ of type string with a default value of “A short description”. It’s considered good practice to define the downgrade() function at the same time as the upgrade() function to ensure a smooth downgrading process in the future.

To implement this, in the downgrade() function, we will reverse the actions performed in the upgrade() function, which in this case involves removing the ‘description’ column from the ‘books’ table. To achieve this, type the following code inside the downgrade() function:

def downgrade() -> None:  
  op.drop_column('books', 'description')

This code will instruct Alembic to remove the ‘description’ column from the ‘books’ table when the downgrade process is initiated.

In this file, you can see another variable revision which contains the hash value of the revision file. Copy this value as it will be useful when applying the revisions to the database.

Applying Revisions

We have successfully created revision to make changes in the database, in order to apply these changes run the following command in your terminal:

alembic upgrade <hash_value>

Note that “<hash_value>” should be replaced with the hash value of your revision file, which you copied in the previous section. When you execute this command, you will receive confirmation messages indicating that your revisions have been applied successfully:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.  
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.  
INFO  [alembic.runtime.migration] Running upgrade  -> e9cbb68b0547, create book description

To verify that the changes have been applied to our database, we can run the application using the following command:

uvicorn main:app --reload

After that, proceed to http://127.0.0.1:8000/ to view the books stored in our database. However, you may notice that the models do not display the newly created ‘description’ field for the book. This is because FastAPI fetches all model information from the models.py file. To ensure FastAPI fetches the ‘description’ field as well, you need to add a new line in the models.py file. Open the models.py file and append this line at the end of the model:

class Books(Base):  
    __tablename__ = "books"  
    id = Column(Integer, primary_key=True, index=True)  
    title = Column(String)  
    price = Column(Float)  
    author = Column(String)  
    rating = Column(Integer)  
    published = Column(Boolean)  
    description = Column(String) # <- This line

Now open the http://127.0.0.1:8000/, you will see the following output: You can also use sqlite3 command line tool or SQL Viewer tool in order to inspect changes in your database.

With that, you have successfully created and applied your very first migration using Alembic. This functionality proves to be highly useful for a production-level application, as it allows for seamless database schema modifications.

For your next migration, the objective is to create a new table called ‘reviews’. This next part will serve as a learning opportunity to understand how to create a new table using Alembic Operations. Eventually, we will remove this table to demonstrate the downgrade functionality of Alembic, showcasing its ability to rollback changes.

Creating Table with Alembic

In order to create a new revision, open the terminal and type the following following command:

alembic revision -m "create reviews table"

A new file with the name <hash_value>_create_reviews_table should now be generated in the “alembic-conf/versions” directory. Open this file, and you’ll notice that it contains a variable called “down_revision” with a value corresponding to the hash value of the previous migration file. This setup makes it easy to track our migrations and ensures that Alembic can keep the upgrade and downgrade processes in sync.

Upgrade and Downgrade function

Now let’s create Operations for creating a new table. Open the newly created migration file and populate the upgrade function with the following code:

def upgrade() -> None:  
    op.create_table(  
        "reviews",  
        sa.Column("id", sa.Integer(), primary_key=True, index=True),  
        sa.Column("author", sa.String(50), nullable=False),  
        sa.Column("content", sa.String(200)),  
        sa.Column("timestamp", sa.TIMESTAMP, server_default=sa.func.now()),  
    )

In the above code, we are creating a new table named reviews
which has the following columns:

• id : An Integer type field for primary_key data of each row.
• author : This column stores the author of the review and is defined as a String(50) type. It means that the column value can’t exceed the length of 50 characters.
• content : Another String field which stores the actual review content. This column cannot exceed the character length of 200.
• timestamp : A field with default value of the current timestamp which signifies the date time when a particular review was created.

As stated above, we should always write the downgrade function in order to revert the changes done by upgrade function. Write the following code in the downgrade function:

def downgrade() -> None:  
    op.drop_table('reviews')

The above code is pretty self-explainatory, we are just removing the table ‘reviews’ that we created in upgrade function.

Applying the Migration

To apply the migration, first copy the hash value from the migration file and then open your terminal and run the following command:

alembic upgrade <hash_value>

Once you’ve executed the provided command, you’ll receive a confirmation message in your terminal indicating that the latest migration has been successfully applied, resulting in the creation of the new “reviews” table. However, as our FastAPI application currently lacks any endpoints that query the “reviews” table, you won’t be able to observe these changes directly from the application.

To inspect the changes, we’ll utilize an SQL Viewer Tool. Simply open the SQL Viewer Tool in your browser. Upon loading the page, you’ll notice a “Drop Here” text. Click on it and navigate to your project directory, where you’ll find the database file named ‘books.sqlite3’. Select this database file, and the SQL tool will read all the tables within it. You’ll then be able to view all the tables available in the dropdown menu within the tool’s interface. This allows you to inspect the newly created “reviews” table and any other tables present in your database.

In the above image you can a reviews table which currently has no rows as we haven’t populated it with data.

Downgrade with Alembic

Downgrading with Alembic is straightforward. Simply enter the downgrade command along with the identifier of the desired state in the terminal. Alembic will automatically handle all the necessary steps to revert your application to the specified state.

Alembic supports alembic downgrade <hashvalue> syntax, but the more common (and easier) way to downgrade the application involves relative identifiers using the -N and +N syntax. Let’s do it practically to understand how it works. Open your terminal and type the following command:

alembic downgrade -1

The above command instructs Alembic to take the database to a state one step previous from the current state. In other words it reverts the last applied migration to the database. Naturally, when you put -2 instead of -1 it reverts the last two migration and so on.

After executing the command, you’ll receive a confirmation message in the terminal indicating that the downgrade operation ran successfully. To verify this, you can open the SQLite Viewer Tool and upload the database again. Upon inspection, you’ll notice that the dropdown menu displaying all the tables no longer includes the ‘reviews’ table. This confirms that the database has indeed been reverted back to its previous state. You can also see in the above image that there is no review table in the database. Make sure to reupload the database to the viewer tool in order for them to reflect in your browser.

Conclusion

Alembic is a really powerful tool to add database migration using SQLAlchemy. It make migration process more intuitve, easy and secure. Developers can easily manage and rollback to a different state in the database with ease. Alembic also supports operation like Batch processing, raw sql execution, adding constraints and much more. You can read about all the operations provided by Alembic in the Documentation.

In this tutorial you learnt about Alembic and how you can use it in your python projects to easily manage your database. You learnt upgrading and downgrading in Alembic, creating columns, tables and and reverting changes. With these functionalities Alembic becomes a must-have when working with Python/SQLAlchemy project in order to easily manage your database.

Hope you like the tutorial, in case you have any question, shoot them in the comments. I will try my best to answer them.