DOOMQL: A DOOM-like multiplayer shooter in pure SQL
I recently stumbled across Patrick’s excellent DOOM clone running in a browser powered by DuckDB-WASM. Ever since I’ve read that, I wanted to push his awesome idea to the logical extreme: Build a multiplayer DOOM-like shooter entirely in SQL with CedarDB doing all the heavy lifting. During a month of parental leave (i.e., a lot of sleepless nights), I tried exactly that. Here’s a sneak peek at DOOMQL: Your browser does not support the video tag. DOOMQL in action Okay, with the flashy demo out of the way, let’s talk about details. What follows is a tour of the architecture, the SQL rendering pipeline, the game loop, and the fun metagame of cheating by issuing SQL commands against the database.
Why even do this?
Playing DuckDB DOOM in your browser is fun, but some things bugged me: First of all, having parts of the rendering pipeline in Javascript felt like cheating. It worked well for DuckDB-Doom where everything is contained in a single HTML page, but I wanted to see if I could do everything in SQL. DuckDB-Doom is also a little bit stuttery with just 8 frames per second and has a pretty tiny viewport. I wanted to see if I could speed that up by switching over to CedarDB. I also wanted real sprites with transparency and they should move around believably in 3D space. And most importantly, making the game multi-player should not just be possible, but easy, right? I got nerd-sniped by the perceived similarity of a database server to a traditional game server: Databases exist to synchronize shared state across clients. Thanks to transaction isolation, each player has a consistent view of the game world, no matter what the other clients are doing. Why not lean into that? I would love to lie to you and claim I did it all to push CedarDB as an awesome database system but to be honest the database nerd in me just wanted to turn all knobs up to 11 and see what breaks.
Architectural overview
At a high level State lives in tables (map, players, mobs, inputs, configs, sprites, …)
Rendering is a stack of SQL views that implement raycasting and sprite projection
The game loop is a tiny shell script that executes a SQL file ~ 30 times per second.
The client is ~ 150 lines of Python: It polls for input and queries the database for your 3D view. You can play, observe other players and even cheat (by sending raw SQL).
Game state, or: Let’s store everything in the database
With a database at hand, it’s natural to store all game configuration, state, and static data in the database: Config: CREATE TABLE config( player_move_speed NUMERIC DEFAULT 0 . 3 , player_turn_speed NUMERIC DEFAULT 0 . 2 , ammo_max INT DEFAULT 10 , ammo_refill_interval_seconds INT DEFAULT 2 ); Map: CREATE TABLE map (x INT , y INT , tile CHAR ); Players and inputs: CREATE TABLE players ( id INT REFERENCES mobs(id), score INT DEFAULT 0 , hp INT DEFAULT 100 , ammo INT DEFAULT 10 , last_ammo_refill int default EXTRACT (EPOCH FROM (now())):: INT ); CREATE TABLE inputs( player_id INT PRIMARY KEY REFERENCES players(id), action CHAR ( 1 ), -- 'w', 'a', 's', 'd', 'x' for shooting timestamp TIMESTAMP DEFAULT NOW() ); Because everything is data, modding a running match is trivial: -- Change a setting update config set ammo_max = 20 ; -- Add a player insert into players values (...); -- Move forward update input set action = 'w' where player_id = < your_id > ; -- Cheat (pls be smarter about it) update players set hp = 100000 where player_id = < your_id > ; -- Ban cheaters (that weren't smart about it) delete from players where hp > 100 ;
Renderer: When a VIEW becomes your 3D view
If you squint enough, in DOOM, a 3D (or more correct: 2.5D) view is just a view over 2D state (i.e., the level map and any players/enemies on it). Well, we’ve got VIEWS in SQL as well. They’re also just views on our (2D) state tables. What’s stopping us from quite literally building a 3D “view” of our 2D map using a simple raycasting algorithm? The pipeline: Send a set of rays from each player’s eye into the world, and see which map tiles are visible Check which walls the player sees, rendering them at the correct height and more or less solid based on the distance Project mobs into the player’s camera space Select sprite LODs based on depth Expand sprites into pixels, scaled to screen space Occlude against walls and other sprites Assemble frame buffer rows with string_agg Build a minimap reusing the visible tiles calculation from earlier Combine the 3D view with minimap and HUD (HP/bullets/players) into a game view Let’s take a more in-depth look at steps 2, 7, and 8.
Raycasting
The recursive ray‑marching logic is adapted from Patrick’s DuckDB DOOM post. Here is a simplified excerpt, adapted for multiplayer: CREATE OR REPLACE VIEW visible_tiles AS WITH RECURSIVE raytrace AS ( -- Starting at the player's eye ... SELECT r.player_id, r.col, 1 AS step_count, r.player_x + COS(r.angle) * s.step AS fx, r.player_y + SIN(r.angle) * s.step AS fy, r.angle, 0 AS dist FROM rays r, settings s -- rays are built in an earlier step UNION ALL -- ... we recursively march along the rays, 1 "step" at a time ... SELECT rt.player_id, rt.col, rt.step_count + 1 , rt.fx + COS(rt.angle) * s.step, rt.fy + SIN(rt.angle) * s.step, rt.angle, step_count * s.step * COS(rt.angle - m.dir) AS dist FROM raytrace rt, settings s, players p, mobs m WHERE rt.step_count < s.max_steps -- ... stopping after our max render distance AND rt.player_id = p.id AND m.id = p.id AND NOT EXISTS ( -- or if we hit a wall SELECT 1 FROM map m WHERE m.x = CAST (rt.fx AS INT ) AND m.y = CAST (rt.fy AS INT ) AND m.tile = '#' ) -- wall ) -- We then determine per player: -- a) which tiles we hit -- b) how far away these tiles are -- c) the column of the screen each tile should correspond to SELECT player_id, tile, CAST (fx AS INT ) AS tile_x, CAST (fy AS INT ) AS tile_y, col, MIN (dist) AS dist FROM raytrace rt, map m WHERE m.x = CAST (rt.fx AS INT ) AND m.y = CAST (rt.fy AS INT ) -- We might hit the same tile multiple times, so we take the closest hit GROUP BY player_id, tile_x, tile_y, tile, col; And that’s just the first step in the pipeline. For the rest, take a look at the code.
Final frame assembly
After all the heavy lifting, the payoff is surprisingly simple: SELECT player_id, y, string_agg(ch, '' ORDER BY x) AS row FROM framebuffer GROUP BY player_id, y; This glues together character pixels into text rows.
HUD + minimap
The same trick builds the HUD and minimap. Here is the health bar: 'HP: [' || repeat( '█' , LEAST( 20 , ROUND( 20 * GREATEST( 0 , LEAST(p.hp, 100 )):: numeric / 100 ):: int )) || repeat( ' ' , GREATEST( 0 , 20 - ROUND( 20 * GREATEST( 0 , LEAST(p.hp, 100 )):: numeric / 100 ):: int )) || '] ' || GREATEST( 0 , p.hp) Add ammo dots with repeat('•', p.ammo) and you’ve got a HUD entirely in SQL: 1: Lukas (L) score: 1 HP: [█████████ ] 50 AMMO: •••••••••• 2: Foobar (F) score: 0 HP: [████████████████████] 100 AMMO: •••••••• We can also re-use our earlier visible_tiles view to build a minimap with a view cone: select * from minimap where player_id = 1 order by y; player_id | y | row -----------+----+------------------------------------------------------------------ 1 | 0 | ################################################################ 1 | 1 | ################################################################ 1 | 2 | ##....... ##### ############################# 1 | 3 | ##.....F. ##### ##### ### 1 | 4 | ##....... ##### ##### ### 1 | 5 | ## ..... ##### ##### ### 1 | 6 | ## ... ### 1 | 7 | ## .L ### 1 | 8 | ## ##### ##### ### 1 | 9 | ## ##### ##### ### 1 | 10 | ## ############# ########## ### 1 | 11 | ########## ################ ########## ### 1 | 12 | ########## ################ ########## ### 1 | 13 | ########## ################ ###################### ########## 1 | 14 | #### ####### ###################### ########## 1 | 15 | #### ####### ###################### ########## 1 | 16 | #### ##### ##### ### 1 | 17 | #### ##### ##### ### 1 | 18 | #### ##### ##### ### 1 | 19 | #### ##### ##### ### 1 | 20 | #### ##### ##### ### 1 | 21 | #### ##### ### 1 | 22 | #### ### 1 | 23 | #### ##### ### 1 | 24 | #### ##### ##### ### 1 | 25 | #### ##### ##### ### 1 | 26 | #### ##### ##### ### 1 | 27 | #### ##### ##### ### 1 | 28 | #### ##### ##### ### 1 | 29 | ################################################################ 1 | 30 | ################################################################ 1 | 31 | ################################################################
The surprisingly elegant game loop
The loop is just a shell script running raw SQL against the database: # Game loop @ 30 ticks per second while true; do psql -qtAX -U " $DB_USER " -d " $DB_NAME " -h " $DB_HOST " -p " $DB_PORT " -f gameloop.sql sleep 0.03 done Inside gameloop.sql , actions like bullet movement, collisions, kills, and respawns run in a single transaction, which keeps state consistent even if something fails mid-tick. Here’s the part processing interactions with bullets: -- Process all bullets BEGIN TRANSACTION ; -- Move bullets forward UPDATE mobs SET x = x + cos(dir) * 0 . 5 , y = y + sin(dir) * 0 . 5 WHERE kind = 'bullet' ; -- Delete bullets that are out of bounds DELETE FROM mobs WHERE (x < 0 OR x >= ( select max (x) from map ) OR y < 0 OR y >= ( select max (y) from map )) AND kind = 'bullet' ; -- Delete bullets that hit walls DELETE FROM mobs b WHERE EXISTS ( SELECT 1 FROM map m WHERE m.x = CAST (b.x AS INT ) AND m.y = CAST (b.y AS INT ) AND m.tile = '#' ) AND kind = 'bullet' ; -- Players hit by a bullet loses 50 HP UPDATE players p SET hp = hp - 50 FROM collisions c WHERE p.id = c .player_id; -- If a player has 0 or less HP, the player killing them gets a point UPDATE players p SET score = score + 1 FROM collisions c WHERE p.id = c .bullet_owner AND EXISTS ( SELECT 1 FROM players p2 WHERE p2.id = c .player_id AND p2.hp <= 0 ); -- Delete bullets that hit players DELETE FROM mobs m USING collisions c WHERE m.id = c .bullet_id; -- Respawn players whose HP is 0 or less UPDATE mobs m SET x = r.x, y = r.y, dir = 0 FROM players p CROSS JOIN ( SELECT x, y FROM map WHERE tile = 'R' ORDER BY random() LIMIT 1 ) AS r WHERE m.id = p.id AND p.hp <= 0 ; -- Reset players' HP to 100 and ammo to 10 after respawn UPDATE players p SET hp = 100 , ammo = 10 FROM mobs m WHERE p.id = m.id AND p.hp <= 0 ; COMMIT ; On my machine, the game loop takes about 1 ms, so we could defintely improve the tick rate. That might be a way to get the Counterstrike snobs who scoff at everything below 128 Hz. It would require some refactoring on my part since I tied the movement speed to the game loop - a big no no in game design! While only someone insane could think a pure SQL raycasting renderer is a good idea in an actual game, I’ll happily defend this transactional game loop. I don’t think this part would be much more concise or less brittle in a real game engine.
Make it multiplayer in two queries
The game client’s job description is simple: Render SELECT full_row FROM screen WHERE player_id = < your_id > ORDER BY y Send input INSERT INTO inputs(player_id, action) VALUES ( < your_id > , < pressed_key > ) ON CONFLICT(player_id) DO UPDATE SET action = EXCLUDED.action The game loop periodically checks the input table and moves all players accordingly - inside a transaction, of course, so we don’t run into any race conditions. That’s it (well, plus a one-time “create player” on first connect). The ~150 lines of Python in the client mostly handle keyboard input and reducing terminal flicker. Bonus: The client provides an observer mode. All it has to do is swap the in the render call.
Performance
At 128 x 64 pixels, a single player view takes ~33 ms on my machine, which is enough for a breezy ~30 FPS, compared to DuckDB DOOM’s 8 FPS at just 32 x 16 pixels. I’m actually quite proud of that performance and quite happy with CedarDB here. I don’t think any other database system can keep up with that. Let me know if you find one! You might worry that rendering the views of all players and filtering late would be very wasteful. CedarDB’s query optimizer pushes the where player_id = <...> predicate through view boundaries, avoiding unncessary work. You can easily check by running: select * from screen order by y; -- render both users -- Time: 57,907 ms (~2x single player 33ms)
The cheating metagame
Because clients send raw SQL as superusers (I didn’t bother setting up any role based access control or row level security, don’t do that!), there’s an emergent metagame: Cheat creatively and try not to get caught. Low effort: update players set score = 0 where id != < your_id > ; update players set hp = 0 where id != < your_id > ; Mischievous: update inputs set action = null where player_id != < your_id > ; Steal kills: update mobs set owner = < your_id > where kind = 'bullet' ; Attempted but didn’t work: DELETE FROM mobs m USING collisions c WHERE m.id = c .bullet_id AND c .player_id = < your_id > ; This doesn’t work because moving bullets, checking for collisions, and respawn happens in the same transaction. As transactions are atomic, you either see everything being applied at once, or nothing. By the time you see the hit, you’re already dead. A property that’s very useful for database systems (and not just to prevent cheating).
What I learned
I set out to see if I could push Patrick’s demo to an extreme: Doing the entire rendering pipeline in SQL. And while it works, I have to admit that it is a pretty… bad idea? Fast enough, but horrible to maintain and debug. The surprise was how natural it felt to express game state and logic in SQL. It even felt like accidentally re-invented the entity-component-system pattern.
And multiplayer “just worked” because the database system which handles all the nasty concurrency is the source of truth.
Try it yourself!