How to Create a RESTful API Using FastAPI with PostgreSQL

In today’s digital landscape, building efficient and scalable web applications is essential for success. One effective way to achieve this is by creating a RESTful API. FastAPI, a modern Python web framework, allows developers to create APIs quickly and efficiently, while PostgreSQL is a powerful, open-source relational database. In this article, we will guide you through the process of creating a RESTful API using FastAPI with PostgreSQL, complete with detailed code examples and actionable insights.

What is FastAPI?

FastAPI is a high-performance web framework for building APIs with Python 3.7+ based on standard Python type hints. It is designed to make it easy to create robust APIs quickly while ensuring high performance and easy integration with various data sources.

Key Features of FastAPI:

• Fast: As the name implies, it is built for speed, allowing for asynchronous programming.
• Easy to Use: FastAPI is intuitive and user-friendly, making API development a breeze.
• Automatic Documentation: It automatically generates interactive API documentation (Swagger UI and ReDoc).
• Type Checking: Leverages Python's type hints for data validation and serialization.

What is PostgreSQL?

PostgreSQL is an advanced, open-source relational database management system (RDBMS) known for its reliability, robustness, and performance. It supports advanced data types and is highly extensible.

Use Cases for PostgreSQL:

• Applications requiring complex queries.
• Systems that require high concurrency.
• Projects needing ACID compliance and data integrity.

Prerequisites

Before we get started, ensure you have the following installed:

• Python 3.7 or later
• PostgreSQL
• pip (Python package manager)
• An IDE or text editor (like VSCode, PyCharm, etc.)

Step 1: Setting Up Your Environment

Let's create a new directory for our project and set up a virtual environment.

mkdir fastapi-postgresql-api
cd fastapi-postgresql-api
python -m venv venv
source venv/bin/activate  # For Windows use: venv\Scripts\activate

Next, install the necessary packages:

pip install fastapi[all] psycopg2-binary sqlalchemy uvicorn

• FastAPI: The web framework.
• psycopg2-binary: PostgreSQL adapter for Python.
• SQLAlchemy: ORM to interact with PostgreSQL.
• uvicorn: ASGI server to run your FastAPI application.

Step 2: Setting Up PostgreSQL

1. Create a Database: Open your PostgreSQL terminal and create a new database:

CREATE DATABASE fastapi_db;

1. Create a Table: For this example, we will create a simple table called items:

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

Step 3: Building the FastAPI Application

Now, let’s start coding our FastAPI application. Create a new file named main.py.

Setting Up the Database Connection

Add the following code to main.py to set up the database connection and define the SQLAlchemy models.

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine, Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql://username:password@localhost/fastapi_db"  # Replace username and password

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    description = Column(Text)

Base.metadata.create_all(bind=engine)

app = FastAPI()

Creating Pydantic Models

Pydantic models are used for data validation. Define an ItemCreate model to create new items:

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

Implementing CRUD Operations

Next, let’s implement the CRUD (Create, Read, Update, Delete) operations for our API.

Create an Item

@app.post("/items/", response_model=ItemCreate)
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)
    db.close()
    return db_item

Read Items

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

Update an Item

@app.put("/items/{item_id}", response_model=ItemCreate)
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:
        db.close()
        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)
    db.close()
    return db_item

Delete an Item

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

Step 4: Running Your FastAPI Application

To run your FastAPI application, execute the following command in your terminal:

uvicorn main:app --reload

Now, navigate to http://127.0.0.1:8000/docs in your web browser to access the interactive API documentation.

Conclusion

Congratulations! You’ve successfully created a RESTful API using FastAPI with PostgreSQL. You now have a robust foundation to build upon, allowing you to expand your application as needed. FastAPI's performance and ease of use, combined with PostgreSQL's reliability, make them a powerful duo for modern web development.

Key Takeaways:

• FastAPI simplifies API development with automatic documentation and type validation.
• PostgreSQL provides a strong relational database solution for storing data.
• CRUD operations form the backbone of your API functionality.

By mastering these tools, you can create scalable and efficient applications that meet the demands of users today. Happy coding!