sqlalchemyfastapialembic

Can't apply alembic migrations


I am building FastApi + sqlalchemy + alembic app and I want to replace table creation Base.metadata.create_all(bind=engine) with alembic

I want to create and apply database migrations with help of alembic. My actions:

alembic init alembic # in the project folder
docker-compose up --build -d # start fastapi and postgres db
docker exec -ti fastapi_template_backend-api_1 bash # log in into fastapi docker container

root@2c498a4c90f7:/app# alembic revision --autogenerate -m "Initial migration" # create migration
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 'users_id_seq' as owned by integer column 'users(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed table 'users'
  Generating /app/alembic/versions/07e67ff16b77_initial_migration.py ...  done

root@2c498a4c90f7:/app# alembic upgrade head # apply migration
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 07e67ff16b77, Initial migration

Expected result - table 'users' in postgres database

Actual result - don't see 'users' table (see only 'alembic_version' table)

My code:

# app/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"
    depends_on:
      - db

  db:
    image: postgres:latest
    ports:
      - "5432:5432"
    expose:
      - 5432
    env_file: ".env"
    volumes:
      - pgdata:/var/lib/postgresql/data 

volumes:
  pgdata:
# /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)
...
# app/database/session.py
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


# Replace with your own PostgreSQL instance
DB_USER = os.getenv("POSTGRES_USER")
DB_PASSWORD = os.getenv("POSTGRES_PASSWORD")
DB_NAME = os.getenv("POSTGRES_DB")
DB_HOST= os.getenv("DB_HOST")
PGPORT = os.getenv("PGPORT")

DATABASE_URL = f'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{PGPORT}/{DB_NAME}'

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)    


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
# app/models/users.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)
# app/models/__init__.py

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
app/main.py

from fastapi import FastAPI, 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, engine
from models import Base

# Base.metadata.create_all(bind=engine) -- table creation is not working without this line


app = FastAPI()

@app.get("/users")
def get_users(db: Session = Depends(get_db)):
    return db.query(User).all()
...

Solution

  • Thank you @MatsLindh

    This solution is working for me

    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)
    

    But it looks ugly (import at the last line)

    What is the best practise to link models description and alembic?