13 ms·
Show HN: SQL Noir – Learn SQL by solving crimes
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.
- deanebarker 2y agoThis is really lovely. It makes me happy that you invested time into building this for other people.
- jppope 2y agothis is awesome! soooo fun. Thank you for building this. Sharing it immediately
- ryanianian 2y agoReally 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.
- SlackingOff123 2y agoI 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 2y agoSELECT 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 2y agoThat's a good suggestion. I'm not sure if screen real estate won't be a problem though.
- hinkley 2y agoMaybe 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 2y agoJust implemented it. Looks really nice on Desktop. Nice and compact.
- __float 2y ago+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!)
- chrisBHappy 2y agoYes! I just implemented a "Side By Side" view that will enable you to view 2 separate tabs side by side. Only on Desktop.
- chocks 2y agoThis 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 2y agoYou 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 2y agoHey! 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 2y agoThank 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 2y agoIt works for the first line, but doing it on a second seems to make it merge lines.
- chrisBHappy 2y agoYep. This was a bug. I just fixed it.
- Terr_ 2y agoThis gives me a childhood flashback to a show called MathNet, an educational police procedural (a la Dragnet) where both investigators have holstered calculators. https://en.m.wikipedia.org/wiki/Mathnet https://en.m.wikipedia.org/wiki/Mathnet
- bhollan 2y agoI cannot even tell you how many glorious memories this brings up! Thanks for a walk down memory lane.
- nedrylandJP 2y agoand there were infamous hackers too https://youtu.be/vNhBq3kjq_Q?t=2011 https://youtu.be/vNhBq3kjq_Q?t=2011
- Terr_ 2y agoTaking advantage of an extra that wasn't charged to the customer? Ah, that's a classic "use after free" vulnerability.
- agumonkey 2y agoreminds me of https://mystery.knightlab.com/ https://mystery.knightlab.com/
- chrisBHappy 2y agoAn honorable mention. This was in fact my inspiration for creating SQL Noir.
- yoda97 2y agoThat was fun! One question, is there a way to execute the current hovered/selected query and not the first on in the workspace?
- chrisBHappy 2y agoYou can try commenting out the first query with -- and running it like that. I have not implemented yet the ability to run multiple queries.
- hobs 2y agoSomething is weird in at least firefox. Type or paste: select \* from crime_scene; then TYPE so you add a comment select \* from crime_scene; -- I see select ----from---------------- but when I select the text I see what I wrote - I like the comment my text (and I was pulling in the instructions) but it renders some interesting garbage pretty fast.
- chrisBHappy 2y agoThank you for pointing that out. Will look into it.
- hinkley 2y agoFeature request: Badges for completing challenges. Issue Closed, Won’t Fix. Badges?? We don’t need no stinking badges!
- doruk101 2y agoWhen 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.
- worble 2y agoThis 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.
- haliskerbas 2y agoI just shared this with folks, it looks incredible! I’m giving it a try myself too. By the way, I was trying to use a bunch of one-liner SQL statements to explore the data, and it seems like the editor doesn’t handle comments very well. Is there a way to make it work better?
- chrisBHappy 2y agoYep, comments are broken. Currently working on a fix.
- RandomUser4976 2y agoThis is so much fun! I’m going to play this with my kids ages 6, 8, 10, and 12. Thank you for your efforts!
- yesthisiswes 2y agoThis is cool! Great job! I completed the first challenge. I guess I’m used to sql server management studio I tried running multiple queries at once and wasn’t able to. I also tried writing a comment with two dashes to keep track of the id’s and it replaced most of my query with dashes. It might have something to do with being on mobile on iOS.
- chrisBHappy 2y agoThanks for pointing that out. I just fixed the single line comments. It should work as expected now.
- yesthisiswes 2y agoAwesome thanks! I’ll give the second challenge a go
- wbakst 2y agoLove 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!
- dylan604 2y ago> - 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 2y agoI like that way of thinking about it!
- chrisBHappy 2y agoGood 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 2y agoI love the updates! Side-by-side is great, the graph view is great. Awesome stuff! Thanks for sharing.
- rockfishroll 2y agoI solved all of them. Minor nitpick: Since every criminal confesses to their crime, the fastest way to solve most of these is to query the confessions table for strings like '%i did%' or '%kill%'.
- sprobertson 2y agoThat's how I solved #3, still trying to reverse-engineer the correct solution.
- dylan604 2y agoIsn't that the reversed method? You mean you're now trying to solve the crime the normal way instead of jumping to the last page of the book first?
- sprobertson 2y agoHmm it still feels apt, reverse-engineering anything is effectively starting at the last page of the book.
- chrisBHappy 2y agoHaha. You found a cheat code I guess. I would have to think of hiding the answers from plain sight for the next cases or adding some more rows that include these specific strings.
- sgarland 2y agoThis is very nice, thank you for making it.
- wagslane 2y agohaha this is such a cool aesthetic! nice work. education needs more stuff like this - less matter-of-fact regurgitation of information (which ai does an okay job of anyhow) and more creativity.
- Factory 2y agoOn case #4: select * from phone_records where caller_id= 11 or recipient_id = 11 The results header is screwed up, there is an extra 'id' column. At least on firefox.
- Geezus_42 2y agoI'm on mobile, so maybe I'm missing something, but I don't understand how youbare even supposed to get started without knowing SQL. If this is supposed to teach SQL then there needs to be some tutorial to guide the way.
- thomaspark 2y agoQuerymon is a more tutorial-like SQL game: https://www.youtube.com/watch?v=kkP_W8xFz5s https://www.youtube.com/watch?v=kkP_W8xFz5s
- abrookewood 2y agoAgreed, but there is a suggested query to get you started at each step.
- dylan604 2y agoSome games require reading of the instructions before playing. Not all games are Candyland or Chutes&Ladders simple. Sometimes you need a bit of basic knowledge gathering to play the most basic levels of the game. There's also plenty of ways to teach. Some methods assume a basic level of the concepts involved, but by continuing, you'll be exposed to new concepts that level up those skillz. Just like how arithmetic comes before algebra comes before trig comes before calculus.
- exabrial 2y agoJust wanted to say this is awesome! sent it to a few coworkers
- webninja 2y agoHow do you make money doing this and if you earn nothing, how can you afford to in this economy?
- solumunus 2y agoThe bitterness is palpable.
- deleted 2y ago[deleted]
- dylan604 2y agohobby--noun--An activity that one enjoys doing in one's spare time. also: https://en.wikipedia.org/wiki/Hobby https://en.wikipedia.org/wiki/Hobby
- chrisBHappy 2y agoGlad you asked. I don’t want to put a price tag on it because I believe everyone should have access to learning for free. The right to learn shouldn’t be behind a paywall. That said, I’ve added a ‘Buy Me a Coffee’ link for anyone who wants to support the project. No pressure, just an option!
- Foofoobar12345 2y agoNice work. Just what I need to train the non-tech ppl in my team. Wish more things were fun to learn like this.
- dylan604 2y agotables: crime_scene, witnesses, interviews, suspects. one of these is not like the others. I've seen it advised to list table names as plurals as it holds more than one of the things. I've been told plural is dumb. I've not yet run into mixed plurals and singular table names. Sure, it's a style, but pick one??? I'm guessing this is like tabs vs spaces, but would a tab person use spaces randomly or vice versa? otherwise, it's a fun way to kill some time. clearly, i played with it long enough to notice this little bitty bit of something. however, if only modern police departments had this ability to link clues. there's no way to drip donut cremes or spill coffee on these notes
- chrisBHappy 2y agoThank you for pointing that out. During the creation of the cases I did not take much consideration on naming the tables. I will either make all of them plural or singular.
- kaeruct 2y agoThe 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 broken
- chrisBHappy 2y agoIndeed. I think I just fixed this bug.
- 8mobile 2y agoWow 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
- gregorvand 2y agoFun! Nice work and will be great to intro newbies to SQL
- arlanrakh 2y agocongrats!
- vim-guru 2y agoWhat a great idea!
- laowantong 2y agoA great addition to a field where there aren't many offerings: SQL Island (https://sql-island.informatik.uni-kl.de https://sql-island.informatik.uni-kl.de) and SQL Murder Mystery (https://mystery.knightlab.com 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 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 https://arxiv.org/abs/2410.16120.
- frontalier 2y agohttps://sqlbolt.com https://sqlbolt.com, https://regexone.com https://regexone.com, https://pgexercises.com https://pgexercises.com
- daluc 2y agoGreat 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 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/ https://eskuel.de/zoo/
- hotfrost 2y agoI'm a big SQL noob, so I barely know what I'm doing, but... I managed to crash both Firefox and Edge whenever I ran this query: select * from crime_scene, suspects
- mhuffman 2y ago>select * from crime_scene, suspects This is a cross-join. It is the equivalent of a nested loop in programing and almost never what you want to do in a database unless you like watching high-combination data spewing out and CPUs burning. It gives you every combination of items in each table you name. What you want is to find something in two or more of those tables that can be linked together somehow using (very likely!) "INNER JOINS". Look into examples or tutorials of those on the internet and use what you find to work through the tables on the "investigations". Luckily, the SQL Noir site tells you exactly the steps to take in the first case. You need to find a link somehow between the crime_scene and suspects tables and then another one between the matching suspects from the suspects table and the interviews table. Note: there is no direct easy link between the crime_scene and suspects tables. You will have to use your investigation skills to trim the suspects list down.
- Gazoche 2y agoOh this is perfect. I was just looking for exercises to practice my SQL, and detective games are my jam.
- RvdV 2y agoI 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 2y ago> - 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.
- scns 2y agoGreat fun, thank you for building this! Was confused since submitting the confession didn't work, would make sense to ask for the name of the person no?
- fodkodrasz 2y agoThe 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 2y agoAgree 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+?
- jkubicek 2y agoCommenting out blocks of code with command-/ would be super useful as well
- chrisBHappy 2y agoThank 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 2y agoGreat job! I'm happy if I could provide useful feedback. I wish you luck, this is a fun learning tool!
- normie3000 2y agoWhen I click on SQL Workspace, I get an error: > Failed to load database Can't find variable: WebAssembly
- chrisBHappy 2y agoOh wow. That's strange. What browser are you using?
- normie3000 2y agoSafari, with lockdown mode enabled.
- bertolt 2y ago[dead]
- deleted 2y ago[deleted]
- bertoltW 2y ago[dead]
- vojtechrichter 2y agoSo much fun! Looking forward to more complex investigations.
- c6p 2y agoI like it, looking forward to new cases. On case #004, even though I found the killer by occupation and the confession. Person 57 does not give me any clue about a Lamborghini, as the solution text mentions. Is there a bug, or what did I miss?