Conway's Game of Life in Pure SQL - DB Pro Blog<br>Limited Time Offer: 50% off

Toggle theme<br>Download

h1]:font-serif [&>h1]:text-3xl [&>h1]:font-semibold [&>h1]:tracking-tight [&>h1]:text-ink [&>h1]:mt-12 [&>h1]:mb-8 [&>h1:first-child]:mt-0 [&>h2]:font-serif [&>h2]:text-2xl [&>h2]:font-semibold [&>h2]:tracking-tight [&>h2]:text-ink [&>h2]:mt-16 [&>h2]:mb-6 [&>h3]:font-serif [&>h3]:text-xl [&>h3]:font-semibold [&>h3]:text-ink [&>h3]:mt-8 [&>h3]:mb-4 [&>p]:text-muted-foreground [&>p]:leading-7 [&>p]:mb-6 [&_p_a]:text-brand [&_p_a]:no-underline [&_p_a:hover]:underline [&>ul]:mb-6 [&>ul]:list-disc [&>ul]:list-inside [&>ul>li]:text-muted-foreground [&>ul>li]:mb-4 [&>ul>li>a]:text-brand hover:[&>ul>li>a]:underline [&>ol]:mb-6 [&>ol]:list-decimal [&>ol]:list-inside [&>ol>li]:text-muted-foreground [&>ol>li]:mb-4 [&>ol>li>a]:text-brand hover:[&>ol>li>a]:underline [&>strong]:text-ink [&>strong]:font-semibold [&>code]:text-ink [&>code]:bg-muted [&>code]:px-1.5 [&>code]:py-0.5 [&>code]:rounded [&>code]:text-sm [&>code]:font-mono [&>pre]:bg-muted [&>pre]:border [&>pre]:border-border [&>pre]:rounded-lg [&>pre]:p-4 [&>pre]:mb-6 [&>pre]:overflow-x-auto [&>pre]:text-sm [&>blockquote]:border-l-4 [&>blockquote]:border-brand [&>blockquote]:bg-surface [&>blockquote]:pl-4 [&>blockquote]:py-2 [&>blockquote]:my-6 [&>blockquote]:italic [&>a]:text-brand [&>a]:no-underline hover:[&>a]:underline [&_table]:w-full [&_table]:mb-6 [&_table]:border-collapse [&_th]:text-left [&_th]:text-ink [&_th]:font-semibold [&_th]:text-sm [&_th]:px-3 [&_th]:py-2 [&_th]:border-b [&_th]:border-border [&_td]:text-muted-foreground [&_td]:text-sm [&_td]:px-3 [&_td]:py-2 [&_td]:border-b [&_td]:border-border ">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.

Play Step ClearGlider gunPulsarGliderRandom

Generation 0 · 36 live cellsLoading SQLite (sql.js)…

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:

A live cell with two or three live neighbours stays alive.

A dead cell with exactly three live neighbours becomes alive.

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:

Loading SQL environment...

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:

Loading SQL environment...

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:

SQL

SELECT x, y FROM counts<br>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.

Loading SQL...