Alembic in 2021 (part 1)

In this tutorial, we will grasp the basics of alembic to have a better solution for managing a PostgreSQL database. Different technologies will be used like Docker, Docker Compose, PostgreSQL, and Python3.

PostgreSQL database will be used along with PostGIS to add the spatial component. Make sure you have docker installed on your computer if you want to follow this tutorial.

What is Alembic?

Alembic is a very useful library widely used for database migration. It can be used to create tables, insert data or even migrate functions from one schema to another. To be able to do all these tasks, the library uses SQLAlchemy, an ORM that is suited for working with PostgreSQL and other relational databases. It is very useful when you have to go back and forth by adding new functionality along the way. Each change you make is stored in a version file that makes it very easy to debug and follow changes without becoming overwhelmed.

How to setup Alembic

Start by creating a python virtual environment by following these commands:

python3 -m venv venv

source venv/bin/activate

The alembic commands have to be run from this python virtual environment we just created.

Initiate an Alembic project

pip install alembic

alembic init alembic

The init command will create all the necessary files to use alembic. The folder will contain different versions based on their creation date. From there, you can create tables or migrate functions for example.

After the init command, a folder structure is created. The file named ‘alembic.ini’ is called every time Alembic is used. It contains all the basic information that makes it possible to connect to the database of your choice. For more information about this file visit:

https://alembic.sqlalchemy.org/en/latest/tutorial.html

Create a PostgreSQL instance

First, let’s start a db instance with the help of a docker compose file:

volumes:
  pgh3-poc-bornes:

services:
  db:
    image: crazycapivara/postgres-postgis:10-pgh3
    restart: always
    environment:
      POSTGRES_PASSWORD: mypassword
    volumes:
      - pgh3-poc-bornes:/var/lib/postgresql/data
      - ./geodata:/geodata
    ports:
      - 5598:5432

To start the service, just run the command below:

docker-compose up -d

Now that your service is running, don’t forget to specify the credentials of the database in your ‘alembic.ini‘ file (the credentials are located in the docker-compose file).

sqlalchemy.url = postgresql://postgres:mypassword@localhost:5598/postgres

Creating tables with Alembic

Every time a command has to be run with Alembic, a version has to be created first. From your python environment run the command:

 alembic revision -m “create geomtable table” 

Each version is a python file that has its own id with the default import of Alembic and sqlalchemy.

The file created has an empty upgrade() and downgrade() function that will be used to move from one version to another. we will populate those functions to create a table. 

As we are working with spatial databases, we have to import geoalchemy to use the geometry type:

from geoalchemy2.types import Geometry

As for the upgrade and downgrade function, we just have to use the Alembic directives create table and drop table to create or delete the table:

To be able to create the table just run the command:

alembic upgrade head

This command will upgrade to the latest version in the folder. However, when you’re moving from one version to the other you just have to run:

alembic upgrade +1

You can downgrade by one version by running:

alembic downgrade -1

You can downgrade to the base by running:

alembic downgrade base

Awesome you created your first spatial table with alembic!!

More advanced features in the next post !!

https://anagraph.io/en/articles/alembic_in_2021_part2/