15 ms·

Use databases without putting domain logic in them

83 points by PKop 4y ago

PaulHoule 4y ago
That's a valid viewpoint, but I've also seen people be very successful with SQL triggers, stored procedures and all of that. Before it was fashionable to build out "service layers" inside a back end application or as an API I've seen people build the equivalent by making all database accesses in an application go through a set of stored procedures that, basically, provide an API. It can work very well. There is also the idea, less well developed, from the RDF world where you could have some system of inference that works like triggers but is based on logic and is potentially more disciplined. Let's say it hasn't caught on and I remember drinking in a hotel bar with the marketing head of one vendor, after a conference, who was frustrated that people didn't see the value.
- GauntletWizard 4y ago
  Once upon a time, I administered a system that was built as a client application that just spole SQL, and a set of administrative tools running as the db superuser to create the db and it's triggers. Permissions were handled entirely internally through functions - user management was just database user management. Most simple operations happened on views. More complex operations were functions. It worked pretty well - the worst part was Microsoft licensing, as we needed a SQL server client license for ever connected user. The second worst was the upgrade path - shutting the db server down such that clients couldn't connect but the super admin could to run the updates was tricky and failure prone, mostly because of a lack of tooling. Today, with features like row-level permissions and notify in postgres, I think you could do great things. That said, I mostly recommend the articles advice to my customers - the database is a database, put logic in your application.
  - hobs 4y ago
    Needing a CAL just means the company was too cheap to license per core :D
  - dvdkon 4y ago
    MSSQL licensing is just all kinds of nuts. As far as I know, the official stance is that anyone accessing data from the DB (not just the DB itself) needs a CAL. This means that with CAL licensing, every user of a web app needs one. I'd feel sorry for people that have to deal with this if it wasn't 90% self-inflicted pain.
    - tatersolid 4y ago
      Microsoft introduced per-processor licensing for SQL Server more than two decades ago to handle public web apps.
oftenwrong 4y ago
Some RDBMS features, such as stored procedures and views, can quite naturally be versioned, and become as ephemeral as your application code. For example, have your application setup a schema to contain its version-specific database components. The schema will contain an immutable application version, such as its commit hash, in its name. This allows a given version of the application to only use its own set of sprocs and views. On deploy, run the SQL scripts to create the sprocs and views for that version. You'll note that sprocs and views are not mutated under this strategy. This allows multiple versions to exist concurrently, which facilitates things like blue-green deploys.
- gonzo41 4y ago
  What you're describing can be done to great success with the right type of requirements. Say you're building an information warehouse for OLAP style work. Totally makes sens to pyt all the heavy stuff in the DB because the data model is going to be pretty rigid and it's the sort of asset to get enough attention from the business to be supported correctly with a full staff. On the other hand, defnining everything in code and using the db as a dumb store that's fractionally better than using files is a pretty good strategy when you may be handing over support to a team that won't be giving full time attention to the app. It also handles those key person risks etc by keeping thigns in one area of the app code.
- Spivak 4y ago
  I've only ever seen this work with "application managed databases" i.e. where the app truly owns and controls everything related to the database and can freely run ddl. The db code is then just part of the app and can be spun up against a completely empty db. The death for this kind of thing is dbas administering the database separately because the coupling will kill you.
cfors 4y ago
I like this viewpoint when you are a small company searching for PMF and your entire backend can fit into a small DB. At one point you'll have to reckon with IO costs and storage once that is over and then you have no choice but to exploit data locality more heavily. Just saying, YMMV depending on how much data is in your database.
MoreQARespect 4y ago
I followed this approach once and ended up abandoning a week's worth of work because it was just far too slow (hours vs < 1 minute) when running on large quantities of data. >This leaves more room for error and makes our logic harder to test since it can’t be easily mocked or executed with the rest of our application-level tests. I ended up writing integration tests on the SQL queries. They were slower than the unit tests I had before but 1.5 seconds per test vs. 0.1 second per test was a price I was pretty happy to pay. In general I would prefer to keep domain logic all in one place but practicality trumps ideology.
- nomel 4y ago
  Yeah, I'm having trouble understanding this. This sentence is very confusing to me: > While this gives you more flexibility, the tricky detail is that filtering happens only after your data is fetched. This is not different from fetching the results and filtering them inside your application. It just saves you some keystrokes. > It just saves you some keystrokes. No, it saves transferring tens of gigabytes to my application. I assume I'm missing something here, or maybe my data is different.
  - Shorel 4y ago
    Agree. This was written by someone writing code so inefficient, I would never want them to be near my code.
bborud 4y ago
My two main reasons for (almost) never putting logic in a database are 1) it will tie you to a specific database. I know a handful of companies that have almost all of their IPR in the shape of stored procedures. Even if they wanted to escape from the database vendor they can't. Not without rewriting everything they have spent a decade or two building. 2) languages such as PL/SQL are much poorer than any modern programming language. If you have ever tried to write a business application in PL/SQL: it is no fun. At least not for me. (What I mean by "almost never" is that I do use constraints quite a lot which you could argue isn't logic, but it is still asking the database to validate my data and model).
- hobs 4y ago
  Even then, the implementation of consistency levels alone makes you have to rethink swaths of your code when you switch databases. This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage. What if the type isn't supported, what limitations on reading and writing exist in THIS engine? Which types of queries are weirdly slow and amazingly fast? The list goes on for things that you don't need to add additional logic to.
  - wruza 4y ago
    This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage Agreed. It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Personally I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db.
    - Clubber 4y ago
      >It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Many ORM's can solve this problem. >I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db. I've had to do it three times in my career. It usually comes from management. It ain't pretty. Two were from M&A and one was to get rid of an obsolete database.
xpil 4y ago
Certain operations use fewer resources when performed in the RDBMS than when performed in the application code. As your data grows significantly, you may encounter new bottlenecks, and for some of these you may need to move some of the logic from your application into the RDBMS (ergo: write an SQL function or a stored procedure or a trigger, etc.).
robertlagrant 4y ago
One of the egregious examples of this is Oracle's Application Express technology - write APIs and screens in PL/SQL. It's such a horrifyingly, nakedly cynical lock-in strategy that it took me a while to come to terms with it when I first came across it.
pipes 4y ago
This is hardly a new opinion. It's also an opinion I've come to completely disagree with.
- sodapopcan 4y ago
  "Completely" as in you'd put ALL your business logic in the db, or that you're open to pushing SOME to it? Most? EDIT: It's genuine question. I use views and write a decent amount of business logic as queries (well, in my language's query DSL). It's always bugged me when people do a lot of post-processing for stuff that the db could do faster.
  - PKop 4y ago
    There's perhaps an interesting 3rd option aside from full stored procedures vs pure application code transformating data pulled down from database... where the processing and transformation is still handled with SQL, but this code and the interaction with the database resides in application code. So instead of triggers and stored procedures, you have database transactions defined in and called from application code.
- doctor_eval 4y ago
  I am with you 100%. SQL-style languages like plpgsql are crazy fast and super concise relative to the hurdles of doing it all in a host language. I’m building a moderate sized application right now - and even though I consider myself a Go programmer, 100% of my code is either Svelte, Typescript or pl/pgsql + postgraphile. Not a line of Go to be seen. I mean, the day will come when I need to write some Go code. But I’m so much more productive with plpgsql when it comes to anything within a single db.
  - yrashk 4y ago
    Being able to implement a lot of business logic in SQL and pl/pgsql is another of the reasons I started developing Omnigres [1]. My initial motivation was to have something almost self-contained, just one thing to deploy infrastructure-wise. But the more time I spend on it, the more I appreciate how having a single place for all the concerns significantly contributes to simplicity. Surely there are some hurdles to overcome. Some are more technical in nature. Some are more on the side of tooling inadequacy/outdatedness. I am trying to contribute to both with the project. [1] https://omnigr.es/ https://omnigr.es/
norman784 4y ago
I think the problem here is NoSQL, for a RDBMS wouldn’t be a problem with data integrity, that’s why I’d prefer it over NoSQL, while it can be easier to start prototyping without a defined structure, I’m more confortable doing with it. Last year I tried a lot of those new document oriented databases and for me, they are awful, you can’t be sure what’s in each record, so I need to be more careful with my application logic and not to mess up, while you can easily define your structure and constraints with a RDBMS and be confident that your data is always correct, also, when adding new fields it’s a nightmare without migrations. This year I started thinking that we might need a database that lets us write proper stored procedures and that it feels like programming (I mean PL/SQL feels pretty hard and it seems that not so many devs are using it), for example what I wanted was to create a trigger when a user buys something, subtract the quantity from the inventory and create a new entry in notifications and orders, that way my app just needs to check if there’s unsent notifications and show the user their orders without that logic being in my app, and it does feels easy to just edit those procedures with migrations.
gampleman 4y ago
Right now I'm more on the opposite side: put all the logic in the database (Postgres is craaazy powerful these days), then use a commoditised backend (we use Hasura, but there is a bunch of good options out there).
ashinybrowncoat 4y ago
So don't use the database for what it is intended because...it might be harder? I very much disagree with this article. It is not so simple to just declare no business logic in the DB when the DB schema itself IS a BIG part of the business logic. When working with data, the best place to work with the data is where it is stored, not pulled out and put back in. And your app is more likely to be replaced long before the data and/or database is. Stop treating the database like a black box and use it for what it is intended.
- Clubber 4y ago
  >So don't use the database for what it is intended because The database is intended to store and retrieve data.
  - ashinybrowncoat 4y ago
    You missed the word maintain and is a very important point. If I can only maintain the data through an app and not directly in the database then the database is nothing more than a card file. Why not use the database features it was intended to provide? The app will be gone long before the data. Always. The next app will need to reproduce the previous apps logic. What if that was already done? It is better to have a mixz used judiciously, than to just consider the database only for storing and retrieving of data. What a wate of resources to not use those things!
    - Clubber 4y ago
      Data integrity is maintained through FK, unique indexes, proper modeling, etc. Also, an RMDBS is hard to scale horizontally, so the more CPU tasks you put on it, the sooner you will hit a bottleneck that is not easy to get out of. A application layer, if designed properly can scale a lot more. >The app will be gone long before the data. When this ever happens, it happens rarely, and the situation is the data enters a read-only state in my experience and happens during sunsetting. Most of the time, if a new system is required, a migration occurs to a new schema that the new app supports.
ryanbrunner 4y ago
I think there's a distinction to be drawn about logic being stored within the DBMS and logic being implemented and executed directly in the DB. Actually storing logic in the DBMS is often challenging unless you want to put a lot of effort in - source control tools and abilities to deal with multiple versions of logic are far less mature in the DBMS world, and while it's possible to make these work, you'll be doing a lot on your own to come up with something bespoke. But there's no reason you can't leverage the DB more directly from your backend code. Given the authors example of like code, that's already inefficient with a single row and would be a nightmare for multiple rows. It's perfectly reasonable to take code like that and have it directly execute SQL statements where a lot of logic is stored in the form of SQL, but still colocated with your application code rather than in stored procedures, triggers, etc. We do use an ORM for most of our DB interactions, but if something starts to stretch beyond the most basic of use cases, we're unafraid to drop to raw SQL and execute that instead. It's been a pretty happy medium for us.
vp8989 4y ago
Ive had a fair amount of low effort "big wins" in my career, simply by reversing the technical decision/implementation of people who took this idea too far. IMO the OP sits at a reasonable level on this particular "spectrum". The title is more controversial sounding than the post which itself is quite reasonable. The implications of being tightly coupled to the (transactional) database are less onerous in a microservices environment where databases are "private" to a single application/service.
deleted 4y ago
[deleted]
dhd415 4y ago
Taking this approach to using a database is always a judgement call, but putting logic in the database is probably useful a lot more often than the "nine times out of ten" that the article claims. And the tradeoff in this approach contradicts the last line in the article: "Utilize the full extent of your database’s capabilities, but don’t put domain logic in it." Putting logic in your database is often necessary to make full use of its capabilities. In applications with complex storage needs and/or performance-sensitive IO needs, I prefer to think about databases as an additional tier that provides complex storage services (as is precisely the case with most modern databases). As an example, I once worked on message routing and transformation server. From the perspective of the application logic, messages simply needed to be durably persisted and retrieved. At the level of the storage layer, I wanted message versioning, provenance, copy-on-write semantics to minimize on-disk size, and indexes to support at least two different retrieval patterns. All of the items at the storage layer were implemented in stored procedures. In other words, the database supplied a custom "storage API" to the business logic tier for persisting and retrieving messages that implemented those routines in a database-specific way that did not concern developers at the application layer. The main arguments against this approach are: * It will tie you to a specific database. This is true, but almost irrelevant since anything other than the most trivial usage of a database will inevitably make use of a feature or syntax specific to the database that would require modification were one to migrate to another database. Further, the mere idea of database independence is kinda' silly. No one talks about how writing your application layer in one particular programming language will limit the ability to migrate to another programming language. We should make technology choices around programming languages, databases, etc. with the intention of matching their strengths to the problem at hand fully understanding that tradeoffs are being made and that rework will be necessary if those choices are ever revisited. * SQL used for writing stored procedures is not as good (for some definition of "good") as other programming languages used in the application layer. SQL is certainly different than most imperative or functional programming languages, but it's expressive and well-suited for its purpose. If you really need to make use of the capabilities of a database, it would behoove you to develop some proficiency in SQL. * Putting logic in the database breaks modern CI/CD processes. IMO, this is the most compelling argument against it. That said, there is tooling that exists for putting stored procedure and other database logic in version control, automatically deploying it to a database, and running tests on it. These tools are not as commonly used, but they do exist. I've also used tooling that introspected the database objects such as stored procedures, etc., and automatically generated type-safe application code to interact with those database objects. That provided compile-time guarantees that application and database code were in sync at least with respect to number and types of arguments, etc. Whether it makes sense to go to the effort of integrating this tooling into your development process is a judgement call, but it can be done and I've seen it work well for application with demanding database needs.
palotasb 4y ago
As a counterpoint, Derek Sivers says Simplify: move code into database functions <https://sive.rs/pg https://sive.rs/pg> I think they are both right, Alex Kondov for keeping his domain logic mostly in the application layer, and Derek Sivers for mostly keeping his in the database layer. Why? Because that's what they know well, and they can keep the domain logic mostly in one place. If Alex can keep most if his domain logic in the application layer that he knows better, that's the better choice for him. If Derek can keep most of his domain logic in the database layer that he knows how to utilize well, that's the better choice for him. (I can't find it now but I thought someone very smartly reflected on the Why Perl? <https://news.ycombinator.com/item?id=35646612 https://news.ycombinator.com/item?id=35646612> post today that the main reason why anyone will choose any programming language is that they know how to use it well. I think the same applies here. This is why many programming languages are valid choices for different people and project, and this is why different architectures are valid choices too.)
- scarface74 4y ago
  When I was choosing jobs in the real world[1], I would avoid any company that depends heavily on stored procedures for business logic and where things were ruled by “database developers” and all the developers were doing were calling stored procedures. Stored procedures are harder to unit test, do automated rollbacks, version control, etc. I’ve never seen a system that 5 years in anyone said “I’m so glad we decided to use stored procedures everywhere” [1] I work in consulting now and have no allusions that I face the same issues that day to day developers do. I get to lead development “solutions” and move on.
  - horsawlarway 4y ago
    It's worse than that. Your database will be the first major thing to cave under load. The traditional tools most folks use (postgres/mysql/maria/etc) want to scale vertically, not horizontally (you can do it, but it's hard, and has lots of drawbacks). Eventually - You will hit hardware limits. Providers only have instances that are so big. There is a ceiling on how much you can scale your DB without having to do major, major work. I've seen this pattern play out now at 3 high growth companies I've worked at. The one that was using lots of stored procs and materialized views got hit the fastest and the hardest.
vivegi 4y ago
If you want Dont-Repeat-Yourself (DRY), you have to decide to put the domain logic either in the database or in a service*. In the first case, two applications that directly talk to the database is feasible since the common logic is implemented at the database level. In the latter case, you need to ensure that every application uses the service and no application directly talks to the database. If multiple apps directly talk to the database where the domain logic is in the service layer, you will end up with code duplication and hard to trace bugs (unless you take extraordinary steps). It is an architectural trade-off. There are use cases where you may choose one over the other. *: service or library
- rufus_foreman 4y ago
  >> If multiple apps directly talk to the database where the domain logic is in the service layer, you will end up with code duplication and hard to trace bugs This is more common than putting it in a service in my experience. I haven't seen it done in the database in a new application since the 90's.
web3-is-a-scam 4y ago
Until your domain logic runs 1000x slower than it needs to and it actually becomes a problem.
vaughan 4y ago
I think all logic should be in the db, but I think we just don't have the right databases. In my ideal world, my database would know about all the queries I want to run, and it would choose query plans based on this knowledge to effectively cache things, and handle streaming changes to queries. You also want to be able to visualize the full data dependency graph of all your data, i.e. when this value changes, what else changes. We need to get rid of all these poor-fitting abstractions between the database (REST, GraphQL, etc.). Your client code should work with a model exactly the same as how its stored in the database. In fact, your entire database should be able to run client-side in the browser. This is what is holding us back. This would allow optimisitic updates, and easy local/offline apps. I think people usually avoid this because they like to chose a different language for their backend and the thought of getting this to run in a browser is frightening. Just think how much easier your life would be if you had direct database access in your browser from your client code, and you didn't have to worry about apis, orms, etc. and that it was secure, and synced automatically. And now think about your current application you work on, and the difficulty in achieving such a thing.
- nschiefer 4y ago
  (Disclaimer: I’m plugging my own work here ;-)) You might enjoy this project, which ties to do basically exactly what you described: stick everything in a database and let it drive the app: https://riffle.systems/essays/prelude/ https://riffle.systems/essays/prelude/ It’s still very much a research prototype but we should have some more writing out soon.
  - PKop 4y ago
    Good essay. Particularly with web development, there is complexity around bridging the gap between server and client, and data crosses this chasm through serialization which exacerbates the problem and limits expressiveness of server languages, requiring massive duplication of code simply to serialize and duplicate on client what is present on server if one wants all the power of client interactivity and API's. This is a big value of recent server-centric frameworks like Phoenix LiveView that provide ability to have code and data co-located and not have to duplicate so much on client and server as with SPA's while attempting to maintain some base level of client interactivity. But seems always a tension between leveraging the full power of client and full power of server. You might find this article [0][1] informative. It disputes the idea that UI's are "pure functions of the data/model" in a compelling way, and points to this incorrect assumption as having introduced some complexity/pain in how frameworks like React work. [0] https://blog.metaobject.com/2018/12/uis-are-not-pure-functions-of-model.html https://blog.metaobject.com/2018/12/uis-are-not-pure-functio... [1] https://news.ycombinator.com/item?id=31979347 https://news.ycombinator.com/item?id=31979347
branko_d 4y ago
On the other hand, there are good reasons to put logic in the database, chiefly: - Performance - Consistency - Security Basically, you want to construct a public API in the form of stored procedures, functions and views, and then funnel all clients through that API, by granting execute/select permissions only on the artifacts which are part of the public API. That can be good for performance not just because computation is close to data so you don’t pay the communication cost, but also because it opens optimization opportunities that might be impractical on the client, e.g. by returning several interdependent resultsets at once, or by cleverly using temporary tables or cursors. It also ensures that a rogue client can’t circumvent the consistency rules implemented by your API. If you care about your data, this is a big one! And finally, the API may be a natural place for performing authorization in case your security needs cannot be readily met by the database’s built-in security. — Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s. For the most part, they’re just pain to work with. But enduring that pain might very well be worth the end result.
- jerf 4y ago
  "Performance" Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible. "Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s." Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_data_with_golang/jg3tf0c/?context=3 https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.
dfragnito 4y ago
The mismatch of code fluidity and the rigidity of the RDBMS is why we created https://schemafreesql.com https://schemafreesql.com . We ofcourse looked at NOSQL but our fondness of SQL kept us from considering.
test6554 4y ago
I work of one of those “everything must be a stored procedure companies.” We are stuck with a single rdbms vendor and the company is perfectly ok with this. Different developers have different skill levels and specialties but they know how to call stored procedures. We have DB admins and a team of SQL specialists who optimize things and make sure everything meets standards. Stored procedure code is source controlled just like other application code, just in different repos. No there aren’t unit tests on this, but integration tests at the application level provide some coverage. I realize there are downsides but they are generally acceptable. We aren’t really putting much if any domain logic in these stored procedures.
whoisstan 4y ago
IMO it's best to start as clean as possible and start migrating logic into the database over time as performance or security needs arise. Same with clean code, start with it and if there are performance issues start adding shortcuts and conditions that might improve the performance but have a negative impact on readability and require much more comments.
nivertech 4y ago
In the DotCom era it wasn't unusual when the entire business was implemented inisde an Oracle database runining on a Sun server (including the web server;) How fun was to debug such a system? Persistence Ignorance [1], also known as PI, is a principle that states that the classes used to model a software application's business domain should not be affected by the way they are stored. This principle applies to traditional application stacks, except for certain Big Data stacks such as kdb+/q, or situations where optimizing the data layer for performance and integrity is necessary. For instance, when using kdb+/q, a separate CI pipeline is utilized alongside the main application's CI pipeline, including separate tests, linters, code or query formatters, etc. When it is necessary to use such systems, the best approach is to isolate them as a separate bounded context, such as a microservice. -- [1] https://deviq.com/principles/persistence-ignorance https://deviq.com/principles/persistence-ignorance
pjmlp 4y ago
Nope, don't waste network bandwith and client CPU on data that shouldn't have left the database in first place.
zzzeek 4y ago
another one of the famous database holy wars, "don't use stored procedures for business logic". and at the moment HN rises to the occasion a ratio of 28 points / 60 comments. I think at this point issues like these are pretty much baked in already. The vast majority of applications don't use stored procedures, because they are a. extremely vendor specific b. written imperatively, rather than declaratively, in vendor specific languages that let's face it are about as user friendly as REXX (Google it, kids) which make them very difficult to be expressive with c. are not very straightforward to keep versioned in source control (of course they can be versioned, but the pipeline from db environment -> source control typically has to be pretty custom, plus you have to get your DBA to use it) d. introduce all kinds of novel problems in integrating with application-level constructs such as database drivers, SQL builders, and dare I say ORMs, where while these are all potentially solvable areas (yes including ORMs), are not being solved, because the folks who swear by stored procedures pretty much despise all those other things. So in some ways the way the stored procedure community is so opposed to application level constructs is kind of what keeps the community isolated, and in some cases, renders what might be useful technologies as completely unused (where I am referring to MySQL stored procedures, which...exist! But I wouldn't dare ever try to use them because who wants to be first, really).
- vp8989 4y ago
  MySQL stored procedures work fine when invoked from application code IME. The lack of native collection types is not ideal when you need to inject N values to a bit of data logic. As such, and for other reasons, I personally prefer raw parameterized SQL passed through a lightweight ORM that handles mapping for me as well as securely marshal a collection value into a parameterized query. But beyond that Id say that they are "usable". Can you elaborate on the challenges you've faced with them?
  - zzzeek 4y ago
    for MySQL? only that they do not seem to be commonly used at all, within the already small set of modern applications that scale out on stored procedures successfully. MySQL's base of maturity is the PHP application that is using straight SQL. I'm not an SP guy so while an SP app using a platform with lots and lots of widespread use and maturity for that style of programming, like Oracle or SQL SQL Server is already unpleasant for me but at least I'd know I was on well-trod ground, doing it for MySQL where issues I hit would have very little precedent / workarounds / community I'd not want to get involved with for anything important.
lowbloodsugar 4y ago
Counterpoint: think of your database server as any other microservice, with an API. Putting a limited amount of logic there can vastly increase (not decrease) the overall application performance. One can also use stored procedures and views to allow the model to change while maintaining backwards compatibility with other services. Alternatively, don't use SQL at all, and use a NoSQL database where you can't put any logic at all. If you aren't going to use the power of the database, then don't "pay" for it.
frithsun 4y ago
The artificial division between the "database" and "real programming" is the original sin behind everything wrong with modern programming.
polyrand 4y ago
I have mixed feelings about using stored procedures and triggers. I was once working on a Postgres app and started storing some logic in the DB. I had some triggers that would automatically set values when a user state changed (e.g: when the user changed to 'inactive', the trigger would also update the tables related to subscriptions, API keys, etc). As another "performance" trick, I was using multiple CTEs with the `RETURNING` clause to execute multiple operations in a single query. Everything was OK when I was working on that app daily. But then I stopped working on it for a few months, and when I came back, I regretted using those tricks. For example, now I need to verify the triggers to make sure that changing a value won't change other tables that I forgot about. Also, I can't compose the SQL queries I wrote because each query does "everything at once". I would have rather paid the cost of doing 3 queries, and in exchange I could have reused some of those queries in different parts of the application [^1]. Of course, the app didn't even get close to the scale at which 1 query vs. 3 queries matter. I still appreciate and like having some business logic in the DB, specially `CHECK` constraints. But the tooling for regular programming languages makes everything easier. Having the logic in the DB is a double-edged sword. [^1]: This can become relevant when building an admin interface/CLI, since you may want to execute partial changes vs. the "everything at once" changes in the user-facing application.
kqr 4y ago
I have worked on a system that was run by customers that were reluctant to make upgrades to their installations. We wanted to be able to evolve the database schema more frequently than we could roll out fleet-wide application upgrades. We could have created a sort of proxy service to handle backwards compatibility between the database and applications, but since these applications needed only a very small number of endpoints, it was much, much easier on operations and maintenance to use the database itself as the compatibility layer through stored procedures.
crawdog 4y ago
This is a major pain point moving customers from Oracle to cloud services. PL/SQL encapsulates a lot of logic in legacy enterprise applications that handcuffs customers to the platform similarly to mainframe systems. Migration tools can get part of the way there but still requires a strangler type pattern when migrating.
NomDePlum 4y ago
https://tsqlt.org/ https://tsqlt.org/ Not making an argument for, or against stored procedures, but just putting this link here as seeing quite a few comments regarding difficulty testing stored procs. It was a fair number of years ago, working on an enterprise data warehouse, but the linked testing library was a bit of a revelation when testing SQLServer stored procs. Basically made unit testing stored procs straightforwards and natural. Haven't used it since, but hopefully helpful for someone, and I do wonder if there are similar testing frameworks for other DBs?
sdfghswe 4y ago
I read that the first version of cloudflare was almost entirely a postgres extension.
- oftenwrong 4y ago
  https://news.ycombinator.com/item?id=22883548 https://news.ycombinator.com/item?id=22883548

83 points by PKop 4y ago

That's a valid viewpoint, but I've also seen people be very successful with SQL triggers, stored procedures and all of that. Before it was fashionable to build out "service layers" inside a back end application or as an API I've seen people build the equivalent by making all database accesses in an application go through a set of stored procedures that, basically, provide an API. It can work very well. There is also the idea, less well developed, from the RDF world where you could have some system of inference that works like triggers but is based on logic and is potentially more disciplined. Let's say it hasn't caught on and I remember drinking in a hotel bar with the marketing head of one vendor, after a conference, who was frustrated that people didn't see the value.

GauntletWizard 4y ago
Once upon a time, I administered a system that was built as a client application that just spole SQL, and a set of administrative tools running as the db superuser to create the db and it's triggers. Permissions were handled entirely internally through functions - user management was just database user management. Most simple operations happened on views. More complex operations were functions. It worked pretty well - the worst part was Microsoft licensing, as we needed a SQL server client license for ever connected user. The second worst was the upgrade path - shutting the db server down such that clients couldn't connect but the super admin could to run the updates was tricky and failure prone, mostly because of a lack of tooling. Today, with features like row-level permissions and notify in postgres, I think you could do great things. That said, I mostly recommend the articles advice to my customers - the database is a database, put logic in your application.
- hobs 4y ago
  Needing a CAL just means the company was too cheap to license per core :D
- dvdkon 4y ago
  MSSQL licensing is just all kinds of nuts. As far as I know, the official stance is that anyone accessing data from the DB (not just the DB itself) needs a CAL. This means that with CAL licensing, every user of a web app needs one. I'd feel sorry for people that have to deal with this if it wasn't 90% self-inflicted pain.
  - tatersolid 4y ago
    Microsoft introduced per-processor licensing for SQL Server more than two decades ago to handle public web apps.

oftenwrong 4y ago

Some RDBMS features, such as stored procedures and views, can quite naturally be versioned, and become as ephemeral as your application code. For example, have your application setup a schema to contain its version-specific database components. The schema will contain an immutable application version, such as its commit hash, in its name. This allows a given version of the application to only use its own set of sprocs and views. On deploy, run the SQL scripts to create the sprocs and views for that version. You'll note that sprocs and views are not mutated under this strategy. This allows multiple versions to exist concurrently, which facilitates things like blue-green deploys.

gonzo41 4y ago
What you're describing can be done to great success with the right type of requirements. Say you're building an information warehouse for OLAP style work. Totally makes sens to pyt all the heavy stuff in the DB because the data model is going to be pretty rigid and it's the sort of asset to get enough attention from the business to be supported correctly with a full staff. On the other hand, defnining everything in code and using the db as a dumb store that's fractionally better than using files is a pretty good strategy when you may be handing over support to a team that won't be giving full time attention to the app. It also handles those key person risks etc by keeping thigns in one area of the app code.
Spivak 4y ago
I've only ever seen this work with "application managed databases" i.e. where the app truly owns and controls everything related to the database and can freely run ddl. The db code is then just part of the app and can be spun up against a completely empty db. The death for this kind of thing is dbas administering the database separately because the coupling will kill you.

cfors 4y ago

I like this viewpoint when you are a small company searching for PMF and your entire backend can fit into a small DB. At one point you'll have to reckon with IO costs and storage once that is over and then you have no choice but to exploit data locality more heavily. Just saying, YMMV depending on how much data is in your database.

MoreQARespect 4y ago

I followed this approach once and ended up abandoning a week's worth of work because it was just far too slow (hours vs < 1 minute) when running on large quantities of data. >This leaves more room for error and makes our logic harder to test since it can’t be easily mocked or executed with the rest of our application-level tests. I ended up writing integration tests on the SQL queries. They were slower than the unit tests I had before but 1.5 seconds per test vs. 0.1 second per test was a price I was pretty happy to pay. In general I would prefer to keep domain logic all in one place but practicality trumps ideology.

nomel 4y ago
Yeah, I'm having trouble understanding this. This sentence is very confusing to me: > While this gives you more flexibility, the tricky detail is that filtering happens only after your data is fetched. This is not different from fetching the results and filtering them inside your application. It just saves you some keystrokes. > It just saves you some keystrokes. No, it saves transferring tens of gigabytes to my application. I assume I'm missing something here, or maybe my data is different.
- Shorel 4y ago
  Agree. This was written by someone writing code so inefficient, I would never want them to be near my code.

bborud 4y ago

My two main reasons for (almost) never putting logic in a database are 1) it will tie you to a specific database. I know a handful of companies that have almost all of their IPR in the shape of stored procedures. Even if they wanted to escape from the database vendor they can't. Not without rewriting everything they have spent a decade or two building. 2) languages such as PL/SQL are much poorer than any modern programming language. If you have ever tried to write a business application in PL/SQL: it is no fun. At least not for me. (What I mean by "almost never" is that I do use constraints quite a lot which you could argue isn't logic, but it is still asking the database to validate my data and model).

hobs 4y ago
Even then, the implementation of consistency levels alone makes you have to rethink swaths of your code when you switch databases. This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage. What if the type isn't supported, what limitations on reading and writing exist in THIS engine? Which types of queries are weirdly slow and amazingly fast? The list goes on for things that you don't need to add additional logic to.
- wruza 4y ago
  This is why I dont really care, changing data stores is almost never trivial even if you just use them for data storage Agreed. It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Personally I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db.
  - Clubber 4y ago
    >It’s strange to hear the lock-in argument when in practice even a set of pretty regular queries and DDL is generally non-portable, neither syntax nor performance-wise. Many ORM's can solve this problem. >I can’t think of saying “let’s move <projectname> to <servername>” seriously at work, unless it’s a prototype which doesn’t require support yet or is a really insignificant auxiliary db. I've had to do it three times in my career. It usually comes from management. It ain't pretty. Two were from M&A and one was to get rid of an obsolete database.

xpil 4y ago

Certain operations use fewer resources when performed in the RDBMS than when performed in the application code. As your data grows significantly, you may encounter new bottlenecks, and for some of these you may need to move some of the logic from your application into the RDBMS (ergo: write an SQL function or a stored procedure or a trigger, etc.).

robertlagrant 4y ago

One of the egregious examples of this is Oracle's Application Express technology - write APIs and screens in PL/SQL. It's such a horrifyingly, nakedly cynical lock-in strategy that it took me a while to come to terms with it when I first came across it.

pipes 4y ago

This is hardly a new opinion. It's also an opinion I've come to completely disagree with.

sodapopcan 4y ago
"Completely" as in you'd put ALL your business logic in the db, or that you're open to pushing SOME to it? Most? EDIT: It's genuine question. I use views and write a decent amount of business logic as queries (well, in my language's query DSL). It's always bugged me when people do a lot of post-processing for stuff that the db could do faster.
- PKop 4y ago
  There's perhaps an interesting 3rd option aside from full stored procedures vs pure application code transformating data pulled down from database... where the processing and transformation is still handled with SQL, but this code and the interaction with the database resides in application code. So instead of triggers and stored procedures, you have database transactions defined in and called from application code.
doctor_eval 4y ago
I am with you 100%. SQL-style languages like plpgsql are crazy fast and super concise relative to the hurdles of doing it all in a host language. I’m building a moderate sized application right now - and even though I consider myself a Go programmer, 100% of my code is either Svelte, Typescript or pl/pgsql + postgraphile. Not a line of Go to be seen. I mean, the day will come when I need to write some Go code. But I’m so much more productive with plpgsql when it comes to anything within a single db.
- yrashk 4y ago
  Being able to implement a lot of business logic in SQL and pl/pgsql is another of the reasons I started developing Omnigres [1]. My initial motivation was to have something almost self-contained, just one thing to deploy infrastructure-wise. But the more time I spend on it, the more I appreciate how having a single place for all the concerns significantly contributes to simplicity. Surely there are some hurdles to overcome. Some are more technical in nature. Some are more on the side of tooling inadequacy/outdatedness. I am trying to contribute to both with the project. [1] https://omnigr.es/ https://omnigr.es/

norman784 4y ago

I think the problem here is NoSQL, for a RDBMS wouldn’t be a problem with data integrity, that’s why I’d prefer it over NoSQL, while it can be easier to start prototyping without a defined structure, I’m more confortable doing with it. Last year I tried a lot of those new document oriented databases and for me, they are awful, you can’t be sure what’s in each record, so I need to be more careful with my application logic and not to mess up, while you can easily define your structure and constraints with a RDBMS and be confident that your data is always correct, also, when adding new fields it’s a nightmare without migrations. This year I started thinking that we might need a database that lets us write proper stored procedures and that it feels like programming (I mean PL/SQL feels pretty hard and it seems that not so many devs are using it), for example what I wanted was to create a trigger when a user buys something, subtract the quantity from the inventory and create a new entry in notifications and orders, that way my app just needs to check if there’s unsent notifications and show the user their orders without that logic being in my app, and it does feels easy to just edit those procedures with migrations.

gampleman 4y ago

Right now I'm more on the opposite side: put all the logic in the database (Postgres is craaazy powerful these days), then use a commoditised backend (we use Hasura, but there is a bunch of good options out there).

ashinybrowncoat 4y ago

So don't use the database for what it is intended because...it might be harder? I very much disagree with this article. It is not so simple to just declare no business logic in the DB when the DB schema itself IS a BIG part of the business logic. When working with data, the best place to work with the data is where it is stored, not pulled out and put back in. And your app is more likely to be replaced long before the data and/or database is. Stop treating the database like a black box and use it for what it is intended.

Clubber 4y ago
>So don't use the database for what it is intended because The database is intended to store and retrieve data.
- ashinybrowncoat 4y ago
  You missed the word maintain and is a very important point. If I can only maintain the data through an app and not directly in the database then the database is nothing more than a card file. Why not use the database features it was intended to provide? The app will be gone long before the data. Always. The next app will need to reproduce the previous apps logic. What if that was already done? It is better to have a mixz used judiciously, than to just consider the database only for storing and retrieving of data. What a wate of resources to not use those things!
  - Clubber 4y ago
    Data integrity is maintained through FK, unique indexes, proper modeling, etc. Also, an RMDBS is hard to scale horizontally, so the more CPU tasks you put on it, the sooner you will hit a bottleneck that is not easy to get out of. A application layer, if designed properly can scale a lot more. >The app will be gone long before the data. When this ever happens, it happens rarely, and the situation is the data enters a read-only state in my experience and happens during sunsetting. Most of the time, if a new system is required, a migration occurs to a new schema that the new app supports.

ryanbrunner 4y ago

I think there's a distinction to be drawn about logic being stored within the DBMS and logic being implemented and executed directly in the DB. Actually storing logic in the DBMS is often challenging unless you want to put a lot of effort in - source control tools and abilities to deal with multiple versions of logic are far less mature in the DBMS world, and while it's possible to make these work, you'll be doing a lot on your own to come up with something bespoke. But there's no reason you can't leverage the DB more directly from your backend code. Given the authors example of like code, that's already inefficient with a single row and would be a nightmare for multiple rows. It's perfectly reasonable to take code like that and have it directly execute SQL statements where a lot of logic is stored in the form of SQL, but still colocated with your application code rather than in stored procedures, triggers, etc. We do use an ORM for most of our DB interactions, but if something starts to stretch beyond the most basic of use cases, we're unafraid to drop to raw SQL and execute that instead. It's been a pretty happy medium for us.

vp8989 4y ago

Ive had a fair amount of low effort "big wins" in my career, simply by reversing the technical decision/implementation of people who took this idea too far. IMO the OP sits at a reasonable level on this particular "spectrum". The title is more controversial sounding than the post which itself is quite reasonable. The implications of being tightly coupled to the (transactional) database are less onerous in a microservices environment where databases are "private" to a single application/service.

deleted 4y ago

[deleted]

dhd415 4y ago

Taking this approach to using a database is always a judgement call, but putting logic in the database is probably useful a lot more often than the "nine times out of ten" that the article claims. And the tradeoff in this approach contradicts the last line in the article: "Utilize the full extent of your database’s capabilities, but don’t put domain logic in it." Putting logic in your database is often necessary to make full use of its capabilities. In applications with complex storage needs and/or performance-sensitive IO needs, I prefer to think about databases as an additional tier that provides complex storage services (as is precisely the case with most modern databases). As an example, I once worked on message routing and transformation server. From the perspective of the application logic, messages simply needed to be durably persisted and retrieved. At the level of the storage layer, I wanted message versioning, provenance, copy-on-write semantics to minimize on-disk size, and indexes to support at least two different retrieval patterns. All of the items at the storage layer were implemented in stored procedures. In other words, the database supplied a custom "storage API" to the business logic tier for persisting and retrieving messages that implemented those routines in a database-specific way that did not concern developers at the application layer. The main arguments against this approach are: * It will tie you to a specific database. This is true, but almost irrelevant since anything other than the most trivial usage of a database will inevitably make use of a feature or syntax specific to the database that would require modification were one to migrate to another database. Further, the mere idea of database independence is kinda' silly. No one talks about how writing your application layer in one particular programming language will limit the ability to migrate to another programming language. We should make technology choices around programming languages, databases, etc. with the intention of matching their strengths to the problem at hand fully understanding that tradeoffs are being made and that rework will be necessary if those choices are ever revisited. * SQL used for writing stored procedures is not as good (for some definition of "good") as other programming languages used in the application layer. SQL is certainly different than most imperative or functional programming languages, but it's expressive and well-suited for its purpose. If you really need to make use of the capabilities of a database, it would behoove you to develop some proficiency in SQL. * Putting logic in the database breaks modern CI/CD processes. IMO, this is the most compelling argument against it. That said, there is tooling that exists for putting stored procedure and other database logic in version control, automatically deploying it to a database, and running tests on it. These tools are not as commonly used, but they do exist. I've also used tooling that introspected the database objects such as stored procedures, etc., and automatically generated type-safe application code to interact with those database objects. That provided compile-time guarantees that application and database code were in sync at least with respect to number and types of arguments, etc. Whether it makes sense to go to the effort of integrating this tooling into your development process is a judgement call, but it can be done and I've seen it work well for application with demanding database needs.

palotasb 4y ago

As a counterpoint, Derek Sivers says Simplify: move code into database functions <https://sive.rs/pg https://sive.rs/pg> I think they are both right, Alex Kondov for keeping his domain logic mostly in the application layer, and Derek Sivers for mostly keeping his in the database layer. Why? Because that's what they know well, and they can keep the domain logic mostly in one place. If Alex can keep most if his domain logic in the application layer that he knows better, that's the better choice for him. If Derek can keep most of his domain logic in the database layer that he knows how to utilize well, that's the better choice for him. (I can't find it now but I thought someone very smartly reflected on the Why Perl? <https://news.ycombinator.com/item?id=35646612 https://news.ycombinator.com/item?id=35646612> post today that the main reason why anyone will choose any programming language is that they know how to use it well. I think the same applies here. This is why many programming languages are valid choices for different people and project, and this is why different architectures are valid choices too.)

scarface74 4y ago
When I was choosing jobs in the real world[1], I would avoid any company that depends heavily on stored procedures for business logic and where things were ruled by “database developers” and all the developers were doing were calling stored procedures. Stored procedures are harder to unit test, do automated rollbacks, version control, etc. I’ve never seen a system that 5 years in anyone said “I’m so glad we decided to use stored procedures everywhere” [1] I work in consulting now and have no allusions that I face the same issues that day to day developers do. I get to lead development “solutions” and move on.
- horsawlarway 4y ago
  It's worse than that. Your database will be the first major thing to cave under load. The traditional tools most folks use (postgres/mysql/maria/etc) want to scale vertically, not horizontally (you can do it, but it's hard, and has lots of drawbacks). Eventually - You will hit hardware limits. Providers only have instances that are so big. There is a ceiling on how much you can scale your DB without having to do major, major work. I've seen this pattern play out now at 3 high growth companies I've worked at. The one that was using lots of stored procs and materialized views got hit the fastest and the hardest.

vivegi 4y ago

If you want Dont-Repeat-Yourself (DRY), you have to decide to put the domain logic either in the database or in a service*. In the first case, two applications that directly talk to the database is feasible since the common logic is implemented at the database level. In the latter case, you need to ensure that every application uses the service and no application directly talks to the database. If multiple apps directly talk to the database where the domain logic is in the service layer, you will end up with code duplication and hard to trace bugs (unless you take extraordinary steps). It is an architectural trade-off. There are use cases where you may choose one over the other. *: service or library

rufus_foreman 4y ago
>> If multiple apps directly talk to the database where the domain logic is in the service layer, you will end up with code duplication and hard to trace bugs This is more common than putting it in a service in my experience. I haven't seen it done in the database in a new application since the 90's.

web3-is-a-scam 4y ago

Until your domain logic runs 1000x slower than it needs to and it actually becomes a problem.

vaughan 4y ago

I think all logic should be in the db, but I think we just don't have the right databases. In my ideal world, my database would know about all the queries I want to run, and it would choose query plans based on this knowledge to effectively cache things, and handle streaming changes to queries. You also want to be able to visualize the full data dependency graph of all your data, i.e. when this value changes, what else changes. We need to get rid of all these poor-fitting abstractions between the database (REST, GraphQL, etc.). Your client code should work with a model exactly the same as how its stored in the database. In fact, your entire database should be able to run client-side in the browser. This is what is holding us back. This would allow optimisitic updates, and easy local/offline apps. I think people usually avoid this because they like to chose a different language for their backend and the thought of getting this to run in a browser is frightening. Just think how much easier your life would be if you had direct database access in your browser from your client code, and you didn't have to worry about apis, orms, etc. and that it was secure, and synced automatically. And now think about your current application you work on, and the difficulty in achieving such a thing.

nschiefer 4y ago
(Disclaimer: I’m plugging my own work here ;-)) You might enjoy this project, which ties to do basically exactly what you described: stick everything in a database and let it drive the app: https://riffle.systems/essays/prelude/ https://riffle.systems/essays/prelude/ It’s still very much a research prototype but we should have some more writing out soon.
- PKop 4y ago
  Good essay. Particularly with web development, there is complexity around bridging the gap between server and client, and data crosses this chasm through serialization which exacerbates the problem and limits expressiveness of server languages, requiring massive duplication of code simply to serialize and duplicate on client what is present on server if one wants all the power of client interactivity and API's. This is a big value of recent server-centric frameworks like Phoenix LiveView that provide ability to have code and data co-located and not have to duplicate so much on client and server as with SPA's while attempting to maintain some base level of client interactivity. But seems always a tension between leveraging the full power of client and full power of server. You might find this article [0][1] informative. It disputes the idea that UI's are "pure functions of the data/model" in a compelling way, and points to this incorrect assumption as having introduced some complexity/pain in how frameworks like React work. [0] https://blog.metaobject.com/2018/12/uis-are-not-pure-functions-of-model.html https://blog.metaobject.com/2018/12/uis-are-not-pure-functio... [1] https://news.ycombinator.com/item?id=31979347 https://news.ycombinator.com/item?id=31979347

branko_d 4y ago

On the other hand, there are good reasons to put logic in the database, chiefly: - Performance - Consistency - Security Basically, you want to construct a public API in the form of stored procedures, functions and views, and then funnel all clients through that API, by granting execute/select permissions only on the artifacts which are part of the public API. That can be good for performance not just because computation is close to data so you don’t pay the communication cost, but also because it opens optimization opportunities that might be impractical on the client, e.g. by returning several interdependent resultsets at once, or by cleverly using temporary tables or cursors. It also ensures that a rogue client can’t circumvent the consistency rules implemented by your API. If you care about your data, this is a big one! And finally, the API may be a natural place for performing authorization in case your security needs cannot be readily met by the database’s built-in security. — Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s. For the most part, they’re just pain to work with. But enduring that pain might very well be worth the end result.

jerf 4y ago
"Performance" Performance is my primary reason for using stored procedures. The thing that almost every high-performance app eventually runs up against is sheer query quantity. No matter how good you are with doing set operations rather than operating on one value at a time, the time inevitably comes when you need to do a query from your app, do something to the result, and generate the next query, and repeat this for an arbitrary number of times. If you can move that process to the database, you can get performance wins so massive your peers won't believe such wins were even possible. "Perhaps the biggest argument against it is the fact that most SQL-based languages are firmly stuck in the 80s." Postgres is chipping away at this at least. It's still a mess, but every time I leave for a couple of years and come back, the game has been upped. I don't know exactly how long it's been in there, but as a for-instance, Postgres stored procedures have print debugging now, which I described in a Go context here but should apply to any other language that has a good Postgres-specific driver: https://www.reddit.com/r/golang/comments/12k6c6m/persistent_data_with_golang/jg3tf0c/?context=3 https://www.reddit.com/r/golang/comments/12k6c6m/persistent_... That's just a night & day change. I can live with print-debugging even if I don't get a true debugger, but working without even print debugging is a little too 1960s for my tastes. There's also been a lot of improvement in being able to intermix arrays, tables, and other such things with each other, which used to be a major hassle, though it's still pretty chaotic in terms of naming schemes and such.

dfragnito 4y ago

The mismatch of code fluidity and the rigidity of the RDBMS is why we created https://schemafreesql.com https://schemafreesql.com . We ofcourse looked at NOSQL but our fondness of SQL kept us from considering.

test6554 4y ago

I work of one of those “everything must be a stored procedure companies.” We are stuck with a single rdbms vendor and the company is perfectly ok with this. Different developers have different skill levels and specialties but they know how to call stored procedures. We have DB admins and a team of SQL specialists who optimize things and make sure everything meets standards. Stored procedure code is source controlled just like other application code, just in different repos. No there aren’t unit tests on this, but integration tests at the application level provide some coverage. I realize there are downsides but they are generally acceptable. We aren’t really putting much if any domain logic in these stored procedures.

whoisstan 4y ago

IMO it's best to start as clean as possible and start migrating logic into the database over time as performance or security needs arise. Same with clean code, start with it and if there are performance issues start adding shortcuts and conditions that might improve the performance but have a negative impact on readability and require much more comments.

nivertech 4y ago

In the DotCom era it wasn't unusual when the entire business was implemented inisde an Oracle database runining on a Sun server (including the web server;) How fun was to debug such a system? Persistence Ignorance [1], also known as PI, is a principle that states that the classes used to model a software application's business domain should not be affected by the way they are stored. This principle applies to traditional application stacks, except for certain Big Data stacks such as kdb+/q, or situations where optimizing the data layer for performance and integrity is necessary. For instance, when using kdb+/q, a separate CI pipeline is utilized alongside the main application's CI pipeline, including separate tests, linters, code or query formatters, etc. When it is necessary to use such systems, the best approach is to isolate them as a separate bounded context, such as a microservice. -- [1] https://deviq.com/principles/persistence-ignorance https://deviq.com/principles/persistence-ignorance

pjmlp 4y ago

Nope, don't waste network bandwith and client CPU on data that shouldn't have left the database in first place.

zzzeek 4y ago

another one of the famous database holy wars, "don't use stored procedures for business logic". and at the moment HN rises to the occasion a ratio of 28 points / 60 comments. I think at this point issues like these are pretty much baked in already. The vast majority of applications don't use stored procedures, because they are a. extremely vendor specific b. written imperatively, rather than declaratively, in vendor specific languages that let's face it are about as user friendly as REXX (Google it, kids) which make them very difficult to be expressive with c. are not very straightforward to keep versioned in source control (of course they can be versioned, but the pipeline from db environment -> source control typically has to be pretty custom, plus you have to get your DBA to use it) d. introduce all kinds of novel problems in integrating with application-level constructs such as database drivers, SQL builders, and dare I say ORMs, where while these are all potentially solvable areas (yes including ORMs), are not being solved, because the folks who swear by stored procedures pretty much despise all those other things. So in some ways the way the stored procedure community is so opposed to application level constructs is kind of what keeps the community isolated, and in some cases, renders what might be useful technologies as completely unused (where I am referring to MySQL stored procedures, which...exist! But I wouldn't dare ever try to use them because who wants to be first, really).

vp8989 4y ago
MySQL stored procedures work fine when invoked from application code IME. The lack of native collection types is not ideal when you need to inject N values to a bit of data logic. As such, and for other reasons, I personally prefer raw parameterized SQL passed through a lightweight ORM that handles mapping for me as well as securely marshal a collection value into a parameterized query. But beyond that Id say that they are "usable". Can you elaborate on the challenges you've faced with them?
- zzzeek 4y ago
  for MySQL? only that they do not seem to be commonly used at all, within the already small set of modern applications that scale out on stored procedures successfully. MySQL's base of maturity is the PHP application that is using straight SQL. I'm not an SP guy so while an SP app using a platform with lots and lots of widespread use and maturity for that style of programming, like Oracle or SQL SQL Server is already unpleasant for me but at least I'd know I was on well-trod ground, doing it for MySQL where issues I hit would have very little precedent / workarounds / community I'd not want to get involved with for anything important.

lowbloodsugar 4y ago

Counterpoint: think of your database server as any other microservice, with an API. Putting a limited amount of logic there can vastly increase (not decrease) the overall application performance. One can also use stored procedures and views to allow the model to change while maintaining backwards compatibility with other services. Alternatively, don't use SQL at all, and use a NoSQL database where you can't put any logic at all. If you aren't going to use the power of the database, then don't "pay" for it.

frithsun 4y ago

The artificial division between the "database" and "real programming" is the original sin behind everything wrong with modern programming.

polyrand 4y ago

I have mixed feelings about using stored procedures and triggers. I was once working on a Postgres app and started storing some logic in the DB. I had some triggers that would automatically set values when a user state changed (e.g: when the user changed to 'inactive', the trigger would also update the tables related to subscriptions, API keys, etc). As another "performance" trick, I was using multiple CTEs with the `RETURNING` clause to execute multiple operations in a single query. Everything was OK when I was working on that app daily. But then I stopped working on it for a few months, and when I came back, I regretted using those tricks. For example, now I need to verify the triggers to make sure that changing a value won't change other tables that I forgot about. Also, I can't compose the SQL queries I wrote because each query does "everything at once". I would have rather paid the cost of doing 3 queries, and in exchange I could have reused some of those queries in different parts of the application [^1]. Of course, the app didn't even get close to the scale at which 1 query vs. 3 queries matter. I still appreciate and like having some business logic in the DB, specially `CHECK` constraints. But the tooling for regular programming languages makes everything easier. Having the logic in the DB is a double-edged sword. [^1]: This can become relevant when building an admin interface/CLI, since you may want to execute partial changes vs. the "everything at once" changes in the user-facing application.

kqr 4y ago

I have worked on a system that was run by customers that were reluctant to make upgrades to their installations. We wanted to be able to evolve the database schema more frequently than we could roll out fleet-wide application upgrades. We could have created a sort of proxy service to handle backwards compatibility between the database and applications, but since these applications needed only a very small number of endpoints, it was much, much easier on operations and maintenance to use the database itself as the compatibility layer through stored procedures.

crawdog 4y ago

This is a major pain point moving customers from Oracle to cloud services. PL/SQL encapsulates a lot of logic in legacy enterprise applications that handcuffs customers to the platform similarly to mainframe systems. Migration tools can get part of the way there but still requires a strangler type pattern when migrating.

NomDePlum 4y ago

https://tsqlt.org/ https://tsqlt.org/ Not making an argument for, or against stored procedures, but just putting this link here as seeing quite a few comments regarding difficulty testing stored procs. It was a fair number of years ago, working on an enterprise data warehouse, but the linked testing library was a bit of a revelation when testing SQLServer stored procs. Basically made unit testing stored procs straightforwards and natural. Haven't used it since, but hopefully helpful for someone, and I do wonder if there are similar testing frameworks for other DBs?

sdfghswe 4y ago

I read that the first version of cloudflare was almost entirely a postgres extension.

oftenwrong 4y ago
https://news.ycombinator.com/item?id=22883548 https://news.ycombinator.com/item?id=22883548