> I was surprised how easy it is to express pretty complicated game logic in SQL. The game logic is just ~5900 lines of SQL.
I still think HN is taking major naps on the capabilities of contemporary SQL.
There are businesses so complicated that maintaining procedural code over the domain is largely infeasible. Implementing business rules in SQL can decompose the problem in ways that allow for a lot more people to interact with it at the same time.
When I was working in semiconductor manufacturing, we relied very heavily on stored procedures and SQL to operate the factory. Very little operational decision logic existed in code. We had hundreds of users who were inspecting and proposing changes to the same set of procedures. Testing this stuff was trivial because we replicated the prod DB every morning and experimented against live data directly. There was no gap between the information of the business and its logic. Most shops are not ran this way. They treat the database like some CRUD retrieval engine instead of the nexus of both the data and logic.
When people advocate for spending big piles of money with Microsoft, Oracle and IBM, they are generally going for something like the above. They want literally one system the business operates inside of. Spreading a solution across 10+ vendors and tools when you could do with one is borderline negligence depending on your role in the organization.
There are very few things I would want to do less than complicated logic in SQL. At least these days we get the alternative of SpacetimeDB functions https://spacetimedb.com/docs/functions But having everything in stringly typed environment with minimal stdlib in things like mssql? Yeah, there's a reason why it's not a popular pattern.
> There are very few things I would want to do less than complicated logic in SQL.
Would you change your mind if I told you it would be giant strings with a bunch of `+ @database_name +` scattered around? Syntax highlighting is a crutch imo
(yes I refactored it to be parameterised yada, but for years those 2000 lines had grown and grown)
At my current job I'm in charge of developing our data analytics platform, and with it being so data-driven I was able to implement like 95% of the logic directly into the database largely as functions and stored procedures. The remaining 5% of the application code is mostly Python, which merely acts as a connector from an HTTP gateway to call said functions and render their output as XLSX files. It's amazing how much I was able to do with mostly SQL (I still had to break down and use plpgsql at times to handle the more procedural stuff, but that was the exception).
And now I'm in the process of replacing some of the more intensive data analytical stuff with DuckDB, which has been just an awesome experience.
Its always a very interesting architectural question when deciding at what level(s) the business logic should live. There absolutely are valid reasons for some to go in SQL, though I tend to avoid putting the most complex logic there when its really tricky.
When it really gets hairy, or when the business logic keeps changing under my feet, I'll try to find constraints I can put in the db as a final backstop while leaving most of the logic somewhere in the application stack.
I can count on one hand the number of people I've worked with that really know SQL well enough to pick up complex business logic at that layer and work with it easily. I've been mainly in small companies for the last decade, I'm sure at larger orgs there are more data engineers running around that could own it.
I hated debugging and fixing stored procedures for legacy 2000s apps in de 2010s. The SP code wasn't treated like regular code, it wasn't source controlled. Mostly bad memories from having business logic inside the DB instead of having it all inside the code.
I have the same memories from many years of working with stored procedures - although you should certainly have been using source control.
But nothing quite matches the satisfaction of someone in a far off engineering group reporting a problem that prevented them from committing data that would have corrupted the database - because your referential integrity constraints protected the database.
In the age of AI and a massive explosion of code touching the database, maybe it makes even more sense to embed logic and constraints inside the database where there's no way to get around them.
You may be storing logic in your database, but most businesses store data in their databases, and the amount of data they store (mostly because they can't tell what's important and what's not) makes copy-pasting their database every morning pretty unrealistic.
Probably because you didn't account for that when architecting the system. Obviously it shouldn't be problematic but this is the real world full of poor technical decisions and mildly incompetent management that we're talking about here.
We do this as well. There are some downsides of course, but overall it has worked out well for years. Funny we were told the use of stored procedures is a problem and the reason the product should be retired. You should see the dependency graph of the replacement.
True. The kind of guarantees you can get when staying within the database can often solve whole categories of problems. But, DX of having non-trivial logic inside Postgres is not great either: I feel that I'm making a significant trade-off. I've also seen interesting languages that compile to SQL, would consider those in some cases.
> There are businesses so complicated that maintaining procedural code over the domain is largely infeasible
And those businesses can have all the pain of intertwingling their logic with their data model, too, wheee!
This is a cultural decision, not a technical decision, and unless I'm in the mood for a particular kind of swampy adventure in someone else's land, one I stay away from.
Yes, this is fantastic! I'm trying it right now. I look forward to the day when LLM inferencing can be done in a sql query and take less than the age of the earth to do something. This works way better than I would have expected, since, well, it's sql all the way down.
I noticed the cedardb.com blogpost on the project is slashdotted at the moment, but the game itself plays just fine.
Off topic, but say you have a program that needs to calculate something but takes a long long time, how to counter hardware failure without needing to calculate everything from the start?
Typically you use redundant computations, which don't have to be done from the start. You could calculate intermediate values and check them before proceeding
They converted linear algebra expressions into relational algebra expressions, optimized them using equality saturation and converted back into linear algebra. With some noticeable speedups.
They used relational algebra for optimization because relational algebra has canonical form - there is only one most optimal expression.
And, equality saturation is most efficiently implemented using generic join algorithm.
The game state being a SQL table just kinda triggered a memory of a year of optimization for me. One thing I'm still unsure of being a good decision or a bad one, when I wrote my casino in 2010, was having every remote call update game states on SQL tables that were used as the source of truth. With multiple players you can imagine that there would sometimes be issues. Some of the deadlock problems early on were horrific; scaling was a nightmare. But everything was atomic. No risk of lost data beyond one turn not reaching the server or deadlocking, nothing like a huge nodejs process choking on everyone's calls at the same time, or losing its memory. You always had state.
Looking back it seems like not a terrible design pattern for multiplayer turn-based games, if you can work out the kinks. Atomicity guarantees at least that there is a consistent state that won't get lost. Doing that read/write loop for an action game? Pure folly, but it's pretty funny to me.
There is a whole chapter in Designing Data Intensive Applications dedicated specifically to handling atomic transaction concurrency issues while avoiding the performance trade-off you're describing here, you will definitely find it interesting.
You mean how instead of fun hacker projects like this we get daily model "updates" and people immediately simping/hating on them based on 5 minute vibes?
> The game logic is just ~5900 lines of SQL. While this sounds a lot, it’s definitely less than the original C source code which does the same in about 9000 lines!
I recommend trying prolog for yourself to find out. It's pretty mind-bending what it can accomplish and how concise the resulting programs tend to be. I've never needed more than a few hundred lines.
I was actually just recently looking if there is a self-hostable alternative to a HTAP system like TiDB + TiFlash with a Postgres-compatible syntax, but LLMs didn't really pick up on CedarDB yet. These guys know game.
Very impressive work. According to the readme you need cedardb community edition to run it yourself. I wonder if it will also work with other database engines or does it use some specific features from cedardb?
With LLMs none of these are that impressive anymore. I want to see a triple A game created from scratch in CSS. A game as good and large as say Witcher 3 or Zelda totk or red dead redemption 2. When I see that… then I’ll be impressed.
> I was surprised how easy it is to express pretty complicated game logic in SQL. The game logic is just ~5900 lines of SQL.
I still think HN is taking major naps on the capabilities of contemporary SQL.
There are businesses so complicated that maintaining procedural code over the domain is largely infeasible. Implementing business rules in SQL can decompose the problem in ways that allow for a lot more people to interact with it at the same time.
When I was working in semiconductor manufacturing, we relied very heavily on stored procedures and SQL to operate the factory. Very little operational decision logic existed in code. We had hundreds of users who were inspecting and proposing changes to the same set of procedures. Testing this stuff was trivial because we replicated the prod DB every morning and experimented against live data directly. There was no gap between the information of the business and its logic. Most shops are not ran this way. They treat the database like some CRUD retrieval engine instead of the nexus of both the data and logic.
When people advocate for spending big piles of money with Microsoft, Oracle and IBM, they are generally going for something like the above. They want literally one system the business operates inside of. Spreading a solution across 10+ vendors and tools when you could do with one is borderline negligence depending on your role in the organization.
There are very few things I would want to do less than complicated logic in SQL. At least these days we get the alternative of SpacetimeDB functions https://spacetimedb.com/docs/functions But having everything in stringly typed environment with minimal stdlib in things like mssql? Yeah, there's a reason why it's not a popular pattern.
That's why I hated working with supabase so much. It's just so much more complicated to work with
> There are very few things I would want to do less than complicated logic in SQL.
Would you change your mind if I told you it would be giant strings with a bunch of `+ @database_name +` scattered around? Syntax highlighting is a crutch imo
(yes I refactored it to be parameterised yada, but for years those 2000 lines had grown and grown)
At my current job I'm in charge of developing our data analytics platform, and with it being so data-driven I was able to implement like 95% of the logic directly into the database largely as functions and stored procedures. The remaining 5% of the application code is mostly Python, which merely acts as a connector from an HTTP gateway to call said functions and render their output as XLSX files. It's amazing how much I was able to do with mostly SQL (I still had to break down and use plpgsql at times to handle the more procedural stuff, but that was the exception).
And now I'm in the process of replacing some of the more intensive data analytical stuff with DuckDB, which has been just an awesome experience.
Long live SQL!
Its always a very interesting architectural question when deciding at what level(s) the business logic should live. There absolutely are valid reasons for some to go in SQL, though I tend to avoid putting the most complex logic there when its really tricky.
When it really gets hairy, or when the business logic keeps changing under my feet, I'll try to find constraints I can put in the db as a final backstop while leaving most of the logic somewhere in the application stack.
I can count on one hand the number of people I've worked with that really know SQL well enough to pick up complex business logic at that layer and work with it easily. I've been mainly in small companies for the last decade, I'm sure at larger orgs there are more data engineers running around that could own it.
I hated debugging and fixing stored procedures for legacy 2000s apps in de 2010s. The SP code wasn't treated like regular code, it wasn't source controlled. Mostly bad memories from having business logic inside the DB instead of having it all inside the code.
I have the same memories from many years of working with stored procedures - although you should certainly have been using source control.
But nothing quite matches the satisfaction of someone in a far off engineering group reporting a problem that prevented them from committing data that would have corrupted the database - because your referential integrity constraints protected the database.
In the age of AI and a massive explosion of code touching the database, maybe it makes even more sense to embed logic and constraints inside the database where there's no way to get around them.
> we replicated the prod DB every morning
You may be storing logic in your database, but most businesses store data in their databases, and the amount of data they store (mostly because they can't tell what's important and what's not) makes copy-pasting their database every morning pretty unrealistic.
Some large telcos are doing it. Why would replicating a database daily be problematic?
Probably because you didn't account for that when architecting the system. Obviously it shouldn't be problematic but this is the real world full of poor technical decisions and mildly incompetent management that we're talking about here.
We do this as well. There are some downsides of course, but overall it has worked out well for years. Funny we were told the use of stored procedures is a problem and the reason the product should be retired. You should see the dependency graph of the replacement.
True. The kind of guarantees you can get when staying within the database can often solve whole categories of problems. But, DX of having non-trivial logic inside Postgres is not great either: I feel that I'm making a significant trade-off. I've also seen interesting languages that compile to SQL, would consider those in some cases.
Makes me think about git-based blob storage
> There are businesses so complicated that maintaining procedural code over the domain is largely infeasible
And those businesses can have all the pain of intertwingling their logic with their data model, too, wheee!
This is a cultural decision, not a technical decision, and unless I'm in the mood for a particular kind of swampy adventure in someone else's land, one I stay away from.
Less lines of code than vanilla C while abusing query planning as a state machine is peak engineering malpractice. I love it.
Yes, this is fantastic! I'm trying it right now. I look forward to the day when LLM inferencing can be done in a sql query and take less than the age of the earth to do something. This works way better than I would have expected, since, well, it's sql all the way down.
I noticed the cedardb.com blogpost on the project is slashdotted at the moment, but the game itself plays just fine.
Off topic, but say you have a program that needs to calculate something but takes a long long time, how to counter hardware failure without needing to calculate everything from the start?
Typically you use redundant computations, which don't have to be done from the start. You could calculate intermediate values and check them before proceeding
I think you could do that already. Just store your weights in a table...
Matrix multiplication is after all nothing but an aggregate sum over a cross join.
1. Write a query planner that compiles SQL into CUDA kernels
2. Write a VRAM-resident backend for Postgres
3. ???
4. Profit!
They converted linear algebra expressions into relational algebra expressions, optimized them using equality saturation and converted back into linear algebra. With some noticeable speedups.
They used relational algebra for optimization because relational algebra has canonical form - there is only one most optimal expression.
And, equality saturation is most efficiently implemented using generic join algorithm.
[flagged]
MY PEOPLE!
https://github.com/seanwevans/pg_shell
https://github.com/seanwevans/pg_gpt2
https://github.com/seanwevans/pg_os
https://github.com/seanwevans/pg_git
These are great.
The game state being a SQL table just kinda triggered a memory of a year of optimization for me. One thing I'm still unsure of being a good decision or a bad one, when I wrote my casino in 2010, was having every remote call update game states on SQL tables that were used as the source of truth. With multiple players you can imagine that there would sometimes be issues. Some of the deadlock problems early on were horrific; scaling was a nightmare. But everything was atomic. No risk of lost data beyond one turn not reaching the server or deadlocking, nothing like a huge nodejs process choking on everyone's calls at the same time, or losing its memory. You always had state.
Looking back it seems like not a terrible design pattern for multiplayer turn-based games, if you can work out the kinks. Atomicity guarantees at least that there is a consistent state that won't get lost. Doing that read/write loop for an action game? Pure folly, but it's pretty funny to me.
There is a whole chapter in Designing Data Intensive Applications dedicated specifically to handling atomic transaction concurrency issues while avoiding the performance trade-off you're describing here, you will definitely find it interesting.
https://www.oreilly.com/library/view/designing-data-intensiv...
If you squint and ignore the brain damage, SQL is kind of... functional.
Almost. In a way.
This is the kind of content I want to see on HN! Pure art.
Man hackernews really took a deep dive.
You mean how instead of fun hacker projects like this we get daily model "updates" and people immediately simping/hating on them based on 5 minute vibes?
Meanwhile I'm too inept to get WordPress to load dynamic content faster than molasses.
My go to comparison is going to a grocery store, asking for tomatoes, and the clerk needs to first pull them out of some shelf under the counter
It's not you, it's WordPress.
> The game logic is just ~5900 lines of SQL. While this sounds a lot, it’s definitely less than the original C source code which does the same in about 9000 lines!
Nice
Makes me wonder how good a better relational language than SQL might be for general programming.
I recommend trying prolog for yourself to find out. It's pretty mind-bending what it can accomplish and how concise the resulting programs tend to be. I've never needed more than a few hundred lines.
Nice piece of advertisement. Kind of hard to stand out in the world of SQL dbs. That definitely raised eyebrows.
I was actually just recently looking if there is a self-hostable alternative to a HTAP system like TiDB + TiFlash with a Postgres-compatible syntax, but LLMs didn't really pick up on CedarDB yet. These guys know game.
Very impressive work. According to the readme you need cedardb community edition to run it yourself. I wonder if it will also work with other database engines or does it use some specific features from cedardb?
This should be illegal :)))) Wow!
This reminds me of LINQ raytracer https://github.com/lukehoban/LINQ-raytracer
It even uses a ycombinator :D
> We ported the original Doom to SQL
Yeah, well, someone had to.
Now we get to ask "but does it run Doom" of every relational DB out there, right?
With LLMs none of these are that impressive anymore. I want to see a triple A game created from scratch in CSS. A game as good and large as say Witcher 3 or Zelda totk or red dead redemption 2. When I see that… then I’ll be impressed.
Does this count? https://github.com/rebane2001/x86CSS
No. Triple A game is way larger than this.
I want to point out that they set up an EU and US multiplayer server! Nice touch.
It's fascinating that it can be applied this way.
Thats ridiculous I love it. The visual in the bottom right it a great illustration.
if is TC it can run Doom.
Not necessarily in real time though
(This has been posted numerous times but none of them made the front page. I've made a new copy of the earliest one that got comments.)
[flagged]
[flagged]
[flagged]
[dead]
[dead]
"Query III Arena", please. /s
This but without the /s
^Query III Arena(, please)?$
“We did [thing extremely present in training data] in [language extremely present in training data]” is the peak form of AI engagement, it seems.