fetch schedule by school/sport

[adampax]

This is a great tool, thanks for sharing it. Is it possible to fetch schedules by school/sport/year?

I'm not setting it on the ncaa site, so guessing it isn't, but thought I would check. Thanks again for setting this up.

[henrygd]

Not directly because ncaa.com only provides schedules by date and not team.

You'd need to loop over all the game dates (/schedules route) and calculate team schedules that way. If you do this, please save the values somewhere so the public API doesn't get absolutely hammered, thanks.

It might make sense to do this behind the scenes and save schedules for teams to a database, but I don't have time to work on that.

You can also try scraping stats.ncaa.com, or checking if you can get team schedules via an ESPN endpoint: https://gist.github.com/akeaswaran/b48b02f1c94f873c6655e7129910fc3b

[Tyler-widd]

@henrygd How would you prefer the database?

It might make sense to do this behind the scenes and save schedules for teams to a database, but I don't have time to work on that.

A simple,

{
    school_slug: (taken from https://ncaa-api.henrygd.me/schools-index), 
    sport: (taken from path parameters examples ie football, fieldhocky, soccer-men, volleyabll-women etc), 
    year: YYYY
}

Your correct that it would be a bit of work, but a few scripts could automate the sports (assuming nothing broke...)

I'm willing to take a stab at this at least for the major sports.
Could you provide some feedback on how you'd like to have this database?

[henrygd]

Probably sqlite and have different tables for each sport/division/season. And store minimal data like cols for id (game id), home (team name), away (team name), start (unix seconds or milliseconds).

The route could be something like /team-schedule/michigan/basketball-men/d1/2025 and it would look up michigan in home/away and return the records.

It would still be suboptimal in some ways. We likely won't be able to provide scores. And I'm not sure how postseason would be handled for the current season.

I haven't looked into this at all since ncaa.com replaced all the casablanca stuff with graphql endpoints. Maybe there's a new resource available for this that we can use instead.

[Tyler-widd]

@henrygd
How does this look?
select * from team_schedule_games limit 5;

[
    {
        "sport": "basketball-men",
        "division": "d1",
        "season_year": 2025,
        "game_id": "6506241",
        "start_unix": 1762174800,
        "team": "Winthrop",
        "team_slug": "winthrop",
        "opponent": "Queens (NC)",
        "opponent_slug": "queens-nc"
    },
    
{
        "sport": "basketball-men",
        "division": "d1",
        "season_year": 2025,
        "game_id": "6506241",
        "start_unix": 1762174800,
        "team": "Queens (NC)",
        "team_slug": "queens-nc",
        "opponent": "Winthrop",
        "opponent_slug": "winthrop"
    }
]

[henrygd]

That looks good in theory. I'm fine with using one big table, but we don't want to duplicate the data for the same game. We will already need to store a ton of data, so we should be able to do one row per game like this:

[
    {
        "sport": "basketball-men",
        "division": "d1",
        "season": 2025,
        "game_id": 6506241,
        "start_unix": 1762174800,
        "home": "Winthrop",
        "home_slug": "winthrop",
        "away": "Queens (NC)",
        "away_slug": "queens-nc"
    }
]

I think game_id is always a number so we should be able to use INTEGER to save a bit of storage.

Ideally we should also use normalization for sport and team names, otherwise the database will be way bigger than it needs to be.

Something like this (generated with AI; ask me if you have any questions):

SQLite schema + indexing plan

To keep the database compact while still optimizing for schedule lookups, the schema should normalize the repeated sport and team strings into lookup tables, while keeping division inline since it is only a short 2–3 character value.

Tables

sports

CREATE TABLE sports (
    id INTEGER PRIMARY KEY,
    slug TEXT NOT NULL UNIQUE
);

teams

This table can be prepopulated with data from the /schools-index route.

CREATE TABLE teams (
    id INTEGER PRIMARY KEY,
    slug TEXT NOT NULL UNIQUE,
    pretty_name TEXT NOT NULL
);

games

CREATE TABLE games (
    game_id INTEGER PRIMARY KEY,
    sport_id INTEGER NOT NULL,
    division TEXT NOT NULL,
    season INTEGER NOT NULL,
    start_unix INTEGER NOT NULL,
    home_team_id INTEGER NOT NULL,
    away_team_id INTEGER NOT NULL,
    FOREIGN KEY (sport_id) REFERENCES sports(id),
    FOREIGN KEY (home_team_id) REFERENCES teams(id),
    FOREIGN KEY (away_team_id) REFERENCES teams(id)
);

Indexing strategy

Since the primary lookup path is always:

• sport
• season
• division
• team

the most important optimization is indexing separately for home and away lookups.

CREATE INDEX idx_games_home_lookup
ON games (sport_id, season, division, home_team_id, start_unix);

CREATE INDEX idx_games_away_lookup
ON games (sport_id, season, division, away_team_id, start_unix);

This keeps schedule lookups fast while preserving sort order by start_unix.

Query pattern

Because a team can appear as either home or away, the most index-friendly query pattern is two indexed queries combined with UNION ALL.

SELECT
    g.game_id,
    g.start_unix,
    ht.pretty_name AS home,
    at.pretty_name AS away
FROM games g
JOIN teams ht ON g.home_team_id = ht.id
JOIN teams at ON g.away_team_id = at.id
WHERE g.sport_id = ?
  AND g.season = ?
  AND g.division = ?
  AND g.home_team_id = ?

UNION ALL

SELECT
    g.game_id,
    g.start_unix,
    ht.pretty_name AS home,
    at.pretty_name AS away
FROM games g
JOIN teams ht ON g.home_team_id = ht.id
JOIN teams at ON g.away_team_id = at.id
WHERE g.sport_id = ?
  AND g.season = ?
  AND g.division = ?
  AND g.away_team_id = ?

ORDER BY start_unix;

This allows SQLite to fully utilize both composite indexes instead of degrading performance with a single OR clause.

Benefits

• minimal storage overhead for large imports
• repeated sport and team strings stored once
• division kept simple since normalization would not save meaningful space
• game_id uses the most storage-efficient SQLite primary key layout
• fast schedule lookups by sport + season + division + team
• efficient sorting by start time
• easy future upserts and deduplication

[Tyler-widd]

@henrygd where its at.
I can proceed with getting it ready - anything else you want to adjust?

sqlite> .tables
games   sports  teams

sqlite> .mode json

sqlite> select * from games limit 5;
[
    {
        "game_id": 6349147,
        "sport_id": 1,
        "division": "d1",
        "season": 2024,
        "start_unix": 1730764800,
        "home_team_id": 343,
        "away_team_id": 421
    },
    
{
        "game_id": 6349148,
        "sport_id": 1,
        "division": "d1",
        "season": 2024,
        "start_unix": 1731186000,
        "home_team_id": 343,
        "away_team_id": 62
    },
    
{
        "game_id": 6349149,
        "sport_id": 1,
        "division": "d1",
        "season": 2024,
        "start_unix": 1731456000,
        "home_team_id": 343,
        "away_team_id": 52
    },
    
{
        "game_id": 6349150,
        "sport_id": 1,
        "division": "d1",
        "season": 2024,
        "start_unix": 1731790800,
        "home_team_id": 343,
        "away_team_id": 65
    },
    
{
        "game_id": 6349151,
        "sport_id": 1,
        "division": "d1",
        "season": 2024,
        "start_unix": 1732221000,
        "home_team_id": 135,
        "away_team_id": 343
    }
]

sqlite> select * from sports limit 5;
[{"id":1,"slug":"basketball-men"}]

sqlite> select * from teams limit 5;
[
    {
        "id": 1,
        "slug": "winthrop",
        "pretty_name": "Winthrop"
    },
    
{
        "id": 2,
        "slug": "queens-nc",
        "pretty_name": "Queens (NC)"
    },
    
{
        "id": 3,
        "slug": "st-bonaventure",
        "pretty_name": "St. Bonaventure"
    },
    
{
        "id": 4,
        "slug": "bradley",
        "pretty_name": "Bradley"
    },
    
{
        "id": 5,
        "slug": "tex-am-commerce",
        "pretty_name": "East Texas A&M"
    }
]

[henrygd]

Looks good 👍

[Tyler-widd]

Super, month later scoping it out further 👍