Quick CRUD Implementation Guide with FastAPI × SQLite × SQLAlchemy

📋 Article Highlights (Summary)

• Who’s this for?
  • Developers who can run a basic FastAPI “Hello, World!” but haven’t yet connected to a database
  • Engineers or learners who want to quickly try CRUD with SQLite
• What you’ll learn
  1. How to set up SQLite + SQLAlchemy environment
  2. Defining SQLAlchemy ORM models
  3. Integrating Pydantic schemas
  4. Implementing CRUD endpoints in FastAPI (Create/Read/Update/Delete)
  5. Troubleshooting tips and next steps
• What you’ll gain
  • A lightweight local DB with SQLite for development
  • Practical understanding of SQLAlchemy ORM with FastAPI
  • A solid foundation in data persistence for future projects

🎯 Target Audience

• Student A (22)
  Has tried FastAPI in class but is new to databases
• Engineer B (28)
  Wants to spin up a SQLite-backed API for a personal project
• Hobbyist C (35)
  Building a simple task manager and needs persistent storage

♿ Accessibility Features

• Structured with clear headings and lists for screen readers
• Readable using balanced Japanese/English notation (for code) and brief annotations
• Commented code in fixed-width blocks, with explanations
• Key-point summaries at the end of each section
• Friendly tone to encourage learning

1. Introduction: Why SQLite + SQLAlchemy?

SQLite is zero-configuration and lightweight. SQLAlchemy ORM lets you interact with the database using Python classes and type hints. Combined with FastAPI, you get full CRUD functionality with minimal setup.

Key Takeaways

• SQLite: Zero-config file-based DB
• SQLAlchemy ORM: Intuitive class-based queries
• FastAPI Integration: Auto-commit and session management boost productivity

2. Environment Setup

2.1 Project Folder & Virtual Environment

mkdir fastapi-sqlite-crud
cd fastapi-sqlite-crud
python3 -m venv .venv && source .venv/bin/activate

Note: Use a virtualenv to isolate dependencies.

2.2 Install Dependencies

pip install fastapi uvicorn sqlalchemy databases aiosqlite

• fastapi: Web framework
• uvicorn: ASGI server
• sqlalchemy: ORM
• databases: Async DB wrapper
• aiosqlite: Async SQLite driver

Key Takeaway: databases + aiosqlite keeps FastAPI’s async features seamless.

3. Define Models & Connect to the Database

3.1 Directory Structure

fastapi-sqlite-crud/
├── main.py
├── database.py
├── models.py
├── schemas.py
└── requirements.txt

3.2 database.py: Connection Setup

# database.py
from databases import Database
from sqlalchemy import create_engine, MetaData

DATABASE_URL = "sqlite:///./test.db"

database = Database(DATABASE_URL)
engine = create_engine(
    DATABASE_URL, connect_args={"check_same_thread": False}
)
metadata = MetaData()

Key Takeaways

• sqlite:///./test.db uses a local file
• check_same_thread=False allows multi-thread access

3.3 models.py: ORM Table Definition

# models.py
from sqlalchemy import Table, Column, Integer, String
from database import metadata, engine

items = Table(
    "items",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("title", String, nullable=False),
    Column("description", String, nullable=True),
)

metadata.create_all(engine)

Key Takeaway: metadata.create_all() auto-creates tables on first run.

3.4 schemas.py: Pydantic Models

# schemas.py
from pydantic import BaseModel

class ItemBase(BaseModel):
    title: str
    description: str | None = None

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int

    class Config:
        orm_mode = True

Key Takeaway: orm_mode = True lets Pydantic read ORM objects directly.

4. CRUD Endpoints in main.py

4.1 Base App & Events

# main.py
from fastapi import FastAPI, HTTPException
from typing import List
from database import database
from models import items
from schemas import Item, ItemCreate

app = FastAPI(title="SQLite + SQLAlchemy CRUD API")

@app.on_event("startup")
async def startup():
    await database.connect()

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()

Key Takeaway: Manage DB connect/disconnect on startup/shutdown.

4.2 Create

@app.post("/items/", response_model=Item)
async def create_item(item: ItemCreate):
    query = items.insert().values(
        title=item.title, description=item.description
    )
    item_id = await database.execute(query)
    return {**item.dict(), "id": item_id}

Key Takeaway: response_model auto-validates the output.

4.3 Read All

@app.get("/items/", response_model=List[Item])
async def read_items():
    query = items.select()
    return await database.fetch_all(query)

4.4 Read One

@app.get("/items/{item_id}", response_model=Item)
async def read_item(item_id: int):
    query = items.select().where(items.c.id == item_id)
    result = await database.fetch_one(query)
    if result is None:
        raise HTTPException(404, "Item not found")
    return result

4.5 Update

@app.put("/items/{item_id}", response_model=Item)
async def update_item(item_id: int, item: ItemCreate):
    query = (
        items.update()
        .where(items.c.id == item_id)
        .values(title=item.title, description=item.description)
    )
    await database.execute(query)
    return {**item.dict(), "id": item_id}

4.6 Delete

@app.delete("/items/{item_id}")
async def delete_item(item_id: int):
    query = items.delete().where(items.c.id == item_id)
    await database.execute(query)
    return {"ok": True}

Key Takeaway: Simple JSON status for delete confirmation.

5. Testing & Verification

5.1 Run the Server

uvicorn main:app --reload

• Browse to http://127.0.0.1:8000/

5.2 Swagger UI

• Explore endpoints at http://127.0.0.1:8000/docs

Key Takeaway: Instant browser-based testing and auto-generated docs.

6. Common Issues & Fixes

7. Summary & Next Steps

We’ve built a full CRUD API with FastAPI, SQLite, and SQLAlchemy:

• Setup: Virtualenv & packages
• Models: SQLAlchemy + Pydantic schemas
• Endpoints: Async Create/Read/Update/Delete
• Testing: Uvicorn & Swagger UI

Next Steps ✨

1. Add Authentication: Use OAuth2/JWT
2. Handle Relations: One-to-many / many-to-many
3. Automate Tests: Write pytest unit tests
4. Scale to Production DB: Migrate to PostgreSQL/MySQL

Use this SQLite CRUD tutorial as your springboard to more advanced database integrations!