Star Trek database, using SQLite

Hacker News

Share card

Star Trek database, using SQLite

This isn't exactly a new project, it dates back to 2015 and started in PostgreSQL (the "postgresql" tag represents the last state of that database). As a weekend project, I overhauled it to port over to SQLite, partly as an exercise, but also to increase its usability. Since SQLite requires no server to install or administer, and the database is a single file, it's now real easy to pass around and play with. While it is biased to Star Trek media that I own and enjoy, it can still be fun to query and get answers to questions you never thought to ask. Example queries: Listing episodes of The Next Generation season 1 by star date: SELECT title, airdate, episode_number, stardate FROM tng WHERE season = 1 ORDER BY stardate; If your birthday is March 15, find the episodes that aired on that day: SELECT series.title AS series, episode.title AS episode, airdate FROM episode JOIN series USING (series_id) WHERE airdate like '%-03-15'; Pick a random episode of Enterprise to watch, and display the Blu-ray disc to find it on: SELECT * FROM ent_bluray ORDER BY random() LIMIT 1;