Creating a Simple Task CRUD App with FastAPI, PostgreSQL, SQLAlchemy, and Docker

In the world of web development, one common task is to build CRUD (Create, Read, Update, Delete) applications. Let’s walk through the process of creating a simple Task CRUD app using FastAPI, PostgreSQL, SQLAlchemy, and Docker. This tutorial assumes you have a basic understanding of Docker and Python.

Step 1: Set Up Your Development Environment

Before diving in, make sure you have Docker and Docker Compose installed on your system. Create a new directory for your project:

mkdir fastapi-task-app cd fastapi-task-app

Set up a virtual environment:

python -m venv venv source venv/bin/activate

Install the required Python packages:

pip install fastapi uvicorn SQLAlchemy alembic databases[postgresql]

Step 2: Initialize a Git Repository

Initialize a Git repository in your project directory:

git init

Step 3: Set Up FastAPI App and Database

Create the following directory structure for your project:

fastapi-task-app/  
    app/  
        __init__.py  
        models.py  
        main.py  
    alembic/  
    config.py

Inside config.py, configure your FastAPI application and database:

# config.py   
  
DATABASE_URL = "postgresql://postgres:password@db:5432/tasks"

Step 4: Create Database Models

Define your database models inside models.py. For this example, let's create a Task model:

# app/models.py  
  
from sqlalchemy import Column, Integer, String  
from app.config import metadata  
  
tasks = Table(  
    "tasks",  
    metadata,  
    Column("id", Integer, primary_key=True),  
    Column("title", String(100), nullable=False),  
    Column("description", String(255)),  
)

Step 5: Create FastAPI Endpoints

Create endpoints for your FastAPI application inside main.py. Implement CRUD operations for tasks:

# app/main.py  
  
from fastapi import FastAPI, HTTPException, Depends, Query  
from databases import Database  
from sqlalchemy import select  
from app.models import tasks  
  
app = FastAPI()  
  
@app.post("/tasks/")  
async def create_task(title: str, description: str = ""):  
    query = tasks.insert().values(title=title, description=description)  
    await database.execute(query)  
    return {"message": "Task created successfully"}  
  
  
@app.put("/tasks/{task_id}/")  
async def update_task( task_id: int,  
    title: str,  
    description: str = ""):  
    query = tasks.update().where(tasks.c.id == task_id).values(title=title, description=description)  
    await database.execute(query)  
    return {"message": "Task updated successfully"}  
  
@app.get("/tasks/{task_id}/")  
async def get_task(task_id: int):  
    query = select([tasks]).where(tasks.c.id == task_id)  
    result = await database.fetch_one(query)  
    if result is None:  
        raise HTTPException(status_code=404, detail="Task not found")  
    return result  
  
  
@app.get("/tasks/")  
async def get_tasks():  
    query = select([tasks])  
    results = await database.fetch_all(query)  
    return results  
  
  
@app.delete("/tasks/{task_id}/")  
async def delete_task(task_id: int):  
    query = tasks.delete().where(tasks.c.id == task_id)  
    await database.execute(query)  
    return {"message": "Task deleted successfully"}  

Step 6: Initialize Database Migrations

Initialize Alembic for database migrations:

alembic init alembic

Edit the generated alembic/env.py file to load your FastAPI application and database configuration:

from your_app import create_app  
from app.config import DATABASE_URL  
  
app = create_app()  
database = Database(DATABASE_URL)  
  
def run_migrations_offline():  
    context.configure(  
        url=DATABASE_URL,  
        target_metadata=target_metadata,  
        literal_binds=True,  
        dialect_opts={"paramstyle": "named"},  
        include_schemas=True,  
    )  

# Implement other Alembic configurations...

Step 7: Create Database Migrations

Generate an initial migration:

alembic revision --autogenerate -m "Initial migration"

Apply the migration to create the database tables:

alembic upgrade head

Step 8: Create a Docker Compose File

Create a docker-compose.yml file to define your application and database services:

# docker-compose.yml  
  
version: '3'  
  
services:  
  web:  
    build: .  
    command: uvicorn app.main:app --host 0.0.0.0 --port 5000 --reload  
    volumes:  
      - ./app:/app  
    ports:  
      - "5000:5000"  
    depends_on:  
      - db  
    environment:  
      DATABASE_URL: postgresql://postgres:password@db:5432/tasks  
  
  db:  
    image: postgres:latest  
    environment:  
      POSTGRES_USER: postgres  
      POSTGRES_PASSWORD: password  
      POSTGRES_DB: tasks  
    volumes:  
      - postgres_data:/var/lib/postgresql/data  
  
volumes:  
  postgres_data:

Step 9: Create a Dockerfile

Create a Dockerfile to build your FastAPI application container:

# Dockerfile  
  
FROM python:3.8-slim  
  
WORKDIR /app  
  
COPY requirements.txt requirements.txt  
RUN pip install -r requirements.txt  
  
COPY . .  
  
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "5000", "--reload"]

Step 10: Create a Requirements File

Create a requirements.txt file to list your project's dependencies:

fastapi==0.68.1   
uvicorn==0.15.0   
SQLAlchemy==1.4.31   
alembic==1.7.3   
databases[postgresql]==0.6.4

Step 11: Build and Run the Docker Containers

Build and run the Docker containers for your application:

docker-compose up --build

Your FastAPI Task CRUD app is now running in a Docker container, and you can access it at http://localhost:5000. Use tools like Postman or cURL to interact with the CRUD API.

This is a basic setup, and you can extend it further by adding authentication, more features, and error handling based on your project requirements.