5 ms·

SQL cannot be "fixed". It's too broken, too ubiquitous, too customized, too optimized. Will these functors integrate with the thousands of optimizations modern

by feoren 2y ago

SQL cannot be "fixed". It's too broken, too ubiquitous, too customized, too optimized. Will these functors integrate with the thousands of optimizations modern database systems rely on? This post has the format: "SQL is extremely terribly awful at writing business logic. But if people implemented this one half-baked, unrealistic idea, then it would suck a little bit less!" The first part is spot-on: SQL is a horrible language for writing business logic. So: stop. Stop doing that. Stop writing business logic in SQL! Imagine you joined a new team, and they said: our program uses a bunch of XML files as its data format, and that's why most of our business logic is implemented in XPath. Just sit there and ponder that situation for a little bit. This is what you all sound like to me. Stop it! > Testing the simplest query requires building up a massive object graph, just to test an infinitesimal slice of it. Yes, correct, because SQL is awful at representing business logic, and foreign key constraints are business logic! The fact that an employee has one supervisor who is also an employee (FK Employee.SupervisorID -> Employee.ID) is a business constraint. (And one that can easily change, by the way.) All these database engineers who can't pull themselves away from their stored procedures will argue with me to their graves about foreign key constraints, but they're generally terrible. They're a business constraint that just happens to be supported by the database engine, but they're still a business constraint. Stop putting your business logic in your persistence layer. We've been getting better at building programming languages that actually support software engineering every year. Use one of those. SQL is an output of one of your program layers. Just like XPath would be an output of your program. Compose SQL in an actual real programming language that isn't horrible instead of doing any of this.

liontwist 2y ago
What is business logic? What specifically is SQL bad at? I’m not sold by your foreign key example. Constraints are great. I guess you can do that in python? Good like getting ACID right As soon as your data stops resembling a tree and starts looking like a graph than all your “business logic” looks a lot like operations on relations and you are back to where you started.
hobs 2y ago
I don't know why you are getting downvoted, and I generally don't like a lot of business logic in the persistence layer, but representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal. If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things. Databases with decent fks and constraints have a tendency to outlive the apps that build atop them, and communicating these constraints in long lived fashions can be ... good actually.
- feoren 2y ago
  > representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal "Normal" has never been compelling in a world where most software sucks and most projects overrun their budgets. > If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things Stop doing those things too! > Databases with decent fks and constraints have a tendency to outlive the apps that build atop them This is oft repeated, but I think there are some flaws with it: 1. I'd ask the question: is that because those apps sucked? It's possible to have a mediocre database and then an even worse app on top of it; in that case it wouldn't be surprising if the app itself died first. 2. Did the next app use the existing schema as-is, or did it adapt/migrate the schema to a different form that worked for that next app? There's a big difference between saying "this data turned out to be useful for other purposes" (of course!) and "this data representation turned out to be so perfect and elegant that we kept using it" 3. Do you have an overly narrow definition of "apps" here? I'd argue if six teams are all doing their own things with the database -- some writing code, some building views, some hooking into it with Excel and PowerBI -- then I'm not overly interested in the fate of the "OG app" that the database was built for. If that one dies, you still have five other "apps" using that database. We're not talking about a schema sticking around because it's so useful, we're talking about an entrenched, locked-in tech that nobody can change because it's too costly. That's a predictable symptom of a bad representation! That's exactly what happens with bad code! Good systems can handle change in isolated parts without breaking things for everybody else. Database schemas are so entangled with themselves, and SQL is so bereft of proper abstraction, that they can't change easily once they have many users.
default-kramer 2y ago
> Compose SQL in an actual real programming language that isn't horrible instead of doing any of this. Got any recommendations? That's a serious question, I've tried many (and built my own) and some are okay but none feel remotely good enough when you consider how much progress we've made basically everywhere else in the wider world of software engineering. Relational databases are so good; they deserve better than SQL!
- jeltz 2y ago
  I have also tried many and so far none have even managed to be as good as the kludge that is SQL.
- feoren 2y ago
  > Got any recommendations? Find a good ORM that allows you to keep things at the "set logic" level for as long as possible and has a good internal query representation. Avoid or turn off any fancier features of the ORM like "navigation properties" or "implicit subtyping" or automatically detecting changes or anything that happens "magically" in the background: prefer simplicity and being explicit about everything. For C#, this is EntityFramework with most of its features turned off -- compose queries by joining IQueryables together, and only drop down into in-memory iteration when you have to. If that's not available, I'd probably look for a library that presents a structured representation of the SQL abstract syntax tree (probably a subset) as an object, along with a translation to the various dialects. Pass those ASTs around and compose them together. I'd probably slowly build abstractions on top of this that eventually look similar to what I described in the first paragraph.
- efromvt 2y ago
  Shameless plug if you've tried a range of alternatives: I've tried building a version (Trilogy) targeted at analytics, and I'd be curious about how it stacks up to your experience with other attempts at this - can read more about the composition/reuse story here: https://trilogydata.dev/blog/composability_reuse.html https://trilogydata.dev/blog/composability_reuse.html
  - default-kramer 2y ago
halflife 2y ago
100% completely agree. You just can’t scale SQL code. If you have a small app, then sure. But then I don’t know why you’d invest so much in testing. But if you need to scale your code to more than 3 people, SQL should only be your persistent state store. You probably already have a BL layer in another, more manageable programming language, so use that. In my team I set a rule to remove all SQL functions and views, and move all the logic to our typescript backend. That resulted in code which is much easier to test, and is version controlled.
- agluszak 2y ago
  And what are the results of your decision in terms of performance?
  - cyberax 2y ago
    These days? It doesn't really matter. For most purposes, you can just hold the entire important dataset in RAM. For example, a humongous company with 500000 employees (Amazon) can trivially hold on a fairly low-end server the database of all the personnel, and all their salary payments, and all their employment history, badge photos, and office access logs.
  - halflife 2y ago
    The DB is rarely our bottleneck. Our reads are very simple, mostly paged sets from a table with a 1 level join. When we need complex abstractions or transformations, instead of using function or views, we listen to table changes, and with a TS transformation layer we save a new row in a specific table for the abstraction. That way the BL is handled in TS, and performed in writes asynchronously instead of in-line in reads. The only downside is that the transformed data is not real time. That mechanism allowed us to scale our data very easily without any performance hits.
- baq 2y ago
  Yeah that's what people who don't have much data do with good results. If you can do that, it's the right choice. Once you have data you move code to data and not data to code. It isn't a decision you get to make. You only decide how you do it.
wvenable 2y ago
> Yes, correct, because SQL is awful at representing business logic, and foreign key constraints are business logic! This strikes me as a unnecessary all-or-nothing argument. I would agree that you shouldn't put all your business logic in the database but that doesn't mean you shouldn't put any business logic in your database. If foreign key constraints are business logic then so are table definitions and column types. It's not awful for those things as it's literally built right in. My advice is to ensure, as best as possible, that invalid states are impossible to represent. If you can't have an address without being attached to a client, make that impossible to represent in the database. It's one less thing you'll ever have to worry about.
- feoren 2y ago
  > This strikes me as a unnecessary all-or-nothing argument I completely understand why it comes across that way. HN Comment length is limited and the current practice is a comfy local optimum, so any one change away from it is going to feel bad, and it's hard to get across my full theory of database design in comment form. I would not recommend most existing teams simply stop using foreign keys, unless they also adapted a lot of other changes that coexist with it. Still, there is a better world for database design, and that world probably does not use foreign key constraints. > I would agree that you shouldn't put all your business logic in the database but that doesn't mean you shouldn't put any business logic in your database. I actually would (lightly) argue that does mean you shouldn't put any business logic in your database, as I find that highly coherent design philosophy leads to highly coherent code. The most important question to me is about how the system can handle change over time. Foreign keys are one part of a larger set of "design principles" that everyone seems to take for granted that lead to a system that cannot handle change. > If foreign key constraints are business logic then so are table definitions and column types. I disagree on this one. Table definitions and column types are a "level below" business logic, as evidenced by the fact that you could probably refactor a lot of schemas for better/different performance characteristics without changing any of the actual business logic that drive them. The business logic doesn't (shouldn't) care whether you've used a bitfield or a bunch of boolean columns or a child set-inclusion table to represent a bunch of yes/no facts about some entity. They're "persistence logic", and the database is exactly the right place for that. > My advice is to ensure, as best as possible, that invalid states are impossible to represent. The problem is that an "invalid state" is highly dependent on your perspective. A state that is invalid to use in one part of your codebase is perfectly valid in other parts. The ubiquity of "draft", "draft final", and "final" shows that humans want to represent "invalid states" all the time! Aren't you glad your IDE lets you represent invalid code? Okay, fine, that data is not ready for the annual submittal, but does that mean I'm really not allowed to save it at all? And these "invalid states" are extremely volatile as your focus expands around your domain. All contact info must have a primary contact method -- well, unless they're just "contact stubs". All facilities need a facility manager -- well, unless they're 3rd party facilities, or storage facilities, or in Region 9, or start with the letter 'P'. Haven't you been 300 hours into the development of a system when suddenly someone springs on you, "Oh yeah, Bob has two bosses. Why should that be a problem?" And you're screaming internally, "Of course it's a problem! You assured me six months ago that everyone only has one supervisor, and now you're telling me this one guy has two!? This changes EVERYTHING!" But why? Why does it change everything? Why does every little change to our database schemas always seem to propagate to the entire system? It's because we're designing databases wrong. And foreign keys are a (small) part of that wrongness.
FridgeSeal 2y ago
I think you’re throwing the baby out with the bath water here a bit. I agree that stored-price are rarely, if ever the solution. I do however think that having something that enforces certain constraints across all your data, and forces you to handle schema evolutions reasonably over time is really powerful. Database implementations are (mostly) full of hard-fought correctness decisions, and I don’t think most devs are in a position to “whip out” an implementation that cares for a number of these on-demand. We had the whole NoSQL thing, we saw what when we threw most of this stuff away. Is sql good enough? For many problems yeah. Is it _really_ good enough? Nah probably not, we could do a lot better, but I think a better design looks more like a better sql-like language and less like a full programming language.
dagss 2y ago
I agree that SQL has problems as a language. I disagree that we should be happy about moving data out of the storage layer into the backend in cases where it does not make sense performance wise. The problem is doing some processing in SQL in some cases has a huge performance advantages over moving data to the backend. If your business logic requires you to take a million rows from table A, apply some rule, and output a million rows into table B -- then piping all that data through a backend is a bad solution performance wise. I reject the idea that all that extra IO (+extra overhead also for the database) is worth it just to use another programming language than SQL. What we need is better alternatives that execute at the storage layer. Probably starting with proper languages that compile to SQL, like we saw with JavaScript.
geysersam 2y ago
I think the fact that these functions/functors/macros already exist in a few different varieties in mainstream database systems presents a challenge to your argument. SQL can be fixed, has been fixed, and is a underutilized tool in my personal opinion.
- feoren 2y ago
  > SQL can be fixed, has been fixed It has? Can you point me to an example?
  - geysersam 2y ago
    I meant in the sense that the additions suggested by the blog post author already exist in multiple sql implementations.
tacone 2y ago
> Imagine you joined a new team, and they said: our program uses a bunch of XML files as its data format, and that's why most of our business logic is implemented in XPath. Just sit there and ponder that situation for a little bit. Been there 20 years ago! It sucks at unimaginable levels!
magicalhippo 2y ago
Agree not to putting your business logic in stored procs, but hard neg on getting rid of all constraints. I consider it defense in depth. Sure your application should absolutely ensure order lines aren't referencing a non-existing order, but bugs happen and I prefer the database returning an error to the database getting full of inconsistent data.
dicytea 2y ago
I agree with most of this, but I don't understand why you'd treat FKs as business logic. IMO, it's clearly a part of the data structure. Like, say if you have a domain that looks like: type User = { username: string; posts: Post[]; } type Post = { content: string; } (for the DDD-brained, assume User is an aggregate and Post is a value object here) The natural way to represent this in the database is: CREATE TABLE users ( username text PRIMARY KEY ); CREATE TABLE posts ( author text NOT NULL REFERENCES users (username), content text NOT NULL ); I just don't see why you'd do it in any other way.
- feoren 2y ago
  <BossVoice> Hey dicytea, thanks for getting that blogging app up. Our users love it. Hey, I was talking to John, and he said he's got some guest columns he wants you to throw up there. Just some blog posts from thinkers he likes that he thinks would be a good fit for our site. Can you throw up some of those? What do you mean they need to be users of the site? They're just some people whose posts we want to feature. No, they shouldn't need to log in. What are you talking about? What does that have to do with it? </BossVoice> Oops, turns out the rule "all blog post authors are users of the site" was actually just a volatile business rule after all.
  - lelanthran 2y ago
    Yes, and isn't it wonderful that you get an error message when you try to change a business rule, forcing you to properly encode the new rule instead? A lot of these types of scenarios are missing the fact that, without these enforcements in the database, sooner or later a developer is going to make a change that violates an existing business rule without realising that they just broke a rule! Rules change. We know this. What is valuable is being told that some new rule conflicts with an existing rule. If you don't enforce the business rules in the database, how do you know when a new rule conflicts with some existing rule?
branko_d 2y ago
I don't understand what you are trying to say about foreign keys. Are you advocating moving FKs from database to application? If yes, you are opening yourself up to race conditions that will only become visible in concurrent environment. I had a distinct misfortune of working with an ERP system based on Oracle which didn't use database-level FKs. Predictably, their customers would occasionally complain about "dangling" data, which, to my knowledge, has never been properly resolved.
- threatofrain 2y ago
  It's inevitable that some data will be split between more than one service / database. I'm not saying we should have data whimsically split up, but foreign key constraint logic must eventually come to live in the app layer too.
- thyristan 2y ago
  It is possible to move foreign keys and other constraints and consistency checks into the application. But that comes at a huge cost: Either you need to be generous with your use of transactions to ensure that no invalid state is ever written, and your application has to know and use those transactions. Which is something application developers in my experience often fail at. Or you have to accept that broken inconsistent data is a fact of life that can happen at any time, and will increase in times of high throughput and concurrency (where the business part of the company will care most about things running smoothly). Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely. There is also a cost of doing constraints and consistency in SQL, the language is somewhat limited in what you can easily represent (anything is possible, but only some things are easy). And the handling of violations still needs to be implemented in the application, which often necessitates at least some duplication and fiddling to e.g. get friendly and useful error messages. So imho, yes, SQL has its problems, but it is still the lesser evil in almost all cases.
  - feoren 2y ago
    > Or you have to accept that broken inconsistent data is a fact of life that can happen at any time You have to accept this anyway. Data can be broken and inconsistent in so many different ways that you cannot protect against every possible way. People will type the wrong thing in sometimes, in ways you never expected. Someone pasting text into the wrong field is just as "broken and inconsistent" as an ID field pointing to a nonexistent entity. How important those errors are to the business depends on how important those columns are, not whether they're an ID vs. text. And as another person pointed out, data always ends up split/replicated among multiple systems anyway. > Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely. Layer your architecture? Expose an API? Microservices? There are all sorts of ways around this problem. Yes, it makes it harder for people to just log into your database and run their own SQL queries. Sometimes that may be politically hard to sell. But I heard this same argument in college when private variables were introduced: wait, but, what if someone wants to directly manipulate private variables of class members? That is not a virtue. It's perfectly reasonable to assert that some internals are simply private implementation details. You are reserving your right to change those details in the future as the application changes. You are separating the details of your persistence layer from the public interface that you're exposing. That is a virtue.
gregwebs 2y ago
It seems like you are operating in an environment where a single code library can implement business logic and mediate all DB write access? Can someone update the db directly with another programming language or do they have to go through an API? The value of business logic in the DB is that all clients must obey it.
zetalyrae 2y ago
If I'm at the point of dropping foreign keys, why not just switch to DynamoDB?

by feoren 2y ago

liontwist 2y ago

What is business logic? What specifically is SQL bad at? I’m not sold by your foreign key example. Constraints are great. I guess you can do that in python? Good like getting ACID right As soon as your data stops resembling a tree and starts looking like a graph than all your “business logic” looks a lot like operations on relations and you are back to where you started.

hobs 2y ago

I don't know why you are getting downvoted, and I generally don't like a lot of business logic in the persistence layer, but representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal. If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things. Databases with decent fks and constraints have a tendency to outlive the apps that build atop them, and communicating these constraints in long lived fashions can be ... good actually.

feoren 2y ago
> representing the things you absolutely dont want another part of your app doing via FKs is...pretty normal "Normal" has never been compelling in a world where most software sucks and most projects overrun their budgets. > If you are arguing it spreads out your business logic into multiple places, that's fair, but so does... a lot of things Stop doing those things too! > Databases with decent fks and constraints have a tendency to outlive the apps that build atop them This is oft repeated, but I think there are some flaws with it: 1. I'd ask the question: is that because those apps sucked? It's possible to have a mediocre database and then an even worse app on top of it; in that case it wouldn't be surprising if the app itself died first. 2. Did the next app use the existing schema as-is, or did it adapt/migrate the schema to a different form that worked for that next app? There's a big difference between saying "this data turned out to be useful for other purposes" (of course!) and "this data representation turned out to be so perfect and elegant that we kept using it" 3. Do you have an overly narrow definition of "apps" here? I'd argue if six teams are all doing their own things with the database -- some writing code, some building views, some hooking into it with Excel and PowerBI -- then I'm not overly interested in the fate of the "OG app" that the database was built for. If that one dies, you still have five other "apps" using that database. We're not talking about a schema sticking around because it's so useful, we're talking about an entrenched, locked-in tech that nobody can change because it's too costly. That's a predictable symptom of a bad representation! That's exactly what happens with bad code! Good systems can handle change in isolated parts without breaking things for everybody else. Database schemas are so entangled with themselves, and SQL is so bereft of proper abstraction, that they can't change easily once they have many users.

default-kramer 2y ago

> Compose SQL in an actual real programming language that isn't horrible instead of doing any of this. Got any recommendations? That's a serious question, I've tried many (and built my own) and some are okay but none feel remotely good enough when you consider how much progress we've made basically everywhere else in the wider world of software engineering. Relational databases are so good; they deserve better than SQL!

jeltz 2y ago
I have also tried many and so far none have even managed to be as good as the kludge that is SQL.
feoren 2y ago
> Got any recommendations? Find a good ORM that allows you to keep things at the "set logic" level for as long as possible and has a good internal query representation. Avoid or turn off any fancier features of the ORM like "navigation properties" or "implicit subtyping" or automatically detecting changes or anything that happens "magically" in the background: prefer simplicity and being explicit about everything. For C#, this is EntityFramework with most of its features turned off -- compose queries by joining IQueryables together, and only drop down into in-memory iteration when you have to. If that's not available, I'd probably look for a library that presents a structured representation of the SQL abstract syntax tree (probably a subset) as an object, along with a translation to the various dialects. Pass those ASTs around and compose them together. I'd probably slowly build abstractions on top of this that eventually look similar to what I described in the first paragraph.
efromvt 2y ago
Shameless plug if you've tried a range of alternatives: I've tried building a version (Trilogy) targeted at analytics, and I'd be curious about how it stacks up to your experience with other attempts at this - can read more about the composition/reuse story here: https://trilogydata.dev/blog/composability_reuse.html https://trilogydata.dev/blog/composability_reuse.html
- default-kramer 2y ago

halflife 2y ago

100% completely agree. You just can’t scale SQL code. If you have a small app, then sure. But then I don’t know why you’d invest so much in testing. But if you need to scale your code to more than 3 people, SQL should only be your persistent state store. You probably already have a BL layer in another, more manageable programming language, so use that. In my team I set a rule to remove all SQL functions and views, and move all the logic to our typescript backend. That resulted in code which is much easier to test, and is version controlled.

agluszak 2y ago
And what are the results of your decision in terms of performance?
- cyberax 2y ago
  These days? It doesn't really matter. For most purposes, you can just hold the entire important dataset in RAM. For example, a humongous company with 500000 employees (Amazon) can trivially hold on a fairly low-end server the database of all the personnel, and all their salary payments, and all their employment history, badge photos, and office access logs.
- halflife 2y ago
  The DB is rarely our bottleneck. Our reads are very simple, mostly paged sets from a table with a 1 level join. When we need complex abstractions or transformations, instead of using function or views, we listen to table changes, and with a TS transformation layer we save a new row in a specific table for the abstraction. That way the BL is handled in TS, and performed in writes asynchronously instead of in-line in reads. The only downside is that the transformed data is not real time. That mechanism allowed us to scale our data very easily without any performance hits.
baq 2y ago
Yeah that's what people who don't have much data do with good results. If you can do that, it's the right choice. Once you have data you move code to data and not data to code. It isn't a decision you get to make. You only decide how you do it.

wvenable 2y ago

> Yes, correct, because SQL is awful at representing business logic, and foreign key constraints are business logic! This strikes me as a unnecessary all-or-nothing argument. I would agree that you shouldn't put all your business logic in the database but that doesn't mean you shouldn't put any business logic in your database. If foreign key constraints are business logic then so are table definitions and column types. It's not awful for those things as it's literally built right in. My advice is to ensure, as best as possible, that invalid states are impossible to represent. If you can't have an address without being attached to a client, make that impossible to represent in the database. It's one less thing you'll ever have to worry about.

feoren 2y ago
> This strikes me as a unnecessary all-or-nothing argument I completely understand why it comes across that way. HN Comment length is limited and the current practice is a comfy local optimum, so any one change away from it is going to feel bad, and it's hard to get across my full theory of database design in comment form. I would not recommend most existing teams simply stop using foreign keys, unless they also adapted a lot of other changes that coexist with it. Still, there is a better world for database design, and that world probably does not use foreign key constraints. > I would agree that you shouldn't put all your business logic in the database but that doesn't mean you shouldn't put any business logic in your database. I actually would (lightly) argue that does mean you shouldn't put any business logic in your database, as I find that highly coherent design philosophy leads to highly coherent code. The most important question to me is about how the system can handle change over time. Foreign keys are one part of a larger set of "design principles" that everyone seems to take for granted that lead to a system that cannot handle change. > If foreign key constraints are business logic then so are table definitions and column types. I disagree on this one. Table definitions and column types are a "level below" business logic, as evidenced by the fact that you could probably refactor a lot of schemas for better/different performance characteristics without changing any of the actual business logic that drive them. The business logic doesn't (shouldn't) care whether you've used a bitfield or a bunch of boolean columns or a child set-inclusion table to represent a bunch of yes/no facts about some entity. They're "persistence logic", and the database is exactly the right place for that. > My advice is to ensure, as best as possible, that invalid states are impossible to represent. The problem is that an "invalid state" is highly dependent on your perspective. A state that is invalid to use in one part of your codebase is perfectly valid in other parts. The ubiquity of "draft", "draft final", and "final" shows that humans want to represent "invalid states" all the time! Aren't you glad your IDE lets you represent invalid code? Okay, fine, that data is not ready for the annual submittal, but does that mean I'm really not allowed to save it at all? And these "invalid states" are extremely volatile as your focus expands around your domain. All contact info must have a primary contact method -- well, unless they're just "contact stubs". All facilities need a facility manager -- well, unless they're 3rd party facilities, or storage facilities, or in Region 9, or start with the letter 'P'. Haven't you been 300 hours into the development of a system when suddenly someone springs on you, "Oh yeah, Bob has two bosses. Why should that be a problem?" And you're screaming internally, "Of course it's a problem! You assured me six months ago that everyone only has one supervisor, and now you're telling me this one guy has two!? This changes EVERYTHING!" But why? Why does it change everything? Why does every little change to our database schemas always seem to propagate to the entire system? It's because we're designing databases wrong. And foreign keys are a (small) part of that wrongness.

FridgeSeal 2y ago

I think you’re throwing the baby out with the bath water here a bit. I agree that stored-price are rarely, if ever the solution. I do however think that having something that enforces certain constraints across all your data, and forces you to handle schema evolutions reasonably over time is really powerful. Database implementations are (mostly) full of hard-fought correctness decisions, and I don’t think most devs are in a position to “whip out” an implementation that cares for a number of these on-demand. We had the whole NoSQL thing, we saw what when we threw most of this stuff away. Is sql good enough? For many problems yeah. Is it _really_ good enough? Nah probably not, we could do a lot better, but I think a better design looks more like a better sql-like language and less like a full programming language.

dagss 2y ago

I agree that SQL has problems as a language. I disagree that we should be happy about moving data out of the storage layer into the backend in cases where it does not make sense performance wise. The problem is doing some processing in SQL in some cases has a huge performance advantages over moving data to the backend. If your business logic requires you to take a million rows from table A, apply some rule, and output a million rows into table B -- then piping all that data through a backend is a bad solution performance wise. I reject the idea that all that extra IO (+extra overhead also for the database) is worth it just to use another programming language than SQL. What we need is better alternatives that execute at the storage layer. Probably starting with proper languages that compile to SQL, like we saw with JavaScript.

geysersam 2y ago

I think the fact that these functions/functors/macros already exist in a few different varieties in mainstream database systems presents a challenge to your argument. SQL can be fixed, has been fixed, and is a underutilized tool in my personal opinion.

feoren 2y ago
> SQL can be fixed, has been fixed It has? Can you point me to an example?
- geysersam 2y ago
  I meant in the sense that the additions suggested by the blog post author already exist in multiple sql implementations.

tacone 2y ago

> Imagine you joined a new team, and they said: our program uses a bunch of XML files as its data format, and that's why most of our business logic is implemented in XPath. Just sit there and ponder that situation for a little bit. Been there 20 years ago! It sucks at unimaginable levels!

magicalhippo 2y ago

Agree not to putting your business logic in stored procs, but hard neg on getting rid of all constraints. I consider it defense in depth. Sure your application should absolutely ensure order lines aren't referencing a non-existing order, but bugs happen and I prefer the database returning an error to the database getting full of inconsistent data.

dicytea 2y ago

I agree with most of this, but I don't understand why you'd treat FKs as business logic. IMO, it's clearly a part of the data structure. Like, say if you have a domain that looks like: type User = { username: string; posts: Post[]; } type Post = { content: string; } (for the DDD-brained, assume User is an aggregate and Post is a value object here) The natural way to represent this in the database is: CREATE TABLE users ( username text PRIMARY KEY ); CREATE TABLE posts ( author text NOT NULL REFERENCES users (username), content text NOT NULL ); I just don't see why you'd do it in any other way.

feoren 2y ago
<BossVoice> Hey dicytea, thanks for getting that blogging app up. Our users love it. Hey, I was talking to John, and he said he's got some guest columns he wants you to throw up there. Just some blog posts from thinkers he likes that he thinks would be a good fit for our site. Can you throw up some of those? What do you mean they need to be users of the site? They're just some people whose posts we want to feature. No, they shouldn't need to log in. What are you talking about? What does that have to do with it? </BossVoice> Oops, turns out the rule "all blog post authors are users of the site" was actually just a volatile business rule after all.
- lelanthran 2y ago
  Yes, and isn't it wonderful that you get an error message when you try to change a business rule, forcing you to properly encode the new rule instead? A lot of these types of scenarios are missing the fact that, without these enforcements in the database, sooner or later a developer is going to make a change that violates an existing business rule without realising that they just broke a rule! Rules change. We know this. What is valuable is being told that some new rule conflicts with an existing rule. If you don't enforce the business rules in the database, how do you know when a new rule conflicts with some existing rule?

branko_d 2y ago

I don't understand what you are trying to say about foreign keys. Are you advocating moving FKs from database to application? If yes, you are opening yourself up to race conditions that will only become visible in concurrent environment. I had a distinct misfortune of working with an ERP system based on Oracle which didn't use database-level FKs. Predictably, their customers would occasionally complain about "dangling" data, which, to my knowledge, has never been properly resolved.

threatofrain 2y ago
It's inevitable that some data will be split between more than one service / database. I'm not saying we should have data whimsically split up, but foreign key constraint logic must eventually come to live in the app layer too.
thyristan 2y ago
It is possible to move foreign keys and other constraints and consistency checks into the application. But that comes at a huge cost: Either you need to be generous with your use of transactions to ensure that no invalid state is ever written, and your application has to know and use those transactions. Which is something application developers in my experience often fail at. Or you have to accept that broken inconsistent data is a fact of life that can happen at any time, and will increase in times of high throughput and concurrency (where the business part of the company will care most about things running smoothly). Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely. There is also a cost of doing constraints and consistency in SQL, the language is somewhat limited in what you can easily represent (anything is possible, but only some things are easy). And the handling of violations still needs to be implemented in the application, which often necessitates at least some duplication and fiddling to e.g. get friendly and useful error messages. So imho, yes, SQL has its problems, but it is still the lesser evil in almost all cases.
- feoren 2y ago
  > Or you have to accept that broken inconsistent data is a fact of life that can happen at any time You have to accept this anyway. Data can be broken and inconsistent in so many different ways that you cannot protect against every possible way. People will type the wrong thing in sometimes, in ways you never expected. Someone pasting text into the wrong field is just as "broken and inconsistent" as an ID field pointing to a nonexistent entity. How important those errors are to the business depends on how important those columns are, not whether they're an ID vs. text. And as another person pointed out, data always ends up split/replicated among multiple systems anyway. > Also, you will limit yourself to exactly one application using the database, because otherwise you will have to duplicate your constraint logic in any application precisely. Layer your architecture? Expose an API? Microservices? There are all sorts of ways around this problem. Yes, it makes it harder for people to just log into your database and run their own SQL queries. Sometimes that may be politically hard to sell. But I heard this same argument in college when private variables were introduced: wait, but, what if someone wants to directly manipulate private variables of class members? That is not a virtue. It's perfectly reasonable to assert that some internals are simply private implementation details. You are reserving your right to change those details in the future as the application changes. You are separating the details of your persistence layer from the public interface that you're exposing. That is a virtue.

gregwebs 2y ago

It seems like you are operating in an environment where a single code library can implement business logic and mediate all DB write access? Can someone update the db directly with another programming language or do they have to go through an API? The value of business logic in the DB is that all clients must obey it.

zetalyrae 2y ago

If I'm at the point of dropping foreign keys, why not just switch to DynamoDB?