grillazz/fastapi-sqlalchemy-asyncpg

Integration of FastAPI framework supported by Pydantic with SQLAlchemy ORM and PostgreSQL on asyncpg driver

fastapi-sqlalchemy-asyncpg

Table of Contents

1. About The Project
   • Built With
2. Getting Started
   • Make will help you
   • How to feed database
   • Rainbow logs with rich
   • Setup user auth
   • Local development with poetry
   • Import xlsx files with polars and calamine
3. Acknowledgments

About The Project

Example of FastAPI integration supported by almighty Pydantic 2.0 with SQLAlchemy ORM and PostgreSQL16 connected via fastest Database Client Library for python/asyncio asyncpg.

Beside of using latest and greatest version of SQLAlchemy with it robustness, powerfulness and speed of asyncpg there is FastAPI (modern, fast (high-performance), web framework for building APIs with Python 3.8+ based on standard Python type hints.) already reviewed on thoughtworks and noted in Python Developers Survey 2021 Results as the fifth official annual Python Developers Survey, conducted as a collaborative effort between the Python Software Foundation and JetBrains.

Built With

(back to top)

Getting Started

Make will help you

To build , run and test and more ... use magic of make help to play with this project.

1. make docker-build
2. make docker-up
3. make docker-apply-db-migrations
4. make docker-feed-db

(back to top)

How to feed database

It took me a while to find nice data set. Hope works of Shakespeare as example will be able to cover first part with read only declarative base configuration and all type of funny selects :) Data set is coming form https://github.com/catherinedevlin/opensourceshakespeare Next models were generated with https://github.com/agronholm/sqlacodegen

(back to top)

Rainbow logs with rich 🌈

To deliver better user(developer) experience when watching logs with tons of information from few emitters (which are really needy on development stage) project is using rich library. Event with rich superpowers reading logs is not easy. Found rich really nice - but it took time to learn how to integrate it as logger object properly and keep it as singleton.

To address below needs:

• it is hard to find what I am looking for even with glasses on.
• don’t want to hire ELK to be able to use logs.
• want to move fast enough with debugging.

Below steps were done to integrate rich into project.

1. Configure emitters with config.ini
2. Eliminate duplicates i.e. sqlalchemy echo by separate handlers
3. Keep logger as singleton pattern to avoid multiple instances
4. add uvicorn parameter --log-config config.ini

(back to top)

Setup User Auth

Setup user authentication with JWT and Redis as token storage.

Local development with poetry

pyenv install 3.12 && pyenv local 3.12

poetry install

Hope you enjoy it.

Import xlsx files with polars and calamine

Power of Polars Library in data manipulation and analysis. It uses the polars library to read the Excel data into a DataFrame by passing the bytes to the pl.read_excel() function - https://docs.pola.rs/py-polars/html/reference/api/polars.read_excel.html In pl.read_excel() “calamine” engine can be used for reading all major types of Excel Workbook (.xlsx, .xlsb, .xls) and is dramatically faster than the other options, using the fastexcel module to bind calamine.

(back to top)

Acknowledgments

Use this space to list resources you find helpful and would like to give credit to. I've included a few of my favorites to kick things off!

• Open Source Shakespeare Dataset
• SQL Code Generator
• Passlib - password hashing library for Python
• Polars - fast DataFrame library for Rust and Python

(back to top)

Change Log

• [long time ago...] it was a long time ago in galaxy far far away...
• [JUN 4 2022] alembic migrations added to project
• [JUN 6 2022] initial dataset for shakespeare models
• [OCT 3 2022] poetry added to project
• [NOV 12 2022] ruff implemented to project as linting tool
• [FEB 14 2023] bump project to Python 3.11
• [APR 10 2023] implement logging with rich
• [APR 28 2023] Rainbow logs with rich 🌈
• [JUL 7 2023] migrate to pydantic 2.0 ⏩
• [JUL 25 2023] add user authentication with JWT and Redis as token storage 🔒 🔑
• [SEP 2 2023] add passlib and bcrypt for password hashing 🔒 🔑
• [OCT 21 2023] refactor shakespeare models to use sqlalchemy 2.0 ⏩
• [FEB 1 2024] bump project to Python 3.12 ⏩
• [MAR 15 2024] add polars and calamine to project 😻

(back to top)

fastapi-sqlalchemy-asyncpg's Issues

ImportError: cannot import name 'getLogger'

Try to debug this app and have problem https://stackoverflow.com/questions/53127794/importerror-cannot-import-name-getlogger

Please, rename logging.py

Unnecessary method

Hey grillazz! Thanks for this awesome repo :)

Does this method have any purpose? It does not seem to be referenced anywhere.

fastapi-sqlalchemy-asyncpg/app/database.py

Lines 36 to 50 in e71d41a

	async def get_async_db() -> AsyncGenerator:
	try:
	session: AsyncSession = AsyncSessionFactory()
	logger.debug(f"ASYNC Pool: {engine.pool.status()}")
	yield session
	except SQLAlchemyError as sql_ex:
	await session.rollback()
	raise sql_ex
	except HTTPException as http_ex:
	await session.rollback()
	raise http_ex
	else:
	await session.commit()
	finally:
	await session.close()

add model relations in async environment

jwt auth sample

SQLAlchemy Engine disposal

I've come across your project and I really appreciate what you've done.
I have a question that bugs me for a long time and I've never seen any project addressing it, including yours.

When you create an AsyncEngine, you are supposed to dispose it, ideally in shutdown event. However, I never see anyone doing it in practice. Is it because you rely on GC of Python to clean it for you?

import xlsx endpoint

boost logging with rich

Discuss about config.py

import os
from functools import lru_cache

from pydantic import PostgresDsn, RedisDsn
from pydantic_settings import BaseSettings


class Settings(BaseSettings):
    asyncpg_url: PostgresDsn = os.getenv("SQL_URL")
    secret_key: str = os.getenv("FERNET_KEY")
    redis_url: RedisDsn = os.getenv("REDIS_URL")
    jwt_algorithm: str = os.getenv("JWT_ALGORITHM")
    jwt_expire: int = os.getenv("JWT_EXPIRE")


@lru_cache
def get_settings():
    return Settings()


settings = get_settings()

It's the same as below, I think. Because get_setting() with @lru_cache will always return the same object.:

import os
from functools import lru_cache

from pydantic import PostgresDsn, RedisDsn
from pydantic_settings import BaseSettings


class Settings(BaseSettings):
    asyncpg_url: PostgresDsn = os.getenv("SQL_URL")
    secret_key: str = os.getenv("FERNET_KEY")
    redis_url: RedisDsn = os.getenv("REDIS_URL")
    jwt_algorithm: str = os.getenv("JWT_ALGORITHM")
    jwt_expire: int = os.getenv("JWT_EXPIRE")


settings = Setting()

add performance tests with locust

python 312

replace pipenv with poetry

reading by roles using AI Voice with websockets

add pylint for CI

static type checking

options are: mypy, pyright, pyre

add make feed

add initial dataset to feed database tables

postgresql full text search

• asgi websockets
• redis

migrate to pydantic 2

alembic migration customs

• one database many schemas
• partitions

asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

Hey,
I'm using your boilerplate to setup my fastapi with sqlalchemy and asyncpg.
Now when I do some load testing on my API, after a few concurrent requests , I have the following issue:
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress
How can I prevent this ?
Thanks,
Henry

add JSON field example

expose key, value as property and test response with pydantic

improve readme

add SQLAlchemy 2.0

drop pytest-asyncio

revisit logging

https://rich.readthedocs.io/en/stable/logging.html

implementation samples
https://github.com/nsidnev/fastapi-realworld-example-app/blob/master/app/core/logging.py

example log.ini
https://github.com/RedHatInsights/policies-notifications/blob/master/logging.ini

additional reading

[QUESTION] How to do logging in a FastApi container, any logging does not appear · Issue #19 · tiangolo/uvicorn-gunicorn-fastapi-docker

add test coverage

sqlalchemy async scoped session

intro alembic migrations

low carbon footprint with good cache :)

• every database update could trigger refresh of all cache values or clear of all cache values
• it can be part of save and update base meths

add slimfast option 💨

add docker-slim as optional use case

add pre-commit

add anyio for pytest

add ruff as code base linter