How Securo Handles Database ORM and Migrations: SQLAlchemy + Alembic Architecture Explained

Securo uses SQLAlchemy as its ORM with async engine support and Alembic for database migrations, all integrated into a FastAPI application structure.

Securo's data persistence layer is built entirely on SQLAlchemy, the standard ORM for Python applications. The project follows modern async patterns suitable for FastAPI, with schema evolution managed through Alembic. This article examines the exact implementation in the securo-finance/securo repository, including model definitions, migration workflows, and the interaction between these components.

SQLAlchemy ORM Setup in Securo

Securo configures SQLAlchemy using the Declarative Base pattern for model definition and an async engine for non-blocking database operations.

Core Database Configuration

The central database setup lives in backend/app/db.py. This file defines three critical objects:

• Base — the declarative base class that all models inherit from
• engine — the async SQLAlchemy engine
• get_async_session — dependency function for FastAPI injection

# backend/app/db.py (conceptual structure)

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import declarative_base
from sqlalchemy.ext.asyncio import async_sessionmaker

Base = declarative_base()  # All models inherit from this

engine = create_async_engine(
    DATABASE_URL,
    future=True,
    echo=False,
)
async_session = async_sessionmaker(engine, expire_on_commit=False)

async def get_async_session() -> AsyncSession:
    async with async_session() as session:
        yield session

Using Base.metadata consistently across the application ensures that model definitions remain synchronized with migration scripts and runtime schema validation.

Model Definitions

All persistent entities reside in backend/app/models/. Each model inherits from Base and uses SQLAlchemy 2.0-style mapped attributes:

# backend/app/models/tag.py — example model structure

from sqlalchemy import Column, String, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from backend.app.db import Base

class Tag(Base):
    __tablename__ = "tag"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, unique=True, index=True)

    # Relationship example

    transactions: Mapped[list["Transaction"]] = relationship(
        "Transaction",
        back_populates="tags",
        secondary="transaction_tag",
    )

Key model files in the repository include:

• backend/app/models/user.py — User entity with authentication fields
• backend/app/models/workspace.py — Workspace/organization entity
• backend/app/models/transaction.py — Financial transaction records

FastAPI-Users Integration

Securo leverages FastAPI-Users for authentication, which provides a pre-built SQLAlchemy-compatible user table. The User model in backend/app/models/user.py mixes in SQLAlchemyBaseUserTableUUID from FastAPI-Users:

from fastapi_users.db import SQLAlchemyBaseUserTableUUID
from backend.app.db import Base

class User(SQLAlchemyBaseUserTableUUID, Base):
    # Inherits: id, email, hashed_password, is_active, is_superuser, etc.

    # Custom Securo fields added below

    full_name: Mapped[str | None] = mapped_column(String, nullable=True)
    created_workspace: Mapped["Workspace"] = relationship("Workspace", back_populates="owner")

This mixin pattern gives Securo all standard authentication fields while allowing custom extensions.

Alembic Migration System

Securo uses Alembic for database schema evolution. The configuration ensures migrations operate against the same metadata that the application uses at runtime.

Directory Structure

backend/alembic/
├── env.py          # Migration environment configuration

├── script.py.mako  # Template for new migration files

└── versions/       # Generated migration scripts

    ├── 2023_01_15_init.py
    ├── 2023_02_03_add_workspace_table.py
    └── ...

Alembic Environment Configuration

The backend/alembic/env.py file bridges Alembic with Securo's SQLAlchemy setup:

# backend/alembic/env.py (key sections)

from logging.config import fileConfig
from sqlalchemy import engine_from_config
from alembic import context

# Import Securo's Base and models to register metadata

from backend.app.db import Base
from backend.app.models.user import User  # noqa: F401

from backend.app.models.workspace import Workspace  # noqa: F401

# ... other models

target_metadata = Base.metadata  # Critical: same metadata as runtime

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
        future=True,
    )
    
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,  # Links to app models

            compare_type=True,
        )
        with context.begin_transaction():
            context.run_migrations()

The target_metadata = Base.metadata assignment is essential—this ensures Alembic compares the database state against the exact same model definitions that the application uses.

Migration Workflow in Practice

Securo follows a standard Alembic workflow integrated with its development pipeline.

Step 1: Modify Models

Edit existing files or create new ones in backend/app/models/:

# Adding a new field to existing model

class Transaction(Base):
    # ... existing fields ...

    
    confirmed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

Step 2: Generate Migration

Run Alembic's autogenerate command:

cd backend
alembic revision --autogenerate -m "Add confirmed_at to transactions"

This produces a file under backend/alembic/versions/:

# backend/alembic/versions/2024_01_10_add_confirmed_at_to_transactions.py

revision = 'abc123'
down_revision = 'xyz789'

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('transaction', sa.Column('confirmed_at', sa.DateTime(), nullable=True))

def downgrade():
    op.drop_column('transaction', 'confirmed_at')

Step 3: Apply Migrations

alembic upgrade head

For production deployments, Securo's CI pipeline runs this automatically. The head target applies all pending migrations in version order.

Docker-Wrapped Commands

Securo likely includes Docker-compose shortcuts for common operations:

# Typical pattern for containerized workflows

docker-compose exec backend alembic revision --autogenerate -m "description"
docker-compose exec backend alembic upgrade head

Using Async Sessions in FastAPI Endpoints

Securo's dependency injection pattern provides clean database access in route handlers:

# backend/app/api/routes/tags.py

from fastapi import APIRouter, Depends
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from backend.app.db import get_async_session
from backend.app.models.tag import Tag

router = APIRouter()

@router.get("/tags")
async def list_tags(
    session: AsyncSession = Depends(get_async_session)
):
    result = await session.execute(select(Tag))
    return result.scalars().all()

@router.post("/tags")
async def create_tag(
    name: str,
    session: AsyncSession = Depends(get_async_session)
):
    tag = Tag(name=name)
    session.add(tag)
    await session.commit()
    await session.refresh(tag)
    return tag

The get_async_session dependency ensures proper session lifecycle management—sessions are created per-request and automatically closed.

Key Design Decisions

Securo's ORM and migration architecture follows several best practices:

• Single source of truth — Base.metadata is shared between application runtime and Alembic configuration
• Async-first — Full async/await support through create_async_engine and AsyncSession
• Declarative models — Modern SQLAlchemy 2.0 syntax with type-hinted Mapped attributes
• Third-party integration — FastAPI-Users reduces authentication boilerplate
• Version-controlled migrations — All schema changes tracked as code in backend/alembic/versions/

Summary

• Securo uses SQLAlchemy with async engine and declarative models for ORM functionality
• All models inherit from Base defined in backend/app/db.py and reside in backend/app/models/
• Alembic handles database migrations, with configuration in backend/alembic/env.py
• The critical link between code and migrations is target_metadata = Base.metadata in env.py
• FastAPI-Users provides authentication tables via mixin pattern in backend/app/models/user.py
• Typical workflow: modify model → alembic revision --autogenerate → alembic upgrade head

Frequently Asked Questions

Why does Securo use SQLAlchemy instead of an async-native ORM like Tortoise or Prisma?

SQLAlchemy remains the most mature and widely-supported ORM in Python, with extensive documentation, plugin ecosystem, and team expertise. The 2.0 release added first-class async support through create_async_engine and AsyncSession, making it suitable for FastAPI without sacrificing the ecosystem benefits.

How does Securo prevent migration drift between environments?

By configuring target_metadata = Base.metadata in backend/alembic/env.py, Alembic always compares against the same model definitions imported from the application code. All models must be imported in env.py (hence the noqa: F401 comments) to register them with Base.metadata. CI pipelines run alembic upgrade head automatically, ensuring production matches the committed schema.

Can I use sync SQLAlchemy with Securo's codebase?

The backend/app/db.py configuration explicitly uses create_async_engine and AsyncSession. While you could add a sync engine for specific use cases, the FastAPI dependency injection expects async sessions. Changing to sync would require updating all endpoints and potentially losing performance benefits for I/O-bound operations.

What happens if autogenerate misses a migration change?

Alembic's autogenerate detects most schema changes but not all—indexes renamed in place, certain constraint modifications, or complex data migrations require manual editing. Always review generated files before committing. For critical changes, write empty migrations with alembic revision -m "manual" and implement custom upgrade()/downgrade() logic.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}