Building a CRUD API with FastAPI and PostgreSQL

In modern web development, creating a CRUD (Create, Read, Update, Delete) API is a common requirement. FastAPI, a modern, fast (high-performance) web framework for building APIs with Python, and PostgreSQL, a powerful open - source relational database, are a great combination for this task. FastAPI leverages Python’s type hints to provide automatic data validation, serialization, and documentation. PostgreSQL offers features like data integrity, transactions, and extensibility. This blog will guide you through the process of building a CRUD API using FastAPI and PostgreSQL.

Table of Contents

  1. Prerequisites
  2. Setting up the Project
  3. Database Setup with PostgreSQL
  4. Defining Data Models
  5. Creating the CRUD Operations
  6. Testing the API
  7. Best Practices
  8. Conclusion
  9. References

Prerequisites

  • Python: You need to have Python 3.7 or higher installed on your system.
  • PostgreSQL: Install PostgreSQL on your machine and create a database for your project.
  • FastAPI and Related Libraries: You will need to install fastapi, uvicorn (a server for running FastAPI applications), sqlalchemy (for database operations), and psycopg2 - binary (a PostgreSQL adapter for Python). You can install them using pip:
pip install fastapi uvicorn sqlalchemy psycopg2-binary

Database Setup with PostgreSQL

First, create a new database in PostgreSQL. You can use the psql command-line tool:

-- Connect to the PostgreSQL server
psql -U your_username

-- Create a new database
CREATE DATABASE your_database_name;

Next, establish a connection to the database using SQLAlchemy in Python. Create a file named database.py:

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

# Database URL
SQLALCHEMY_DATABASE_URL = "postgresql://your_username:your_password@localhost/your_database_name"

# Create the SQLAlchemy engine
engine = create_engine(SQLALCHEMY_DATABASE_URL)

# Create a session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for declarative models
Base = declarative_base()


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

Defining Data Models

Create a file named models.py to define your data models. For example, let’s create a simple Item model:

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)
    description = Column(String)

To create the database tables based on the models, you can add the following code to your main application file (e.g., main.py):

from database import engine
from models import Base

Base.metadata.create_all(bind=engine)

Creating the CRUD Operations

Create a file named crud.py to define the CRUD operations:

from sqlalchemy.orm import Session
from models import Item


def create_item(db: Session, name: str, description: str):
    item = Item(name=name, description=description)
    db.add(item)
    db.commit()
    db.refresh(item)
    return item


def get_item(db: Session, item_id: int):
    return db.query(Item).filter(Item.id == item_id).first()


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


def update_item(db: Session, item_id: int, name: str, description: str):
    item = db.query(Item).filter(Item.id == item_id).first()
    if item:
        item.name = name
        item.description = description
        db.commit()
        db.refresh(item)
    return item


def delete_item(db: Session, item_id: int):
    item = db.query(Item).filter(Item.id == item_id).first()
    if item:
        db.delete(item)
        db.commit()
    return item

Now, create the API endpoints in main.py:

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from database import get_db
from crud import create_item, get_item, get_items, update_item, delete_item

app = FastAPI()


@app.post("/items/")
def create_new_item(name: str, description: str, db: Session = Depends(get_db)):
    return create_item(db, name=name, description=description)


@app.get("/items/{item_id}")
def read_item(item_id: int, db: Session = Depends(get_db)):
    return get_item(db, item_id)


@app.get("/items/")
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    return get_items(db, skip=skip, limit=limit)


@app.put("/items/{item_id}")
def update_existing_item(item_id: int, name: str, description: str, db: Session = Depends(get_db)):
    return update_item(db, item_id, name=name, description=description)


@app.delete("/items/{item_id}")
def delete_existing_item(item_id: int, db: Session = Depends(get_db)):
    return delete_item(db, item_id)

Testing the API

You can start the FastAPI application using Uvicorn:

uvicorn main:app --reload

You can then use tools like curl or Postman to test the API endpoints. For example, to create a new item:

curl -X POST "http://127.0.0.1:8000/items/?name=TestItem&description=This is a test item"

Best Practices

  • Error Handling: Implement proper error handling in your API endpoints. For example, return appropriate HTTP status codes and error messages when something goes wrong.
  • Input Validation: Use FastAPI’s built - in input validation to ensure that the data received from the client is in the correct format.
  • Security: Protect your API from common security vulnerabilities such as SQL injection. SQLAlchemy helps prevent SQL injection by using parameterized queries.
  • Logging: Implement logging to track the execution of your API and diagnose issues.

Conclusion

Building a CRUD API with FastAPI and PostgreSQL is a straightforward process. FastAPI provides a high - performance and easy - to - use framework for creating APIs, while PostgreSQL offers a reliable and feature - rich database solution. By following the steps outlined in this blog, you can create a fully functional CRUD API that can handle basic data operations.

References