7 ms·
I have worked on an application that heavily used sprocs for business logic. I would not wish it on anyone. It will seem fine (and actually it will seem better
by mdoms 6y ago
I have worked on an application that heavily used sprocs for business logic. I would not wish it on anyone. It will seem fine (and actually it will seem better - faster and easier to manage) while your project is small.
In a few years when you need to make some major changes you will see why it was a bad idea, and it will be too late. Have fun modifying hundreds or thousands of sprocs when you need to make a large-scale change to the structure of your data, because SQL won't compose. Have fun modifying dozens of sprocs for each change in business logic, because SQL won't compose. I guarantee you will have mountains of duplicated code because SQL won't compose.
- rishav_sharan 6y agoCan you elaborate a bit more on duplicated code/composition issue? I would think that functions would ensure that there isn't much duplicated code.
- kqr 6y agoI agree. To me this sounds more like bad programming (high coupling and low cohesion) and like it would be a problem even if a separate application acted as the backend.
- aaomidi 6y agoWell some systems make good programming really difficult. Stored procedures, especially back then.... Yeah.
- throwaway894345 6y agoI believe this in the abstract, but would be very interested in some concrete explanation for why sprocs compose worse than other programming languages. How would the OP's situation have improved if instead of sprocs he had a similarly complex webapp and had to deal with the schema changes there? I'm not challenging the OP; I don't have enough experience with sprocs to have an informed opinion, but this information is necessary to make sense of the OP's criticism. The best argument against sprocs that I've heard is that you really don't want any code running on your database hosts that you don't absolutely need because it steals CPU cycles from those hosts and they don't scale horizontally as well as stateless web servers. This is a completely different argument than the OP's, however.
- cameronh90 6y agoDatabases are engineered for high reliability - data integrity above all else. That means they develop more slowly and are perpetually behind what you expect from a modern programming language. If someone created a database with the intention of it being a good development framework, it would probably be more pleasurable to code against, but would you trust it with your data?
- richardfey 6y ago> Databases are engineered for high reliability - data integrity above all else. So should be most of your (micro)services. I have seen more instances of sloppy system design (non-transactional but sold as such) than coherent eventually consistent ones.
- cameronh90 6y agoSurely that's situation dependent? Databases are built to handle anything up to and including financial and health data. On the other hand, does it matter if my social network updoot microservice loses a few transactions? With code running outside of the database, you get to decide how careful you want to be.
- richardfey 6y agoYes it does. Are you mentioning in the internal services documentation that they can lose data second unknown conditions? And on your product hunt page that "your content might be randomly lost"? If you do not then you are lying. Everybody using the service expects transactional consistency. Note that what you described is not eventual consistency but rather "certain non-determinism", there is an abyss of difference.
- cameronh90 6y agoI don't mean specifically with transactional consistency (I don't think I even mentioned eventual consistency), but just generally the level of engineering required to write a system as well tested as postgres inevitably will slow down its feature development. This means databases don't have the latest features typically enjoyed within application development environments. However I believe you CAN tolerate some level of failure and defects in your app code, knowing the more battle hardened database will - for the most part - ensure your data is safe once committed. Yes, there will always probably be bugs and yes some of those bugs may cause data loss in extreme cases, but if you're saying you perform the same level of testing and validation on a product hunt style app as you would on a safety critical system, or as postgres do on their database, I find that extraordinary and very unrepresentative of most application development. I'm not saying defects are good or tolerated when found, but from an economic perspective you have to weigh up the additional cost of testing and verification against the likely impact these unknown bugs could have. Obviously everyone expects any given service to work correctly - but when is that ever true outside of medical, automotive and aerospace which have notoriously slow development cycles? Personally I'd pick rapid development over complete reliability in most cases.
- aszen 6y agoBecuase of lack of tooling there is tons of duplicate logic in SQL based systems, I see the same logic repeated across queries which already exists in some view but no knows about it so it ends up being repeated. Lack of abstractions like modules and namespaces for logically grouping stored procedures makes it hard to understand where is what
- fiddlerwoaroof 6y agoEvery database I’ve used has namespaces: most application code ignores SQL schemas, but that’s what they’re for.
- aszen 6y agoThanks that is very interesting, I would imagine having all the data in one schema, and then few other schemas containing views and functions grouped together logically.
- cerved 6y agobut they're just one level, no?
- fiddlerwoaroof 6y agoYeah, but conventional naming is almost as good: foo_bar_baz.users and foo_bar_other.users
- deleted 6y ago[deleted]
- mdoms 6y agoSay for example you need two queries, one for fetching a paged list of customers ordered by birth date, and one for fetching a paged list of customers who are male. You will find you'll need two queries with a huge amount of duplicate logic - the SELECT clauses, even if they're pulling the same columns, will need to be duplicated (don't forget to keep them in sync when the table changes!), because there's no reasonable way to compose the query from a common SELECT clause. The paging logic will be duplicated for the same reason. So although the queries are similar, only sorted and filtered differently, you will need to duplicate logic if you want to avoid pulling the entire data set first and performing filtering and sorting in separate modules. These problems become significantly worse when you're talking about inserting, updating or upserting data that involves validation and business rules. These rules are not only more difficult to implement in SQL, but they change more often than the structure of data (in most cases) so the duplication becomes a huge issue. I haven't tried this on a system that uses Postgres functions so I could be way off base here, my experience was pure sprocs in MS SQL.
- panarky 6y agoViews help with this. Write one view for selecting customers, with all the joins, sub-queries and case-when logic. Then select from this view order by birth date, and select from the same view by sex. Yes, the limit/offset paging clause will be duplicated, but all the joins, sub-queries and case-when stuff can be DRY'd in a single view.
- DenisM 6y agoMSSQL can certainly do all of that. panarky offered one way of doing it, but you can also write a single SQL Function that takes a parameter for filtering by gender (or not), and encapsulates the pagination logic. You will then be able to select form that function passing different parameters for filtering by gender and only adding a sort at the end. Likewise the business rule validation could be encapsulated in triggers or in a stored procedure that would validate all changes, much like your C# code would.
- astine 6y agoI'm trying to figure out what kind of code duplication that you'd be dealing with that you can get around by avoid stored procedures. Unless you're relying on an ORM to construct your queries for you, you're going to have SQL queries somewhere in your code. It's either going to be stored in application code or in the database but either way you're going to have the same problem.
- cneu 6y ago21 years ago, I joined a young team that wrote an ERP, with all business logic in PL/SQL. Customers are still amazed how fast these guys are going. 10 years ago I joined Oracle to work on APEX (created in 1999), which is a web app development and runtime environment, written in PL/SQL. We estimate that there are about 260K developers world-wide who are developing apps with APEX. All of their business logic is also on the server. Call me biased because I am working for the man, but my data points are completely different.
- zubairq 6y agoI agree, Apex is awesome. Oracle Apex being written in stored procedures was actually a big inspiration for a low code tool project I did myself, which while not being written in stored procedures took alot of other ideas from Apex (I was an Oracle Apex developer for 4 years)
- sbuttgereit 6y agoI worked with two other ERP class systems that did this, one using Oracle (with an Oracle Forms/Reports front-end), the other PostgreSQL. I can confirm your experience. There's no doubt that stored procedure/function based business logic requires a certain discipline, knowledge set, and the ability to get a team in marching in the same direction. But if you can achieve the organizational discipline to make it work there are definite advantages, especially in the ERP space. And that may well be differentiator... those of us working with more traditional business systems have trade-offs that you won't find in a startup. For example, a COTS ERP system is more likely to not really be a single "application", but a bunch of applications sharing data. This means a lot of application servers, integrations, etc., not all from the people that made the ERP, needing access to the ERP data. The easiest integration is often at the database, especially since you can have technology from different decades (and made according to the fashions of their time) needing to access that one common denominator. Since many ERP systems are built on traditional RDBMSs, and talking to those doesn't change much over long periods of time... having the logic be there to make these best of breed systems work sensibly with the ERP can be very helpful. All that said, in this context I'm an advocate of the approach. Now, take a team that maybe has a high churn, sees the database as some dark and frighting mystical power that can only safely be approached with an ORM, or a team that thinks their "cowboy coding" is their core strength (if perhaps not that thought directly) and database based business logic certainly has many foot-guns. But, there are really few technologies that don't suffer without a good well-rounded, consistent approach.
- deleted 6y ago[deleted]
- ak39 6y agoIf you write your views, tvf and stored procedures to use common elements defined once and once only (this starts with once and once only tables), maintenance is easy peasy. If you let every developer create their own view for the same business logic, then you are creating an unmanageable creature. This also applies to "composable" code written in the application layer outside the database.
- Retric 6y agoThe issue is when central ideas about how your application work changes. Suppose you’re a US bank and after a few decades you want to open a branch in Europe and suddenly every dollar value needs to be associated with a specific currency. At it’s core that’s a very basic change, but the issue with this stuff is how your adapting to such changes without introducing massive technical debt. Databases that elegantly to your data are easy to work with. However, it’s always tempting to use the lazy solution, which is why systems relying on stored procedures tends to age poorly. In effect they tend to corrupt the purity of your schema.
- RussianCow 6y agoI don't see how that has anything to do with stored procedures. For such large changes, your schema and application logic both need to be changed anyway. So if you're already changing the schema, why is it easier to change the application logic if it lives in a separate codebase? If anything, it seems like it would go the other way around: With stored procedures, you're guaranteed that any application logic is at least consistent with the current database schema, whereas with separate services, you need to maintain that consistency yourself. Not that I'm advocating for stored procedures in general because they come with a mountain of other drawbacks, I just don't buy this particular argument.
- Retric 6y ago> why is it easier to change the application logic if it lives in a separate codebase? Because stored procedures don’t include the ability to do things like use inheritance to easily add extra layers of abstraction and let the compiler detect when you’re messing up. Adding a column is easy enough, but enforcing that it means something and every one of your prior calculations are now meaningless without taking it into account is difficult. Making such changes elegant and enforcing them to avoid future bugs is even harder. Remember this example is at it’s core a minor difference. If you’re changing something like amounts being measured by volume instead of weight, then basic assumptions no longer hold making things much worse.
- xupybd 6y agoI have seen large database heavy apps. They are the cleanest I've ever worked with. I've had no trouble composing SQL. I can't think of one example where this has been an issue. You need to rely on views, functions and tables to store your business rules and look up the rules. The big down side I see is the learning curve. Writing business logic in the database is not easy and not easy to maintain. It can take half a day to grow a large function. Even longer to debug sometimes. But you learn to structure things well.
- moocowtruck 6y agoi never want to go back to this, and i'm not sure why people love to relive 'glory days'
- dacracot 6y agoSpreading your business logic across all of your code base is a bad idea in any language. Not doing it as a primary design pattern is language agnostic. Your argument has nothing to do with whether the code executes in the database, a JVM, or native to the OS.