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.