FastAPI powers h.api’s engine that provides instant, real-time mortgage rate data. 

I love FastAPI, and I highly recommend it as a backend for nearly any project. I also love Postgres, and often use it in tandem with FastAPI. Here’s a quick breakdown on how to set up FastAPI to work well with your Postgres DB.

1. Add your Postgres connection string in your .env file

I wrote a more in-depth guide on how to set up environment variables in FastAPI, but I’ll include a quick version here as it’s a necessary step in connecting to any DB.

First, make sure you have a .env file at the root of your project:

# .env

PG_CONNECTION_STRING="postgresql://user:pass@host:port/postgres"

Then, create a config.py file at the root with a get_settings() function:

# config.py

from functools import lru_cache
from pydantic_settings import BaseSettings, SettingsConfigDict


class Settings(BaseSettings):
    PG_CONNECTION_STRING: str

    model_config = SettingsConfigDict(env_file=".env")

@lru_cache()
def get_settings():
    return Settings()

Now you can use your environment variables anywhere in your FastAPI app:

# main.py

from fastapi import FastAPI
from config import get_settings

app = FastAPI()

# Example use of a .env variable in an app route
@app.get("/pg-connection-string")
async def get_pg_connection_string():
    PG_CONNECTION_STRING = get_settings().PG_CONNECTION_STRING
    return {"pg_connection_string": PG_CONNECTION_STRING}

2. Set up data models based on your Postgres tables

FastAPI uses SQLModel, which uses SQLAlchemy + Pydantic to interact with SQL databases.

Basically, every table should be represented with a Pydantic BaseModel-like class: SQLModel 

# models/user.py

from sqlmodel import Field, SQLModel


class User(SQLModel, table=True):
    __tablename__ = 'users'

    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str
    password: str

Every field represents a column, and __tablename__ will make sure your model always routes to the correct table.

3. Create db.py and a global SessionDep

Create a file called db.py at the root of your app. This is where we will create our database connection dependency that we can inject throughout the app.

Here’s a complete db.py file you can use. I’ll break it down below.

# db.py

from typing import Annotated

from fastapi import Depends
from sqlmodel import Session, create_engine

from config import get_settings
from models.user import User

engine = create_engine(get_settings().PG_CONNECTION_STRING)

def get_session():
    with Session(engine) as session:
        yield session

SessionDep = Annotated[Session, Depends(get_session)]

First, we need to create our DB connection with SQLModel.create_engine(). 

Then, a function that will yield our connection with a context manager. 

Finally, we use FastAPI’s Depends with Annotated to give us a global SessionDep we can inject as a dependency to routes.

3.5 (Bonus, skip if you want) Create tables on app start

FastAPI provides a pretty quick and easy to create tables without having to manually create them elsewhere. We need to add a couple things to db.py, then one thing to main.py.

First, add a very simple function to db.py using SQLModel.metadata.create_all(engine). Then, we need to arbitrarily import any table models into db.py. Here’s how that looks in a full db.py file:

# db.py

from typing import Annotated

from fastapi import Depends
from sqlmodel import SQLModel, Session, create_engine

from config import get_settings
from models.user import User

engine = create_engine(get_settings().PG_CONNECTION_STRING)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]

if __name__ == '__main__':
    # Useless section just to keep model imports
    a = User

SQLModel.metadata.create_all(engine) will take any SQLModel classes that are imported in this file and create them as tables in your DB.

Simple reason for adding the bottom section: I use PyCharm’s “Optimize Import” tool often, and it will remove unused imports. Having it assigned to a variable like this doesn’t affect any code, but it keeps the import at the top of the file when linters run to make sure it gets captured by create_all().

Finally, we need to add an on_startup() function to our main.py file. Here’s what that looks like:

# main.py

from contextlib import asynccontextmanager
from fastapi import FastAPI
from db import create_db_and_tables

@asynccontextmanager
async def on_startup(application: FastAPI):
    create_db_and_tables()
    yield

app = FastAPI(lifespan=on_startup)

Now, any tables based on the models in db.py will be created on startup if they aren’t already. If they are already created, they won’t be affected.

Note — changing fields to add/remove/modify columns will not persist to your database. Column changes must be made manually or by other means.

4. Use the SessionDep in routes

Now we can simply inject SessionDep into our code anywhere throughout the app 🎉

@app.post("/user")
def create_user(user: User, session: SessionDep) -> User:
    session.add(user)
    session.commit()
    session.refresh(user)
    return user

I often organize my code with service classes (highly recommend!) and the session can be nicely injected into classes using the __init__ method:

# users_service.py

from sqlmodel import select
from db import SessionDep
from .models.user import User

class UsersService:

    def __init__(self, db: SessionDep):
        self.db = db

    def create_user(self, user: User) -> User:
        self.db.add(user)
        self.db.commit()
        self.db.refresh(user)
        return user

    def get_user_by_id(self, id: int) -> User:
        res = self.db.exec(
            select(User).where(User.id == id)
        )
        return res.one()

Then you can inject the service into routes using Depends:

# users_router.py

from .models.user import User
from .users_service import UsersService

@router.post('/user')
def create_user(user: User, service: UsersService = Depends(UsersService)) -> User:
    return service.create_user(user)

I prefer this structure to separate out router logic and business logic.

Congrats, you can now use Postgres throughout your FastAPI app 🎊

Powered by Synscribe