Building a REST API with FastAPI, Async SQLAlchemy, and PostgreSQL

In this article, we will explore how to build a RESTful API using FastAPI, Async SQLAlchemy, and PostgreSQL. FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.7+ based on standard Python type hints. It is lightweight, easy to use, and provides support for asynchronous programming.

Async SQLAlchemy is an asynchronous database library that provides support for working with relational databases in an asynchronous manner. PostgreSQL is a powerful open-source relational database management system that is widely used and well-supported.

Setting up the Environment

Before we begin building our REST API, we need to set up our development environment. First, make sure you have Python 3.7 or later installed on your system. Then, install FastAPI and Async SQLAlchemy using pip:

pip install fastapi async-sqlalchemy

Next, we need to install the PostgreSQL database server. You can download and install PostgreSQL from the official website or use a containerized version using Docker. Once PostgreSQL is installed, create a new database and user for our application.

Defining Models

Once our environment is set up, we can start defining the models for our API. We will create a simple User model that represents a user in our system. Using Async SQLAlchemy, we can define the model as a Python class and use the @as_declarative decorator to mark it as an asynchronous declarative base:

    from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
    from sqlalchemy.ext.declarative import as_declarative, declared_attr

    @as_declarative()
    class Base:
        id: Any
        __name__: str
    

Next, we define the User model:

    from sqlalchemy import Column, String

    class User(Base):
        __tablename__ = 'users'

        id = Column(Integer, primary_key=True, index=True)
        username = Column(String, unique=True, index=True)
        email = Column(String, unique=True, index=True)
    

Creating the API

With our models in place, we can now create the API using FastAPI. We will define endpoints for creating, retrieving, updating, and deleting users. FastAPI makes it easy to define API routes and request parameters using Python type hints. We can also use async and await keywords to work with Async SQLAlchemy in an asynchronous manner:

    from fastapi import FastAPI
    from app.models import User
    from app.database import async_session

    app = FastAPI()

    @app.post("/users/")
    async def create_user(username: str, email: str):
        async with async_session() as session:
            user = User(username=username, email=email)
            session.add(user)
            await session.commit()
            return user

    @app.get("/users/{user_id}")
    async def get_user(user_id: int):
        async with async_session() as session:
            user = await session.get(User, user_id)
            return user

    # Other CRUD operations can be defined in a similar manner

    

Testing the API

Once our API is defined, we can start testing it using tools like Postman or curl. We can send requests to the API endpoints to create, retrieve, update, and delete users in the database. Since FastAPI provides automatic interactive API documentation using Swagger UI, we can also use it to test the API and explore the available endpoints and request parameters.

Building a REST API with FastAPI, Async SQLAlchemy, and PostgreSQL is a powerful combination that provides a scalable and high-performance solution for building modern web applications. It leverages the strengths of Python’s asynchronous programming capabilities and PostgreSQL’s robust relational database management system.