FastAPI with SQLAlchemy, PostgreSQL and Alembic and of course Docker [Part-1]

Intro

The purpose of this article is to create a simple guide on how to use FastAPI with relational databases and use Alembic for migrations. An implementation that can be used in production

Installation

I will be using pipenv to manage both my packages and the virtual environment. Feel free to manage your own packages in any way you like

Dependencies

  • python ≥ 3.5
  • fastapi
  • pydantic
  • fastapi-sqlalchemy
  • alembic
  • psycopg2
  • uvicorn

Main.py

lets start with a simple main.py for Fastapi

import uvicorn
from fastapi import FastAPI
app = FastAPI()
@app.post("/user/", response_model=User)
def create_user(user: User):
return user
if __name__ == "__main__":
uvicorn.run(app, host="0.0.0.0", port=8000)

Docker Configuration

Dockerfile

# Pull base image
FROM python:3.7

# Set environment varibles
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

WORKDIR /code/

# Install dependencies
RUN pip install pipenv
COPY Pipfile Pipfile.lock /code/
RUN pipenv install --system --dev

COPY . /code/

EXPOSE 8000
version: "3"

services:
db:
image: postgres:11
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=test_db
web:
build: .
command: bash -c "alembic upgrade head && uvicorn main:app --host 0.0.0.0 --port 8000 --reload"
volumes:
- .:/code
ports:
- "8000:8000"
depends_on:
- db

pgadmin:
container_name: pgadmin
image: dpage/pgadmin4
environment:
- PGADMIN_DEFAULT_EMAIL=pgadmin4@pgadmin.org
- PGADMIN_DEFAULT_PASSWORD=admin
ports:
- "5050:80"
depends_on:
- db
  1. web container — where the actual code will run
  2. db container
  3. pgadmin container
  1. Pipfile
  2. Pipfile.lock
  3. Dockerfile
  4. docker-compose.yml
  5. main.py

Alembic

Let initialize Alembic by running the following cmd in the terminal in the same directory

sqlalchemy.url = driver://user:pass@localhost/dbname
sqlalchemy.url =
DATABASE_URL = postgresql+psycopg2://postgres:postgres@db:5432
DATABASE_URL = postgresql+psycopg2://{user}:{password}@{host}:{port}
db:
image: postgres:11
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=test_db
DATABASE_URL = postgresql+psycopg2://postgres:postgres@db:5432
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.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None
from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import poolfrom alembic import context
# ---------------- added code here -------------------------#
import os, sys
from dotenv import load_dotenv

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
load_dotenv(os.path.join(BASE_DIR, ".env"))
sys.path.append(BASE_DIR)
#------------------------------------------------------------## this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# ---------------- added code here -------------------------#
# this will overwrite the ini-file sqlalchemy.url path
# with the path given in the config of the main code
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])
#------------------------------------------------------------## Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
# ---------------- added code here -------------------------#
import models
#------------------------------------------------------------#
# ---------------- changed code here -------------------------#
# here target_metadata was equal to None
target_metadata = models.Base.metadata
#------------------------------------------------------------#

Models

Now lets create our Models to be migrated to PostgreSQL

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
first_name = Column(String,)
last_name = Column(String)
age = Column(Integer)
docker-compose run web alembic revision --autogenerate -m "First migration"
docker-compose run web alembic upgrade head

Pgadmin

in order to check our created migrations

pgadmin:
container_name: pgadmin
image: dpage/pgadmin4
environment:
- PGADMIN_DEFAULT_EMAIL=pgadmin4@pgadmin.org
- PGADMIN_DEFAULT_PASSWORD=admin
ports:
- "5050:80"
depends_on:
- db

Schema — Pydantic Model

schema.py

from pydantic import BaseModel


class User(BaseModel):
first_name: str
last_name: str = None
age: int
class Config:
orm_mode = True
import uvicorn
from fastapi import FastAPI
#--------------- added code ------------------------#
import os
from fastapi_sqlalchemy import DBSessionMiddleware
from fastapi_sqlalchemy import db
from models import User as ModelUser
from schema import User as SchemaUser
from dotenv import load_dotenv

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
load_dotenv(os.path.join(BASE_DIR, ".env"))
#---------------------------------------------------#

app = FastAPI()
#--------------- added code ------------------------#
app.add_middleware(DBSessionMiddleware, db_url=os.environ["DATABASE_URL"])
#---------------------------------------------------#
#--------------- modified code ---------------------#
@app.post("/user/", response_model=SchemaUser)
def create_user(user: SchemaUser):
db_user = ModelUser(
first_name=user.first_name, last_name=user.last_name, age=user.age
)
db.session.add(db_user)
db.session.commit()
return db_user
#---------------------------------------------------#

if __name__ == "__main__":
uvicorn.run(app, host="0.0.0.0", port=8000)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store