How to Create a RESTful API with FastAPI and PostgreSQL

Creating a RESTful API has become a crucial skill for developers, especially with the rise of microservices architecture and the need for efficient data handling. FastAPI, a modern Python web framework, makes building APIs easy and fast. Coupled with PostgreSQL, a powerful relational database, you can create robust applications that are both scalable and maintainable. In this article, we will guide you step-by-step on how to create a RESTful API using FastAPI and PostgreSQL.

What is FastAPI?

FastAPI is a high-performance web framework for building APIs with Python 3.6+ based on standard Python type hints. It is designed to be easy to use, fast (high-performance), and robust. FastAPI is built on top of Starlette for the web parts and Pydantic for the data parts, making it a great choice for building RESTful APIs.

Key Features of FastAPI

• Fast: High performance, on par with Node.js and Go.
• Easy: Simple to learn and use.
• Automatic documentation: Generates OpenAPI and JSON Schema documentation automatically.
• Data validation: Uses Pydantic for data validation and settings management.

What is PostgreSQL?

PostgreSQL is a powerful, open-source relational database system that is known for its robustness, extensibility, and standards compliance. It supports advanced data types and performance optimizations, making it an excellent choice for applications requiring complex queries.

Why Use PostgreSQL?

• ACID compliant: Ensures reliable transactions.
• Extensible: Supports custom data types and functions.
• Performance: Optimized for concurrent access and complex queries.

Setting Up Your Environment

Before we dive into coding, we need to set up our environment. For this, you will need:

1. Python 3.6 or higher: Ensure Python is installed on your machine.
2. PostgreSQL: Install PostgreSQL and create a database.
3. FastAPI: We will use FastAPI along with Uvicorn, an ASGI server.

Installing Necessary Packages

You can install FastAPI and other required packages using pip. Open your terminal and run:

pip install fastapi[all] psycopg2

• fastapi[all] installs FastAPI along with all optional dependencies.
• psycopg2 is the PostgreSQL adapter for Python.

Step-by-Step Guide to Create a RESTful API

Step 1: Setting Up PostgreSQL Database

1. Create a Database: Open your PostgreSQL command line or pgAdmin and create a new database named fastapi_db:

CREATE DATABASE fastapi_db;

1. Create a Table: Next, create a table for storing user data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Step 2: Building the FastAPI Application

Create a new directory for your project and navigate into it. Inside, create a file named main.py.

from fastapi import FastAPI
from pydantic import BaseModel
import psycopg2
from psycopg2 import sql

app = FastAPI()

# Database connection
def get_db_connection():
    conn = psycopg2.connect(
        dbname="fastapi_db", user="your_user", password="your_password", host="localhost"
    )
    return conn

# Pydantic model for user
class User(BaseModel):
    name: str
    email: str

@app.post("/users/")
async def create_user(user: User):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute(
        sql.SQL("INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id;"),
        (user.name, user.email)
    )
    user_id = cur.fetchone()[0]
    conn.commit()
    cur.close()
    conn.close()
    return {"id": user_id, "name": user.name, "email": user.email}

@app.get("/users/{user_id}")
async def read_user(user_id: int):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users WHERE id = %s;", (user_id,))
    user = cur.fetchone()
    cur.close()
    conn.close()
    if user:
        return {"id": user[0], "name": user[1], "email": user[2]}
    return {"error": "User not found"}

Step 3: Running the Application

To run your FastAPI application, use Uvicorn. In your terminal, execute:

uvicorn main:app --reload

This command starts your FastAPI application on http://127.0.0.1:8000. The --reload flag enables auto-reloading when you make changes to your code.

Step 4: Testing Your API

You can test your API using tools like Postman or curl. Here’s how to create a new user:

curl -X POST "http://127.0.0.1:8000/users/" -H "Content-Type: application/json" -d '{"name": "John Doe", "email": "john@example.com"}'

To retrieve the user, run:

curl "http://127.0.0.1:8000/users/1"

Step 5: Error Handling and Optimization

It's essential to handle potential errors gracefully. You can use FastAPI's exception handlers to manage errors effectively. For example, you could return a 404 status code if a user is not found:

from fastapi import HTTPException

@app.get("/users/{user_id}")
async def read_user(user_id: int):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users WHERE id = %s;", (user_id,))
    user = cur.fetchone()
    cur.close()
    conn.close()
    if user:
        return {"id": user[0], "name": user[1], "email": user[2]}
    raise HTTPException(status_code=404, detail="User not found")

Conclusion

In this article, we walked through creating a RESTful API using FastAPI and PostgreSQL. FastAPI's simplicity and PostgreSQL's robustness provide a powerful combination for any developer looking to build high-performance applications. With the steps outlined, you can expand your API by adding more functionalities, such as user authentication, additional endpoints, or integrating third-party services. Happy coding!