DEV Community

Cover image for Build a Sports Data Sync Service with FastAPI & SQLite
orbistats
orbistats

Posted on

Build a Sports Data Sync Service with FastAPI & SQLite

Calling a third-party API on every user request works in a demo. In production it causes three problems:

Latency: your response time is now your provider’s response time.
Cost and limits: traffic spikes become quota spikes (and 429 errors).
Fragility: if the upstream hiccups, your whole product hiccups.

The standard fix is a sync service: fetch data on a schedule, store it locally, and serve your users from your own database.

In this tutorial we’ll build exactly that with FastAPI, httpx (async) and SQLite:

An async API client with pagination, retry with exponential backoff, and Retry-After support
Idempotent upserts so re-running a sync never duplicates data
A background scheduler inside FastAPI’s lifespan
Fast local query endpoints
Docker packaging and a pytest test with a mocked API

The upstream data comes from the Orbistats Sports Data API, which exposes fixtures across 13 sports with data, meta and pagination in every list response.

Architecture
every N minutes
┌────────────────────────────────┐
│ Background sync loop │
│ for sport in SPORTS: │
│ for day in next 7 days: │
│ paginate → upsert │
└──────────────┬─────────────────┘
│ httpx (Bearer auth, retries)
▼
api.orbistats.com/v1
│
▼
SQLite (fixtures.db) ◄──── GET /fixtures (FastAPI, instant)
Prerequisites
Python 3.11+
An Orbistats API key: request access or try the sandbox
Step 1: Project Setup
bash
mkdir fixtures-sync && cd fixtures-sync
python -m venv .venv && source .venv/bin/activate
pip install fastapi "uvicorn[standard]" httpx pytest pytest-asyncio
pip freeze > requirements.txt
mkdir app && touch app/init.py

Create .env.example (and copy it to .env):

bash
ORBISTATS_API_KEY=your_api_key_here
SPORTS=football,cricket
SYNC_DAYS=7
SYNC_INTERVAL_SECONDS=900
DB_PATH=fixtures.db

SPORTS is configurable so you can add more later. Football and cricket are used in Orbistats’ public examples. Confirm other sport slugs in the API reference.

Step 2: A Resilient Async Client

Create app/client.py. This is the heart of the integration.

python
import asyncio
import random
from typing import Any, AsyncIterator

import httpx

BASE_URL = "https://api.orbistats.com/v1"

class OrbistatsClient:
def init(
self,
api_key: str,
client: httpx.AsyncClient | None = None,
max_retries: int = 5,
) -> None:
self._client = client or httpx.AsyncClient(
base_url=BASE_URL,
timeout=httpx.Timeout(10.0),
headers={"Authorization": f"Bearer {api_key}", "Accept": "application/json"},
)
self._max_retries = max_retries

@staticmethod
def _backoff(attempt: int) -> float:
    # 1s, 2s, 4s, 8s... capped at 30s, plus jitter to avoid thundering herds
    return min(30.0, 2.0**attempt) + random.random()

@staticmethod
def _retry_after(resp: httpx.Response) -> float | None:
    value = resp.headers.get("Retry-After")
    try:
        return float(value) if value is not None else None
    except ValueError:
        return None

async def get(self, path: str, **params: Any) -> dict:
    params = {k: v for k, v in params.items() if v is not None}

    for attempt in range(self._max_retries + 1):
        last = attempt == self._max_retries
        try:
            resp = await self._client.get(path, params=params)
        except (httpx.TimeoutException, httpx.TransportError):
            if last:
                raise
            await asyncio.sleep(self._backoff(attempt))
            continue

        if resp.status_code == 429 or resp.status_code >= 500:
            if last:
                resp.raise_for_status()
            delay = self._retry_after(resp) or self._backoff(attempt)
            await asyncio.sleep(delay)
            continue

        resp.raise_for_status()  # 4xx (401, 403, 404...) fail fast, no retry
        return resp.json()

    raise RuntimeError("unreachable")

async def paginate(
    self, path: str, per_page: int = 50, **params: Any
) -> AsyncIterator[dict]:
    """Yield every item across all pages."""
    page = 1
    while True:
        body = await self.get(path, page=page, per_page=per_page, **params)
        for item in body.get("data", []):
            yield item
        total_pages = body.get("pagination", {}).get("total_pages", page)
        if page >= total_pages:
            break
        page += 1

async def aclose(self) -> None:
    await self._client.aclose()
Enter fullscreen mode Exit fullscreen mode

Three rules this code follows, worth stealing for any API integration:

Retry only what is retryable. 429, 5xx and network errors are transient. 401 and 404 are not, so fail fast.
Respect Retry-After when the server provides it, otherwise use exponential backoff with jitter.
Hide pagination inside the client so the rest of your app just iterates.
Step 3: The Database Layer

Create app/db.py:

python
import os
import sqlite3
from contextlib import closing
from datetime import datetime, timezone

DB_PATH = os.getenv("DB_PATH", "fixtures.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS fixtures (
sport TEXT NOT NULL,
fixture_id TEXT NOT NULL,
home_team TEXT,
away_team TEXT,
kickoff TEXT,
competition TEXT,
updated_at TEXT NOT NULL,
PRIMARY KEY (sport, fixture_id)
);
CREATE INDEX IF NOT EXISTS idx_fixtures_sport_kickoff ON fixtures (sport, kickoff);
"""

def connect() -> sqlite3.Connection:
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
return conn

def init_db() -> None:
with closing(connect()) as conn:
conn.execute("PRAGMA journal_mode=WAL") # readers don't block the writer
conn.executescript(SCHEMA)
conn.commit()

def upsert_fixtures(sport: str, items: list[dict]) -> int:
now = datetime.now(timezone.utc).isoformat()
rows = [
(
sport,
str(i["fixture_id"]),
i.get("home_team"),
i.get("away_team"),
i.get("kickoff"),
i.get("competition"),
now,
)
for i in items
if i.get("fixture_id") is not None
]
with closing(connect()) as conn:
conn.executemany(
"""
INSERT INTO fixtures
(sport, fixture_id, home_team, away_team, kickoff, competition, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(sport, fixture_id) DO UPDATE SET
home_team = excluded.home_team,
away_team = excluded.away_team,
kickoff = excluded.kickoff,
competition = excluded.competition,
updated_at = excluded.updated_at
""",
rows,
)
conn.commit()
return len(rows)

def query_fixtures(
sport: str, date: str | None, competition: str | None, limit: int, offset: int
) -> list[dict]:
sql = "SELECT * FROM fixtures WHERE sport = ?"
args: list = [sport]
if date:
sql += " AND substr(kickoff, 1, 10) = ?"
args.append(date)
if competition:
sql += " AND competition = ?"
args.append(competition)
sql += " ORDER BY kickoff LIMIT ? OFFSET ?"
args += [limit, offset]

with closing(connect()) as conn:
    return [dict(r) for r in conn.execute(sql, args).fetchall()]
Enter fullscreen mode Exit fullscreen mode

The ON CONFLICT ... DO UPDATE clause makes every sync idempotent. Run it ten times and you still have exactly one row per fixture, with the latest data. That’s what lets you retry failed syncs without fear.

Step 4: The Sync Logic

Create app/sync.py:

python
import asyncio
import logging
from datetime import date, timedelta

from .client import OrbistatsClient
from .db import upsert_fixtures

log = logging.getLogger("sync")

async def sync_sport_day(client: OrbistatsClient, sport: str, day: date) -> int:
items = [f async for f in client.paginate(f"/{sport}/fixtures", date=day.isoformat())]
# sqlite3 is blocking, so keep it off the event loop
return await asyncio.to_thread(upsert_fixtures, sport, items)

async def sync_all(client: OrbistatsClient, sports: list[str], days: int) -> None:
sem = asyncio.Semaphore(3) # be polite: at most 3 concurrent upstream jobs
today = date.today()

async def job(sport: str, offset: int) -> None:
    day = today + timedelta(days=offset)
    async with sem:
        try:
            count = await sync_sport_day(client, sport, day)
            log.info("synced %s %s: %d fixtures", sport, day, count)
        except Exception:
            # one failed sport/day must not abort the rest
            log.exception("sync failed for %s %s", sport, day)

await asyncio.gather(*(job(s, d) for s in sports for d in range(days)))
Enter fullscreen mode Exit fullscreen mode

async def sync_loop(
client: OrbistatsClient, sports: list[str], days: int, interval: int
) -> None:
while True:
await sync_all(client, sports, days)
await asyncio.sleep(interval)

Notes:

asyncio.Semaphore(3) caps concurrency so a 13-sport × 7-day sync doesn’t fire 91 requests at once and trigger rate limits.
Failures are isolated per job with logging. One bad day doesn’t poison the whole run.
asyncio.to_thread keeps blocking SQLite calls from freezing the event loop.
Step 5: The FastAPI App

Create app/main.py:

python
import asyncio
import logging
import os
from contextlib import asynccontextmanager

from fastapi import FastAPI, HTTPException, Query

from .client import OrbistatsClient
from .db import init_db, query_fixtures
from .sync import sync_loop

logging.basicConfig(level=logging.INFO)

SPORTS = [s.strip() for s in os.getenv("SPORTS", "football,cricket").split(",") if s.strip()]
SYNC_DAYS = int(os.getenv("SYNC_DAYS", "7"))
SYNC_INTERVAL = int(os.getenv("SYNC_INTERVAL_SECONDS", "900"))

@asynccontextmanager
async def lifespan(app: FastAPI):
api_key = os.environ["ORBISTATS_API_KEY"]
init_db()
client = OrbistatsClient(api_key)
task = asyncio.create_task(sync_loop(client, SPORTS, SYNC_DAYS, SYNC_INTERVAL))
try:
yield
finally:
task.cancel()
await client.aclose()

app = FastAPI(title="Fixtures Sync Service", lifespan=lifespan)

@app.get("/health")
def health():
return {"status": "ok", "sports": SPORTS}

@app.get("/sports")
def sports():
return {"sports": SPORTS}

@app.get("/fixtures")
def fixtures(
sport: str,
date: str | None = Query(None, pattern=r"^\d{4}-\d{2}-\d{2}$"),
competition: str | None = None,
limit: int = Query(50, ge=1, le=200),
offset: int = Query(0, ge=0),
):
if sport not in SPORTS:
raise HTTPException(status_code=404, detail=f"Sport '{sport}' is not synced")
return {
"sport": sport,
"count": len(rows := query_fixtures(sport, date, competition, limit, offset)),
"data": rows,
}

Run it:

bash
export $(grep -v '^#' .env | xargs)
uvicorn app.main:app --reload

Then query your own API:

bash
curl "http://localhost:8000/fixtures?sport=football&date=2026-11-21"

Open http://localhost:8000/docs for FastAPI’s automatic interactive documentation. Responses now come from local disk in milliseconds, whatever the upstream is doing.

Step 6: Test It Without Calling the Real API

httpx.MockTransport lets us simulate paginated responses and a 429. Create tests/test_client.py:

python
import httpx
import pytest

from app.client import OrbistatsClient

pytestmark = pytest.mark.asyncio

async def test_paginates_and_retries_on_429(monkeypatch):
async def no_sleep(_): # keep the test fast
return None

monkeypatch.setattr("app.client.asyncio.sleep", no_sleep)
calls = {"n": 0}

def handler(request: httpx.Request) -> httpx.Response:
    calls["n"] += 1
    page = int(request.url.params["page"])
    if calls["n"] == 1:
        return httpx.Response(429, headers={"Retry-After": "0"})
    return httpx.Response(
        200,
        json={
            "data": [{"fixture_id": f"p{page}"}],
            "pagination": {"page": page, "total_pages": 2},
        },
    )

mock = httpx.AsyncClient(
    base_url="https://api.orbistats.com/v1",
    transport=httpx.MockTransport(handler),
)
client = OrbistatsClient("test-key", client=mock)

ids = [i["fixture_id"] async for i in client.paginate("/football/fixtures")]

assert ids == ["p1", "p2"]
assert calls["n"] == 3  # 429 + page 1 + page 2
await client.aclose()
Enter fullscreen mode Exit fullscreen mode

Add a pytest.ini:

ini
[pytest]
asyncio_mode = auto

Run:

bash
pytest -q
Step 7: Dockerize It
dockerfile
FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY app ./app
ENV DB_PATH=/data/fixtures.db
VOLUME ["/data"]
EXPOSE 8000
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]
bash
docker build -t fixtures-sync .
docker run -p 8000:8000 -v fixtures-data:/data \
-e ORBISTATS_API_KEY=your_key -e SPORTS=football,cricket fixtures-sync

Mount a volume for /data so your database survives container restarts.

Where to Take This Next
More sports. Add the other slugs to SPORTS. Orbistats covers 13 sports with one schema, so the sync code doesn’t change.
Statistics and standings. Add tables for team and player data from the Sports Statistics API.
Historical backfill. Loop sync_sport_day over past dates, or use the Historical Sports Data API for analytics and model training.
Postgres. Swap SQLite for Postgres when you need multiple writers. The ON CONFLICT syntax is nearly identical.
Push, not poll. For near-real-time fields, add webhooks or the WebSocket API on top of the scheduled sync.
Monitoring. Check the provider’s status page and changelog when debugging sync anomalies.
Who Needs This Pattern?
Fantasy platforms scoring thousands of users from one dataset (Fantasy & Analytics solutions)
Data science teams that need clean, queryable history (Analytics & Data Science)
Media sites that want instant page loads without hammering an upstream API

Working on odds-related features? The Python Odds Converter tutorial pairs nicely with this service.

Production Checklist
[ ] API key from environment variables or a secrets manager
[ ] Retries limited to 429, 5xx and network errors (done)
[ ] Concurrency cap on upstream calls (done)
[ ] Idempotent upserts (done)
[ ] Persistent volume for the database
[ ] A health check and alert if updated_at goes stale
[ ] Review the data licensing terms before storing or redistributing data
[ ] Pick an access plan on the pricing page that fits your sync window
Recap

You built a service that:

Syncs fixtures on a schedule with pagination, retries and backoff
Stores them with idempotent upserts
Serves users from a fast local database
Is tested without hitting the real API
Ships as a Docker container

Get started with the Orbistats documentation, explore the Glossary for sports-data terms, or request API access.

Would you like a follow-up that adds Postgres and a live odds-movement table? Let me know in the comments. 💬

Top comments (0)