How to Create RESTful APIs with FastAPI and PostgreSQL

In today’s digital landscape, creating efficient and scalable APIs is essential for any web application. FastAPI, a modern web framework for building APIs with Python, combined with PostgreSQL, a powerful relational database, offers a robust solution for developers. This article will guide you step-by-step through the process of creating RESTful APIs using FastAPI and PostgreSQL, complete with code examples and actionable insights.

What is FastAPI?

FastAPI is a web framework designed for building APIs with Python 3.6+ based on standard Python type hints. It is known for its speed, ease of use, and automatic generation of OpenAPI documentation. Some of its key features include:

• Fast Performance: Asynchronous support allows for high performance, making it one of the fastest frameworks available.
• Easy to Use: Intuitive syntax and automatic data validation enhance developer productivity.
• Built-in Documentation: Automatically generated interactive API documentation through Swagger UI.

What is PostgreSQL?

PostgreSQL is an advanced open-source relational database that is known for its robustness, extensibility, and standards compliance. It supports a wide variety of data types and offers powerful querying capabilities. Key features include:

• ACID Compliance: Ensures reliable transactions.
• Extensible: Supports custom data types and functions.
• Rich Ecosystem: A wide array of libraries and tools available for various programming languages.

Use Cases for FastAPI and PostgreSQL

FastAPI and PostgreSQL can be used in a variety of applications, including:

• Web Applications: Developing backends for interactive websites.
• Microservices: Building lightweight, independently deployable services.
• Data-Driven Applications: Creating APIs that serve complex data queries.

Setting Up Your Environment

Before we begin coding, you’ll need to set up your development environment. Follow these steps:

1. Install Python: Ensure you have Python 3.6 or higher installed on your machine.
2. Install PostgreSQL: Download and install PostgreSQL from the official website.
3. Create a Virtual Environment: Create and activate a virtual environment to manage your dependencies:

bash python -m venv venv source venv/bin/activate # On Windows use `venv\Scripts\activate`

1. Install Required Packages: Use pip to install FastAPI, Uvicorn (an ASGI server), and asyncpg (PostgreSQL driver):

bash pip install fastapi uvicorn asyncpg sqlalchemy

Creating a Simple RESTful API

Let’s create a simple RESTful API that manages a list of items in a PostgreSQL database.

Step 1: Set Up Database

First, create a PostgreSQL database and a table. You can do this through the PostgreSQL command line or a GUI tool like pgAdmin.

CREATE DATABASE fastapi_db;

\c fastapi_db

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

Step 2: Create Your FastAPI Application

Now, let’s create a file named main.py for our FastAPI application.

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine, Column, Integer, String
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)
Base = declarative_base()
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

app = FastAPI()

class Item(Base):
    __tablename__ = "items"

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

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

Base.metadata.create_all(bind=engine)

Step 3: Implement CRUD Operations

Next, we will implement the Create, Read, Update, and Delete (CRUD) operations.

Creating an Item

@app.post("/items/", response_model=ItemCreate)
async def create_item(item: ItemCreate):
    db = SessionLocal()
    db_item = Item(name=item.name, description=item.description)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Retrieving Items

@app.get("/items/{item_id}", response_model=ItemCreate)
async def read_item(item_id: int):
    db = SessionLocal()
    item = db.query(Item).filter(Item.id == item_id).first()
    if item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return item

Updating an Item

@app.put("/items/{item_id}", response_model=ItemCreate)
async def update_item(item_id: int, item: ItemCreate):
    db = SessionLocal()
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")

    db_item.name = item.name
    db_item.description = item.description
    db.commit()
    db.refresh(db_item)
    return db_item

Deleting an Item

@app.delete("/items/{item_id}")
async def delete_item(item_id: int):
    db = SessionLocal()
    db_item = db.query(Item).filter(Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")

    db.delete(db_item)
    db.commit()
    return {"detail": "Item deleted"}

Step 4: Run Your Application

To run your FastAPI application, use Uvicorn:

uvicorn main:app --reload

Visit http://localhost:8000/docs to see the interactive API documentation generated by FastAPI.

Conclusion

Building RESTful APIs with FastAPI and PostgreSQL is straightforward and efficient. By leveraging the power of FastAPI's asynchronous capabilities and PostgreSQL's robust data management features, you can create scalable and high-performance APIs.

As you continue to develop your API, consider implementing advanced features such as authentication, pagination, and error handling. Happy coding!