Task API

A lightweight, RESTful CRUD API built with Python and FastAPI for managing a personal to-do list, now powered by SQLite for persistent file-based storage.

Features

• Create Task: Add new tasks with title validation.
• Read Tasks: Retrieve all tasks or fetch a single task by ID.
• Update Tasks: Perform partial updates on titles and/or task completion status.
• Delete Tasks: Remove tasks from the list.
• Swagger UI Documentation: Automatically generated interactive API documentation and test harness.
• Input Validation: Robust input parsing and error reporting using Pydantic.
• SQLite Storage: Persistent, file-based database that survives server restarts.
• Automatic Initialization: Database and table are created automatically if missing, with example data seeded on first run.

Architecture

This project follows a clean, decoupled architecture using the Repository pattern. Only the storage implementation was replaced — from an in-memory array (Assignment 1) to a SQLite database file — while all other layers (FastAPI routes, validations, models, exception handlers, and external API behaviours) remain completely unchanged.

Client  →  API (FastAPI)  →  SQLite Database (tasks.db)

The client doesn't know the difference. The URLs, request bodies, response models, and status codes are identical.

Why SQLite?

SQLite was chosen for this assignment because:

• Zero configuration: No separate database server to install, configure, or manage.
• Single file: The entire database lives in one file (tasks.db), making it simple to inspect and portable.
• Built-in Python support: The sqlite3 module is part of the Python standard library — no external dependencies required.
• Lightweight: Ideal for learning SQL fundamentals and small-to-medium applications.
• Persistence: Unlike in-memory storage, data survives server restarts.

Where is the database stored?

The database file is stored at:

tasks.db

in the project root directory. This file is automatically created on first run and is excluded from version control via .gitignore. If deleted, it will be recreated automatically with the example tasks on the next server start.

Requirements

• Python 3.10+
• pip (Python package manager)

Installation & Setup

1. Clone the repository:

   git clone <repository-url>
   cd task-api
   git checkout week3-sqlite

2. Setup virtual environment:

   python -m venv .venv
   .venv\Scripts\activate        # Windows
   # source .venv/bin/activate   # macOS/Linux

3. Install dependencies:

   pip install -r requirements.txt

4. Run the server:

   uvicorn main:app --reload

   The API will be available at http://localhost:8000.

API Documentation & Interactive Swagger UI

Once the server is running, the interactive API documentation and test suite is available at:

http://localhost:8000/docs

API Endpoints

Method	Endpoint	Description
GET	/	API Metadata
GET	/health	Health Check
GET	/tasks	Get All Tasks
GET	/tasks/{task_id}	Get Task
POST	/tasks	Create Task
PUT	/tasks/{task_id}	Update Task
DELETE	/tasks/{task_id}	Delete Task

Database Schema

CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT 0
);

Database Viewer Screenshot

Below is a screenshot of the SQLite database viewed in DB Browser for SQLite:

Example SQL Queries

List every task:

SELECT * FROM tasks;

Show only completed tasks:

SELECT * FROM tasks WHERE done = 1;

Count all tasks:

SELECT COUNT(*) FROM tasks;

Mark every task as completed:

UPDATE tasks SET done = 1;

Delete all completed tasks:

DELETE FROM tasks WHERE done = 1;

Persistence Verification

To verify that task data persists across server restarts:

1. Start the server:

   uvicorn main:app --reload

2. Create a task:

   curl -X POST http://localhost:8000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Verify Persistence\"}"

3. Verify it is saved:

   curl http://localhost:8000/tasks

4. Stop the server (Ctrl+C).

5. Start the server again:

   uvicorn main:app --reload

6. Confirm task still exists:

   curl http://localhost:8000/tasks

   The response will contain the task Verify Persistence.

---

Git Commits

Our repository contains the following incremental stages on the week3-sqlite branch:

• Stage 0: create SQLite database — Created SQLite database file, tasks table, and seeded 3 example tasks
• Stage 1: database read endpoints — Connected GET /tasks and GET /tasks/{id} to SQLite queries
• Stage 2: insert into database — Connected POST /tasks to SQLite INSERT query
• Stage 3: update and delete with SQL — Connected PUT and DELETE endpoints to SQLite UPDATE and DELETE queries
• Stage 4: explored SQLite — Executed manual SQL queries (SELECT, COUNT, UPDATE, DELETE) against the database
• Stage 5: database documentation — Updated README with SQLite documentation and database viewer screenshot

---

Author

Abhi T A

Backend AI Engineer Intern @ FlyRank AI
Software Developer @ INVOLYNK

GitHub: https://github.com/Abhi-T-A
Email: abhi.t.a1806@gmail.com

---

© 2026 Abhi T A. All Rights Reserved.