FRESH

Hacker News

Home

Chess in SQL

178 points by upmostly

by upmostly

7 subcomments

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.

by Beestie

0 subcomment

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.

by eelinki

1 subcomments

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.

by temporallobe

0 subcomment

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.

by antonautz

0 subcomment

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.

by danielszlaski

0 subcomment

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?

by jimgoneill

1 subcomments

And they didn’t call it ChessQL?

by herodoturtle

1 subcomments

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!

by lovegrenoble

0 subcomment

It's possible to code anything in SQL

by cat-whisperer

1 subcomments

can someone do DOOM in sql?

by grimm8080

0 subcomment

Amazing, how do I play it?

by PhishClean

0 subcomment

Great idea. Appreciate your efforts

by dorianmariecom

0 subcomment

doom in sql when? (real doom)

by OfirMarom

0 subcomment

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

by FergusArgyll

0 subcomment

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

by devlx

0 subcomment

Very cool concept

by hahooh

1 subcomments

I had no idea SQL could do something like this lol

by The_Blade

0 subcomment

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

by pagecalm

0 subcomment

[dead]

by skrun_dev

0 subcomment

[dead]

by catlifeonmars

2 subcomments

> 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

by landsman

0 subcomment

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.