• 44.4k
• 7
• 80
• 157

Review sqlalchemy and alembic integration in FastAPI

version: '3.8'
services:
 backend-api:
 build:
 context: ./app
 dockerfile: Dockerfile
 ports:
 - "8080:8080"
 command: uvicorn main:app --host 0.0.0.0 --port 8080 --reload
 env_file: ".env"
 volumes:
 - ./app/alembic/versions/:/app/alembic/versions/
 depends_on:
 - db
 db:
 image: postgres:latest
 ports:
 - "5432:5432"
 expose:
 - 5432
 env_file: ".env"
 volumes:
 - pgdata:/var/lib/postgresql/data 
volumes:
 pgdata:
```

• 459
• 2
• 13

I am building a fastAPI + sqlalchemy + alembic + docker-compose reusable template. Full source code: https://github.com/mascai/fastapi_template (commit 77ce7f2)

I would appreciate pull-requests =)

The project is working but I worry about alembic and sqlalchemy integration

1. The first point:

I have to import users (orm model) in the init.py file, alembic doesn't see changes in the user model without this import:

# app/models/__init__.py
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from . import users # IMPORTANT: intended to register models for alembic (should be at the end of the file), alembic revision --autogenerate -m "migration name"

2. I am creating migrations manually like this: (What is the best practise to create and apply migrations?)

docker exec -ti fastapi-container-name bash
alembic revision --autogenerate -m "migration name"
alembic upgrade head

What is the proper way to link sqlalchemy and alembic in my code?

Project tree and main files:

├── app
│ ├── Dockerfile
│ ├── __init__.py
│ ├── alembic
│ │ ├── README
│ │ ├── env.py
│ │ ├── script.py.mako
│ │ └── versions
│ │ └── 486ef6640756_initial_commit.py
│ ├── alembic.ini
│ ├── api
│ │ └── v1
│ │ ├── __init__.py
│ │ └── users.py
│ ├── database
│ │ ├── __init__.py
│ │ └── session.py
│ ├── main.py
│ ├── models
│ │ ├── __init__.py
│ │ └── users.py
│ ├── requirements.txt
│ ├── schemas
│ │ ├── __init__.py
│ │ └── users.py
│ └── utils
│ └── __init__.py
├── docker-compose.yaml

Alembic settings:

# /app/alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
 fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from app.models import Base
target_metadata = Base.metadata
from app.database.session import DATABASE_URL
config.set_main_option('sqlalchemy.url', DATABASE_URL)
...

Main file

# app/main.py
from fastapi import FastAPI, APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List, Dict, Any
from models.users import User
from schemas.users import UserSchema, BaseUserSchema
from database.session import get_db
from api.v1.users import users_router
app = FastAPI()
app.include_router(users_router)
@app.get("/health_check")
def health_check():
 return {"Hello": "World"}

Model description

# app/models.py
from sqlalchemy import Column, Integer, String
from models import Base
class User(Base):
 __tablename__ = 'users'
 id = Column(Integer, primary_key=True)
 name = Column(String, unique=True)
 age = Column(Integer)

Api example

app/api/v1/users.py
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List, Dict, Any
from models.users import User
from schemas.users import UserSchema, BaseUserSchema
from database.session import get_db
users_router = APIRouter()
@users_router.get("/users", tags=["user"])
def get_users(db: Session = Depends(get_db)):
 return db.query(User).all()
@users_router.post("/users", response_model=UserSchema, tags=["user"])
def create_user(user_data: BaseUserSchema, db: Session = Depends(get_db)):
 query = db.query(User).filter(User.name == user_data.name).first()
 if query:
 raise HTTPException(status_code=400, detail="User already exist")
 new_user = User(name=user_data.name, age=user_data.age)
 db.add(new_user)
 db.commit()
 return new_user

docker-compose.yaml

version: '3.8'
services:
 backend-api:
 build:
 context: ./app
 dockerfile: Dockerfile
 ports:
 - "8080:8080"
 command: uvicorn main:app --host 0.0.0.0 --port 8080 --reload
 env_file: ".env"
 volumes:
 - ./app/alembic/versions/:/app/alembic/versions/
 depends_on:
 - db
 db:
 image: postgres:latest
 ports:
 - "5432:5432"
 expose:
 - 5432
 env_file: ".env"
 volumes:
 - pgdata:/var/lib/postgresql/data 
volumes:
 pgdata:
```