How to Set Up a FastAPI Application with PostgreSQL

FastAPI is a modern, fast (high-performance) web framework for building APIs with Python 3.6+ based on standard Python type hints. It’s particularly well-suited for building RESTful APIs due to its speed and ease of use. When combined with PostgreSQL, a powerful relational database system, you can create robust applications that can handle complex data interactions. In this article, we’ll walk through the step-by-step process of setting up a FastAPI application with PostgreSQL, including code examples and troubleshooting tips.

Table of Contents

1. Prerequisites
2. Setting Up Your Environment
3. Creating the FastAPI Application
4. Connecting to PostgreSQL
5. Creating CRUD Operations
6. Running the Application
7. Conclusion

Prerequisites

Before you start, ensure you have the following installed on your machine:

• Python 3.6 or later
• PostgreSQL
• pip (Python package installer)

You can check your Python version with:

python --version

Setting Up Your Environment

Start by creating a new directory for your FastAPI project:

mkdir fastapi-postgresql
cd fastapi-postgresql

Next, set up a virtual environment to manage your project dependencies:

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

Now, install FastAPI and an ASGI server (like Uvicorn) along with the PostgreSQL driver:

pip install fastapi uvicorn psycopg2-binary

You might also want to install SQLAlchemy for ORM (Object Relational Mapping) capabilities:

pip install sqlalchemy

Creating the FastAPI Application

Create a new file named main.py in your project directory:

# main.py
from fastapi import FastAPI

app = FastAPI()

@app.get("/")
def read_root():
    return {"Hello": "World"}

This simple application creates a FastAPI instance and defines a root endpoint that returns a JSON response.

Connecting to PostgreSQL

To connect to PostgreSQL, we need to define our database configuration. Create a new file named database.py:

# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "postgresql://username:password@localhost/dbname"

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

Make sure to replace username, password, and dbname with your PostgreSQL credentials.

Creating a Database Model

Next, let’s create a model for our application. Create a new file named models.py:

# models.py
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)

Creating the Database Tables

To create the database tables, add the following code in your main.py:

# main.py (continued)
from fastapi import FastAPI
from database import engine, Base
import models

Base.metadata.create_all(bind=engine)

This will create the items table in your PostgreSQL database.

Creating CRUD Operations

Let’s implement the Create, Read, Update, and Delete (CRUD) operations in our FastAPI application. Update your main.py to include the following:

# main.py (continued)
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
import models
from database import SessionLocal

app = FastAPI()

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

@app.post("/items/", response_model=models.Item)
def create_item(item: models.Item, db: Session = Depends(get_db)):
    db.add(item)
    db.commit()
    db.refresh(item)
    return item

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

@app.put("/items/{item_id}", response_model=models.Item)
def update_item(item_id: int, item: models.Item, db: Session = Depends(get_db)):
    db_item = db.query(models.Item).filter(models.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

@app.delete("/items/{item_id}", response_model=models.Item)
def delete_item(item_id: int, db: Session = Depends(get_db)):
    db_item = db.query(models.Item).filter(models.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 db_item

Running the Application

Now that we have set up our FastAPI application with PostgreSQL, you can run the application using Uvicorn:

uvicorn main:app --reload

Visit http://127.0.0.1:8000/docs to view the automatically generated API documentation by FastAPI. Here, you can test your CRUD operations interactively.

Conclusion

Setting up a FastAPI application with PostgreSQL is a straightforward process that allows you to build powerful APIs quickly. By following the steps outlined in this article, you can create a fully functional web service capable of handling CRUD operations seamlessly.

As you develop your application further, consider implementing additional features such as authentication, advanced query handling, and data validation to enhance its capabilities. The combination of FastAPI and PostgreSQL offers a solid foundation for any web application, making it easier than ever to build high-performance APIs.

Happy coding!