TIL: Using SQLModel Asynchronously with FastAPI (and Air) with PostgreSQL

"SQLModel is a really useful library for working with SQL databases in Python, built on top of SQLAlchemy and Pydantic. However, AFAIK there's no documentation supporting asynchronous operations for PostgreSQL, which can be a limitation when building high-performance web applications with FastAPI and Air. First, let's set up our environment. We'll need to install the necessary packages: ```bash uv venv uv add "FastAPI[standard]" SQLModel asyncpg psycopg2-binary greenlet ``` A quick quick guide to these dependencies:"

"**psycopg2-binary**: A PostgreSQL adapter for Python, which will be used by SQLModel for synchronous operations - **asyncpg**: An asynchronous PostgreSQL client library, which will be used by SQLModel for async operations - **greenlet**: A dependency for SQLAlchemy, which powers SQLModel, to support asynchronous operations. Yes, we need BOTH synchronous and asynchronous PostgreSQL drivers. SQLModel (and SQLAlchemy) use the synchronous driver for certain operations, even when we are mostly working in an async context."

Set up an environment with FastAPI, SQLModel, asyncpg, psycopg2-binary, and greenlet to support both synchronous and asynchronous PostgreSQL access. Install both asyncpg and psycopg2-binary because SQLModel and SQLAlchemy require the synchronous driver for some operations while asyncpg enables asynchronous queries. Use a synchronous engine to create database tables and asynchronous sessions for request handlers. Define a Story model synchronously and implement asynchronous endpoints for retrieving and creating stories in a FastAPI application. Adjust database connection strings for user and database names as needed for each deployment.

Read at https://daniel.feldroy.com