7 ms·

My colleagues hate me, but I also found that SQL is The way to write business logic. Lots of caveats about difficulty to test and weird syntax. But it is just

• Etheryte 3y ago
  I feel like you've come to the right conclusion, but partly for the wrong reasons. Terseness in and of itself is not useful per se. Code golfing languages are the tersest there is, but we don't write code that way.
  • ndriscoll 3y ago
    Or they just used the wrong word. I'd agree with them, but clarify that SQL is not just terse, but concise. A join or a group by is going to be much clearer than writing the code the query plan is going to generate, creating temporary hash maps, doing nested loops, etc.
    • ttfkam 3y ago
      Yep, because unlike general purpose programming languages, you describe WHAT data you want from SQL, not HOW to get that data as in most application languages.
      • fifilura 3y ago
        Exactly. And yes, maybe concise was the word I meant to use (not native English speaker).
• randomdata 3y ago
  > And that in itself is the most important factor to avoid bugs. I'm not sure about that. SQL was the first language I learned and the language that has always been there throughout the decades, and is also where I make the most mistakes.
  • hot_gril 3y ago
    If you were to write the equivalent of a SQL query in some other language, you'll probably make a lot more mistakes. Especially if you're trying to achieve the same performance. And I mean a read-only query, not even something with multi-writes and locking.
    • randomdata 3y ago
      I don't think that is true as I could use a better relational calculus that exhibits all the of the same positive attributes that SQL brings to the table. SQL's traps are not all fundamental, just straight up poor language design. SQL's biggest trap is where it grossly deviates from Codd's relational algebra model. A relational system that is more "pure" would solve many bug vectors right out of the gate.
      • hot_gril 3y ago
        What's an example of a SQL query that's misleading but wouldn't be if it stayed more like Codd's model? The issues I have with SQL are simpler things like `timestamp with time zone` not really storing a time zone and actually being the thing you want for tz-independent timestamps.
        • randomdata 3y ago
          I'm not sure I have a great example in front of me – the bugs get fixed. I can point out that even `SELECT * FROM table` violates the relational model. I don't expect anyone is encountering bugs from that simple query as it is easy enough to reason about, but when you compound the complexity it becomes harder to remember where SQL is non-relational and to remember to account for that. I suppose it can be avoided with extreme care, like properly managing memory in C, but a good language will help you out. Much like C, SQL is old so those traps may be excusable in a legacy language, but it is shame that we haven't put much effort into building something more modern. Where is the 'Rust' of SQL?
          • 3y ago
• surprisetalk 3y ago
  I completely agree! Transaction-safety is also sublime for business logic.
• buryat 3y ago
  that's how you end up with multi-thousand LOC sql files that contain all the historical business logic and edge cases of a company and it just keeps being piled up on and its complexity keeps growing because not everything is expressible in SQL. So you end up having a big monster that someone needs to support and one that's not very testable easily
  • nextworddev 3y ago
    What you need is a semantic layer on top of SQL
    • ttfkam 3y ago
      SQL is the semantic layer on top of relational algebra. Adding another layer on top is like having Google translate from Spanish to Russian before translating back to English. Each level of indirection just opens the door for more nonsense to creep in.
  • cma256 3y ago
    Glad that doesn't happen any where else!
    • nightpool 3y ago
      Most other programming languages are more composable than a SQL query. You can make functions, split logic into different classes, extract common logic, etc. Much harder with big sql queries
      • ako 3y ago
        Use views and stored procedures if you need composability.
        • FridgeSeal 3y ago
          Counterpoint: don’t do this. The path to hell is paved with good intentions. Some co-workers and I inherited a code-base where the authors went down the views and stored procedures route. It was basicallly impossible to untangle; there was no knowing what relied on a view or a proc, so you couldn’t touch them at all, there were no docs, there were duplicates of everything (and again, no way to know what’s used). A good number of them had all kinds of side effects and weird performance characteristics. They weren’t version controlled. The issues just kept going.
• crazygringo 3y ago
  > But it is just that SQL is the most terse and standard way so express logic. As someone who has written a ton of complex SQL... I couldn't disagree more. Trying to shoehorn things that can trivially and intuitively be expressed in a couple of for-loops with a couple of variables, into SQL expressions making use of joins and aggregate functions and GROUP BY's and (god forbid) correlated subqueries... having to replicate subqueries because their results in one part of the query can't be re-used in another part... teaching people arcane terminology distinctions like between WHERE and HAVING... not having basic functions for calculating percentiles or even a basic median... certain basic kinds of logic operations that simply can't be done... flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run... I have never encountered more bugs in business logic than in dealing with SQL. It obfuscates what should be clear. SQL isn't a solution for avoiding bugs, it's what causes so many of them.
  • deadbabe 3y ago
    Are you using Postgres? It seems like you haven’t discovered CTEs or lateral joins.
    • crazygringo 3y ago
      Oh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.
      • paulddraper 3y ago
        Well Java and JavaScript differ a lot too. Some similarities, but a lot of differences too.
      • magicalhippo 3y ago
        We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL. Imagine the fun we're having...
• hot_gril 3y ago
  Same, every backend service I write now has the majority of the business logic in SQL and a little pre/post-processing in regular code. A well-designed schema will mean that your queries don't get messy. If some more complex thing starts feeling forced, I add a bit more non-SQL code to make it reasonable. When teammates look at my code, and logic is all right there instead of scattered around, and there's a schema file backing it all that makes it clear what all the relations are, they have an easy time making tweaks or adding on. Yes it's very testable too. This also kinda depends on having a multi-service architecture if your system is large. Separate database for each. That's a good thing anyway.
  • whstl 3y ago
    The fear of committing SQL to the codebase is one of the most baffling things in modern backends. To me the most infuriating thing is the "SQL query scattered around multiple files" pattern, where a backend engineer will decompose a perfectly fine SQL query into 3 or 4 files, with multiple functions, often with very artificial separations (for example: a function just for the "select ..." part, another for the joins). All that in the name of having small files, small functions, small lines. You take complexity away from the "micro" parts and embed it into the invisible parts of your program.
    • hot_gril 3y ago
      We're using some tool at work that recommends putting SQL into dedicated separate files, one file per query. Or you can use the query builder which is just SQL but with extra steps, limitations, and caveats. It's so annoying.
    • icedchai 3y ago
      I hate this, also. This was a "design pattern" (antipattern) at a previous company. It was incredibly difficult to navigate SQL built like this. We were also asked to write "unit tests" for each micro-function. This was essentially a pointless exercise that compared each function call to a string, copy-and-pasted from the function itself, with no regard to the correctness of the overall query. At least it was easy to make the tests pass.
• devoutsalsa 3y ago
  Have you written an ORM in SQL to help make querying easier?
• vlovich123 3y ago
  I’ve heard that datalog (and similar languages in that vein) is far easier and terser to write. What do you think about that?
• bob1029 3y ago
  We've been doing this for a while and it has some pretty severe limitations at scale. The biggest issue is that humans are human and you cannot make everyone else on the team use your elegant SQL DSL business engine in exactly the same way you had envisioned. Eventually, it will grow into a bit of a monster. We are headed back in the other direction, but with some hedge. Code with a very simple & transparent CI/CD experience seems equivalent to some SQL configurable thing in our minds now, but with way more potential upside. In our latest code-based abstractions, nothing is stopping you from breaking out a SQL connection and running any arbitrary query. In fact, all of the data is still in SQL and it's still authoritative, you just now have access to way more powerful tooling to work with that data. Code+SQL together is the answer. For non-expert team members, modern C# is turning out to be way more intuitive than SQL the moment you encounter a 3+ table join scenario.
  • devjab 3y ago
    I think you make some decent points, but at the same time I really don’t understand this part: > For non-expert team members, modern C# is turning out to be way more intuitive than SQL the moment you encounter a 3+ table join scenario. Partly because this is so inefficient that it’s never going to work at scale, but also because of how you’re moving the complexity into the realm of “magic”. I know it’s very easy and intuitive to use one of C#s ORMs, but with that comes an reliance on things like linq and a model builder, both of which may not work the way you think they do. If your developers think about it at all, and having seen so many C# developers use IEnumerable where they really should have been using IQuerable… well if your developers can’t do relatively basic SQL then I’m not sure I’d trust them with the abstraction either. I don’t think you’re really, wrong, either, but I think it’s much more a question of building developer tools to help your developers handle good data access than it is about picking a particular tech on the consuming side. As far as data storage goes, however, I think we’re just beginning to see the move away from the classics because the classics just weren’t build to support how we use data in 2023. I can’t begin to tell you the nightmare it is to update old school SQL “data wells” to be capable of temporality, good BI access as well as being compliant with the various EU legislative rules, and almost none of that can be done above the database. Well I guess you could, but you’d probably go insane. And that’s just path of it, the other part is just how much data we transact now. It used to be that banks were basically the only organisations to move massive amounts of tiny bits of data, and now we all do it. Like, a single solar plant moves a gazillion points of data into your various systems a minute, where 30 years ago the entire data for that place might’ve been a couple of kb a year, it’s now mbs a day.