Database

SQLAlchemy and Alembic

This documentation explains how to set up SQLAlchemy and Alembic for a new project.

This document is more intended for developers who are setting up a new project from scratch.

Requirements

• Python 3.12 or greater.
• Poetry (see installation instructions)
• SQLAlchemy (poetry add sqlalchemy) for database connection and ORM.
• Alembic (poetry add alembic) for database migrations.

Setup

1. Initialize Alembic environment.

alembic init alembic

This will create a alembic.ini file in the project root directory and a directory called alembic that contains the Alembic version control scripts.

2. Configure Alembic with SQLAlchemy.

   2.1. Go to alembic/env.py and configure the SQLALCHEMY_DATABASE_URI variable with the database connection string using the method config.set_main_option.

   For example:

   # RECOMMENDED: use environment variables for setting the database URL
   # because it might contain sensitive informations
   config.set_main_option("sqlalchemy.url", "sqlite:///app.db")
   

   2.2. Still in alembic/env.py, configure the target_metadata variable with the metadata object for the database using the method config.set_main_option.

   For example:

   # Base class is used for all models in the application. It contains the
   # metadata for the database.
   target_metadata = Base.metadata