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()
...
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?