What is Alembic?

Alembic is a database migration tool for SQLAlchemy, designed to help alter and manage your database schema throughout the lifecycle of your application. Here's a basic guide on how to set up Alembic for your project. Please note that this assumes you already have a Python environment set up and SQLAlchemy installed (if not, you will need to handle that prior to setting up Alembic).

1. Install Alembic: First, you need to install Alembic. You can do it using pip:

pip install alembic

1. Initialize Alembic: Navigate to the root of your project directory in the terminal, and then initialize Alembic, which will create a new directory called alembic and a configuration file alembic.ini in your project:

alembic init alembic

1. Configure Alembic: Edit the alembic.ini file to set the sqlalchemy.url to your database connection string. If you're using environment variables for your database URI, you'll want to configure this in the env.py file instead:

# alembic.ini
sqlalchemy.url = driver://user:pass@localhost/dbname

Or in env.py:

# env.py
from myapp.config import my_database_uri
config.set_main_option('sqlalchemy.url', my_database_uri)

Replace driver://user:pass@localhost/dbname with your actual database connection string.

1. Define and Run Migrations: With the alembic directory now in your project, create a new database migration with alembic revision. You can add an optional -m flag to give your migration a descriptive message:

alembic revision -m "create account table"

This command will generate a new migration script in the alembic/versions directory. Edit this file to define your database schema changes using the upgrade() function to apply them and the downgrade() function to revert them:

"""create account table

Revision ID: 1234567890ab
Revises:
Create Date: 2023-04-09 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '1234567890ab'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # Commands for upgrading the database to this revision
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )

def downgrade():
    # Commands for reverting the database back to the previous revision
    op.drop_table('account')

1. Running Migrations: To apply your migrations to your database, run the following command:

alembic upgrade head

This will apply all available migrations up to the "head", which is the latest revision.

To roll back the last migration, you can use:

alembic downgrade -1

1. Autogenerate Migrations (optional): Alembic can autogenerate migration scripts based on the current database schema as defined by your SQLAlchemy models and the actual schema of the connected database. It is important to manually review autogenerated migrations, as they might not capture every change or might include unintended alterations.

To autogenerate a migration use:

alembic revision --autogenerate -m "add new column to account table"

Inspect the autogenerated script, then apply the migration as usual.

Alembic offers many more features, including branching migrations and various options for managing the migration environment. Please consult the Alembic documentation for advanced usage and complete reference.