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.
jollygoodshow 7 minutes ago [-]
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 5 minutes ago [-]
Thank you, and thanks for highlighting that. I'll take a look now.
andoando 1 hours ago [-]
Technically you can model anything in SQL including execution of any Turing complete language
eastbound 2 hours 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.
“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 1 hours ago [-]
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.
danielszlaski 30 minutes ago [-]
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?
eelinki 2 hours ago [-]
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.
jimgoneill 2 hours ago [-]
And they didn’t call it ChessQL?
upmostly 5 minutes ago [-]
I thought about it, but, not surprisingly, that already exists.
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.
grimm8080 2 hours ago [-]
Amazing, how do I play it?
FergusArgyll 3 hours ago [-]
Very cool! I think the dragon is missing a white rook - ascii chess pieces are heard to see...
catlifeonmars 31 minutes 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
Rendered at 08:42:27 GMT+0000 (Coordinated Universal Time) with Vercel.
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.
Just FYI your statement for the checkmate state in the opera game appears to be incorrect
“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.
https://pypi.org/project/chessql/
> 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