Load Chess.com data to PostgreSQL

Chess.com’s read-only Published-Data API exposes public player, game, club, tournament, country, puzzle, streamer, and leaderboard data as JSON/JSON-LD. Everything needed to build a working Chess.com → PostgreSQL pipeline is on this page: the API's base URL, authentication, endpoints, pagination and incremental field — plus a prompt that hands the whole job to your coding agent.

---

Build your Chess.com to PostgreSQL pipeline

Paste this prompt into Claude, Codex, or Cursor. The agent does the rest.

That scaffolds a dltHub workspace and installs the dltHub AI harness — the project rules, the secrets-management skill, and the dlt MCP server your agent needs to work safely. From there it reads the Chess.com API, proposes the endpoints to load, then writes, runs and validates the pipeline while you review rather than type. Credentials are inspected through MCP tools, so your agent never reads secrets.toml itself. How the LLM-native workflow works →

Prefer to write it yourself? Every fact the agent uses is below.

---

Chess.com API at a glance

These values were checked by running the pipeline below against the live API. The Chess.com Published-Data API reference is the authoritative source if anything here looks out of date.

---

How do I authenticate with the Chess.com API?

You don't. The Published-Data API is public and read-only: there is no API key, no OAuth token and no Authorization header, so the pipeline has no auth block and needs no secrets.toml entry.

Chess.com does ask every client to identify itself. Requests with an empty User-Agent, or with the default python-requests/... agent, are rejected with HTTP 403. Put an app name and a contact in the User-Agent header; the pipeline below sends it through client.headers.

Configure the players and the User-Agent in .dlt/config.toml:

Copy
[sources.chess_com]
players = ["MagnusCarlsen", "Hikaru"]
user_agent = "my-dlt-pipeline/1.0 (contact: you@example.com)"

These are configuration values, not secrets. dlt reads the file automatically at runtime. See configuration and secrets in dlt.

---

What Chess.com data can I load into PostgreSQL?

These are the Chess.com endpoints dlt can load into PostgreSQL:

---

How do I load only new Chess.com records?

The Chess.com API has no "changed since" filter, and game records carry end_time only as data, not as a request parameter. What it does have is monthly archives: player/{username}/games/archives lists one URL per month (.../games/2026/09), and every month except the current one is immutable.

So the incremental cursor is the archive month, taken from the archive URL. dlt keeps the last month it loaded and, on the next run, requests only that month and the newer ones. Set "primary_key": [] on the incremental so the last month is fetched again: the current month keeps growing, and with the default boundary de-duplication its new games would be skipped. Games merge on uuid, so the re-fetched month does not create duplicates.

Copy
{
    "name": "archives",
    "primary_key": "url",
    "write_disposition": "merge",
    "endpoint": {
        "path": "player/{resources.players.username}/games/archives",
        "data_selector": "archives",
        "incremental": {
            "cursor_path": "month",          # YYYY/MM, added by _archive_row below
            "initial_value": "2026/01",
            "primary_key": [],               # re-fetch the last seen month
        },
    },
    "processing_steps": [{"map": _archive_row}],
}

The games resource then reads each archive URL from this resource, so only new or still-growing months are downloaded. Use end_time (Unix seconds, typed as a timestamp below) as the game time in your queries. See incremental loading.

dlt also maintains a verified chess source (dlt init chess <destination>) that loads players, profiles and monthly games with its own archive tracking.

---

What does the generated Chess.com pipeline look like?

A dlt REST API pipeline that loads player profiles, the monthly archive list and the games in each archive. players is a plain list from config; every Chess.com path is scoped to a username, so the other resources take the username from it:

Copy
from typing import Any, List

import dlt
from dlt.sources.rest_api import RESTAPIConfig, rest_api_resources


def _archive_row(url: str) -> dict:
    # https://api.chess.com/pub/player/{username}/games/{YYYY}/{MM}
    parts = url.rstrip("/").split("/")
    return {"url": url, "username": parts[-4], "month": f"{parts[-2]}/{parts[-1]}"}


@dlt.source(name="chess_com")
def chess_com_source(
    players: List[str] = dlt.config.value,
    user_agent: str = dlt.config.value,
    start_month: str = "2026/01",
) -> Any:
    @dlt.resource(name="players", selected=False)
    def players_resource():
        yield [{"username": p.lower()} for p in players]

    config: RESTAPIConfig = {
        "client": {
            "base_url": "https://api.chess.com/pub/",
            # public API: no auth block, but Chess.com rejects requests without a User-Agent
            "headers": {"User-Agent": user_agent},
            # every endpoint returns one JSON document
            "paginator": "single_page",
        },
        "resource_defaults": {"write_disposition": "replace"},
        "resources": [
            players_resource,
            {
                "name": "players_profiles",
                "primary_key": "player_id",
                "endpoint": {"path": "player/{resources.players.username}"},
            },
            {
                "name": "archives",
                "primary_key": "url",
                "write_disposition": "merge",
                "endpoint": {
                    "path": "player/{resources.players.username}/games/archives",
                    "data_selector": "archives",
                    "incremental": {
                        "cursor_path": "month",
                        "initial_value": start_month,
                        # re-fetch the last seen month: it may still get new games
                        "primary_key": [],
                    },
                },
                "processing_steps": [{"map": _archive_row}],
            },
            {
                "name": "games",
                "primary_key": "uuid",
                "write_disposition": "merge",
                "columns": {"end_time": {"data_type": "timestamp"}},
                "endpoint": {
                    # the absolute archive URL from the parent resource
                    "path": "{resources.archives.url}",
                    "data_selector": "games",
                },
                "include_from_parent": ["month", "username"],
            },
        ],
    }
    yield from rest_api_resources(config)


def load_chess_com_to_postgres() -> None:
    pipeline = dlt.pipeline(
        pipeline_name="chess_com_pipeline",
        destination="postgres",
        dataset_name="chess_com_data",
    )
    load_info = pipeline.run(chess_com_source())
    print(load_info)


if __name__ == "__main__":
    load_chess_com_to_postgres()

Run it with uv run python chess_com_pipeline.py. The agent iterates on this until it loads cleanly — you review and approve, rather than write it from scratch.

---

How do I configure PostgreSQL as the destination?

Install the extra with uv add "dlt[postgres]", then add the PostgreSQL credentials to .dlt/secrets.toml:

Copy
[destination.postgres.credentials]
host = "<host>"
port = 5432
database = "<database>"
username = "<user>"
password = "<password>"

A connection string works too: credentials = "postgresql://<user>:<password>@<host>:5432/<database>" under [destination.postgres]. See the dlt PostgreSQL destination docs.

---

How do I query Chess.com data in PostgreSQL?

After a run, pipeline.dataset() holds the loaded tables, one per resource. A Relation is a query on that dataset, either a whole table or a SQL string, and reading it gives you the data as a Pandas DataFrame, a PyArrow table or Python tuples. Nothing is read until you ask for it.

Copy
import dlt

pipeline = dlt.pipeline("chess_com_pipeline")

# Step 1: Get the dataset from the pipeline
dataset = pipeline.dataset()

# Step 2: Get a Relation, either a whole table...
games = dataset.table("games")
# ...or any SQL query against PostgreSQL
recent = dataset("SELECT _archives_username, end_time, time_class, url FROM games ORDER BY end_time DESC LIMIT 100")

# Step 3: Read the data
df = games.df()  # or .df(chunk_size=50) for a large table
arrow_table = recent.arrow()  # a PyArrow Table
rows = recent.fetchall()  # a list of Python tuples

# Which tables were loaded, and how big are they?
print(dataset.row_counts().df())

The columns are the fields the Chess.com API returns. Your agent can run the same queries through the MCP server's execute_sql_query tool, and uv run dlthub show opens the Pipeline Dashboard with the schema, load metrics and a query box. See access loaded data in Python and exploring it in marimo notebooks.

---

How do I deploy the Chess.com to PostgreSQL pipeline in production?

The pipeline runs locally, which is ideal for prototyping and one-off analysis. When you need it on a schedule, monitored on every load, and shared with your team, deploy the same dlt code on the dltHub platform — no infrastructure to maintain. The prompt above already ends with "run it on dltHub", so your agent can take it there directly.

• Deploy & schedule — run the pipeline as a managed job with automatic retries.
• Monitor — observable job queues, alerting, and load metrics for every run.
• Transform — promote raw Chess.com loads into governed, documented models.
• Visualize & share — explore data in notebooks and publish live dashboards instead of static screenshots.

Book a demo →

---

What other destinations can I load Chess.com data to?

dlt loads into any of these — only the destination argument changes:

Set dlt.pipeline(destination="snowflake") and add credentials in .dlt/secrets.toml. On the dltHub platform the same pipeline runs against a managed Iceberg lakehouse. See the full destinations list.

---

Next steps

• Ready for production? Book a demo of the dltHub platform →
• dlt LLM-native workflow docs
• REST API source reference
• Join the dlt community on Slack