[HN Gopher] Chess in SQL
___________________________________________________________________
Chess in SQL
Author : upmostly
Score : 177 points
Date : 2026-03-29 13:25 UTC (3 days ago)
(HTM) web link (www.dbpro.app)
(TXT) w3m dump (www.dbpro.app)
| upmostly wrote:
| Author 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 wrote:
| SQL 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 wrote:
| DuckDB 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 wrote:
| PostgresSQL
|
| "crosstab ( source_sql text, category_sql text ) - setof
| record"
|
| https://www.postgresql.org/docs/current/tablefunc.html
|
| VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-
| postgres... as a current googlable reference/guide
| andersmurphy wrote:
| > 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 wrote:
| in 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 wrote:
| Technically you can model anything in SQL including execution
| of any Turing complete language
| eru wrote:
| Yes, but OP wants to preserve the relational goodness.
| jollygoodshow wrote:
| Great 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 wrote:
| Thank you, and thanks for highlighting that. I'll take a look
| now.
| mwigdahl wrote:
| Can 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 wrote:
| They definitely used an LLM for it
| andersmurphy wrote:
| Yup. Works even better with R*Trees[1]. Great article btw!
|
| - [1] https://www.sqlite.org/rtree.html
| traderj0e wrote:
| I've done huge sparse matrix math with SQL before. It
| parallelizes well like you'd expect.
| FergusArgyll wrote:
| Very cool! I think the dragon is missing a white rook - ascii
| chess pieces are heard to see...
| landsman wrote:
| Tool 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 wrote:
| You 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 wrote:
| This 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 wrote:
| Interesting. 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 wrote:
| I 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
| vakrdotme wrote:
| That'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 wrote:
| I don't know I'm sorry. I'm not involved in the project,
| just a curious bystander!
| StilesCrisis wrote:
| It's not, no. The data you see in these files is
| reconstituted from the data that shipped with the game
| client, but they're not a perfect match for the real
| data.
|
| The game servers are all C++ and don't use stored
| procedures for general gameplay management. They do
| handle inventory management so that item duping is
| generally not possible, and complex things like cross-
| server character transfer use stored procedures.
| davis wrote:
| Do you have a source? Curious to learn more about this
| grimm8080 wrote:
| Amazing, how do I play it?
| jimgoneill wrote:
| And they didn't call it ChessQL?
| upmostly wrote:
| I thought about it, but, not surprisingly, that already exists.
|
| https://pypi.org/project/chessql/
| catlifeonmars wrote:
| > 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 wrote:
| Yeah, 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 wrote:
| The 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 wrote:
| Nice. 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 wrote:
| Of all the use cases for SQL chess would not have been on that
| list haha. Amazing.
| antonautz wrote:
| Nice 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 wrote:
| Very cool concept
| herodoturtle wrote:
| This 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 wrote:
| Thank you! That means a lot.
| The_Blade wrote:
| very 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 wrote:
| Fascinating 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 (dX
| _dY=0), knight (dX_ dY=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 wrote:
| I had no idea SQL could do something like this lol
| bob1029 wrote:
| SQL can do anything as of recursive CTEs and application-
| defined functions with side-effects. Performance and ergonomics
| are the only remaining concerns.
| cat-whisperer wrote:
| can someone do DOOM in sql?
| evnc wrote:
| Someone has done a ray tracer in DuckDB, so essentially, yes:
| https://simonwillison.net/2025/Apr/22/duckdb-wasm-doom/
| PhishClean wrote:
| Great idea. Appreciate your efforts
| dorianmariecom wrote:
| doom in sql when? (real doom)
| temporallobe wrote:
| Impressive! 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 wrote:
| It's possible to code anything in SQL
___________________________________________________________________
(page generated 2026-04-01 23:01 UTC)