Skip to main content

​
Overview

VidaPlus API uses Alembic for database migrations, providing version control for your database schema. Alembic works seamlessly with SQLAlchemy to track and apply schema changes over time.

​
Alembic Configuration

The project uses Alembic with the following configuration:

​
Configuration File

The alembic.ini file is located in the project root and contains: 
[alembic]
script_location = migrations
prepend_sys_path = .
version_path_separator = os

sqlalchemy.url = driver://user:pass@localhost/dbname
The sqlalchemy.url in alembic.ini is a placeholder. The actual database URL is set dynamically in migrations/env.py from your environment variables.

​
Environment Configuration

The migrations/env.py file configures Alembic to use your application’s settings: 
from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
from vidaplus.settings import Settings
from vidaplus.models.models import table_registry

config = context.config
config.set_main_option('sqlalchemy.url', Settings().DATABASE_URL)

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = table_registry.metadata
Key points:
  • Database URL is loaded from Settings().DATABASE_URL environment variable
  • Metadata is imported from table_registry which contains all SQLAlchemy models
  • Supports both offline and online migration modes

​
Environment Variables

Ensure your .env file contains the required database URL: 
DATABASE_URL=postgresql://username:password@localhost:5432/vidaplus
For SQLite (development/testing): 
DATABASE_URL=sqlite:///./vidaplus.db

​
Creating Migrations

​
Auto-generating Migrations

Alembic can automatically detect changes to your SQLAlchemy models and generate migration scripts:
1

Modify Your Models

Make changes to your models in vidaplus/models/models.py. For example, adding a new field:
class PacienteUser(BaseUser):
    __tablename__ = 'paciente_users'
    
    cpf: Mapped[str] = mapped_column(String(14), unique=True)
    data_nascimento: Mapped[date]
    endereco: Mapped[str]
    complemento: Mapped[str | None]
    numero: Mapped[int]
    bairro: Mapped[str]
    cidade: Mapped[str]
    estado: Mapped[str]
    cep: Mapped[str]
    # New field
    rg: Mapped[str | None] = mapped_column(String(20), nullable=True)
2

Generate Migration Script

Use the Alembic revision command with --autogenerate:
alembic revision --autogenerate -m "add rg field to paciente"
This creates a new migration file in migrations/versions/ with a name like:
abc123def456_add_rg_field_to_paciente.py
3

Review the Migration

Always review the generated migration before applying it:
def upgrade() -> None:
    """Upgrade schema."""
    op.add_column('paciente_users', 
        sa.Column('rg', sa.String(length=20), nullable=True))

def downgrade() -> None:
    """Downgrade schema."""
    op.drop_column('paciente_users', 'rg')
Always review auto-generated migrations! Alembic may not detect all changes or may generate suboptimal SQL. Manual adjustments may be necessary.

​
Manual Migrations

For complex schema changes, create an empty migration and write the upgrade/downgrade logic manually: 
alembic revision -m "custom migration description"
Then edit the generated file: 
"""Custom migration description

Revision ID: xyz789abc123
Revises: abc123def456
Create Date: 2025-05-17 20:49:24.855399
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = 'xyz789abc123'
down_revision: Union[str, None] = 'abc123def456'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None:
    """Upgrade schema."""
    # Add your upgrade SQL here
    op.execute("""
        CREATE INDEX idx_paciente_cpf ON paciente_users(cpf);
    """)

def downgrade() -> None:
    """Downgrade schema."""
    # Add your downgrade SQL here
    op.execute("DROP INDEX idx_paciente_cpf;")

​
Running Migrations

​
Upgrade to Latest Version

Apply all pending migrations to bring your database to the latest schema: 
alembic upgrade head

​
Upgrade to Specific Revision

Upgrade to a specific migration revision: 
alembic upgrade abc123def456

​
Upgrade by Relative Steps

Upgrade by a specific number of revisions: 
alembic upgrade +2  # Apply next 2 migrations

​
Downgrade Migrations

Downgrade to a previous revision: 
alembic downgrade -1  # Downgrade by 1 revision
alembic downgrade abc123def456  # Downgrade to specific revision
alembic downgrade base  # Downgrade all migrations
Downgrading migrations in production should be done with extreme caution. Always backup your database first!

​
Migration Examples from VidaPlus

​
Creating Tables

Here’s a real example from the VidaPlus codebase that creates the estoques and leitos tables: 
"""Generate tables

Revision ID: bc7827844a23
Revises: 8411d3b4e410
Create Date: 2025-05-17 20:49:24.855399
"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa

revision: str = 'bc7827844a23'
down_revision: Union[str, None] = '8411d3b4e410'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None:
    """Upgrade schema."""
    op.create_table('estoques',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('tipo_item', sa.Enum('MEDICAMENTO', 'INSUMO', 
            'EQUIPAMENTO', name='tipoitem'), nullable=False),
        sa.Column('nome', sa.String(length=255), nullable=False),
        sa.Column('quantidade', sa.Integer(), nullable=False),
        sa.Column('unidade', sa.String(length=50), nullable=False),
        sa.Column('data_validade', sa.Date(), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )
    
    op.create_table('leitos',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('numero_leito', sa.String(length=10), nullable=False),
        sa.Column('paciente_id', sa.Integer(), nullable=False),
        sa.Column('tipo', sa.Enum('UTI', 'ENFERMARIA', 'EMERGENCIA', 
            'ISOLAMENTO', 'SEMI_INTENSIVO', 'PEDIATRICO', 'NEONATAL', 
            'PSIQUIATRICO', name='tipoleito'), nullable=False),
        sa.Column('status', sa.Enum('OCUPADO', 'LIVRE', 'RESERVADO', 
            'EM_MANUTENCAO', name='statusleito'), nullable=False),
        sa.ForeignKeyConstraint(['paciente_id'], ['paciente_users.id'], ),
        sa.PrimaryKeyConstraint('id')
    )

def downgrade() -> None:
    """Downgrade schema."""
    op.drop_table('leitos')
    op.drop_table('estoques')

​
Checking Migration Status

​
Current Revision

Check which migration revision your database is currently at: 
alembic current
Output: 
bc7827844a23 (head)

​
Migration History

View the migration history: 
alembic history
Output: 
bc7827844a23 -> head, Generate tables
8411d3b4e410 -> bc7827844a23, Generate table
7f60e1ea6219 -> 8411d3b4e410, Create tables

​
Show Pending Migrations

See which migrations haven’t been applied yet: 
alembic current
alembic history
Compare the current revision with the history to identify pending migrations.

​
Best Practices

1

Always Use Version Control

Commit migration files to version control (Git) along with your model changes. This ensures migrations and models stay in sync across your team.
2

Test Migrations Locally

Before applying migrations to production, test them on a local database or staging environment:
# Test upgrade
alembic upgrade head

# Test downgrade
alembic downgrade -1
alembic upgrade head
3

Backup Production Database

Always backup your production database before running migrations:
# PostgreSQL backup example
pg_dump -h localhost -U username vidaplus > backup_$(date +%Y%m%d).sql

# Then run migrations
alembic upgrade head
4

Use Descriptive Messages

Write clear, descriptive migration messages:
# Good
alembic revision --autogenerate -m "add index to paciente cpf field"

# Bad
alembic revision --autogenerate -m "update"
5

Handle Data Migrations Carefully

When migrations involve data transformation, split them into separate steps:
  1. Add new column (nullable)
  2. Populate data in new column
  3. Make column non-nullable if needed
  4. Remove old column

​
Running Migrations in Docker

When using Docker, run migrations inside the container: 
# For a running container
docker exec vidaplus-api alembic upgrade head

# Or using Poetry
docker exec vidaplus-api poetry run alembic upgrade head
For automated deployments, you can run migrations as part of your container startup script.

​
Troubleshooting

​
”Can’t locate revision identified by…”

This usually means your migration history is out of sync. Check:
  1. All migration files are present in migrations/versions/
  2. The alembic_version table in your database matches a valid revision

​
”Target database is not up to date”

Run alembic upgrade head to apply pending migrations.

​
Environment Variable Not Found

Ensure your .env file contains DATABASE_URL and is in the project root: 
DATABASE_URL=postgresql://user:password@localhost:5432/vidaplus

​
Migration Conflicts

If multiple developers create migrations simultaneously, you may have conflicts. Resolve by:
  1. Identifying conflicting revisions
  2. Using alembic merge to create a merge revision
  3. Reordering migrations if necessary

​
Next Steps

Testing

Learn how to test your application with pytest

Database Models

Explore the database models and schema

Build docs developers (and LLMs) love

Get started for freeTalk to us