Python Friday #235: DB Migrations With Alembic and FastAPI

Our current way to create the tables in the database when we run the application works fine until we need to extend an existing table. Then SQLAlchemy will not do that for us, and we must make the change manually. With Alembic we have a solution for that problem that works great with SQLAlchemy. Let us add it to our to-do application.

This post is part of my journey to learn Python. You find the code for this post in my PythonFriday repository on GitHub.

 

Install and configure Alembic

You can find all the necessary steps to install and configure Alembic in the post #86 Database Migrations With Alembic and SQLAlchemy. If you want to follow along, run the commands inside the extended_todo folder.

If you installed Alembic in the past, you should make sure that you get the newest version:

 

Application specific configuration

We can initialise Alembic with this command:

In alembic.ini (next to our main.py) we need to set the path to the database:

In the alembic folder, we add this code to the env.py file:

Make sure that you remove the existing line that sets target_metadata to None:

We need to decide if we want to delete our existing database or if we want to add code to only create the task table if it does not exist (see here for a tutorial and check the Git repository for the syntax for SQLAlchemy 2.x).

I go with the first option and delete the database so that Alembic can later recreate it with the tasks table.

To create the migration for the now deleted database, we can use this command inside the extended_todo folder:

This creates us a migration file inside extended_todo\alembic\versions\ that will create the tasks table when we migrate upwards and that deletes the table when we migrate down:

We can run the migration with this command:

 

Remove the create tables in FastAPI

In our data/database.py file we need to remove the call to the create_tables() method and the method itself:

This prevents us from accidentally creating tables outside of our Alembic migrations.

 

New or changed tables

We can add new tables or change existing ones in the entities.py file as we like. When we create a migration, Alembic checks for the differences between the current database and our entities and makes the change for us. We then can apply the migration and the change is in our database.

 

Fix the tests

We currently use our startup hook to delete the test database and then recreate it from scratch. With the changes to use Alembic that does no longer work. Instead of just deleting the database, we need to get a new database that has the tables in it.

We can create a copy of the current database and put it into a template file:

In our hook, we can delete the existing test database and copy the template database to use it as the new test database:

We can now run our tests and they work again. If we change the table structure, we need to update our template database as well. It is a bit of extra work, but that way we get all the flexibility we need.

 

Next

With Alembic in place, we can evolve our database should the need arise. As history told us, those changes may be closer than we expect. Next week we take our application into a different direction and add a web interface to our API.