Task Tracker API with Python and PostgreSQL

Build a REST API from scratch with Flask and raw SQL against PostgreSQL.

Introduction

30 Second Summary

Every app you use, from to-do lists to social media feeds, is powered by the same invisible engine. An API takes in requests, talks to a database, and sends back the right data.

In this project, you will build a Task Tracker API from scratch using Python, Flask, and PostgreSQL. You will write every line of code by hand so you can explain each piece in an interview.

What You'll Build

Picture yourself opening a terminal, firing off a request, and watching a new task land in a real database that you built and wired up from scratch.

By the end of this project, you'll have:

  • A working REST API that responds to HTTP requests to create, list, update, and delete tasks in a real PostgreSQL database.
  • Raw SQL queries you wrote yourself, from INSERT and SELECT to aggregate functions like COUNT and GROUP BY, running against a real database with no ORM hiding the mechanics.
  • A clear picture of the request-response cycle, where you trace a request from your terminal through a Python handler, into a SQL query, and back as a JSON response.
  • Secret Mission: an optional challenge to push your skills further.

Are there any prerequisites?

No prior experience is needed. This guide walks you through installing every tool from scratch, including Python, PostgreSQL, and VS Code.

Before We Start

Before we dive in, let's commit to what you're building and why. Turn off Copilot, Codex, and any AI code completion tool before continuing, because you'll write every line of this project yourself.

Set Up Python, PostgreSQL, and Flask

Every application needs a foundation. Before you write a single line of API code, you need three things: a Python runtime to execute your code, a PostgreSQL database to store your data, and Flask to handle HTTP requests.

In this step, you will install Python and PostgreSQL on your machine, create a project folder with a virtual environment, and install the two Python packages your project depends on.

In this step, get ready to:
  • Install Python 3.14.7 and PostgreSQL 18 on your machine.
  • Create a project folder with a Python virtual environment.
  • Install Flask and psycopg inside the virtual environment.
Install Python and PostgreSQL

Python is the programming language you will use to write your entire API. You need version 3.14.7 installed on your machine.

  • Open the Python downloads page in your browser.
  • Download the Python 3.14.7 Windows installer (64-bit).
  • Run the installer.
  • On the very first screen, check the Add Python to PATH checkbox at the bottom before clicking Install Now.

Do not skip the PATH checkbox

Without this checkbox, your terminal will not recognize the python command. If you already clicked through the installer without it, re-run the installer, select Modify, and enable the PATH option.

Once the installer finishes, you need a fresh terminal so it picks up the new PATH entries.

  • Press the Windows key to open your search bar.
  • Type Terminal and press Enter to open it.
  • Check your Python installation by running this command:
python --version

✔️ I see Python 3.14 or higher

Python is installed and ready. Move on to the PostgreSQL installation below.

ⓧ I see an older version

Your machine has an older Python installed. You need version 3.14.7 for this project.

  • Open the Python downloads page and download the 3.14.7 Windows installer (64-bit).
  • Run the installer. The new version will replace your existing Python installation.
  • Make sure Add Python to PATH is checked.
  • Close your terminal, open a new one, and run python --version again to confirm it now shows 3.14.7.

ⓧ Command not found

Python is not installed yet. Follow these steps to install it.

  • Open the Python downloads page and download the 3.14.7 Windows installer (64-bit).
  • Run the installer and check Add Python to PATH on the first screen.
  • Click Install Now.
  • Close your terminal, open a new one, and run python --version again to confirm it shows Python 3.14.7.

Next up is PostgreSQL, the relational database that will store your tasks. You need version 18 installed.

  • Open the PostgreSQL Windows download page in your browser.
  • Click the Download the installer link to go to the Interactive installer by EDB.
  • Download and run the PostgreSQL 18 installer for Windows x86-64.

What will the installer ask?

The installer walks through several screens. Keep every default except for the superuser password. When prompted, set a password you will remember. You will need this password later to connect your Python code to the database.

Keep the default port 5432. When the installer finishes, you can uncheck the StackBuilder launch option and click Finish.

The EDB installer does not add PostgreSQL to your system PATH automatically. You need to add it so your terminal can find the psql command.

  • Add PostgreSQL to your PATH by running this command in your terminal:
[Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\Program Files\PostgreSQL\18\bin", "User")

What does this command do?

This adds the PostgreSQL bin folder to your user PATH permanently. The bin folder is where PostgreSQL keeps its command-line tools, including psql.

  • Close your terminal completely and open a new one (press the Windows key, type Terminal, press Enter) so the PATH change takes effect.
  • Check your PostgreSQL installation by running this command:
psql --version

✔️ I see version 18 or higher

PostgreSQL is installed and on your PATH. You are ready to create the project folder.

ⓧ I see an older version

Your PATH is pointing to an older PostgreSQL installation. You need to update it so the terminal finds the version 18 tools you just installed.

  • Press the Windows key, type Environment Variables, and press Enter.
  • Click Environment Variables at the bottom of the System Properties window.
  • Under User variables, select Path and click Edit.
  • If you see an entry pointing to an older PostgreSQL version (e.g. C:\Program Files\PostgreSQL\16\bin), select it and click Delete.
  • Click New and add C:\Program Files\PostgreSQL\18\bin.
  • Click OK on all open dialogs.
  • Close your terminal, open a new one, and run psql --version again to confirm it shows version 18.

ⓧ Command not found

The PATH update may not have taken effect. Try these steps.

  • Make sure you closed your terminal completely and opened a brand new one after running the PATH command.
  • Confirm the folder exists by running:
Test-Path "C:\Program Files\PostgreSQL\18\bin\psql.exe"
  • If this prints True, re-run the PATH command from above, close and reopen your terminal, then try psql --version again.
  • If this prints False, PostgreSQL was installed to a different location. Check C:\Program Files\PostgreSQL\ for the correct version folder and use that path instead.

If PostgreSQL is not installed at all, go back and download the installer from the PostgreSQL Windows download page.

Create the project folder and virtual environment

With your tools installed, you need a dedicated folder for your project. You also need a virtual environment so the Python packages you install stay isolated from the rest of your system.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir task-tracker-api
cd task-tracker-api

Your terminal prompt should now show task-tracker-api in the path, confirming you are inside the new folder.

  • Create a Python virtual environment by running this command:
python -m venv venv

What is a virtual environment?

A virtual environment is an isolated Python installation inside your project folder. Packages you install here stay separate from your system Python, so different projects can use different package versions without conflicts.

  • Activate the virtual environment by running this command:
venv\Scripts\activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

Seeing a "running scripts is disabled" error?

Windows PowerShell blocks script execution by default. You will see a message that says the activate script cannot be loaded because running scripts is disabled on this system.

  • Fix this by running the following command, then try activating again:
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

Help me activate my Python virtual environment on Windows.

  • Open the project folder in VS Code by running this command:
code .

What does this command do?

The code . command tells VS Code to open the current folder as a project. The dot (.) means "this directory."

code command not recognized?

If your terminal does not recognize the code command, open VS Code manually. Press the Windows key, type VS Code, and press Enter. Then click File, click Open Folder, and select the task-tracker-api folder on your Desktop.

Help me open my project folder in VS Code on Windows.

Install Flask and psycopg

Flask is the web framework that will handle HTTP requests to your API. psycopg is the Python library that connects to PostgreSQL and lets you run SQL queries from your code.

  • Install both packages by running this command in the terminal where (venv) is active:
pip install Flask==3.1.3 "psycopg[binary]==3.3.4"

What does this command do?

Flask==3.1.3 installs Flask pinned to the exact version this project uses.

psycopg[binary]==3.3.4 installs psycopg with a pre-compiled C extension bundled in. The [binary] extra means you do not need PostgreSQL client libraries installed separately on your system for psycopg to work.

You should see pip download and install both packages along with their dependencies. Look for a line near the end that says Successfully installed followed by a list of package names.

  • Verify Flask is installed correctly by running this command:
python -c "import flask; print(flask.__version__)"

You should see 3.1.3 printed in your terminal.

  • Verify psycopg is installed correctly by running this command:
python -c "import psycopg; print(psycopg.__version__)"

You should see 3.3.4 printed in your terminal.

Seeing a ModuleNotFoundError?

  • Check that your terminal prompt starts with (venv). If it does not, activate the virtual environment again with venv\Scripts\activate and re-run the pip install command.
  • If you opened a new terminal or VS Code terminal, you need to activate the virtual environment in each new terminal session.

Help me install Flask and psycopg in my virtual environment.

Your development environment is ready. Python, PostgreSQL, Flask, and psycopg are all installed and verified. Next up, you will build your first API endpoints and watch them handle real HTTP requests.

Build an In-Memory Task API

Your tools are installed and your project folder is ready. Now it's time to build the actual API.

Before connecting to a database, you'll build everything with data stored in a simple Python list. This isolates how HTTP requests and responses work, without mixing in SQL concepts. There's a catch, though. That data lives only in your computer's memory.

In this step, get ready to:
  • Create a Flask app with GET /tasks and POST /tasks endpoints.
  • Test the API using curl.exe in a second terminal.
  • Discover what happens to your data when the server restarts.
Create the Flask app with a task list

Your API needs a web server to listen for requests and a place to store tasks. You'll start with a single file that handles both.

  • Create a new file called app.py inside your task-tracker-api folder and add the following code:
from flask import Flask, request, jsonify

# Create the Flask application
app = Flask(__name__)

# Store tasks in memory (a simple Python list)
tasks = []
next_id = 1

# Return all tasks as JSON
@app.route("/tasks", methods=["GET"])
def get_tasks():
    return jsonify(tasks)

# Run the app with debug mode enabled
if __name__ == "__main__":
    app.run(debug=True)

What does this code do?

  • Flask(__name__) creates a web application that can listen for incoming HTTP requests.
  • tasks is a plain Python list that holds your task data. next_id tracks the next ID number to assign.
  • @app.route("/tasks", methods=["GET"]) registers a URL endpoint. When someone sends a GET request to /tasks, Flask calls get_tasks() and returns the task list as JSON.
  • app.run(debug=True) starts the development server. Debug mode automatically reloads the server whenever you save changes to your code.
  • Save app.py.
  • Start the server by running this command in your terminal:
python app.py

You should see output that includes Running on http://127.0.0.1:5000. Your API server is now listening for requests.

Why does the terminal look frozen?

The server has taken over this terminal. It's actively listening for requests, so you can't type new commands here. You'll open a second terminal to send requests to it.

Let's confirm the GET endpoint works. You need a second terminal to send requests while the server runs.

  • Open a new terminal window. In VS Code, click **Terminal** in the top menu bar, then click **New Terminal**.
  • Send a GET request to your API by running this command:
curl.exe http://127.0.0.1:5000/tasks

You should see [] printed in the terminal. That's an empty JSON array, which makes sense because you haven't created any tasks yet.

Why curl.exe and not curl?

On Windows PowerShell, curl is an alias for a different command called Invoke-WebRequest. Using curl.exe calls the real curl program bundled with Windows 10 and later.

Seeing 'connection refused' or no output?

  • Make sure your first terminal still shows the server running. If you accidentally closed it, run python app.py again from your task-tracker-api folder.
  • Check that you typed curl.exe (with .exe), not curl.
  • Confirm the URL uses port 5000 and the path is /tasks.

Still stuck? Help me debug why curl.exe cannot connect to my Flask server on port 5000.

Your GET endpoint is working. Now you need a way to add tasks to the list.

  • Switch back to app.py in VS Code.
  • Find the line that says if __name__ == "__main__": and add the following code above it:
@app.route("/tasks", methods=["POST"])
def create_task():
    global next_id
    # Read JSON data from the request body
    data = request.get_json()

    # Validate that a title was provided
    if not data or "title" not in data:
        return jsonify({"error": "title is required"}), 400

    # Build the task dictionary with an auto-incrementing ID
    task = {
        "id": next_id,
        "title": data["title"],
        "description": data.get("description", ""),
        "status": "todo",
    }
    tasks.append(task)
    next_id += 1

    # Return the new task with a 201 Created status
    return jsonify(task), 201

What does this code do?

  • request.get_json() reads the JSON body from the incoming request. This is how the client sends data to your API.
  • The if not data or "title" not in data check is input validation. If someone sends a request without a title, the API responds with a 400 Bad Request error.
  • global next_id lets the function modify the counter defined outside it. Each new task gets a unique ID, then the counter increments.
  • Returning with status 201 signals "Created." Different HTTP status codes tell the client exactly what happened with their request.
  • Save app.py. Flask's debug mode detects the change and automatically restarts the server in your first terminal.

You should see a line in your server terminal that says Restarting with stat. This confirms Flask picked up your changes.

Server crashed with a SyntaxError?

  • Make sure the POST route is placed above the if __name__ == "__main__": block, not inside it.
  • Check that the indentation inside create_task() uses 4 spaces per level. Mixing tabs and spaces causes Python errors.

Help me fix a SyntaxError in my Flask app.py file.

✔️ Awesome, I've got everything!

Great. Double check you've saved app.py and that the server reloaded without errors.

ⓧ I'd like to double check the full code

Scroll up and confirm your app.py contains these sections in order:

  • The imports (Flask, request, jsonify), the app instance, the tasks list, and the next_id counter.
  • The GET /tasks route (get_tasks function).
  • The POST /tasks route (create_task function).
  • The if __name__ == "__main__": block at the very bottom.
Run and test the API

Your server is running and both routes are in place. Time to send some real requests and see the full request-response cycle in action.

  • In your second terminal, create your first task by running this command:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Buy groceries\"}"

You should see a JSON response containing your new task with an id of 1 and a status of todo.

What do these curl.exe flags mean?

  • -X POST tells curl to send a POST request instead of the default GET.
  • -H "Content-Type: application/json" sets a header telling the server that the request body is JSON.
  • -d sends the data that follows as the request body. The escaped quotes (\") are needed because Windows command line uses double quotes for the outer string.
  • Create a second task by running this command:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Walk the dog\"}"

This task should come back with an id of 2.

  • Now fetch the full task list to confirm both tasks are stored by running:
curl.exe http://127.0.0.1:5000/tasks

You should see a JSON array containing both tasks. Your API can receive data, store it, and return it on demand.

Seeing an empty list even after POSTing?

  • Check the server terminal for error messages. A crash between your POST and GET means the data was lost when the server restarted.
  • Confirm your POST command includes -H "Content-Type: application/json". Without it, Flask's get_json() returns None and the task is rejected.

Help me figure out why my POST request is not saving tasks.

Watch the data disappear

You've created tasks and confirmed they show up when you list them. Everything seems to be working. But there's a problem hiding in plain sight.

  • Switch to the terminal where the Flask server is running.
  • Stop the server by pressing Ctrl+C.
  • Restart it by running:
python app.py

Before you check the task list, take a guess. Do you think your tasks are still there?

  • Switch to your second terminal and fetch the task list by running:
curl.exe http://127.0.0.1:5000/tasks

You see []. Every task you created is gone.

Why did the data disappear?

Your tasks were stored in a Python list, which lives in your computer's RAM. When you stopped the server, the Python process ended and all of its memory was released. Restarting the server created a brand-new empty tasks list.

This is exactly the problem that databases solve. A database writes data to disk so it survives process restarts, crashes, and even power outages.

You now understand the full HTTP request-response cycle. You've seen requests arrive, data get created, and responses go back. But you've also felt the limit of in-memory storage. In the next step, you'll connect to PostgreSQL and make your data permanent.

Connect to PostgreSQL

You just watched your tasks vanish into thin air. That happened because a Python list lives in your computer's memory and disappears the moment the process stops.

A database solves this. In this step, you will create a PostgreSQL database, design a table to hold your tasks, and rewire your API endpoints to read from and write to real SQL instead of a Python list.

In this step, get ready to:
  • Create a PostgreSQL database and a tasks table with a structured schema.
  • Build a Python module that manages database connections.
  • Rewire your GET and POST endpoints to use SQL queries so data survives a server restart.
Create the database and table

Before your Python code can store tasks in PostgreSQL, the database and table need to exist. You will use psql, the PostgreSQL command-line tool, to create them.

  • If your Flask server is still running, press Ctrl+C in that terminal to stop it.
  • Connect to PostgreSQL by running this command:
psql -U postgres
  • When you see the Password: prompt, enter the superuser password you set during PostgreSQL installation.

Seeing 'psql' is not recognized?

The PostgreSQL bin folder may not be in your system PATH. Search for Environment Variables in the Windows Start menu, open Edit the system environment variables, click Environment Variables, and edit the Path variable under System variables.

Add the path to your PostgreSQL bin folder (typically C:\Program Files\PostgreSQL\18\bin). Open a new terminal and try again.

help me fix psql not found on Windows

You should see a postgres=# prompt. You are now inside the PostgreSQL interactive shell.

  • Create your database and table by running these SQL commands one at a time:
CREATE DATABASE tasktracker;
  • Connect to the new database:
\c tasktracker

You should see a message confirming you are now connected to the tasktracker database. The prompt changes to tasktracker=#.

  • Create the tasks table by running this SQL statement:
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT DEFAULT '',
    status TEXT NOT NULL DEFAULT 'todo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this schema do?

  • id SERIAL PRIMARY KEY creates an auto-incrementing integer ID for each task. PostgreSQL handles the counting for you.
  • title TEXT NOT NULL stores the task name. NOT NULL means every task must have a title.
  • status TEXT NOT NULL DEFAULT 'todo' gives every new task a default status of todo if none is provided.
  • created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP records exactly when each task was created. The database fills this in automatically.
  • Verify the table was created by running:
\dt

You should see a List of relations table with your tasks table listed under the public schema.

  • Exit psql by running:
\q

Don't see your tasks table?

Make sure you connected to the tasktracker database with \c tasktracker before running CREATE TABLE. If you created the table in the wrong database, connect to tasktracker and run the CREATE TABLE statement again.

help me create a table in PostgreSQL

Create the database module

Your Flask app needs a way to connect to PostgreSQL. You will create a separate db.py module that holds the connection settings and two helper functions.

  • In VS Code, create a new file called db.py inside your task-tracker-api folder.
  • Add the connection config and the get_connection() function by pasting this code:
import psycopg

# Connection settings for the local PostgreSQL database
DB_CONFIG = {
    "dbname": "tasktracker",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": 5432,
}

def get_connection():
    # Open and return a new database connection
    return psycopg.connect(**DB_CONFIG)

What does this code do?

  • DB_CONFIG is a dictionary holding all the details psycopg needs to reach your local PostgreSQL server.
  • get_connection() unpacks that dictionary into psycopg.connect() and returns a live connection object. Every route will call this to talk to the database.
  • Replace your-password-here with the superuser password you set when you installed PostgreSQL.

Keep your password out of version control

Hardcoding a password in source code is fine for local learning. In a real project, you would store it in an environment variable or a .env file and add that file to .gitignore.

  • Below get_connection(), add the init_db() function:
def init_db():
    # Create the tasks table if it doesn't already exist
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id SERIAL PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT DEFAULT '',
                status TEXT NOT NULL DEFAULT 'todo',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
    conn.commit()
    conn.close()

Why CREATE TABLE IF NOT EXISTS?

You already created the table manually in psql. This function uses IF NOT EXISTS so the app can safely call it every time it starts. If the table is already there, the statement does nothing.

conn.commit() saves the change to disk. Without it, PostgreSQL would discard the work when the connection closes.

  • Save db.py.
  • Test that Python can connect to your database by running this command in your terminal:
python -c "from db import init_db; init_db(); print('Database ready')"

You should see Database ready printed with no errors.

Seeing a connection error?

Double-check that the password in DB_CONFIG matches your PostgreSQL superuser password exactly. Also confirm that PostgreSQL is running. You can check in the Windows Services app by searching for postgresql and verifying its status is Running.

help me connect Python to PostgreSQL

✔️ Awesome, I've got everything!

Great. Make sure you have saved db.py.

ⓧ I'd like to double check the full code

import psycopg

DB_CONFIG = {
    "dbname": "tasktracker",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": 5432,
}


def get_connection():
    return psycopg.connect(**DB_CONFIG)


def init_db():
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id SERIAL PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT DEFAULT '',
                status TEXT NOT NULL DEFAULT 'todo',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
    conn.commit()
    conn.close()
Rewire GET and POST to use SQL

Your database module is ready. Now you need to update app.py so both endpoints talk to PostgreSQL instead of the in-memory list.

  • Open app.py in VS Code.
  • Find this line at the top of the file:
from flask import Flask, request, jsonify
  • Add this import line directly below it:
from db import get_connection, init_db
  • Now replace the entire get_tasks() function (the @app.route decorator and all the lines inside it) with this code:
@app.route("/tasks", methods=["GET"])
def get_tasks():
    # Open a database connection and fetch all tasks
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, title, description, status, created_at "
            "FROM tasks ORDER BY created_at DESC"
        )
        rows = cur.fetchall()
    conn.close()

    # Convert each database row into a dictionary
    tasks = []
    for row in rows:
        tasks.append({
            "id": row[0],
            "title": row[1],
            "description": row[2],
            "status": row[3],
            "created_at": row[4].isoformat(),
        })

    return jsonify(tasks)

What does this code do?

  • get_connection() opens a live connection to your tasktracker database.
  • cur.execute() runs a SELECT query that pulls every column from the tasks table, sorted newest first.
  • fetchall() returns the results as a list of tuples. Each tuple is one row from the database.
  • The for loop converts each tuple into a dictionary that Flask can serialize to JSON.
  • Save app.py and start the server by running:
python app.py
  • Open a second terminal, activate your virtual environment, and test the GET endpoint:
curl.exe http://127.0.0.1:5000/tasks

You should see an empty JSON array [] because the database table has no rows yet.

Seeing an import error?

Make sure db.py is saved in the same folder as app.py (your task-tracker-api folder). Python can only import modules that are in the same directory or on the Python path.

help me fix an import error for db.py

  • Stop the server with Ctrl+C.
  • Now replace the entire create_task() function (the @app.route decorator and all lines inside it) with this code:
@app.route("/tasks", methods=["POST"])
def create_task():
    data = request.get_json()

    # Reject requests that don't include a title
    if not data or "title" not in data:
        return jsonify({"error": "title is required"}), 400

    title = data["title"]
    description = data.get("description", "")
    status = data.get("status", "todo")

    # Insert the new task and get back its ID and timestamp
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO tasks (title, description, status) "
            "VALUES (%s, %s, %s) RETURNING id, created_at",
            (title, description, status),
        )
        row = cur.fetchone()
    conn.commit()
    conn.close()

    task = {
        "id": row[0],
        "title": title,
        "description": description,
        "status": status,
        "created_at": row[1].isoformat(),
    }

    return jsonify(task), 201

What changed in POST?

  • Instead of appending to a Python list, the handler now runs an INSERT query that writes the task directly into the database.
  • %s placeholders are parameterized queries. psycopg sends the values separately from the SQL string so user input can never be interpreted as SQL code. This prevents SQL injection attacks.
  • RETURNING id, created_at is a PostgreSQL feature that sends back the auto-generated ID and timestamp in the same round trip, without a second query.
  • conn.commit() saves the insert to disk. Reads (SELECT) do not need a commit, but writes (INSERT, UPDATE, DELETE) do.
  • Now remove the old in-memory storage. Find these two lines near the top of app.py (below app = Flask(__name__)):
tasks = []
next_id = 1
  • Delete both lines. Your endpoints now use the database, so these are no longer needed.
  • Finally, find the if __name__ block at the bottom of the file:
if __name__ == "__main__":
    app.run(debug=True)
  • Add init_db() on a new line before app.run(debug=True), so the block looks like this:
if __name__ == "__main__":
    init_db()
    app.run(debug=True)
  • Save app.py.
  • Start the server:
python app.py
  • In your second terminal, create a task:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Buy groceries\"}"

You should see the new task returned with an id, a created_at timestamp, and a 201 status code.

  • Create a second task so you have more data to test with:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Walk the dog\"}"

Seeing a 500 error?

A 500 error usually means the SQL query failed. Check your terminal where the Flask server is running for the full error message. Common causes: the password in DB_CONFIG is wrong, the tasktracker database does not exist, or the tasks table was not created.

help me fix a 500 error on my Flask task API

Now for the moment of truth. In the last step, restarting the server wiped all your tasks. Before you test, think about what you expect to happen this time.

  • Stop the server with Ctrl+C.
  • Start it again:
python app.py
  • Fetch your tasks:
curl.exe http://127.0.0.1:5000/tasks

Your tasks are still there. Buy groceries and Walk the dog survived the restart because they live in PostgreSQL, not in Python's memory.

✔️ Awesome, I've got everything!

Great. Make sure both app.py and db.py are saved.

ⓧ I'd like to double check the full code

Your app.py should have these sections in order. Scroll through your file and confirm each one is present:

  • Two import lines at the top: from flask import Flask, request, jsonify and from db import get_connection, init_db.
  • app = Flask(__name__) with no tasks list or next_id below it.
  • The get_tasks() function using SELECT and a for loop to build a list of dicts.
  • The create_task() function using INSERT ... RETURNING with parameterized %s values.
  • The if __name__ block calling init_db() before app.run(debug=True).

Your db.py should match this:

import psycopg

DB_CONFIG = {
    "dbname": "tasktracker",
    "user": "postgres",
    "password": "your-password-here",
    "host": "localhost",
    "port": 5432,
}


def get_connection():
    return psycopg.connect(**DB_CONFIG)


def init_db():
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id SERIAL PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT DEFAULT '',
                status TEXT NOT NULL DEFAULT 'todo',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
    conn.commit()
    conn.close()

Your API now stores tasks in a real database. No more disappearing data. Next up, you will add the ability to fetch a single task, update it, and delete it to complete the full set of CRUD operations.

Complete the CRUD Operations

Your API can list all tasks and create new ones. In a real application, you also need to fetch a single task by its ID, update it, and delete it.

These three operations complete the CRUD cycle: Create, Read, Update, Delete. Each one uses a SQL WHERE clause to target a specific row, and each needs to handle the case where the task does not exist.

In this step, get ready to:
  • Add a GET route that fetches a single task by ID.
  • Add PUT and DELETE routes for updating and removing tasks.
  • Test the full CRUD cycle from creation through deletion.
Add the GET single task route

Your GET /tasks endpoint returns every task. To fetch just one, you need a route that accepts a task ID in the URL and uses a SQL WHERE clause to find that specific row.

  • In app.py, find the last line of your create_task function:
    return jsonify(task), 201
  • Add the following route below it to fetch a single task by its ID:


@app.route("/tasks/<int:task_id>", methods=["GET"])
def get_task(task_id):
    # Connect and query for the specific task by ID
    conn = get_connection()
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, title, description, status, created_at "
            "FROM tasks WHERE id = %s",
            (task_id,),
        )
        row = cur.fetchone()
    conn.close()

    # Return 404 if no task has that ID
    if row is None:
        return jsonify({"error": "task not found"}), 404

    task = {
        "id": row[0],
        "title": row[1],
        "description": row[2],
        "status": row[3],
        "created_at": row[4].isoformat(),
    }

    return jsonify(task)

What does this code do?

  • <int:task_id> in the route tells Flask to capture the number from the URL and pass it as the task_id argument.
  • WHERE id = %s filters the SQL query to match only the row with that ID. The %s placeholder prevents SQL injection by letting psycopg safely substitute the value.
  • fetchone() returns a single row or None. If the task does not exist, the handler returns a 404 status code with an error message.
  • Save app.py. Flask's debug mode auto-reloads the server when you save.
  • In your second terminal (with the venv activated), test fetching a single task by running:
curl.exe http://127.0.0.1:5000/tasks/1

You should see a JSON object for the task with id 1. If you have no tasks yet, create one first with a POST request from the previous step.

Seeing a 404 or an error?

  • If you see {"error": "task not found"}, the task with that ID does not exist. Create one with a POST request and try again.
  • Make sure you saved app.py and that the server reloaded. Check the terminal running the Flask server for any syntax errors.
  • Confirm the new route is placed after the create_task function and before the if __name__ == "__main__" block.
  • Why is my GET single task route not working?
Add the PUT route for updating tasks

Updating a task is more involved than reading one. The handler needs to check that the task exists, then run an UPDATE query that only changes the fields the caller sent.

  • In app.py, add the first half of the update route below the get_task function. This part validates the request and checks that the task exists:


@app.route("/tasks/<int:task_id>", methods=["PUT"])
def update_task(task_id):
    data = request.get_json()

    if not data:
        return jsonify({"error": "request body is required"}), 400

    conn = get_connection()
    with conn.cursor() as cur:
        # Check if the task exists before updating
        cur.execute("SELECT id FROM tasks WHERE id = %s", (task_id,))
        if cur.fetchone() is None:
            conn.close()
            return jsonify({"error": "task not found"}), 404

Why check existence first?

Running an UPDATE on a row that does not exist would silently succeed with zero rows affected. By checking with a SELECT first, you can return a clear 404 error instead of letting the caller think the update worked.

  • Now add the UPDATE query and response directly below the 404 check, still inside the with conn.cursor() block:
        # COALESCE keeps existing values when a field is not sent
        cur.execute(
            "UPDATE tasks "
            "SET title = COALESCE(%s, title), "
            "    description = COALESCE(%s, description), "
            "    status = COALESCE(%s, status) "
            "WHERE id = %s "
            "RETURNING id, title, description, status, created_at",
            (data.get("title"), data.get("description"), data.get("status"), task_id),
        )
        row = cur.fetchone()
    conn.commit()
    conn.close()

    task = {
        "id": row[0],
        "title": row[1],
        "description": row[2],
        "status": row[3],
        "created_at": row[4].isoformat(),
    }

    return jsonify(task)

What is COALESCE doing here?

COALESCE(%s, title) means "use the new value if one was sent, otherwise keep the existing column value." This lets callers send partial updates. For example, updating only the status without resending the title.

The RETURNING clause is a PostgreSQL feature that returns the updated row inline. Without it, you would need a separate SELECT query to get the updated values back.

  • Save app.py and test updating a task's status by running:
curl.exe -X PUT http://127.0.0.1:5000/tasks/1 -H "Content-Type: application/json" -d "{\"status\": \"in-progress\"}"

You should see the full task returned with its status changed to in-progress, while the title and description stay unchanged.

Update not working?

  • Check your indentation carefully. The second cur.execute block (the UPDATE) must be inside the with conn.cursor() block, at the same indent level as the first cur.execute.
  • Make sure conn.commit() is outside the with block but still inside the function. Without commit(), the change is never saved to the database.
  • Why is my PUT update route returning an error or not saving changes?
Add the DELETE route

The DELETE route removes a task from the database entirely. It uses the same RETURNING trick to check whether a row was actually deleted.

  • In app.py, add the delete route below the update_task function:


@app.route("/tasks/<int:task_id>", methods=["DELETE"])
def delete_task(task_id):
    conn = get_connection()
    with conn.cursor() as cur:
        # Delete the task and return its ID to confirm it existed
        cur.execute(
            "DELETE FROM tasks WHERE id = %s RETURNING id",
            (task_id,),
        )
        row = cur.fetchone()
    conn.commit()
    conn.close()

    # If RETURNING gave no row, the task did not exist
    if row is None:
        return jsonify({"error": "task not found"}), 404

    return jsonify({"message": "task deleted"})

How does RETURNING help with DELETE?

DELETE FROM tasks WHERE id = %s RETURNING id deletes the row and returns its ID in one step. If no row matched the WHERE clause, fetchone() returns None, which tells you the task never existed. No separate SELECT needed.

  • Save app.py.
  • First, create a task you can delete by running:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Task to delete\"}"
  • Note the id in the response. Delete that task by running (replace 2 with whatever ID you received):
curl.exe -X DELETE http://127.0.0.1:5000/tasks/2

You should see {"message": "task deleted"} confirming the task was removed.

Seeing a server error on DELETE?

  • Check that conn.commit() is called before conn.close(). Without the commit, PostgreSQL rolls back the deletion.
  • Make sure the route is placed before the if __name__ == "__main__" block at the bottom of the file.
  • Why is my DELETE route failing or not removing the task?

✔️ Awesome, I've got everything!

Double check you have saved app.py and that the Flask server reloaded without errors.

ⓧ I'd like to double check the full code

Your app.py file is now too long to show in one block. Scroll up and confirm each section is present in this order:

  • The imports and app = Flask(__name__) line at the top.
  • The get_tasks function (GET all tasks).
  • The create_task function (POST).
  • The get_task function (GET single task).
  • The update_task function (PUT).
  • The delete_task function (DELETE).
  • The if __name__ == "__main__" block at the very bottom.
Test the full CRUD cycle

Now that all four operations exist, run through a full cycle: create a task, fetch it, update it, verify the update, delete it, and confirm it is gone.

  • Create a new task by running:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Buy groceries\"}"
  • Note the id in the response. Fetch that task by its ID by running (replace 1 with your task's ID):
curl.exe http://127.0.0.1:5000/tasks/1
  • Update its status to done by running:
curl.exe -X PUT http://127.0.0.1:5000/tasks/1 -H "Content-Type: application/json" -d "{\"status\": \"done\"}"

The response shows the task with "status": "done", while the title remains unchanged. That is COALESCE in action.

  • Delete the task by running:
curl.exe -X DELETE http://127.0.0.1:5000/tasks/1

You should see {"message": "task deleted"}.

Before you run the next command, what do you think will happen when you try to fetch that same task again?

  • Try fetching the deleted task by running:
curl.exe http://127.0.0.1:5000/tasks/999

You see {"error": "task not found"} with a 404 status. Your API correctly handles missing resources across every operation.

Your API now handles every standard CRUD operation with proper error handling. Next up, you will build a summary endpoint that uses SQL aggregate functions to count tasks by status.

Build a Summary Endpoint with Aggregate SQL

Your API can create, read, update, and delete individual tasks. That covers the full CRUD cycle, which is a solid foundation.

Real applications also need analytics. "How many tasks are in progress?" "How many are done?" These questions require the database to count and group rows, not just return them one by one. The SQL functions that do this are called aggregate functions, and they are among the most common interview questions for backend roles.

In this step, get ready to:
  • Add a summary endpoint that uses SQL COUNT and GROUP BY to aggregate task data.
  • Test the endpoint with tasks of different statuses and confirm the counts update live.
Add the summary endpoint

SQL aggregate functions let the database do the math instead of your Python code. COUNT(*) counts rows, and GROUP BY splits those counts by a column value (like status).

  • In app.py, find the if __name__ == "__main__": block at the bottom of the file.
  • Add the following function directly above that block:
@app.route("/tasks/summary", methods=["GET"])
def get_summary():
    conn = get_connection()
    with conn.cursor() as cur:
        # Count tasks grouped by their status
        cur.execute(
            "SELECT status, COUNT(*) AS count "
            "FROM tasks "
            "GROUP BY status "
            "ORDER BY count DESC"
        )
        rows = cur.fetchall()

        # Get the overall total
        cur.execute("SELECT COUNT(*) FROM tasks")
        total = cur.fetchone()[0]
    conn.close()

    # Build the status breakdown list
    breakdown = []
    for row in rows:
        breakdown.append({"status": row[0], "count": row[1]})

    return jsonify({"total_tasks": total, "by_status": breakdown})

What does this code do?

  • The first query uses GROUP BY status to bucket tasks by their status, then COUNT(*) counts how many tasks fall into each bucket.
  • ORDER BY count DESC sorts the results so the status with the most tasks appears first.
  • The second query runs a separate SELECT COUNT(*) FROM tasks to get the overall total across all statuses.
  • The response combines both into a single JSON object with total_tasks and a by_status breakdown list.
  • Save app.py.

Your Flask server auto-reloads because you started it with debug=True. You should see a "Restarting with stat" message in the terminal where the server is running.

  • Quick-test the new endpoint by running this in your second terminal:
curl.exe http://127.0.0.1:5000/tasks/summary

You should see a JSON response. If there are no tasks in your database, it returns {"by_status":[],"total_tasks":0}. If you have leftover tasks from previous steps, you will see their counts.

Seeing a 404 error?

  • Make sure you placed the get_summary function above the if __name__ == "__main__": block, not inside it.
  • Check that the route decorator reads /tasks/summary exactly. A typo in the path means Flask does not register the route.
  • Confirm the server reloaded after you saved. If you do not see a reload message, stop the server with Ctrl+C and restart it with python app.py.
  • Still stuck? Help me debug why my /tasks/summary route returns a 404 in Flask.

✔️ Awesome, I've got everything!

Great. Double check you have saved app.py.

ⓧ I'd like to double check the full code

Your app.py is now a long file. Scroll through it and confirm these sections appear in this order:

  • Imports (from flask import Flask, request, jsonify and from db import get_connection, init_db) and app = Flask(__name__).
  • The get_tasks function (GET /tasks).
  • The create_task function (POST /tasks).
  • The get_task function (GET /tasks/<int:task_id>).
  • The update_task function (PUT /tasks/<int:task_id>).
  • The delete_task function (DELETE /tasks/<int:task_id>).
  • The new get_summary function (GET /tasks/summary) you just added.
  • The if __name__ == "__main__": block at the very bottom.
Populate tasks and test the summary

To see the summary in action, you need tasks with a mix of statuses in your database. Each POST command below creates a task with a specific status.

  • Create a task with the default todo status by running:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Write unit tests\"}"
  • Create another todo task by running:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Update README\"}"
  • Create a task with in-progress status by running:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Review pull request\", \"status\": \"in-progress\"}"
  • Create two tasks with done status by running these commands one at a time:
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Set up database\", \"status\": \"done\"}"
curl.exe -X POST http://127.0.0.1:5000/tasks -H "Content-Type: application/json" -d "{\"title\": \"Deploy to staging\", \"status\": \"done\"}"

Each command returns the created task as JSON, confirming it was stored in PostgreSQL.

Before you hit the summary endpoint, think about what you expect to see. You created 2 todo tasks, 1 in-progress task, and 2 done tasks. What should the counts look like?

  • Test the summary endpoint by running:
curl.exe http://127.0.0.1:5000/tasks/summary

You should see a JSON response with a total_tasks count and a by_status breakdown. If you had no tasks from previous steps, the response looks like this:

{"by_status":[{"status":"done","count":2},{"status":"todo","count":2},{"status":"in-progress","count":1}],"total_tasks":5}

Why two separate queries?

The GROUP BY query only returns rows for statuses that have at least one task. If all your tasks are todo, the breakdown shows one row, not three.

A separate SELECT COUNT(*) gives you the total across all statuses in a single number, so the response always has a clean total_tasks field regardless of the breakdown shape.

Now confirm the summary updates dynamically when data changes. Delete one of your tasks by using its ID from the POST responses earlier.

  • Delete a task by running (replace the ID with one from your POST responses):
curl.exe -X DELETE http://127.0.0.1:5000/tasks/1
  • Hit the summary endpoint again by running:
curl.exe http://127.0.0.1:5000/tasks/summary

The total_tasks count dropped by one, and the status that task belonged to lost a count. The database computes these numbers fresh every time you call the endpoint.

Counts don't match what you expected?

  • If your total_tasks is higher than expected, you may have leftover tasks from previous steps. Run curl.exe http://127.0.0.1:5000/tasks to see all tasks in the database.
  • If the delete returned task not found, the ID you used does not exist. Check the IDs in your task list and try again with a valid one.
  • Need help? My summary endpoint counts don't match the tasks I created. Help me debug.

Your API is now a complete task tracker with full CRUD and analytics. You can create, read, update, and delete tasks, plus ask the database to crunch the numbers for you. Next up, there is an optional Secret Mission that takes your SQL skills further.

Secret mission

Add Filtering and Sorting

Your API returns every task every time. Can you add query parameters to filter by status and sort by column, building the SQL dynamically while keeping it safe from injection?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The task-tracker-api project folder on your Desktop (includes app.py, db.py, and the venv virtual environment).
  • The tasktracker PostgreSQL database.
  • Python 3.14.7.
  • PostgreSQL 18.

Keep everything running

No action needed. Choose this if you are still actively building or want to revisit the project later.

  • Your Flask dev server, database, and project files remain on your machine ready for development.
  • Python and PostgreSQL stay installed on your system.
  • The venv virtual environment keeps your project's Flask and psycopg packages separate from any other Python projects.

Pause - I'll come back to this later

Shut down running processes to free up system resources while keeping your files and data intact.

  • Stop the Flask dev server by pressing Ctrl+C in the terminal where it is running.
  • Your project files in the task-tracker-api folder remain on your Desktop.
  • Your tasktracker database and all its task data remain in PostgreSQL.
  • When you return, reactivate your virtual environment with venv\Scripts\activate and start the server with python app.py.

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • Stop the Flask dev server by pressing Ctrl+C in the terminal where it is running.

Drop the tasktracker database by running these commands:

psql -U postgres
  • Enter the superuser password you set during PostgreSQL installation.
  • Run this SQL command to drop the database:
DROP DATABASE tasktracker;
  • Exit psql:
\q
  • Delete the project folder from your Desktop. Open File Explorer, navigate to your Desktop, right-click the task-tracker-api folder, and select Delete. This removes your code files, the virtual environment, and all installed packages.

To uninstall Python and PostgreSQL from your system:

  • Press the Windows key to open your search bar.
  • Type Add or remove programs and press Enter.
  • Search for Python 3.14.7, select it, and click Uninstall.
  • Search for PostgreSQL 18, select it, and click Uninstall. Follow the EDB uninstaller prompts to complete the removal.

Nice Work!

Nice Work!

You just built a complete Task Tracker REST API from scratch using Python, Flask, and PostgreSQL. Every line of code is yours, every SQL query is yours, and you can explain each piece in an interview.

You've learned how to:

  • Build a complete REST API with full CRUD operations (GET, POST, PUT, DELETE) using Flask, returning proper HTTP status codes for success, creation, missing resources, and bad input.
  • Write raw SQL queries (INSERT, SELECT, UPDATE, DELETE) with parameterized values to prevent SQL injection, and design a database schema with appropriate column types, defaults, and constraints.
  • Use PostgreSQL aggregate functions (COUNT, GROUP BY) to build an analytics endpoint, and experience the problem-solution arc of in-memory data loss leading to a real database.
  • Secret Mission: Build dynamic SQL queries with filtering and sorting using safe whitelist patterns to protect against SQL injection.

Ready to quiz yourself?