Abusing DuckDB-WASM by making SQL draw 3D graphics (Sort Of)

pjot

Author here, wild to see this at the top of HN!

You can play it here: https://patricktrainer.github.io/duckdb-doom/

Pressing “L” enables (very) verbose logging in the dev console and prints much of the sql being executed.

adornKey

Finally somebody did it! Back in the day my attempts to write a game in SQL were thwarted by buggy query-optimizers. They cached my calls to rand() way too often although documentation promised not to do that.

dspillett

> They cached my calls to rand() way too often although documentation promised not to do that.

For some DBs (SQL Server definitely), RAND() and similar are handled as if they are deterministic and so are called once per use. For instance:

    SELECT TOP 10 RAND() FROM sys.objects
    SELECT TOP 10 RAND() FROM sys.objects

just returned ten lots of 0.680862566387624 and ten lots of 0.157039657790194.

    SELECT TOP 10 RAND(), RAND(), RAND()-RAND() FROM sys.objects

returns a different value for each column (0.451758385842036 & 0.0652620609942665, -0.536618123021777), so the optimisation is per use not per statement or even per column (if it were per column that last value would be 0, or as close to as floating point arithmetic oddities allow).

This surprises a lot of people when they try “… ORDER BY RAND()” and get the same order on each run.

One workaround for this is to use a non-deterministic function like NEWID(), though you need some extra jiggery-pokery to get a 0≤v<1 value to mimic rand:

    SELECT TOP 10 CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS BIGINT) AS FLOAT)/(4.0*1024*1024*1024) FROM sys.objects

For the example of sorting, the outer cast is not needed. You might think just using “ORDER BY NEWID()” would be sufficient, but that is an undefined behaviour so you shouldn't rely upon it. It might work now, a quick test has just worked as expected here, but at any point the optimiser could decide it is more efficient to consider all UUIDs as having the same weight for sorting purposes.

enescakir

Like running Doom on a printer, but now it’s in the same engine powering your BI dashboards. Peak 2025 energy.

dkga

Very interesting!

You know it gets wild when you read "... Here's the core of the raycasting algorithm in SQL"!

nonethewiser

Given the first post in the blog says "not made by a [ROBOT EMOJI]", should I assume this one which does not have this message, is made by a [ROBOT EMOJI]?

https://www.hey.earth/posts

NitpickLawyer

I swear we're gonna start seeing disclaimers like "100% handcrafted code, our devs eat only grass-fed beef, free-range devops teams, specialty coffee sustained QA department, no IDEs, no intelisense, we code in notepad++" soon...

andhuman

100% organic!

bstsb

it's the footer at the bottom of all pages. it's also present on the blog pages

pjot

Ha! I made this. I’m not a robot either :)

marcellus23

This one also has that same footer.

robertsdionne

https://x.com/geocucu_t/status/1909291486367166717

kevingadd

> But because tick() and render() involved async database calls, sometimes a new interval would fire before the previous one finished.

This is a tricky one when writing games using async APIs. The game I've been working on is written in C# but I occasionally hit the same issue when game code ends up needing async, where I have to carefully ensure that I don't kick off two asynchronous operations at once if they're going to interact with the same game state. In the old days all the APIs you're using would have been synchronous, but these days lots of libraries use async/await and/or promises and it kind of infects all the code around it.

It does depend on the sort of game you're building though. Some games end up naturally having a single 'main loop' you spend most of your time in, i.e. Doom where you spend all your time either navigating around the game world or looking at a pause/end-of-stage menu - in that case you can basically just have an is_menu_open bool in your update and draw routines, and if you load all your assets during your loading screen(s), nothing ever needs to be async.

Other games are more modal, and might have a dozen different menus/scenes (if not hundreds), i.e. something like Skyrim. And sometimes you have modals that can appear in multiple scenarios, like a settings menu, so you need to be able to start a modal loop in different contexts. You might have the player in a conversation with an NPC, and then during the conversation you show a popup menu asking them to choose what to say to the NPC, and they decide while the conversation menu is open they want to consult the conversation log, so you're opening a modal on top of a modal, and any modal might need to load some assets asynchronously before it appears...

In the old days you could solve a lot of this by starting a new main loop inside of the current one that would exit when the modal went away. Win32 modal dialogs work this way, for example (which can cause unpleasant re-entrant execution surprises if you trigger a modal in the wrong place). I'm still uncertain whether async/await is a good modern replacement for it.

null

[deleted]

r3tr0

We use duck db wasm to make live system performance dashboards based on eBPF.

It really is magic!

You can check it out here.

https://yeet.cx/play

dndn1

Neat UI, are you using a library for that?

r3tr0

nope. everything off shelf was too slow.

robertclaus

This is great! I did a similar project a while back to do image processing in a SQL database with pixels being individual records. It's amazing what SQL can do with the right table structures.

karmakaze

I'd like to see something like this done in SpacetimeDB which was made specifically for game backends. I haven't looked into it yet, only seen the 1.0 announcement on HN and in my YT feed, and curious how its feature set makes this sort of thing easier or more natural.

xnx

Impressive project, the subhead might attract even more attention: "Building a SQL-Powered Doom Clone in the Browser"