integrating-postgresql-with-fastapi-using-sqlalchemy-orm.html

Integrating PostgreSQL with FastAPI using SQLAlchemy ORM

FastAPI is rapidly gaining traction among developers for its performance and ease of use in building APIs. When combined with PostgreSQL, a powerful open-source relational database, and SQLAlchemy, a versatile ORM (Object-Relational Mapping) tool, you can create efficient and scalable applications with minimal effort. In this article, we’ll explore how to integrate PostgreSQL with FastAPI using SQLAlchemy ORM, providing step-by-step instructions and code examples to help you build your own applications.

What is FastAPI?

FastAPI is a modern web framework for building APIs with Python 3.6+ based on standard Python type hints. It offers automatic generation of OpenAPI documentation and is designed for performance, making it one of the fastest frameworks available.

Key Features of FastAPI

Fast: One of the fastest Python frameworks, built on Starlette for the web parts and Pydantic for the data parts.
Automatic Docs: Generates interactive API documentation automatically.
Easy to Use: Intuitive and easy to learn, focusing on developer experience.
Asynchronous Support: Built to support async and await, making it perfect for scalable applications.

What is PostgreSQL?

PostgreSQL is a powerful, open-source relational database management system (RDBMS) that uses and extends the SQL language. It is known for its reliability, robustness, and flexibility.

Key Features of PostgreSQL

ACID Compliance: Ensures reliable transactions.
Extensibility: Supports custom data types and functions.
Concurrency: Handles multiple transactions effectively with its MVCC (Multi-Version Concurrency Control).

What is SQLAlchemy?

SQLAlchemy is an ORM library for Python that provides a set of high-level API abstractions for interacting with databases. It allows developers to use Python classes to represent database tables, enabling a more intuitive way of building database queries.

Key Features of SQLAlchemy

ORM Support: Maps Python objects to database tables.
Database Abstraction: Works with multiple database backends.
Query Building: Provides a flexible and powerful query-building interface.

Setting Up Your Environment

Before diving into the code, you need to set up your environment. Here's how to do it step-by-step.

Step 1: Install Required Packages

You'll need FastAPI, SQLAlchemy, and an async driver for PostgreSQL called asyncpg. Install them using pip:

pip install fastapi[all] sqlalchemy asyncpg psycopg2

Step 2: Create Your Database

Make sure you have PostgreSQL installed and running. Create a new database for your application:

CREATE DATABASE fastapi_db;

Step 3: Create Your Application Structure

Create a new directory for your FastAPI application and navigate into it:

mkdir fastapi_postgres_app
cd fastapi_postgres_app

Inside this directory, create the following files:

main.py
models.py
database.py
schemas.py

Building the Application

Step 4: Database Configuration

In database.py, set up the database connection using SQLAlchemy:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql+asyncpg://username:password@localhost/fastapi_db"

engine = create_engine(DATABASE_URL, echo=True)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Step 5: Define Your Models

In models.py, define your database models using SQLAlchemy:

from sqlalchemy import Column, Integer, String
from .database import Base

class User(Base):
    __tablename__ = "users"

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

Step 6: Create Pydantic Schemas

In schemas.py, define your Pydantic models which will be used for data validation:

from pydantic import BaseModel

class UserCreate(BaseModel):
    name: str
    email: str

class User(BaseModel):
    id: int
    name: str
    email: str

    class Config:
        orm_mode = True

Step 7: Create the FastAPI Application

In main.py, create the FastAPI application and define the API endpoints:

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from . import models, schemas
from .database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = models.User(name=user.name, email=user.email)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = db.query(models.User).filter(models.User.id == user_id).first()
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user

Step 8: Run Your Application

Now that everything is set up, you can run your FastAPI application:

uvicorn main:app --reload

You can access the API documentation by navigating to http://127.0.0.1:8000/docs in your browser.

Conclusion

Integrating PostgreSQL with FastAPI using SQLAlchemy ORM provides a robust foundation for building high-performance APIs. With clear structure and separation of concerns, you can easily scale your application as needed. Whether you’re building a simple CRUD application or a more complex system, this combination offers the tools necessary for success.

By following the steps outlined in this article, you should now have a basic understanding of how to set up FastAPI with PostgreSQL and SQLAlchemy. Explore further by adding more features, experimenting with different endpoints, and optimizing your database interactions as you grow more comfortable with these technologies. Happy coding!