Show HN: SQL Noir – Learn SQL by solving crimes
89 comments
·February 13, 2025RvdV
I like it! Very fun! Few ideas:
- The "submit" box isn't clear on whether it needs the name or the suspect id for the first case
- It would be nice to have a "copy to notes" button in the output
- It would be nice to have some docs on what functions are available / which dialect this is.
I think this very valuable, it's so much more easy to learn if you actually have a small project to work on
caro_kann
> - The "submit" box isn't clear on whether it needs the name or the suspect id for the first case
I think it's implemented to be general, they wrote what to submit in 'Objectives' section though. For me as a user, I deduced that they wanted "suspect" name, after all, this is a detective game.
worble
This is super fun!
One small thing which would be nice is the ability to just download the sqlite database myself so I could use my preferred application to query it, and just use the site for the brief, notes and submission.
Also a nitpick, while you say it's for "learning" SQL, I would probably expect something a little more guided, or at least some example queries, if the intention was to teach SQL. As it stands, if you don't know SQL you're probably going be completely lost as to what you should be doing. It's really cool, just not specifically as a learning tool.
laowantong
A great addition to a field where there aren't many offerings: SQL Island (https://sql-island.informatik.uni-kl.de) and SQL Murder Mystery (https://mystery.knightlab.com) come to mind. The mechanism of SQL Noir is close to that of the latter, i.e., an undirected, essentially standalone adventure. I myself am working on SQLab, a SQL game engine that allows you to augment an arbitrary base with exercises on that base to produce directed, standalone adventures: https://github.com/laowantong/sqlab. You download a dump of the database (currently MySQL, SQLite, Postgres), and can play under any administrator interface. On the same page there's a link to the long police investigation I designed for my (French) students. If you're a teacher or researcher in the field, the principles of SQLab are explained here: https://arxiv.org/abs/2410.16120.
daluc
Great project, totally agree! Also further great projects posted here. I'll chip in here my project of an SQL Game Console that comes with a SQL Game Editor -- allowing students to build their own SQL escape game in the browser: https://eskuel.de . It's been fun trying this out in the class room (only available in German right now). Example game: https://eskuel.de/zoo/
Terr_
This gives me a childhood flashback to a show called MathNet, an educational police procedural (a la Dragnet) where both investigators have holstered calculators.
nedrylandJP
and there were infamous hackers too
Terr_
Taking advantage of an extra that wasn't charged to the customer?
Ah, that's a classic "use after free" vulnerability.
bhollan
I cannot even tell you how many glorious memories this brings up! Thanks for a walk down memory lane.
wbakst
Love this! The style is unique and awesome.
I see the beginnings of a really fun way to learn / practice / remember SQL.
A few notes:
- Would be cool if it was a single workspace (no tabs). Was constantly switching tabs back and forth.
- Saving previous SQL queries and results would be cool. I was copying results into the notes. Feel like this is important as things get more complex.
Excited to see where you take this!
chrisBHappy
Good notes indeed. I have just implemented a side by side view so that you can view 2 tabs at once. I hope this helps. Also as other users suggested you can use the notes for saving queries but you can also comment them out in the editor.
wbakst
I love the updates! Side-by-side is great, the graph view is great. Awesome stuff! Thanks for sharing.
dylan604
> - Saving previous SQL queries and results would be cool. I was copying results into the notes. Feel like this is important as things get more complex.
I imagined myself with my flip notepad at the crime scene taking notes every time I'd copy queries/results to the notepad section. All I needed was a hat, and then say "just the facts, ma'am" a couple of times.
wbakst
I like that way of thinking about it!
fodkodrasz
The idea is good, but the UI is all but immersive.
Things that would it make more immersive:
- autocomplete
- and/or the ability to view the schema alongside the query (on a wide enough screen)
- a way to copy table and column names easily with a click (table names cannot be selected at all!)
- ability to just add more queryies under/over the already executed ones, instead of only being able to replace them.
tiu
Agree with all but autocomplete should either be a hidden feature or disabled by default since I think it takes away from the immersion as well forces you to recall.
Maybe enabled on a NG+?
chrisBHappy
Thank you for the suggestions. I have just implemented the following: - Side by Side view to open up two different tabs at the same time - Copy button to copy table names
fodkodrasz
Great job! I'm happy if I could provide useful feedback. I wish you luck, this is a fun learning tool!
jkubicek
Commenting out blocks of code with command-/ would be super useful as well
8mobile
Wow what a great idea, I had fun solving the cases. I think maybe a comic version would help junior programmers. The SQL editor sometimes behaves strangely and does not allow correct writing. Thanks
kaeruct
The editor behaves weirdly when I try to add comments.
It's hard to explain, but you can reproduce like this:
1. Write several lines, for example:
select i.*, s.*
from interviews i
join suspects s
on s.id = i.suspect_id
2. Try to comment each line on its own by typing -- in front of each line. -- select i.*, s.*
from interviews i
join suspects s
on s.id = i.suspect_id
-- select i.*, s.*
-- from interviews i
join suspects s
on s.id = i.suspect_id
3. As soon as you do it for "from interviews i", that line will move itself to the previous line, and the syntax highlighting will be brokenchrisBHappy
Indeed. I think I just fixed this bug.
doruk101
When I was in university, my instructor linked something like this for SQL practice (a crime solving minigame, just like this one).
I remember getting really into it, even going to the extreme of trying to find the most efficient one-liner solution.
Thanks for making this. I’ll be passing the torch by linking it to anyone interested in learning SQL.
chocks
This is pretty fun, I tried the two free mysteries and was fun solving them. One nit, would be nice if the SQL editor supported comments so we can comment out old queries before running new one so as keeping a history esp if we need to run the same queries again. Good stuff :)
chrisBHappy
You should be able to add -- and comment out lines of queries. If not, this is a bug that I will investigate. Also you can use the Notes tab to store any past queries and findings.
mymacmachine
Hey!
Firstly - great work - one of my favourite games is Chronicles of Crime and this immediately made me think back to how I would've gone about investigating some of those stories using SQL.
w.r.t the comments - you can add `--` to comment out lines of queries but when I do it on two or more lines they get mashed into a single line and I can't reliably uncomment them again without the queries getting mangled.
I've found you can use multiline comments /* and */ though which worked for me - just a bit more back and forth on the keyboard.
Anyway - again - awesome work.
chrisBHappy
Thank you very much. That's actually a great suggestion. I completely forgot about that. As for the single line comments, indeed this is a bug, I'll try to fix it.
pledg
It works for the first line, but doing it on a second seems to make it merge lines.
chrisBHappy
Yep. This was a bug. I just fixed it.
agumonkey
reminds me of https://mystery.knightlab.com/
chrisBHappy
An honorable mention. This was in fact my inspiration for creating SQL Noir.
ryanianian
Really cute. But I really want the ability to put the different tabs -- Brief, Workspace, Schema -- side-by-side. I know SQL and wanted to play with this, but the UX was frustrating enough to drive me away, even though it is really pretty.
__float
+1 to this. Remembering the schema is kind of a big barrier when you're just jumping in, and that's when you want to be able to explore at random the most.
(I also struggled with the schema because crime_scene was singular and suspects was plural!)
SlackingOff123
I agree. I also wish it was possible to run multiple queries in the same window e.g.:
select * from crime_scene;
select * from suspects;
sgarland
SELECT
cs.*,
s.*
FROM crime_scene cs
FULL JOIN
suspects s ON cs.id = s.id;
This maintains zero relationship between the tables, of course, but it shows you both. You could also specify individual columns.chrisBHappy
Yes! I just implemented a "Side By Side" view that will enable you to view 2 separate tabs side by side. Only on Desktop.
chrisBHappy
That's a good suggestion. I'm not sure if screen real estate won't be a problem though.
hinkley
Maybe modeled after browser dev tools. Big display at the top, small repl at the bottom with a dragged to resize. Also tab structure and the “menu” are chewing up a lot of vertical real estate for two classes of data most users won’t see as separate.
The schema ui seems to be big because of the graph display… which is not at all done baking yet. The hard part of displaying graphs is the pathing, and the very first one I opened has an arrow coming from the left, going across the mode, and attaching to the right side. In a perfectly horizontal line. That’s gonna need to cook a lot longer. It it worth having a bad vis over having no vis?
chrisBHappy
Just implemented it. Looks really nice on Desktop. Nice and compact.
I built SQL Noir, an interactive detective game that challenges you to solve mysteries using real SQL queries. It’s fully open source, designed to give you a practical and immersive way to learn SQL while engaging with a narrative-driven mystery.