5 ms·
Chess in SQL
- upmostly 6mo agoAuthor here. I had the idea of building a working Chess game using purely SQL. The chess framing is a bit of a trojan horse, honestly. The actual point is that SQL can represent any stateful 2D grid. Calendars, heatmaps, seating plans, game of life. The schema is always the same: two coordinate columns and a value. The pivot query doesn't change. A few people have asked why not just use a 64-char string or an array type. You could! But you lose all the relational goodness: joins, aggregations, filtering by piece type. SELECT COUNT(*) FROM board WHERE piece = '♙' just works.
- eastbound 6mo agoSQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. “Pivot tables”: I often have a list of dates, then categories that I want to become columns. SQL can’t do that so there is a technique of spreading values to each column then doing a MAX of each value per date. It is clumsy and verbose but works perfectly… as long as categories are known in advance and fixed. There should be an SQL instruction to pivot those rows into columns. Example: SELECT date, category, metric; -- I want to show 1 row per date only, with each category as a column. ``` SELECT date, MAX( CASE category WHEN ‘page_hits’ THEN metric END ) as “Page Hits”, MAX( CASE category WHEN ‘user_count’ THEN metric END ) as “User Count” GROUP BY date; ^ Without MAX and GROUP BY: 2026-03-30 Value1 NULL 2026-03-30 NULL Value2 2026-03-31 Value1 NULL (etc) The MAX just merges all rows of the same date. ``` SQL should just have an instruction like: SELECT date, PIVOT(category, metric); to display as many columns as categories. This thought should be extended for more than 2 dimensions.
- tn1 6mo agoDuckDB and Microsoft Access (!) have a PIVOT keyword (possibly others too). The latter is of course limited but the former is pretty robust - I've been able to use it for all I've needed.
- amichal 6mo agoPostgresSQL "crosstab ( source_sql text, category_sql text ) → setof record" https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/current/tablefunc.html VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgresql/ https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgres... as a current googlable reference/guide
- andersmurphy 6mo ago> SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. R*Trees are what you are looking for. The sqlite implementation supports up to 5 dimensions.
- h3lp 6mo agoin sqlite you can do it with FILTER: $ sqlite :memory: create table t (product,revenue, year); insert into t values ('a',10,2020),('b',14,2020),('c',24,2020),('a',20,2021),('b',24,2021),('c',34,2021); select product,sum(revenue) filter (where year=2020) as '2020',sum(revenue) filter (where year=2021) as '2021' from t group by product;
- andoando 6mo agoTechnically you can model anything in SQL including execution of any Turing complete language
- eru 6mo agoYes, but OP wants to preserve the relational goodness.
- jollygoodshow 6mo agoGreat showcase. Cool to see how any 2d state can be presented with enough work. Just FYI your statement for the checkmate state in the opera game appears to be incorrect
- upmostly 6mo agoThank you, and thanks for highlighting that. I'll take a look now.
- mwigdahl 6mo agoCan you comment on whether you wrote the article yourself or used an LLM for it? To me it reads human (in a maybe slightly overly-punchy, LinkedIn-esque way), but a lot of folks are keying on the choppiness and exclusion chains and concluding it's AI-written. I'm interested in whether others are oversensitive or I'm not sensitive enough... :)
- slopinthebag 6mo agoThey definitely used an LLM for it
- andersmurphy 6mo agoYup. Works even better with R*Trees[1]. Great article btw! - [1] https://www.sqlite.org/rtree.html https://www.sqlite.org/rtree.html
- traderj0e 6mo agoI've done huge sparse matrix math with SQL before. It parallelizes well like you'd expect.
- btown 6mo agoOne of the things that LLMs "excel" at, pun very much intended, is this exact pattern - creating filtered aggregates for a finite set of columns, and using this at the end of a CTE! OP's example, for reference, was: SELECT rank, MAX(CASE WHEN file = 1 THEN COALESCE(piece, '·') END) AS a, MAX(CASE WHEN file = 2 THEN COALESCE(piece, '·') END) AS b, MAX(CASE WHEN file = 3 THEN COALESCE(piece, '·') END) AS c, MAX(CASE WHEN file = 4 THEN COALESCE(piece, '·') END) AS d, This pattern is incredible for generating financial model drivers (where every column is a calendar/fiscal month/quarter/year, and every row is a different type of statistic/measure). The broader pattern is, in successive CTEs: 1. Group by Date w/ Aggregates 2. "Melt" to [optional groupings +] month + measure_name + value tuples: select group, month, '# Bookings' as measure_name, num_bookings as value from base_data UNION ALL select group, month, 'Revenue', total_revenue from base_data 3. Then "pivot": MAX(CASE WHEN month = '2019-01' THEN value END) AS "2019-01", MAX(CASE WHEN month = '2019-02' THEN value END) AS "2019-02", MAX(CASE WHEN month = '2019-03' THEN value END) AS "2019-03", And what you get is a full analysis table, with arbitrary groupings, that can be dropped into an Excel model in a way that makes life easy for business teams. And while the column breakouts are painful to type out by hand - they're very amenable to LLM generation!
- FergusArgyll 6mo agoVery cool! I think the dragon is missing a white rook - ascii chess pieces are heard to see...
- landsman 6mo agoTool looks nice, but I would prefer such a tool written in a better (native?) language than JavaScript. Security is also important to me, so I only use open-source tools. I’m going to stick with DBeaver and DataGrip.
- eelinki 6mo agoYou could take this even further and add triggers to see if your move is legal or not. Or delete row with a conflict when you capture a piece.
- bob1029 6mo agoThis is getting dangerously close to how some AAA MMORPGs handle[d] much of their logic and state management. At the scales these games operate, enterprisey oracle clusters start to look like a pretty good solution if you don't already have some custom tech stack that perfectly solves the problem.
- upmostly 6mo agoInteresting. I'm a big fan of MMORPGs so hearing that this is how they were made is really cool. I've always wondered what kind of stack games like EverQuest were built on.
- ctippett 6mo agoI started playing World of Warcraft at the same time I was studying database systems at university and had a similar curiosity. Twenty years later the AzerothCore project pretty much satisfies this curiosity, they've done an incredible job reverse engineering the game server and its database. https://www.azerothcore.org/wiki/database-world https://www.azerothcore.org/wiki/database-world
- vakrdotme 6mo agoThat’s fascinating. I didn’t realize the WoW server was so database heavy. do you know if the original game logic was implemented mostly in stored procedures, or was it just used for persistence and the engine handled the rules elsewhere?
- ctippett 6mo agoI don't know I'm sorry. I'm not involved in the project, just a curious bystander!
- grimm8080 6mo agoAmazing, how do I play it?
- jimgoneill 6mo agoAnd they didn’t call it ChessQL?
- upmostly 6mo agoI thought about it, but, not surprisingly, that already exists. https://pypi.org/project/chessql/ https://pypi.org/project/chessql/
- catlifeonmars 6mo ago> No JavaScript. No frameworks. Just SQL. > Let's build it. Cool concept; but every blog post sounds exactly the same nowadays. I mean it’s like they are all written by the exact same person /s
- streetfighter64 6mo agoYeah, I wish this would have just been a short human-written post of a few paragraphs. The emdashes, bulletpoints and unnecessary dramatic flourishes really detract from it. > Not "store chess moves in a database." Not "track game state in a table." Actually render a chess board. With pieces. That you can move around. In your browser. Using nothing but SELECT, UPDATE, and a bit of creative thinking. Please, just write like a person.
- mahogany 6mo agoThe web was already not doing so well, but now I fear LLMs will be the final blow for me. This sort of thing is just unreadable. I don't see how people put up with it. Well, maybe not "people". This thread is full of new accounts saying "cool!". Unfortunately I think HN is on its way out.
- danielszlaski 6mo agoNice. The trojan horse framing works well, once you see that any 2D state is just coordinates + a value, it’s hard to unsee it. Did you consider using this to enforce move legality via CHECK constraints or triggers, or did that get too hairy?
- OfirMarom 6mo agoOf all the use cases for SQL chess would not have been on that list haha. Amazing.
- antonautz 6mo agoNice post! It looks like the colors of the pieces are swapped though. Perhaps you could replace the dots with something else to indicate the colors of the individual squares too.
- devlx 6mo agoVery cool concept
- herodoturtle 6mo agoThis is such a cool way to build brand awareness - kudos to the author. I'd never heard of dbpro.app until now - and this article is just so awesome. Nice job!
- upmostly 6mo agoThank you! That means a lot.
- The_Blade 6mo agovery cool, you got the classic games as well :) i once published a "translation" of the Opera Game (chess annotation as a literary device) after reading too much Lautremont so it is disgusting
- Beestie 6mo agoFascinating idea. Since the board starting position never changes, I'd skip the initial table and pivot and just go straight to loading an 8x8 grid with the pieces. I would also make a table of the 6 piece types and movement parameters. So, for ex, the bishop move restriction is dX=dY, the rook (dXdY=0), knight (dXdY=2), etc. Then a child table to record for each piece, the changes in X,Y throughout the game (so the current position of any piece is X = (Xstart + SUM(dX)) & Y = (Ystart + SUM(dY)) and a column to show if the piece was captured. Any proposed "move" (e.g., 3 squares up) would be evaluated against the move restrictions, the current location of the piece and whether or not the move will either land on an empty square, an opponent piece or gulp off the board and either allow or disallow it. I'm still working on an idea to have a "state" check to know when checkmate happens but that's gonna take a wee bit more time. But, the idea is very novel and very thought provoking and has provided me with a refreshing distraction from the boring problem I was working on before seeing your post.
- hahooh 6mo agoI had no idea SQL could do something like this lol
- bob1029 6mo agoSQL can do anything as of recursive CTEs and application-defined functions with side-effects. Performance and ergonomics are the only remaining concerns.
- skrun_dev 6mo ago[dead]
- cat-whisperer 6mo agocan someone do DOOM in sql?
- evnc 6mo agoSomeone has done a ray tracer in DuckDB, so essentially, yes: https://simonwillison.net/2025/Apr/22/duckdb-wasm-doom/ https://simonwillison.net/2025/Apr/22/duckdb-wasm-doom/
- pagecalm 6mo ago[dead]
- PhishClean 6mo agoGreat idea. Appreciate your efforts
- dorianmariecom 6mo agodoom in sql when? (real doom)
- temporallobe 6mo agoImpressive! Incidentally, I built my own Chess game from scratch pretty recently, using nothing but my own knowledge of the game rules and I am seeing some of the same patterns emerge, though I used plain data structures instead of tables. It’s always interesting to see different ways of solving the same problem, especially with inappropriate/inadequate tools. It’s kind of like figuring out how to make pizza without a proper oven.
- lovegrenoble 6mo agoIt's possible to code anything in SQL
- mergisi 6mo ago[dead]