How to Create a RESTful API with FastAPI and PostgreSQL

In today’s tech-driven world, building APIs has become a fundamental skill for developers. RESTful APIs are particularly popular due to their simplicity and scalability. FastAPI, a modern web framework for Python, allows developers to quickly create APIs that are fast and easy to use. Coupled with PostgreSQL, a powerful relational database, you can build robust applications that handle complex tasks efficiently. In this article, we’ll walk through the steps needed to create a RESTful API using FastAPI and PostgreSQL.

What is FastAPI?

FastAPI is a Python web framework designed for building APIs quickly and efficiently. It’s based on standard Python-type hints and is built on top of Starlette for the web parts and Pydantic for the data parts. FastAPI is known for:

• Speed: It's one of the fastest Python frameworks available.
• Easy to Use: It has a simple interface that makes getting started easy.
• Automatic Documentation: FastAPI automatically generates interactive API documentation using Swagger UI and ReDoc.

Why Use PostgreSQL?

PostgreSQL is an advanced, open-source relational database that stands out for its robustness, scalability, and support for complex queries. It’s an excellent choice for applications that require:

• Data Integrity: With ACID compliance, PostgreSQL ensures reliable transactions.
• Complex Queries: Capable of handling sophisticated queries and large datasets.
• Extensibility: Supports custom data types, functions, and operators.

Setting Up Your Environment

Before we dive into the code, let’s set up our development environment. You’ll need Python 3.6 or higher, FastAPI, and PostgreSQL installed on your machine. You can install FastAPI and an ASGI server like Uvicorn using pip:

pip install fastapi uvicorn psycopg2-binary

You’ll also need to set up PostgreSQL. Create a new database and a user with the necessary permissions.

Database Setup

1. Open your PostgreSQL command line or pgAdmin.
2. Create a new database:

sql CREATE DATABASE fastapi_db;

1. Create a user:

sql CREATE USER fastapi_user WITH PASSWORD 'password123';

1. Grant privileges:

sql GRANT ALL PRIVILEGES ON DATABASE fastapi_db TO fastapi_user;

Building the FastAPI Application

Now we can create the FastAPI application. Let’s create a simple API for managing a list of items (like a to-do list).

Directory Structure

Create your project directory and navigate into it:

mkdir fastapi_postgresql
cd fastapi_postgresql

Create the following files:

• main.py
• models.py
• database.py
• schemas.py
• crud.py

Step 1: Setting Up the Database Connection

In database.py, set up the connection to your PostgreSQL database:

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

SQLALCHEMY_DATABASE_URL = "postgresql://fastapi_user:password123@localhost/fastapi_db"

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

Base = declarative_base()

Step 2: Creating the Models

In models.py, define your database models:

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

class Item(Base):
    __tablename__ = "items"

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

Step 3: Creating the Schemas

In schemas.py, define the data validation schemas:

from pydantic import BaseModel

class ItemBase(BaseModel):
    name: str
    description: str

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True

Step 4: CRUD Operations

Next, in crud.py, implement the CRUD operations:

from sqlalchemy.orm import Session
from models import Item
from schemas import ItemCreate

def create_item(db: Session, item: ItemCreate):
    db_item = Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

def get_items(db: Session, skip: int = 0, limit: int = 10):
    return db.query(Item).offset(skip).limit(limit).all()

Step 5: Creating the API Endpoints

Finally, in main.py, set up your FastAPI application and define the routes:

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

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

app = FastAPI()

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

@app.post("/items/", response_model=schemas.Item)
def create_item(item: schemas.ItemCreate, db: Session = Depends(get_db)):
    return crud.create_item(db=db, item=item)

@app.get("/items/", response_model=list[schemas.Item])
def read_items(skip: int = 0, limit: int = 10, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Running the Application

Start your FastAPI application by running:

uvicorn main:app --reload

You can now access your API at http://127.0.0.1:8000/items/. Use tools like Postman or your browser to interact with your API.

Conclusion

Creating a RESTful API using FastAPI and PostgreSQL is a straightforward process that can dramatically enhance your development workflow. With FastAPI’s speed and PostgreSQL’s robustness, you can build applications that are both efficient and scalable. This guide provided you with the foundational steps to build your API, but there’s much more to explore with FastAPI, including authentication, middleware, and deployment options. Happy coding!