Hello!

It’s a clear-skied, crisp day here in Minnesota. Winter is coming, but not today.

While we wait for the world to turn and the seasons to change, why not pass some time thinking about database configuration?

I recently moved from SQLite to PostgreSQL as the database for pythondocs.xyz, my project that tries to bring a Google-like experience to Python’s official documentation. (The main motivation was to improve search results with Postgres’ full-text search capabilities.)

Swapping the DB engines proved to be remarkably straightforward: the SQLAlchemy ORM abstracted away dialect differences, and I was already using an abstract Database class with FastAPI, so I just needed to write another implementation.

Keep reading for a walkthrough of the code…

Pre-requisites

You need Postgres running on your machine or in a Docker container.

I’m using Docker Compose for this project. I won’t explain more today but I’m happy to make it the subject of a future blog post if people are interested?

You also need these Python packages:

• uvicorn (web server), python-dotenv (settings loader), fastapi (web framework), sqlalchemy (ORM) , asyncpg (database driver)

I’m presenting this example as a single Python file so that it’s easy to copy and understand. But I’ll also mention original file paths, as you’ll definitely want more organization in a real project.

Here are the imports for the example:

import os
from abc import ABC, abstractmethod
from typing import AsyncIterator, Optional

import uvicorn
from dotenv import load_dotenv
from fastapi import Depends, FastAPI
from fastapi.responses import JSONResponse
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

# not included in example
from app.models import Folder

Config elation

The config module provides production or development settings, depending on environment variable. It also loads the Postgres password from an .env file. The idea is that you add .env to your .gitignore file so that you don’t mix secrets with the rest of your code.

# config.py
load_dotenv()


class Config(ABC):
    POSTGRES_USERNAME = "postgres"
    POSTGRES_DB_NAME = "postgres"
    # localhost for development purposes
    POSTGRES_HOST = "localhost"
    POSTGRES_PORT = "5432"
    # password stored in .env file
    POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD")
    SQL_COMMAND_ECHO = False


class DevelopmentConfig(Config):
    SQL_COMMAND_ECHO = True


class ProductionConfig(Config):
    # hostname in Docker network for production
    POSTGRES_HOST = "db"


def get_config() -> Config:
    env = os.getenv("ENV")
    if env == "development":
        return DevelopmentConfig()
    return ProductionConfig()


config = get_config()

Data based

Here, we define an abstract Database class, with a __call__() method that works with FastAPI’s dependency injection. Its setup() method is provided in concrete implementations, like PostgresDatabase.

# database/base.py
class Database(ABC):
    def __init__(self):
        self.async_sessionmaker: Optional[sessionmaker] = None

    async def __call__(self) -> AsyncIterator[AsyncSession]:
        """For use with FastAPI Depends"""
        if not self.async_sessionmaker:
            raise ValueError("async_sessionmaker not available. Run setup() first.")
        async with self.async_sessionmaker() as session:
            yield session

    @abstractmethod
    def setup(self) -> None:
        ...


# database/postgres.py
def get_connection_string(driver: str = "asyncpg") -> str:
    return f"postgresql+{driver}://{config.POSTGRES_USERNAME}:{config.POSTGRES_PASSWORD}@{config.POSTGRES_HOST}:{config.POSTGRES_PORT}/{config.POSTGRES_DB_NAME}"


class PostgresDatabase(Database):
    def setup(self) -> None:
        async_engine = create_async_engine(
            get_connection_string(),
            echo=config.SQL_COMMAND_ECHO,
        )
        self.async_sessionmaker = sessionmaker(async_engine, class_=AsyncSession)

Stitch it all together

There are a few things going on here:

1. An instance of PostgresDatabase is created in the depends module.
2. The fast_api object is created in main.
3. db.setup() is run as a FastAPI startup event only. This means that all code files can be safely imported without side effects.
4. A route is definied which performs a simple DB query using FastAPI’s Depends.
5. Finally, the uvicorn web server is started.

# depends.py
db = PostgresDatabase()

# main.py
fast_api = FastAPI()


@fast_api.on_event("startup")
async def setup_db() -> None:
    db.setup()


# routes.py
@fast_api.get("/example/")
async def db_query_example(
    session: AsyncSession = Depends(db),
) -> JSONResponse:
    results = await session.execute(select(Folder))
    return results.all()


# run_uvicorn.py
if __name__ == "__main__":
    uvicorn.run(
        "fastapi_postgres_async_example:fast_api",
    )

That’s it!

If the computer Gods smile upon you, you now have a working – and pretty fast – database configuration.

Thanks for reading! I hope this has been useful to someone. Please let me know in the comments.

The full code is over here on GitHub.