Building out our tool Wiring up the Backend

In this lesson we replace the in-memory Faker-based generator with a small SQLite-backed repository and wire that repository into a FastAPI endpoint. This gives deterministic, realistic records for testing and demonstrates proper database connection handling, parameter binding, and package import considerations. Overview

Start: existing FastAPI endpoint used the Faker library to produce fake values.
Goal: read rows from a local SQLite database (fakedata.db) and return full records: first name, last name, email, age, city, occupation.
Key troubleshooting items fixed:
- Database file path (working directory differences).
- Connection lifecycle (closing connections too early).
- Correct binding of LIMIT in SQLite queries.
- Relative import/module path issues in package layouts.
Architecture: a small repository module handles DB access while a thin router calls the repository.

Note on tools

Use tools like GitHub Copilot to speed development, but always validate generated code. Pay special attention to imports, file layout, DB paths, and the connection lifecycle.

Initial Faker-based endpoint (before wiring to DB)

# api/endpoints/router.py (original, Faker-based)
from fastapi import APIRouter, HTTPException
from faker import Faker
from ..models.fake_data_request import FakeDataRequest

router = APIRouter()

@router.post("/getfakedata")
async def generate_fake_data(request: FakeDataRequest) -> dict:
    """
    Generate fake data based on the provided parameters.

    Args:
        request (FakeDataRequest): Request parameters for data generation

    Returns:
        dict: Generated fake data
    """
    try:
        fake = Faker(request.locale)

        if not hasattr(fake, request.data_type):
            raise HTTPException(status_code=400, detail="Invalid data_type")
        # ... faker-based generation logic ...
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

Why switch to SQLite?

Deterministic test data: a small SQLite DB produces repeatable records (useful for tests and demos).
Realism: returns full records matching your schema, rather than single fake attributes.
Education: demonstrates repository patterns, SQL parameter binding, correct connection handling.

Repository: data/fake_data_repository.py

This module contains all DB logic: creating a connection and retrieving records.
Important: do not close the connection in get_db_connection() before returning it. Close connections after use in the caller (or in finally blocks inside repository functions).

Final repository implementation:

# data/fake_data_repository.py
"""Database operations for fake data."""

import sqlite3
from typing import List, Dict, Any
from fastapi import HTTPException

DB_PATH = "./fakedata.db"  # ensure the DB file is located relative to where the app runs

def get_db_connection() -> sqlite3.Connection:
    """
    Create and return a connection to the SQLite database.

    Returns:
        sqlite3.Connection: Database connection object

    Raises:
        HTTPException: If database connection fails
    """
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        return conn
    except sqlite3.Error as e:
        raise HTTPException(status_code=500, detail=f"Database connection error: {str(e)}")

def get_fake_data(count: int) -> List[Dict[str, Any]]:
    """
    Retrieve fake data records from the database.

    Args:
        count (int): Number of records to return

    Returns:
        List[Dict[str, Any]]: List of fake data records (dicts)
    """
    query = (
        "SELECT first_name, last_name, email_address, age, city, occupation "
        "FROM fake_data ORDER BY RANDOM() LIMIT ?"
    )

    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(query, (count,))  # binding the LIMIT parameter
        results = cursor.fetchall()
        return [dict(row) for row in results]
    except sqlite3.Error as e:
        raise HTTPException(status_code=500, detail=f"Database error: {str(e)}")
    finally:
        if 'conn' in locals() and conn:
            conn.close()

Router: api/endpoints/router.py

Keep the endpoint thin and delegate DB interactions to the repository.
The request model is a simple Pydantic model containing just count (and an optional locale retained for compatibility).

# api/endpoints/router.py
"""API endpoints for fake data generation."""
from fastapi import APIRouter
from api.models.fake_data_request import FakeDataRequest
from data.fake_data_repository import get_fake_data

router = APIRouter()

@router.post("/getfakedata")
async def generate_fake_data(request: FakeDataRequest) -> dict:
    """
    Endpoint to retrieve fake data.

    Args:
        request (FakeDataRequest): Request parameters

    Returns:
        dict: Object containing retrieved fake data
    """
    data = get_fake_data(request.count)
    return {"data": data}

Request model: api/models/fake_data_request.py

After switching to full-record retrieval, data_type was no longer needed. Keep locale optional for backward compatibility.

# api/models/fake_data_request.py
from pydantic import BaseModel
from typing import Optional

class FakeDataRequest(BaseModel):
    """
    Request model for fake data retrieval.

    Attributes:
        count (int): Number of records to return
        locale (str, optional): Locale for data generation (kept for compatibility; unused by DB)
    """
    count: int
    locale: Optional[str] = "en_US"

Common troubleshooting steps and fixes

Issue	Symptom	Fix / Notes
No such table: `fake_data`	Server raises “no such table” error or returns empty set	Verify DB file path; ensure the server opens the same DB you inspected. Use an absolute path or place `fakedata.db` relative to the process working directory.
Incorrect number of bindings	`sqlite3.ProgrammingError: Incorrect number of bindings` when executing SQL with `?`	Pass a tuple with exactly the placeholders required. Example: `cursor.execute(query, (count,))` (note trailing comma for a 1-tuple).
Empty result set despite rows in DB	Query returns no rows but DB shows data in a browser	Confirm the app opened the same DB file you inspected; print `os.path.abspath(DB_PATH)` from the app during debugging.
ModuleNotFoundError for packages	Import errors when running from different working directory	Use relative imports or ensure PYTHONPATH/working directory is set consistently (project root containing `api/` and `data/`).

Diagnostic SQL and verification

Use the sqlite CLI or DB Browser for SQLite to confirm schema:
- PRAGMA table_info(fake_data);
Example schema verification output:

cid | name         | type    | notnull | dflt_value | pk
 | first_name   | TEXT    | 0       | NULL       | 0
 | last_name    | TEXT    | 0       | NULL       | 0
 | email_address| TEXT    | 0       | NULL       | 0
 | age          | INTEGER | 0       | NULL       | 0
 | city         | TEXT    | 0       | NULL       | 0
 | occupation   | TEXT    | 0       | NULL       | 0

Debugging tips used

Print the SQL and DB path before executing to confirm behavior:

print("DB path:", os.path.abspath(DB_PATH))
print("Query:", query)

Run the printed SQL directly in DB Browser or the sqlite3 CLI to validate results.
From your application, run PRAGMA table_info(fake_data); to verify the schema accessible to your running process.

Example request / response (final implementation) Request body:

{
  "count": 3
}

Response body:

{
  "data": [
    {
      "first_name": "Rita",
      "last_name": "Kim",
      "email_address": "rita.kim@example.com",
      "age": 31,
      "city": "Orlando",
      "occupation": "Dentist"
    },
    {
      "first_name": "Liam",
      "last_name": "Jones",
      "email_address": "lijones@example.com",
      "age": 26,
      "city": "San Antonio",
      "occupation": "Pharmacist"
    },
    {
      "first_name": "Emily",
      "last_name": "Jones",
      "email_address": "emily.jones@example.com",
      "age": 27,
      "city": "Boston",
      "occupation": "Graphic Designer"
    }
  ]
}

Notes about package imports and running the app

If you hit ModuleNotFoundError for top-level package names like src, prefer relative imports or ensure the working directory/PYTHONPATH is set to the project root containing api/ and data/.
The examples above use api.models... and data.fake_data_repository assuming you run the app from the project root.

Warning about over-reliance on code generation tools

Automatic code tools (e.g., GitHub Copilot) can speed development, but they may:

Introduce unexpected file-layout or import changes.
Create new files in the wrong locations if the tool lacks full project context.
Generate code that looks correct but has runtime issues (e.g., closing connections too early, wrong DB path). Always review generated suggestions, run tests, and validate runtime behavior.

What we achieved

Replaced a Faker-based generator with a DB-backed repository that returns real rows.
Implemented a clean separation: thin router -> repository -> DB.
Resolved DB location, parameter binding, and connection lifecycle issues.
Documented troubleshooting steps to help diagnose similar issues quickly.

Next steps

Add query parameters to filter by fields (e.g., age range, city).
Implement pagination instead of relying on ORDER BY RANDOM() LIMIT ? for large tables.
Consider caching or a lightweight connection pool for heavier workloads (SQLite is best for small datasets/testing).

Example utility to inspect the DB (for local debugging)

# scripts/check_db.py
import sqlite3
import os

DB_PATH = "./fakedata.db"

def check_database():
    print("DB path:", os.path.abspath(DB_PATH))
    if not os.path.exists(DB_PATH):
        print("Database file not found!")
        return

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    try:
        cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cur.fetchall()
        print("Tables:", [t[0] for t in tables])

        cur.execute("PRAGMA table_info(fake_data);")
        cols = cur.fetchall()
        print("fake_data columns:")
        for c in cols:
            print(f"- {c[1]} ({c[2]})")
    finally:
        conn.close()

if __name__ == "__main__":
    check_database()

Closing thoughts

This lesson demonstrates wiring a FastAPI endpoint to a local SQLite database, covering common pitfalls and fixes.
Use automation tools to assist, but validate structure, imports, and runtime behavior yourself.

Links and references

Introduction

GitHub Copilot Basics

Prompt Engineering with Copilot

Advanced Features

Management of GitHub Copilot

Using Copilot Efficiently

Building out our tool Wiring up the Backend

Watch Video