Chess in SQL

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.

Author here.

I had the idea of building a working Chess game using purely SQL.

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 (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.

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.

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.

And they didn’t call it ChessQL?

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?

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!

I had no idea SQL could do something like this lol

Amazing, how do I play it?

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.

Of all the use cases for SQL chess would not have been on that list haha. Amazing.

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

Very cool! I think the dragon is missing a white rook - ascii chess pieces are heard to see...

> 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

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.

SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved.

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.

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.

And they didn’t call it ChessQL?

Amazing, how do I play it?

Very cool! I think the dragon is missing a white rook - ascii chess pieces are heard to see...

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.

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!

Thank you! That means a lot.

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.

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

I had no idea SQL could do something like this lol

SQL can do anything as of recursive CTEs and application-defined functions with side-effects. Performance and ergonomics are the only remaining concerns.

> 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

Of all the use cases for SQL chess would not have been on that list haha. Amazing.

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.

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.

Thank you! That means a lot.

SQL can do anything as of recursive CTEs and application-defined functions with side-effects. Performance and ergonomics are the only remaining concerns.

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.

Please, just write like a person.

What if I told you SQL could play chess?

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.

Loading chess board...

No JavaScript. No frameworks. Just SQL.

Let's build it.

The Board

First, we need to represent the chess board. A chess board is an 8x8 grid. Each square can either be empty or contain a piece. That's just a table:

⚡

Loading SQL environment...

We've got 32 rows - one for each piece on the starting board. But that's not very... chess-like. We want to see an actual board.

The Magic: Pivoting Rows into a Grid

Here's where it gets interesting. SQL doesn't naturally output grids - it outputs rows. But we can transform rows into columns using a technique called conditional aggregation.

The idea: GROUP BY the rank (row), and for each file (column), use a CASE statement inside MAX() to pick out the piece:

⚡

Loading SQL environment...

There it is. A chess board. Rendered entirely in SQL.

Let's break down what's happening:

The CTE (WITH full_board AS ...) generates all 64 squares by cross-joining ranks 1-8 with files 1-8, then LEFT JOINs our pieces
The pivot uses MAX(CASE WHEN file = N THEN piece END) to extract each column's piece
COALESCE fills empty squares with · so we can see the grid structure
ORDER BY rank DESC puts rank 8 at the top (black's back rank), like a real board

Making Moves

Now for the fun part. To move a piece, we just UPDATE the board:

⚡

Loading SQL environment...

Both pawns have advanced! The most common chess opening, executed in pure SQL.

Your Turn: The Sandbox

Here's a fully set up board. Try making some moves yourself. Some ideas:

Play the Italian Game: 1. e4 e5 2. Nf3 Nc6 3. Bc4
Try the Queen's Gambit: 1. d4 d5 2. c4
Set up a checkmate position
Or just mess around!

Remember:

Files: a=1, b=2, c=3, d=4, e=5, f=6, g=7, h=8
DELETE the piece from its starting square, INSERT it at the destination
For captures, DELETE both the moving piece AND the captured piece first

⚡

Loading SQL environment...

The Opera Game: A Chess Masterpiece in SQL

Let's replay one of the most famous chess games ever played. In 1858, Paul Morphy played against the Duke of Brunswick and Count Isouard at the Paris Opera (during a performance of The Barber of Seville, no less).

It's a beautiful demonstration of rapid development and tactical brilliance. Let's watch it unfold in SQL.

Morphy has developed his pieces aggressively, targeting the weak f7 pawn:

⚡

Loading SQL environment...

Morphy sacrificed his bishop, but now his knight joins the attack with devastating effect:

⚡

Loading SQL environment...

The finale is stunning. Morphy plays Rd8+, and when the Queen takes the rook, the other rook delivers checkmate:

⚡

Loading SQL environment...

The white rook on d8 delivers checkmate. The bishop on f8 blocks the king's escape, and the knight on b5 covers d6. A masterpiece then, a masterpiece now - rendered in SQL.

Wrapping Up

We just built a fully playable chess board in pure SQL. No JavaScript. No frameworks. Just:

A simple table with rank, file, and piece
A clever pivot query using conditional aggregation
DELETE and INSERT to move pieces

The same pivot technique works for any grid-based visualization - calendars, seating charts, game boards, heatmaps. SQL is more expressive than most people give it credit for.

Now if you'll excuse me, I have a rematch against a database to prepare for.

Jay

Hacker Times

Hacker Times

Discussion

Discussion

The Board

The Magic: Pivoting Rows into a Grid

Making Moves

Your Turn: The Sandbox

The Opera Game: A Chess Masterpiece in SQL

Wrapping Up