Hacker News Reader

Can you keep an open mind? We’ve used stored procedures for years. It has worked wonderfully for creating a single source and producer of truth for business data. Instead of potentially having business logic across multiple repos and deployments, everything exists in one place, with absolute unquestionable authority.

It’s not difficult to debug at all, you might just be unskilled.

> It’s not difficult to debug at all, you might just be unskilled.

I agree with your larger point but this seems too harsh: it’s definitely harder to debug simply because, as with microservices, understanding how the app is functioning now requires you to understand different code in multiple languages and locations, you’re highly likely to hit non-portable behavior across databases for authoring and debugging, and you’re never going to get a debugger with the whole flow in context.

That doesn’t mean there aren’t benefits as well and it could be especially useful as a way to force distinctions about contracts for common operations, but I wouldn’t say it’s right for all or even most projects. The sweet spot is going to vary widely.

The tradeoff seems to be „ability to deploy working software without reliance on single central authority”. You may get rid of several smaller bottlenecks this way, introducing an enormous, all-encompassing one. Or am I wrong?

> Databases should never, ever, ever, be used to perform logic

You're talking about a best practice like it's a fundamental law. It's not, it's just how we've mostly been doing things. A lot of interesting innovations in distributed systems / architecture (serverless, graphql, thin clients, thick clients, ORMs, RSC, WSGI, nodejs) have been made because the designers tried relaxing a constraint or taking a counterintuitive idea to a maximalist place.

In fact, if you look harder, there are a fair number of existence proofs of successful systems built on stored procedures. There's even a "best practice" phrase recommending doing compute as close as possible to the data.

Stored procedures ensure all your clients get the same logic. They're only "the worst feature of any database" if the language you're writing them in is not suitable (which is the case with PL/SQL and co, which were tacked on to RDBs and reek of bad 80s syntax and facilities).

If the language is nice and has well designed access to db facilities like records and such, it can be better than writing your code outside the database, especially coupled with a data-oriented design model/ECS (which can be extremely debuggable and offer great visibility).

>and increasing the chance that you will fuck up the most valuable part of your system, your data.

Since you can write anything outside you can inside, no. I can send a "delete from/drop <table>" from any client at any moment, or make any mistake in updating. That's what backups are for, and databases make them even easier (not to mention transactions being very good and neglected part of business logic).

> Why? Databases should never, ever, ever, be used to perform logic, they are datastores, that is it. Your logic goes elsewhere. Stored procedures are the worst "feature" of any database, you are just asking for a hard time debugging, troubleshooting, and increasing the chance that you will fuck up the most valuable part of your system, your data.

You cannot sanely use a database with multiple heterogenous clients without putting logic at least about what valid data should be in it. This is gonna include some “business logic” in practically any real-world system.

Otherwise you have to elevate the same functionality to some gatekeeper-daemon that’ll almost certainly perform far worse, lack features, and be an eternal source of dumb bugs, including, I can just about guarantee, data corruption bugs.

This is of course what SpacetimeDB does. The stored procedures fail any transactions that are not authorized to be carried out. e.g. if a player is not high enough level or something.

> Databases should never, ever, ever, be used to perform logic, they are datastores, that is it.

I wouldn't go that far. Relational algebra is performing logic. Constraints and foreign keys are logic, as well.

I'm not going to argue that you should go back in time 15-20 years and start shredding XML strings in stored procedures again. But thinking of the database as one step above a flat file is similarly backward thinking.

More than that, the concept of putting application logic adjacent to the data store is sound. That's exactly what a web API or a microservice is doing. They allow a uniform mechanism of requests and responses to a data store. At a concept level, that's exactly the same thing. The concept of keeping logic at or immediately adjacent to the data layer so that a whole range of disparate applications can request and maintain data from the source is what the design goal of "database side logic" is.

> At a concept level, that's exactly the same thing.

But at a practical level they very different. If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc).

I'm not hard-core against stored procedures used lightly but they have a lot of downsides and they simply aren't needed. There's no performance advantage. There are complexity advantages and disadvantages that might be a wash.

>If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc).

And nothing stops a DB internal language used to write store procedures and such be a "real language" -- with source control, debugging and everything. RDBs can do a whole lot more offering an even better environment for those than common RDBs do - Smalltalk or Symbolics level good.

In fact in the case of this project, that language is Rust.

> If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc).

I'm not sure what you're using, but you can absolutely use source control for stored procedures. There's any number of database change management tools available. You should already be using one for your schema. Stored procedure debugging tools also exist for most platforms.

I'm not really interested in the "it's not a real programming language" topic. That's almost universally someone going on an ego trip about what they like. If your point is, "We get to use a single language that the whole team is experienced and familiar with," then sure that's valuable. But that's about the team's capabilities more than anything.

> There's no performance advantage.

No, that's an outrageous claim. I've seen and implemented some processes as stored procedures and in some of those cases it's worked much better simply because we don't have to pull the data pool out of the cloud and across the country to wherever the CPU is, manipulate it, and then push it all back up to the data store.

Stored procedures are not some universal panacea that the 4GL crowd wanted them to be, but it's also not something that's universally worse.

> If you have a middleware layer as you are describing, it's written in a real programming language with all the adjacent tools (source control, debugging, etc).

This is very much the point of SpacetimeDB. We write stored procedures in Rust or C# (or theoretically, any language that compiles to WASM), and we get all the same developer tools we're used to.

> There's no performance advantage.

There very much is a performance advantage to executing WASM in-process compared to communicating over an IPC barrier or, god forbid, a network.

Nothing you mentioned is an inherent problem with the databases. In fact, databases have arguably more potential to solve these problems then any currently used system.

Like debugging - if every memory access is a database access, then you have a builtin logging for all your memory access that is both more performant and optimal then any normal normal debugger. You can take snapshots of your 'memory' pretty much at any time, the data layout is clearly defined. You can manually edit your memory any time you want, and serialization is already solved for you.

The more I think about it, the more possibilities I come up with. Damn, this is actually genius!

I'm writing a quite similar system to this so I can give the differences why this is useful (esp for this usecase).

1: Separating stores was crucial during the 90s and earlier when people were still writing in memory-unsafe languages (C/C++ cough) since it could cause wild corruptions with simple stray pointers. Process-separation was just a sanity thing. As you notice these are other languages in play here so random corruptions shouldn't happen (memory exhaustion can still be a thing though with their model)

2: Yes, debugging stored procedures/triggers on SQL-Server,etc is a PITA because they're database first centric objects, however the idea here is to make the database fill the job of app-servers of the 90s/00s with "regular" debugging workflows for developers. (Don't confuse implementations with the concept)

3: And MOST importantly, this is a game-focused thing, gamedevs will often end up replicating most cache/database functionality (badly) to squeeze things into main memory with the goal to achieve realtime performance targets anyhow, why not forgo that duplicated crazy work with a solid framework?

4: As a corollary to the above, the benefits of separating storage from applications (to run multiple applications against the dataset as it often happens in enterprise scenarios) isn't really the focus, application to database mappings are intended to be more or less 1:1

> It also means you have a single point of failure, no read-replicas or redundancy. Hate everything about this.

How does writing an application as a single language/binary prevent read-replicas or redundancy?

The application could do that.

Or you could do it at disk level (RAID).

>The application could do that.

How do you manage multiple instances of the application and not introduce split brain?

> Or you could do it at disk level (RAID).

That is not comparable. RAID's redundancy is not the same as the redundancy in a multi node database cluster. You have one service, not multiple services, network card gets fried, your database goes down, you can't promote a standby to master and be on your way. Also RAID is a single disk as far as the OS is concerned, so you could hit I/O limits (especially if you have a single binary) that cause your app to chug, you cannot split your writes and reads across different physical or virtual machines that have different disks.

> you are just asking for a hard time debugging, troubleshooting, and increasing the chance that you will fuck up the most valuable part of your system, your data.

This sounds like a tooling problem. One could imagine a database that doesn't have these issues.

> It also means you have a single point of failure, no read-replicas or redundancy.

What? Why would any of this be the case?

> Why?

less moving parts, diversity and complexity in your stack (different languages, servers, build/deployment systems, etc).

> you are just asking for a hard time debugging, troubleshooting

what specifically hard about this in your opinion?..