Building out our tool Wiring up the Backend

In this lesson we finish wiring the fake-data tool so the FastAPI endpoint reads real rows from a local SQLite database (fakedata.db) instead of generating values with Faker. This walkthrough demonstrates how to:

Replace in-memory or stub logic with a repository-backed data source
Create a small DB helper for SQLite connections and row-to-dict conversion
Use parameterized queries (LIMIT ?) and avoid common pitfalls (bindings, imports, DB path)
Debug integration issues (module paths, missing tables, binding errors)

This is practical guidance for adapting an existing endpoint to a persistent data source while keeping the router thin and testable.

1) Original endpoint (Faker-based)

This was the original FastAPI endpoint that used the Faker library as a source of stubbed values:

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")

        # ... generate using Faker ...
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

Goal: replace the Faker generation with queries against an SQLite table so the endpoint returns real rows stored in fake_data.

2) Database table (reference)

Here is a sample table DDL used for the demo database:

CREATE TABLE fake_data (
    first_name TEXT,
    last_name TEXT,
    email_address TEXT,
    age INTEGER,
    city TEXT,
    occupation TEXT
);

Validate the table schema with:

PRAGMA table_info(fake_data);

This returns rows for the columns: first_name, last_name, email_address, age, city, occupation. Table summary:

Column name	Type	Description
first_name	TEXT	Given name
last_name	TEXT	Family name
email_address	TEXT	Contact email
age	INTEGER	Age in years
city	TEXT	City name
occupation	TEXT	Job / profession

Tip: Use DB Browser for SQLite or the sqlite3 CLI to inspect the file and verify the fake_data table exists.

3) Add a DB connection helper

Create a tiny utility that opens the SQLite connection and sets row_factory so rows are easily converted to dictionaries for JSON responses:

# data/fake_data_repository.py
"""Database operations for fake data."""
import sqlite3
from typing import List, Dict, Any
from fastapi import HTTPException

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

    Returns:
        sqlite3.Connection: Database connection object

    Raises:
        HTTPException: If database connection fails
    """
    try:
        # Use the relative path to the DB from where your app runs
        conn = sqlite3.connect('./fakedata.db')
        conn.row_factory = sqlite3.Row
        return conn
    except sqlite3.Error as e:
        raise HTTPException(status_code=500, detail=f"Database connection error: {str(e)}")

Important: adjust './fakedata.db' to the correct path relative to your application process. Common mistakes are running the server from a different working directory or deploying with a different file layout.

4) Implement repository function to fetch rows

Add a repository function that executes a parameterized query and returns a list of dictionaries. Use LIMIT ? with parameters passed as a tuple (e.g., (count,)) to avoid binding errors and SQL injection risks:

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

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

    Returns:
        List[Dict[str, Any]]: List of complete fake data records
    """
    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,))
        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():
            conn.close()

Key points:

Use LIMIT ? and pass parameters as a tuple: (count,).
Convert sqlite3.Row to a dict before returning so FastAPI can JSON-serialize the response cleanly.

5) Update the router to call the repository

Replace the Faker-based business logic with a simple repository call. The router should be a thin adapter that validates the request, calls the repository, and returns the rows in a JSON response:

# api/endpoints/router.py
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}

Note: ensure your import paths match the actual package layout. In this project the working solution used api.models.fake_data_request and data.fake_data_repository.

6) Request model (Pydantic)

Keep the Pydantic model small and focused. For the repository-driven endpoint you primarily need count. Optionally include locale if you plan to keep locale-aware behavior later:

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

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

    Attributes:
        count (int): Number of records to generate
        locale (str, optional): Locale for data generation (default: 'en_US')
    """
    count: int
    locale: Optional[str] = "en_US"

If you later reintroduce per-field selection (e.g., data_type), extend this model and update repository logic accordingly.

7) Common errors encountered (and how to fix them)

When wiring DB-backed repositories into existing projects, these issues are common. The table below summarizes errors and remedies.

Symptom / Error message	Likely cause	Fix / diagnostic steps
`ModuleNotFoundError: No module named 'src'`	Incorrect imports or running from wrong working directory	Run the app from the project root and use package imports (e.g., `api.models...`, `data...`). Verify `PYTHONPATH` if needed.
`ImportError: attempted relative import beyond top-level package`	Relative imports exceeding package boundaries	Use absolute package imports or correct the package structure so relative imports stay within the package.
`Database error: no such table: fake_data`	DB file missing or wrong file path, or table not created in that DB	Confirm DB path (`./fakedata.db`) from the running process. Inspect file with DB Browser for SQLite or run `PRAGMA table_info(fake_data);`.
`sqlite3.ProgrammingError: Incorrect number of bindings supplied`	Parameterized query placeholders mismatch	When using `?` placeholders, pass parameters as a tuple, e.g., `(count,)`.
Unexpected empty result set	Table exists but has no rows or random ordering excluded rows	Verify table contents and adjust query (remove `ORDER BY RANDOM()` for deterministic results while testing).

Debugging tips:

Temporarily log the query and params to the application logs while debugging (remove or lower log level in production):

print("Query:", query)
print("Params:", (count,))

Confirm the Python process’ current working directory and the absolute path of the DB file:

import os
print("CWD:", os.getcwd())
print("DB exists:", os.path.exists('./fakedata.db'))

8) Example client requests & responses

Request to retrieve 3 records:

POST /getfakedata
Content-Type: application/json

{
  "count": 3
}

Example successful response:

{
  "data": [
    {
      "first_name": "Carol",
      "last_name": "Smith",
      "email_address": "carol.smith@example.com",
      "age": 45,
      "city": "Austin",
      "occupation": "Teacher"
    },
    {
      "first_name": "Tina",
      "last_name": "Lee",
      "email_address": "tina.lee@example.com",
      "age": 45,
      "city": "New York",
      "occupation": "Professor"
    },
    {
      "first_name": "Quinn",
      "last_name": "Gilbert",
      "email_address": "quinn.gilbert@example.com",
      "age": 33,
      "city": "Oklahoma City",
      "occupation": "Hairdresser"
    }
  ]
}

If the DB file is missing or the table doesn’t exist you will get a 500 with a detail message like:

{
  "detail": "Database error: no such table: fake_data"
}

This error is useful during development because it proves the endpoint is hitting the DB path instead of returning stubbed results.

Use tools like GitHub Copilot to accelerate repetitive tasks, but keep full control of architecture and imports. The LLM can suggest code and new files, but you must verify project structure, package imports, and the database path/permissions.

9) Summary / next steps

What we changed and next improvements:

Replaced Faker-based generation with a repository that reads rows from an SQLite fake_data table.
Added a DB connection helper that sets row_factory for easy dict conversion.
Implemented a repository function using parameterized queries and safe bindings.
Simplified the router to be a lightweight adapter that returns JSON-ready dicts.
Covered common integration errors: import paths, DB location, missing tables, and incorrect parameter bindings.

Next steps:

Add robust validation and error messages on the Pydantic model (e.g., ensure count is positive).
Add optional query filters and field selectors to the repository/API for more flexible responses.
Add unit and integration tests that exercise the database layer using a temporary test DB file or an in-memory SQLite DB.
Consider migrating to a connection pool (e.g., aiosqlite or a higher-level ORM) for production workloads that need concurrency.

Introduction to GitHub Copilot

Core Features

Using Copilot Efficiently

Building out our tool Wiring up the Backend

1) Original endpoint (Faker-based)

2) Database table (reference)

3) Add a DB connection helper

4) Implement repository function to fetch rows

5) Update the router to call the repository

6) Request model (Pydantic)

7) Common errors encountered (and how to fix them)

8) Example client requests & responses

9) Summary / next steps

Links and references

Watch Video

​1) Original endpoint (Faker-based)

​2) Database table (reference)

​3) Add a DB connection helper

​4) Implement repository function to fetch rows

​5) Update the router to call the repository

​6) Request model (Pydantic)

​7) Common errors encountered (and how to fix them)

​8) Example client requests & responses

​9) Summary / next steps

​Links and references

Watch Video

1) Original endpoint (Faker-based)

2) Database table (reference)

3) Add a DB connection helper

4) Implement repository function to fetch rows

5) Update the router to call the repository

6) Request model (Pydantic)

7) Common errors encountered (and how to fix them)

8) Example client requests & responses

9) Summary / next steps

Links and references