FastAPI and PostgreSQL | Database Connectivity in Python

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.6+ based on standard Python type hints. It is easy to use, comes with automatic interactive API documentation (using Swagger UI), and high-performance thanks to the use of asynchronous programming.

PostgreSQL is a powerful, open-source relational database management system. It is widely used in web development for its reliability, scalability, and support for advanced features like JSONB, full-text search, and geospatial data types.

In this article, we will explore how to connect FastAPI with PostgreSQL using SQLAlchemy as the ORM (Object-Relational Mapping) tool and Alembic for database migrations.

Setting Up FastAPI with PostgreSQL

To get started, you will need to install the necessary Python packages. You can do this using pip:

pip install fastapi
pip install sqlalchemy
pip install databases

Next, you will also need to install the PostgreSQL database server on your machine or use a cloud-based solution like Amazon RDS or Heroku Postgres.

Connecting FastAPI to PostgreSQL

To connect FastAPI to PostgreSQL, you will need to configure SQLAlchemy with the database connection URL. You can create a database session using the databases library:

from fastapi import FastAPI
from sqlalchemy import create_engine
from databases import Database

# Database connection URL
DATABASE_URL = "postgresql://username:password@localhost/database"

# Create a database session
database = Database(DATABASE_URL)

Now, you can create FastAPI routes that interact with the database using the SQLAlchemy ORM:

from fastapi import Depends
from sqlalchemy.orm import Session
from sqlalchemy.ext.asyncio import AsyncSession

from .models import User
from .database import SYNTAX_DB

# Dependency to get database session
def get_db():
    db = SYNTAX_DB()
    try:
        yield db
    finally:
        db.close()

Database Migrations with Alembic

Database migrations are used to manage changes to the database schema over time. Alembic is a lightweight database migration tool for SQLAlchemy that can generate migration scripts automatically.

To create a new migration with Alembic, you can run the following command:

alembic revision --autogenerate -m "Add user table"

This will create a new migration script with the necessary changes to the database schema. You can then apply the migration using:

alembic upgrade head

Conclusion

FastAPI and PostgreSQL make a powerful combination for building high-performance web applications with a robust database backend. By using SQLAlchemy as the ORM tool and Alembic for database migrations, you can easily manage and scale your application’s data layer.