The SQL Game Loop

At the heart of any video game is a "Game Loop"—a continuous cycle that reads input, updates the game state, renders the graphics to the screen, and sleeps to maintain a target framerate.

Implementing a game loop in C, Python, or JavaScript is trivial. Implementing one purely in SQL requires exploiting the Turing completeness of recursive Common Table Expressions (CTEs).

Understanding WITH RECURSIVE

Since the SQL:1999 standard, SQL has included recursive CTEs. These were originally designed for querying hierarchical data, like traversing an organizational chart (employees and managers) or a folder structure. However, because they allow iterative execution based on previous results, they can act as while loops.

A recursive CTE consists of two mandatory parts separated by a UNION ALL or UNION:

1. The Non-Recursive Term: This executes exactly once. It establishes the base case or the "initial state" of the loop.
2. The Recursive Term: This executes repeatedly. On each iteration, it is allowed to reference the row(s) generated by the immediately preceding iteration.

A Simple Example: Counting to 5

WITH RECURSIVE loop_example(i) AS (
    -- 1. The Non-Recursive Term (Base Case)
    SELECT 1

    UNION ALL

    -- 2. The Recursive Term (Iteration)
    SELECT i + 1 
    FROM loop_example 
    WHERE i < 5 -- 3. The Exit Condition
)
SELECT * FROM loop_example;

Structuring the Tetris Game Loop

Tetris-SQL uses this exact mechanism, but instead of just passing an integer i, it passes the entire game state (board array, score, piece coordinates, timestamps) from one iteration to the next.

WITH RECURSIVE main AS (
    -- INITIALIZATION (Frame 0)
    SELECT 
        0 AS frame,
        -- Initialize empty 1D array board
        string_to_array(repeat(repeat('f', const.width) || 't', const.height), NULL)::bool[] AS board,
        0 AS score,
        clock_timestamp() AS last_frame_time,
        ... 
    FROM const

    UNION ALL

    -- ITERATION (Frame N)
    SELECT 
        main.frame + 1,
        next_board.board,
        main.score + next_board.earned_points,
        -- Render output to screen
        notify(render.string),
        -- Sleep to maintain framerate
        pg_sleep(...),
        clock_timestamp()
    FROM main, ...
    WHERE main.max_drop_lines >= 0 -- Exit condition: Game Over
)
SELECT 'score: ' || max(score) AS game_over FROM main;

The Rendering Problem (RAISE NOTICE)

There is a major obstacle with using a CTE as a game loop: SQL clients wait for queries to finish before showing results.

If we just ran the query above, the terminal would hang silently for the duration of the game, and only print the final rows when you died. This is useless for an interactive game.

To solve this, we must force PostgreSQL to push data to the client during the execution of the query. We achieve this using the RAISE NOTICE command, which bypasses the standard result set buffer and sends a message directly to the client's standard error/output stream.

The plpgsql Wrapper

Because RAISE is a procedural command, it cannot be called directly inside a pure SQL SELECT statement. We must wrap it in a lightweight PlpgSQL function:

CREATE OR REPLACE FUNCTION notify(str varchar) RETURNS void AS $$
BEGIN
    RAISE NOTICE '%', str;
END
$$ LANGUAGE PLPGSQL;

During every iteration of the recursive CTE, we generate an ASCII string of the current board state and call notify(render.string).

The side effect of this hack is that PostgreSQL prefixes every message with NOTICE:. To hide this and create a clean "screen refresh" effect, the render string includes multiple newline characters (E'\n\n') at the beginning, pushing the NOTICE: tag off the top of the visible terminal window.

Frame Timing & pg_sleep

To prevent the game from running at the maximum speed of your CPU, the loop must pause.

At the end of every frame, the query uses pg_sleep() to pause execution. To ensure a stable framerate (e.g., 60 FPS), the sleep duration is dynamically calculated based on how long the logic and rendering actually took during the current frame.

pg_sleep(extract(epoch FROM 
    main.last_frame_time + make_interval(secs => 1 / const.fps::decimal) - clock_timestamp()
))

This calculates the delta: (Time of Last Frame + 16.6ms) - Current Time. If the database took 5ms to calculate the collisions, it only sleeps for 11.6ms.