Conway's Game of Life in Pure SQL

A while back we ran a chess engine in pure SQL, and a lot of you enjoyed watching a query language do something it was never meant to do. So here is another one.

This is Conway's Game of Life. Every generation you see below is computed by a single SQLite query, running right here in your browser. There is no game loop in JavaScript driving the cells. Each frame is the result of a SQL statement. Press play, drop in a glider gun, or click cells to draw your own.

By the end of this post you will have built that, from an empty table to a running simulation, with SQL you can run yourself in the boxes below.

The rules, quickly

The Game of Life is a grid of cells that are either alive or dead. Each step, every cell looks at its eight neighbours and follows three rules:

1. A live cell with two or three live neighbours stays alive.
2. A dead cell with exactly three live neighbours becomes alive.
3. Everything else dies, or stays dead.

That is the entire game. From those three rules you get gliders, oscillators, and patterns that build other patterns. It turns out all of it fits in SQL.

The board is just a table

We do not store a full grid. We only store the cells that are alive, as rows in a table. An empty universe is an empty table, and the work scales with the number of live cells rather than the size of the grid.

Here is a glider, the most famous pattern in the game, sitting in a cell table. The query underneath draws it. Borrowing the trick from the chess post, we generate a window of coordinates and pivot the live cells into a grid of # and . characters:

The xs and ys recursive CTEs generate the columns and rows of a window. We cross join them to get every coordinate, left join the live cells, and then MAX(CASE WHEN x = N ...) pivots each column into place. Empty squares become ., live ones become #.

Counting neighbours

To find the next generation we need to know, for every cell, how many live neighbours it has. The neat part is that we never loop. We take each live cell and fan it out into the eight squares around it, then count how many times each coordinate gets hit.

The eight directions live in their own little table:

Cross joining the five live cells against the eight offsets gives forty rows, one per neighbour relationship. Group by coordinate, count, and you have a neighbour map. Any coordinate not in the result has zero live neighbours, so we do not even have to think about empty space.

One query, one generation

Now we apply the rules. A cell is alive next step if it has exactly three live neighbours, or if it has two and was already alive. That is the whole of Conway's Life expressed as a WHERE clause:

SELECT x, y FROM counts
WHERE n = 3 OR (n = 2 AND (x, y) IN (SELECT x, y FROM cell));

Born on three, survives on two. Nothing else makes it.

Step it yourself

Here is the payoff. The query below computes the next generation, writes it back into the cell table, and draws the result. Because the table persists between runs, every time you press Run the glider takes one more step. Watch it walk down and to the right. Press Reset to put it back where it started.

That is the engine. The animation at the top of the page is doing exactly this, just faster and on a bigger grid, feeding each query's output straight back in as the next input.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

The honest part

When I started this, I assumed the whole thing would be one beautiful recursive query that evolves the board for a hundred generations on its own. SQLite had other ideas. It does not allow aggregate functions inside the recursive part of a WITH RECURSIVE, and counting neighbours needs an aggregate.

So instead of one clever query, it is one honest query run once per generation. I actually prefer it. You can read the entire simulation in a single screen, and there is no magic hiding in a recursive term you have to squint at.

Try it in DB Pro

These boxes run SQLite through sql.js, the same engine behind our interactive shell. Nothing here is faked. If you want to run it against a real database, open DB Pro, connect to a SQLite file, paste in the cell table and the step query, and hit run a few times. You will be playing Life in your own database.

The pivot trick we used to draw the board is the same one from the chess post, and it works for any grid you can think of: calendars, heatmaps, seating charts, game boards. SQL is far more expressive than it gets credit for.

We build DB Pro because working with data should feel fast, and every so often, a little bit fun. Sometimes that means slow query diagnostics and schema tools. Sometimes it means a glider gun running on a database engine. Both count.

Jay