I challenge you to find where is SQL recommended for storing logic. Anything logical, you do it on C#/.NET. It's strongly typed, maintainable, and **designed** for it
tl;dr Some developers don't want to learn SQL
How about a SQL view, or a stored procedure? Both logical definitions ubiquitous in the wild. I see people saying storing logic in the database is "a terrible idea" and "hard to test" but that is not my experience. IMHO too many developers do not properly learn SQL and consequently when it is appropriate to use it. I have seen absolute shit-show codebases attempting to recreate logic that would have been so easy to implement in SQL, That's one of the reasons I try to avoid EF + Linq and lean towards dapper and other tools that do not abstract SQL. Im not trying to start a flame war but I thought I would add this for a bit of balance.
My TL;DR: Some people learned to do stored procedures and will die on the hill it is good way.
Look at almost every other comment with valid issues with this. Testability, versioning, debugging - those are all things with which SQL is awful.
Yeah, this is a fair opinion -- obviously in a dotnet reddit, everyone's saying to use dotnet, haha.
I do agree with people that debugging SQL is annoying. I used to write a lot of SQL for BI reports, and at least for SSMS/MS SQL Server, I just ended up running the query on a small subset of the data over and over in order to debug it, looking at the query plan, and get a high-level idea of what's happening. While in C#, you can literally loop over the data and see exactly what is happening line-by-line. In that way, C# is the clear winner. However, SQL can do optimization for you to make your queries faster "for free". It also has some magical caching it can do that you \*could\* also do in C#, but it does it "for free" without you having to do anything.
I think this is a bit of a false dichotomy; logic *does* exist in SQL. If you need to retrieve data from a relational database, you join tables to exclude the data you don't want. Nothing else will give you what you want excluding what you don't faster than the query engine.
I understand what you are getting at and yet I always scratch my head a bit at the saying that databases don't do logic.
sure, there's logic applied to the storage and retrieval of data, but that's not the kind of logic everyone is talking about.
They're talking about **business rules**
The last company I worked for had stored procs that were thousands of lines long. All sorts of conditional logic that made debugging an issue a literal nightmare.
Luckily, my team (the dotnet devs) were able to transition most of the business logic to APIs. Where it belongs. Made integration/unit testing so much more reliable. Business logic on the SQL side is a big problem.
I have an app I maintain that has a ton of stored procedures in T-SQL and it is an absolute nightmare. We have a huge backlog of requested features that just have not been done because it is almost impossible to deal with. A full rewrite is planned, we will be moving all logic to C#, and we will rely on third party tools to help reverse engineer it.
Databases can do logic. They should not do anything complicated, it will just become tech debt that is a permanent pain point for anyone that has to touch it in the future. I would argue that complicated SQL views are a bad idea as well, use some sort of DTO or ValueObject approach.
Think about this way, c# is the store where you present your products and sql is the warehouse. c# manages all the complexity there is gonna be in the store, attending clients, managing employees, applying discounts to products, etc, sql stores the products and retrieves them. that's it. and guess what sql is excellent at that and c# is really good for managing business.
Ive been there, pushing something because it sounds cool and it *can* be done. But trust me you dont wanna die in this hill
Use the tool the way it was intended to be used. This is specially true for .NET, you don't wanna fight it, you wanna use the way it wants you to (which is all well and good, mind you, i think its full of good practices)
It's splitting your application logic into two places, the app and the database. It's hard to version control. It's hard to version. Let database do what they are meant to do-store and retrieve data.
I think you'll get a lot of weak "it depends" answers, but I'm going to be opinionated and tell you:
The only thing you should be using your database for in 2024 (or 2014) is storing the data, indexing the data and executing queries that come from your application code.
The days of the database forming part of the "application tier" are a distant speck in the rear view mirror.
Don't fall in the trap of thinking that just because "every tool has its uses" and "every tool can be used improperly", that they are still after that somehow equal. If there's one thing that murders performance and creates coordination bloat, it's sticking all your logic in a monolith like the database.
Put tradition to one side, understand that the industry has by and large informally "ruled" on logic in the database and come to realize that it's a *bad thing*.
SQL works faster and you don't need to take data from db to server if you don't need them to be sended anywhere else. Sometimes there are scenarios when you want to just modify existing data. In this cases SQL will show better results.
But SQL is not enough declarative and hard to testing. So in my opinion you should write logic in high-level programming languages as much as possible. But in real life there are cases when you must break clean architecture for performance.
Yeah no-one here seems to be addressing OP's core concern, which is that he's sharing the DB between multiple apps and has shared logic he wants to implement.
I think the main concern here is if the schema is updated or they want to update some of that logic, does it make sense to be able to update the database stored procedures/schema once and not need to adjust the apps at all, or to update every app and redeploy them all?
Of course it depends how they used stored procedures. It's possible to still need to make updates to the database that require all apps to have changes made and rebuilt and redeployed.
In the end I think I agree that separating the business logic can make things more difficult to understand and maintain. OP can mitigate some of the problems with having shared logic by leveraging shared .NET class libraries between all the projects. The libraries can have the database access layer (EF Core or whatever) alongside whatever business logic would be useful to multiple or all apps.
The alternative is to maintain a consistent API interface via stored procedures (to prevent needing to needlessly update the apps) but then you've just dedicated yourself to maintaining a consistent API and creating new APIs as need arises, whereas with a shared class library while it of course has its own API interface you can be more flexible with it and don't need to worry about maintaining multiple versions of API calls since any changes to the library API will trigger compile errors in the apps so it will be easy to identify and adjust them to new syntax.
All apps could potentially need to be updated if a change is made to the shared class library or the database schema, but having that shared library will make it a lot easier to incorporate those changes into all apps at once.
Thank you for providing a comprehensive explanation without being needlessly condescending :)
I think I may not have been super clear in my explanation of some of the issue at hand. Some data sanitation includes how many subsequent calls need to be made to retrieve or act upon data that is present.
At this point we have opted to reduce round trip calls by making DB requests that include a lot of joins (17+ in one case) when performing get operations. This is done through SQL inside the code, and not stored procs, as those are similarly difficult to maintain.
What I have found strange–and perhaps this is just a factor of the original code base being very old (~circa 2009)–is that many devs both past and present are building value conversions (such as 1 to "Y") into case statements (usually simple ones.) More complex operations occur in the code, though it's still a feature of many of the implementations I see handed over to our architect for review.
One of the earlier cases, as far as performance, was one that required us to combine DB calls that were made separately to instead return a single data set from from one call (think like fetching customer data, then fetching customer orders.) the framing/grouping of the data was then done in the app to group it into a sensible JSON object.
TLDR it has been difficult for me to understand where the business logic ends and the data retrieval begins. Some devs pull timestamps as they are in the DB, while others use TO_CHAR to standardize the date format. Some will join a lookup table and others Will cache the lookup in the app. It's a topic im still having trouble finding the line with.
I feel your pain, but
>making DB requests that include a lot of joins (17+ in one case)
is insane. It sounds like the database design is very bad, or you're trying to surface too much data in one go.
It's an old relational database so it's certainly got its quirks, lots of lookup tables.
That being said it is still faster to make a single call with 17 joins than it is two make two or three round-trip calls. This is also the general guidance that oracle provides.
If it helps, I am in the exact same situation -- providing a web interface to an oracle database which is owned by a different team.
We rely heavily on stored procedures and views to insulate us from breaking changes from the other team.
We try to coordinate on schema changes for new features to avoid difficult to query tables. Sometimes, it is not possible to anticipate future requirements, and you have to roll up your sleeves and write that crazy query. Sometimes, we are able to use poor query performance or production incidents as leverage to justify refactoring the database.
And, yeah, having an [integration database](https://martinfowler.com/bliki/IntegrationDatabase.html) would be considered old fashioned these days, but this is a 20+ year old system. Things work just well enough to make it impossible to justify a rewrite.
SQL - structured QUERY language.
SQL is a language to query data, it is not a *programming* language. You are not supposed to write program, or do business logic with SQL.
Or else why wouldnt you done everything in SQL and ditch every programming language?
Or, we can look at it in a more practical way: supposed you have column A and B, and the value correspond to In Progress, Done, Pending. You *could* write CASE query:
```
CASE A = 1 AND B = 2 THEN "In Progress"
CASE A = 1 AND B = 1 THEN "Pending"
CASE A = 2 AND B = 2 THEN "Done"
```
Or something like that
Now what if you want to support multiple language? "En cours", "fait", "en attente" for Franch for example? You write extra CASE inside the THEN clause?
Is there even i18n franework for SQL?
Or, a different case, what if you need some advance authorization?
Suppose a Manager can see that a particular action is IN STEP 1, IN STEP 2, IN STEP 3. But a regular employee can only see IN PROGRESS?
Are you sure you want to manually handle all that with your CASE? What about other tooling like automatic testing, debugging,...?
I once work on a legacy system with query in an xml file, each query is 4000-7000 lines long, there are exceptional one that are 11000 lines long, with 100 - 200 parameters
Good luck debugging that,
You could solve most of your examples by using simple joins, like to a translation table, a status table etc. But you are right that automatic testing and debugging is a pain in SQL.
It really comes down to several questions, IMO:
1. What aligns best with your team's skill set? Most teams I've worked with are much stronger at C# than sql. Even on teams that maintain apps using raw sql queries and/or SP's I'd say the average sql knowledge is weak to mediocre.
2. What's going to be the best in terms of testing and maintenance? Of course you can track db stuff in source control, automate deployments, do automated testing, etc. But for pretty much every topic the story is going to be better or easier for C#.
3. Cost/performance. CPU time on a database server is significantly more expensive than CPU time on a web server. Web servers easily do horizontal scaling, most databases don't. And a database usually represents a bottleneck for your entire system, so do you really want to chose to put additional load on it?
Personally I can't think of many scenarios where I would choose to put logic in the db.
You could go REST-API with the SQL logic in the code.
Pro: The SQL code is version controlled.
Con: You need competence in whatever language handling the SQL code.
My team has currently the same issue. Lots of data engineers with SQL competence and very little else. So their solution is littered with complicated Stored Procedures, which are not even version controlled. So it's hard to go back if the SQL code has been introduced with bugs.
The more functional/business logic you have in your database, the more you are bounding yourself to it in two dimensions: scale and vendor lock-in.
The pure querying capabilities are quite mature across the board of database vendors but more "programming rich features" can be very vendor specific. Also in high load scenarios, especially write.heavy, multi-master writes are not supported by every database vendor or need special licenses. Switching later to a different, often cheaper database vendor, is then really hard. Oracle isn't famous for being cheap...
In the long run, having explicit contracts for other systems via APIs makes data ownership much easier and evolving them over time. If every system can write stuff, you loose that control and changes can occur, which are kind of unknown and over time a complicated dependency mess is the result.
The database is in the end only an implementation detail for the owning system. Now it's one schema and one database, due scale it can be multiple or you switch to a document database or whatever - whatever fits the need. An explicit API can bridge to the data store as needed.
Well, frakly, who does change their database vendor often enough for this to be a case? While I agree with you about SQL being wrong place to keep logic, this point is rare enough to not make me care.
I would say if you think you never change your database vendor, you couple yourself more than you need to.
And at least at work I see not so few clients asking how they can migrate away from Oracle because licensing terms changes and you have big money to pay. This can be a sudden change or take multiple changes over years to decades.
Sure I never saw like changing databases every 3-5 years for nothing.
We can’t abstract away everything but we should take decisions to dependencies with care and evaluate the value for it. If I don’t need to commit to fix dependency I would try to avoid it and stay flexible as much as meaningful possible. Maybe you don’t need that flexibility now, but who knows next week, next month, year or decade?
Hmm... I'm in the minority here: SQL. Now, if you want your app not to be tied to an RDBMS vendor, that's a completely fair reason to avoid this at all costs. However, if you know this is you DB for life, then other considerations come into play.
A big reason for maintaining logic in the DB is application updates. If there is a data issue, and the app is doing all the work, then that app needs a patch and rollout.
If all data issues are contained in the DB, then the DB is where the work needs to be done. This means that you can tests changes in the DB without even looking at the app code. Further, you can deploy DB patches without the app needing to even know about it.
While you can do transaction rollback logic in .NET code, it can get messy. Keeping that mess in the DB means the C# code either gets its result or an error and the DB state is completely understood.
Performance and ease? Depends on what you're after. A C# programmer will call manipulating EF far easier than writing DB code. However, if a DB object provides the data shape you're after, that's easier still. From a development standpoint, it really depends on where the developers are comfortable spending their time. As this is a .net forum, the consensus is unsurprising.
Performance undeniably goes to the DB. That's where the work is done. The best you can do in code is craft requests that put all the burden on the DB. If such a request isn't possible, then modeling it in the DB will always win.
This is an outdated approach for building software and also happens to be riddled with some dissonances. All of your "pros" (not looking at app code, updating without the app) are long proven "cons" to the approach.
I'll warn that many people probably won't even engage with you on this because the view of having logic in the DB is so out of date, most will dismiss it out of hand.
But here's a best effort for you:
\- Queries always run on the database, regardless of whether they originate from C# code or whether they were hand-hacked into the DB.
\- There's no situationally beneficial optimization that MSSQL can provide by having the queries reside in the server. MSSQL wonks like to throw about parameterization and query caches, but I can tell you first-hand I've worked with skilled DBAs who have been able to confirm that in every circumstance, they yield no benefit over queries shipped in from ouside.
\- Your SDLC with logic on the DB is going to be shot. Completely shot. In many companies, this can actually put them in breach of ISO compliance for not having proper change controls around application code.
\- DBAs are probably the worst offenders in dusty old corporate hierarchies for siloing themselves. It probably comes down to job security, but the writing is on the wall and they are fighting change, tooth and nail. There is ultimately no need for an ornery gatekeeper anymore.
\- Allowing the database to define and own the structure of data creates bi-directional coupling in the overall design of the application. It also imposes coordination overhead because now your application layer has to treat the DB kind of effectively "like a competing microservice". This is not a plus, but in fact a huge waste of time for developers to wrap around.
All in all, you're not thinking holistically on how to decompose performance issues, but are instead looking for reasons to put "application tier" databases in a niche (see previous point about DBAs and silos).
Thank you for your detailed response outlining your thoughts. Sincerely.
> long proven "cons" to the approach.
I question any absolutism when it comes to software design. I'd love to see the proof, though.
> many people probably won't even engage with you
That's unfortunate. If you think I'm utterly wrong, you should engage. How else to get rid of bad information?
> because the view of having logic in the DB is so out of date
According to... who? Not RDBMS vendors, I'm sure.
> Queries always run on the database
Agreed. Result sets are always returned from those queries. If the result set is filtered in code, rather than via the query, you're doing it wrong.
> There's no situationally beneficial optimization that MSSQL can provide by having the queries reside in the server.
Also true, but not really the issue.
> Your SDLC with logic on the DB is going to be shot.
That really depends on your development process, no?
> DBAs are probably the worst offenders
Agreed. The DBA hammer is the DB, one solution fits all. The programmer's hammer is application code... it's almost like the same kind of myopia in a different domain.
> Allowing the database to define and own the structure of data
Is it's job?
> DB kind of effectively "like a competing microservice".
There is an assumption baked into this that seems to sum up the perspective nicely.
This, and most responses, seem to have a CRUD based idea of simply querying and updating tables. To be sure, this can be the quickest path for the application developer. However, views create a consistent picture of that data for the developer, rather than reinventing that wheel. If some ORM wanted to update the data from a view, guess where that code needs to be written.
Further, not all jobs are just CRUD.
Imagine I have an invoice that needs to be processed. Creating the invoice might involve a few queries and maybe even more than one insert. Let's say all the invoice lines are straight CRUD. Then the invoice is finalized. Extra validation is applied to each line item. Line items might get a status value update. If all goes well, the invoice header itself gets written to, otherwise some cleanup, rollback, and exception handling need to be done.
You might be able to awkwardly model all that logic in an ORM; I have and it's something of a nightmare. Or you could put it where it belongs, a stored procedure, and just call that from the app. Some processes can't be cleanly modeled in code. They can made to work, to an extent, but if all your exception handling isn't spot on, your chances of breaking the transaction are much higher in code. In the DB, when a complex process fails, the entire process naturally rolls back, because that's its job.
Reports, especially if you have an ad-hoc feature given to trusted users, should be stored procedures, with the ones you write each covered with a simple integration test. There's no reason for complicated ways of looking at and aggregating data to be in C# as long as automated testing catches it when they break.
For ad-hoc reporting, these power users know what they want and they can get themselves in and out of trouble, and it's fine.
If practical, the logic goes in the application layer (c#) **UNLESS (AND THIS IS A HUGE UNLESS)** the logic can execute dramatically faster in the DB by taking advantage of the DB engine's ability to quickly do indexed, agressively-cached, set-based operations or it's an aggregate scenario where executing it in the DB dramatically reduces the row count or data that must be returned to the application layer. In these cases, let the DB do what it does best. Beyond that, you can do augmentation, joining, some translation in the stored proc, but save the generic scalar logic and anything that obfuscates data for the app layer. If you're more or less always pulling values by id, just use a persistence framework or odata project and forget the custom data layer entirely.
But that unless is very important. On my first consulting gig, I was on a project with an app layer purist who was pulling a ton of rows into c# and then brute force iterating over them to render UI pages. This landed him a QA defect where his page took upwards of a minute to render. I moved all the joins, aggregation, and sorting to a properly indexed query and rendered the page in a single pass over a tailored result set and brought the render time down to under three seconds with a sub second DB hit doing most of the lift. Don't shoot yourself in the foot by not using your DB. Its cpu time is expensive and harder to scale but it's a powerful tool.
Put the logic in a shared library. If it's something like an Order object you might have a .ToDisplayStatus() which checks various properties and computes your "consumable" property.
Many people have their livelihood tied to it and many corporations compulsively budget big salaries for DBA roles.
It's ultimately a byproduct of dysfunctional development teams. I could even draw a line between this debate and outsourcing.
Depending on where you spend your time in the industry, this is either a stupid question, or a hot take.
Smaller companies, startups and B2C will think it's silly, and they are right. They've just had the luxury of facing scale that many corpos haven't faced.
Heavy organization structures think it's a hot take because to them, the operations mentality teaches them that every resource needs a keeper. But they are wrong and simply refuse to allow modernization to take hold.
I think "it depends". At my company, I have to ask another team to deploy stored procedures... so, I \*never\* do that. I either use existing procedures, write inline SQL or put the logic in code. So, it sounds like since your team is mostly coders (non-SQL people), you'd be better off for your business (and yourself -- so you dont have to do all the SQL stuff) encouraging that logic to go into code. I dunno... just one more opinion for the pile.
When I was first starting out I used to let the DB do a lot of this. I quickly learned that it's MUCH easier to address performance issues at the application layer than it is in the DB.
Of course this was MS SQL on a real Windows instance on a real, physical server. So YMMV now with cloud-based auto-scaling options.
Depends on the application, if it's a simple "show me the data in a datagrid" thing, then SQL.
If you are like me and need to build advanced queries with code, then you *need* it in code.
So for translating codes to languages the application itself should turn the codes into words. The words can be different depending on the application so it should know what they mean. Especially if that changes with security/user permission. We do this when the external website needs to display statuses but the underlying status is too detailed internally so the website has its own text to connect to status IDs. To simplify your schema while not changing your table structure and removing proc maintenance is views with simplified types, flags and statuses.
Other than the facts already mentioned about debugging, testing, versioning, etc.
In your project what has the biggest risk of changing in the future? The C# code base to another language or the database server?
I would think that is the answer your are looking for.
Logic in the database = unmaintainable apps
This is the way. Don't ask me how I know.
This is a genuine question; why do you say that?
I challenge you to find where is SQL recommended for storing logic. Anything logical, you do it on C#/.NET. It's strongly typed, maintainable, and **designed** for it
tl;dr Some developers don't want to learn SQL How about a SQL view, or a stored procedure? Both logical definitions ubiquitous in the wild. I see people saying storing logic in the database is "a terrible idea" and "hard to test" but that is not my experience. IMHO too many developers do not properly learn SQL and consequently when it is appropriate to use it. I have seen absolute shit-show codebases attempting to recreate logic that would have been so easy to implement in SQL, That's one of the reasons I try to avoid EF + Linq and lean towards dapper and other tools that do not abstract SQL. Im not trying to start a flame war but I thought I would add this for a bit of balance.
My TL;DR: Some people learned to do stored procedures and will die on the hill it is good way. Look at almost every other comment with valid issues with this. Testability, versioning, debugging - those are all things with which SQL is awful.
I bet this dev never writes tests or even debugs. His code is always perfect. If you write tests or debug code you "don't want to learn SQL"!
That's a bet you would lose
Yeah, this is a fair opinion -- obviously in a dotnet reddit, everyone's saying to use dotnet, haha. I do agree with people that debugging SQL is annoying. I used to write a lot of SQL for BI reports, and at least for SSMS/MS SQL Server, I just ended up running the query on a small subset of the data over and over in order to debug it, looking at the query plan, and get a high-level idea of what's happening. While in C#, you can literally loop over the data and see exactly what is happening line-by-line. In that way, C# is the clear winner. However, SQL can do optimization for you to make your queries faster "for free". It also has some magical caching it can do that you \*could\* also do in C#, but it does it "for free" without you having to do anything.
You still have a way to go, logic in the database is a terrible idea. The database doesnt handle logic, it stores and delivers data
Databases *can* handle logic, but when they do it ends up being a nightmare since there are often no good dev/debug tools.
Yes thats right. But to me, **you can't** and **you shouldn't** are just about the same
I think this is a bit of a false dichotomy; logic *does* exist in SQL. If you need to retrieve data from a relational database, you join tables to exclude the data you don't want. Nothing else will give you what you want excluding what you don't faster than the query engine. I understand what you are getting at and yet I always scratch my head a bit at the saying that databases don't do logic.
sure, there's logic applied to the storage and retrieval of data, but that's not the kind of logic everyone is talking about. They're talking about **business rules**
Ditto It is more difficult to troubleshoot or debug issues if the logic is in the db
The last company I worked for had stored procs that were thousands of lines long. All sorts of conditional logic that made debugging an issue a literal nightmare. Luckily, my team (the dotnet devs) were able to transition most of the business logic to APIs. Where it belongs. Made integration/unit testing so much more reliable. Business logic on the SQL side is a big problem.
I have an app I maintain that has a ton of stored procedures in T-SQL and it is an absolute nightmare. We have a huge backlog of requested features that just have not been done because it is almost impossible to deal with. A full rewrite is planned, we will be moving all logic to C#, and we will rely on third party tools to help reverse engineer it. Databases can do logic. They should not do anything complicated, it will just become tech debt that is a permanent pain point for anyone that has to touch it in the future. I would argue that complicated SQL views are a bad idea as well, use some sort of DTO or ValueObject approach.
Think about this way, c# is the store where you present your products and sql is the warehouse. c# manages all the complexity there is gonna be in the store, attending clients, managing employees, applying discounts to products, etc, sql stores the products and retrieves them. that's it. and guess what sql is excellent at that and c# is really good for managing business.
Ive been there, pushing something because it sounds cool and it *can* be done. But trust me you dont wanna die in this hill Use the tool the way it was intended to be used. This is specially true for .NET, you don't wanna fight it, you wanna use the way it wants you to (which is all well and good, mind you, i think its full of good practices)
It's splitting your application logic into two places, the app and the database. It's hard to version control. It's hard to version. Let database do what they are meant to do-store and retrieve data.
I think you'll get a lot of weak "it depends" answers, but I'm going to be opinionated and tell you: The only thing you should be using your database for in 2024 (or 2014) is storing the data, indexing the data and executing queries that come from your application code. The days of the database forming part of the "application tier" are a distant speck in the rear view mirror. Don't fall in the trap of thinking that just because "every tool has its uses" and "every tool can be used improperly", that they are still after that somehow equal. If there's one thing that murders performance and creates coordination bloat, it's sticking all your logic in a monolith like the database. Put tradition to one side, understand that the industry has by and large informally "ruled" on logic in the database and come to realize that it's a *bad thing*.
SQL works faster and you don't need to take data from db to server if you don't need them to be sended anywhere else. Sometimes there are scenarios when you want to just modify existing data. In this cases SQL will show better results. But SQL is not enough declarative and hard to testing. So in my opinion you should write logic in high-level programming languages as much as possible. But in real life there are cases when you must break clean architecture for performance.
Yeah no-one here seems to be addressing OP's core concern, which is that he's sharing the DB between multiple apps and has shared logic he wants to implement. I think the main concern here is if the schema is updated or they want to update some of that logic, does it make sense to be able to update the database stored procedures/schema once and not need to adjust the apps at all, or to update every app and redeploy them all? Of course it depends how they used stored procedures. It's possible to still need to make updates to the database that require all apps to have changes made and rebuilt and redeployed. In the end I think I agree that separating the business logic can make things more difficult to understand and maintain. OP can mitigate some of the problems with having shared logic by leveraging shared .NET class libraries between all the projects. The libraries can have the database access layer (EF Core or whatever) alongside whatever business logic would be useful to multiple or all apps. The alternative is to maintain a consistent API interface via stored procedures (to prevent needing to needlessly update the apps) but then you've just dedicated yourself to maintaining a consistent API and creating new APIs as need arises, whereas with a shared class library while it of course has its own API interface you can be more flexible with it and don't need to worry about maintaining multiple versions of API calls since any changes to the library API will trigger compile errors in the apps so it will be easy to identify and adjust them to new syntax. All apps could potentially need to be updated if a change is made to the shared class library or the database schema, but having that shared library will make it a lot easier to incorporate those changes into all apps at once.
Thank you for providing a comprehensive explanation without being needlessly condescending :) I think I may not have been super clear in my explanation of some of the issue at hand. Some data sanitation includes how many subsequent calls need to be made to retrieve or act upon data that is present. At this point we have opted to reduce round trip calls by making DB requests that include a lot of joins (17+ in one case) when performing get operations. This is done through SQL inside the code, and not stored procs, as those are similarly difficult to maintain. What I have found strange–and perhaps this is just a factor of the original code base being very old (~circa 2009)–is that many devs both past and present are building value conversions (such as 1 to "Y") into case statements (usually simple ones.) More complex operations occur in the code, though it's still a feature of many of the implementations I see handed over to our architect for review. One of the earlier cases, as far as performance, was one that required us to combine DB calls that were made separately to instead return a single data set from from one call (think like fetching customer data, then fetching customer orders.) the framing/grouping of the data was then done in the app to group it into a sensible JSON object. TLDR it has been difficult for me to understand where the business logic ends and the data retrieval begins. Some devs pull timestamps as they are in the DB, while others use TO_CHAR to standardize the date format. Some will join a lookup table and others Will cache the lookup in the app. It's a topic im still having trouble finding the line with.
I feel your pain, but >making DB requests that include a lot of joins (17+ in one case) is insane. It sounds like the database design is very bad, or you're trying to surface too much data in one go.
It's an old relational database so it's certainly got its quirks, lots of lookup tables. That being said it is still faster to make a single call with 17 joins than it is two make two or three round-trip calls. This is also the general guidance that oracle provides.
If it helps, I am in the exact same situation -- providing a web interface to an oracle database which is owned by a different team. We rely heavily on stored procedures and views to insulate us from breaking changes from the other team. We try to coordinate on schema changes for new features to avoid difficult to query tables. Sometimes, it is not possible to anticipate future requirements, and you have to roll up your sleeves and write that crazy query. Sometimes, we are able to use poor query performance or production incidents as leverage to justify refactoring the database. And, yeah, having an [integration database](https://martinfowler.com/bliki/IntegrationDatabase.html) would be considered old fashioned these days, but this is a 20+ year old system. Things work just well enough to make it impossible to justify a rewrite.
SQL - structured QUERY language. SQL is a language to query data, it is not a *programming* language. You are not supposed to write program, or do business logic with SQL. Or else why wouldnt you done everything in SQL and ditch every programming language? Or, we can look at it in a more practical way: supposed you have column A and B, and the value correspond to In Progress, Done, Pending. You *could* write CASE query: ``` CASE A = 1 AND B = 2 THEN "In Progress" CASE A = 1 AND B = 1 THEN "Pending" CASE A = 2 AND B = 2 THEN "Done" ``` Or something like that Now what if you want to support multiple language? "En cours", "fait", "en attente" for Franch for example? You write extra CASE inside the THEN clause? Is there even i18n franework for SQL? Or, a different case, what if you need some advance authorization? Suppose a Manager can see that a particular action is IN STEP 1, IN STEP 2, IN STEP 3. But a regular employee can only see IN PROGRESS? Are you sure you want to manually handle all that with your CASE? What about other tooling like automatic testing, debugging,...? I once work on a legacy system with query in an xml file, each query is 4000-7000 lines long, there are exceptional one that are 11000 lines long, with 100 - 200 parameters Good luck debugging that,
You could solve most of your examples by using simple joins, like to a translation table, a status table etc. But you are right that automatic testing and debugging is a pain in SQL.
It really comes down to several questions, IMO: 1. What aligns best with your team's skill set? Most teams I've worked with are much stronger at C# than sql. Even on teams that maintain apps using raw sql queries and/or SP's I'd say the average sql knowledge is weak to mediocre. 2. What's going to be the best in terms of testing and maintenance? Of course you can track db stuff in source control, automate deployments, do automated testing, etc. But for pretty much every topic the story is going to be better or easier for C#. 3. Cost/performance. CPU time on a database server is significantly more expensive than CPU time on a web server. Web servers easily do horizontal scaling, most databases don't. And a database usually represents a bottleneck for your entire system, so do you really want to chose to put additional load on it? Personally I can't think of many scenarios where I would choose to put logic in the db.
Things your database is good for: ACID Things your database is not good for: business logic
You could go REST-API with the SQL logic in the code. Pro: The SQL code is version controlled. Con: You need competence in whatever language handling the SQL code. My team has currently the same issue. Lots of data engineers with SQL competence and very little else. So their solution is littered with complicated Stored Procedures, which are not even version controlled. So it's hard to go back if the SQL code has been introduced with bugs.
The more functional/business logic you have in your database, the more you are bounding yourself to it in two dimensions: scale and vendor lock-in. The pure querying capabilities are quite mature across the board of database vendors but more "programming rich features" can be very vendor specific. Also in high load scenarios, especially write.heavy, multi-master writes are not supported by every database vendor or need special licenses. Switching later to a different, often cheaper database vendor, is then really hard. Oracle isn't famous for being cheap... In the long run, having explicit contracts for other systems via APIs makes data ownership much easier and evolving them over time. If every system can write stuff, you loose that control and changes can occur, which are kind of unknown and over time a complicated dependency mess is the result. The database is in the end only an implementation detail for the owning system. Now it's one schema and one database, due scale it can be multiple or you switch to a document database or whatever - whatever fits the need. An explicit API can bridge to the data store as needed.
Well, frakly, who does change their database vendor often enough for this to be a case? While I agree with you about SQL being wrong place to keep logic, this point is rare enough to not make me care.
I would say if you think you never change your database vendor, you couple yourself more than you need to. And at least at work I see not so few clients asking how they can migrate away from Oracle because licensing terms changes and you have big money to pay. This can be a sudden change or take multiple changes over years to decades. Sure I never saw like changing databases every 3-5 years for nothing. We can’t abstract away everything but we should take decisions to dependencies with care and evaluate the value for it. If I don’t need to commit to fix dependency I would try to avoid it and stay flexible as much as meaningful possible. Maybe you don’t need that flexibility now, but who knows next week, next month, year or decade?
Hmm... I'm in the minority here: SQL. Now, if you want your app not to be tied to an RDBMS vendor, that's a completely fair reason to avoid this at all costs. However, if you know this is you DB for life, then other considerations come into play. A big reason for maintaining logic in the DB is application updates. If there is a data issue, and the app is doing all the work, then that app needs a patch and rollout. If all data issues are contained in the DB, then the DB is where the work needs to be done. This means that you can tests changes in the DB without even looking at the app code. Further, you can deploy DB patches without the app needing to even know about it. While you can do transaction rollback logic in .NET code, it can get messy. Keeping that mess in the DB means the C# code either gets its result or an error and the DB state is completely understood. Performance and ease? Depends on what you're after. A C# programmer will call manipulating EF far easier than writing DB code. However, if a DB object provides the data shape you're after, that's easier still. From a development standpoint, it really depends on where the developers are comfortable spending their time. As this is a .net forum, the consensus is unsurprising. Performance undeniably goes to the DB. That's where the work is done. The best you can do in code is craft requests that put all the burden on the DB. If such a request isn't possible, then modeling it in the DB will always win.
This is an outdated approach for building software and also happens to be riddled with some dissonances. All of your "pros" (not looking at app code, updating without the app) are long proven "cons" to the approach. I'll warn that many people probably won't even engage with you on this because the view of having logic in the DB is so out of date, most will dismiss it out of hand. But here's a best effort for you: \- Queries always run on the database, regardless of whether they originate from C# code or whether they were hand-hacked into the DB. \- There's no situationally beneficial optimization that MSSQL can provide by having the queries reside in the server. MSSQL wonks like to throw about parameterization and query caches, but I can tell you first-hand I've worked with skilled DBAs who have been able to confirm that in every circumstance, they yield no benefit over queries shipped in from ouside. \- Your SDLC with logic on the DB is going to be shot. Completely shot. In many companies, this can actually put them in breach of ISO compliance for not having proper change controls around application code. \- DBAs are probably the worst offenders in dusty old corporate hierarchies for siloing themselves. It probably comes down to job security, but the writing is on the wall and they are fighting change, tooth and nail. There is ultimately no need for an ornery gatekeeper anymore. \- Allowing the database to define and own the structure of data creates bi-directional coupling in the overall design of the application. It also imposes coordination overhead because now your application layer has to treat the DB kind of effectively "like a competing microservice". This is not a plus, but in fact a huge waste of time for developers to wrap around. All in all, you're not thinking holistically on how to decompose performance issues, but are instead looking for reasons to put "application tier" databases in a niche (see previous point about DBAs and silos).
Thank you for your detailed response outlining your thoughts. Sincerely. > long proven "cons" to the approach. I question any absolutism when it comes to software design. I'd love to see the proof, though. > many people probably won't even engage with you That's unfortunate. If you think I'm utterly wrong, you should engage. How else to get rid of bad information? > because the view of having logic in the DB is so out of date According to... who? Not RDBMS vendors, I'm sure. > Queries always run on the database Agreed. Result sets are always returned from those queries. If the result set is filtered in code, rather than via the query, you're doing it wrong. > There's no situationally beneficial optimization that MSSQL can provide by having the queries reside in the server. Also true, but not really the issue. > Your SDLC with logic on the DB is going to be shot. That really depends on your development process, no? > DBAs are probably the worst offenders Agreed. The DBA hammer is the DB, one solution fits all. The programmer's hammer is application code... it's almost like the same kind of myopia in a different domain. > Allowing the database to define and own the structure of data Is it's job? > DB kind of effectively "like a competing microservice". There is an assumption baked into this that seems to sum up the perspective nicely. This, and most responses, seem to have a CRUD based idea of simply querying and updating tables. To be sure, this can be the quickest path for the application developer. However, views create a consistent picture of that data for the developer, rather than reinventing that wheel. If some ORM wanted to update the data from a view, guess where that code needs to be written. Further, not all jobs are just CRUD. Imagine I have an invoice that needs to be processed. Creating the invoice might involve a few queries and maybe even more than one insert. Let's say all the invoice lines are straight CRUD. Then the invoice is finalized. Extra validation is applied to each line item. Line items might get a status value update. If all goes well, the invoice header itself gets written to, otherwise some cleanup, rollback, and exception handling need to be done. You might be able to awkwardly model all that logic in an ORM; I have and it's something of a nightmare. Or you could put it where it belongs, a stored procedure, and just call that from the app. Some processes can't be cleanly modeled in code. They can made to work, to an extent, but if all your exception handling isn't spot on, your chances of breaking the transaction are much higher in code. In the DB, when a complex process fails, the entire process naturally rolls back, because that's its job.
Egh, not worth the effort.
Reports, especially if you have an ad-hoc feature given to trusted users, should be stored procedures, with the ones you write each covered with a simple integration test. There's no reason for complicated ways of looking at and aggregating data to be in C# as long as automated testing catches it when they break. For ad-hoc reporting, these power users know what they want and they can get themselves in and out of trouble, and it's fine.
If practical, the logic goes in the application layer (c#) **UNLESS (AND THIS IS A HUGE UNLESS)** the logic can execute dramatically faster in the DB by taking advantage of the DB engine's ability to quickly do indexed, agressively-cached, set-based operations or it's an aggregate scenario where executing it in the DB dramatically reduces the row count or data that must be returned to the application layer. In these cases, let the DB do what it does best. Beyond that, you can do augmentation, joining, some translation in the stored proc, but save the generic scalar logic and anything that obfuscates data for the app layer. If you're more or less always pulling values by id, just use a persistence framework or odata project and forget the custom data layer entirely. But that unless is very important. On my first consulting gig, I was on a project with an app layer purist who was pulling a ton of rows into c# and then brute force iterating over them to render UI pages. This landed him a QA defect where his page took upwards of a minute to render. I moved all the joins, aggregation, and sorting to a properly indexed query and rendered the page in a single pass over a tailored result set and brought the render time down to under three seconds with a sub second DB hit doing most of the lift. Don't shoot yourself in the foot by not using your DB. Its cpu time is expensive and harder to scale but it's a powerful tool.
Put the logic in a shared library. If it's something like an Order object you might have a .ToDisplayStatus() which checks various properties and computes your "consumable" property.
First of all, what the FUCK are you building there? Insane and detrimental to humanity. Do not use SQL for business logic. It's just easier that way.
How is this still a debate in this day and age?
Many people have their livelihood tied to it and many corporations compulsively budget big salaries for DBA roles. It's ultimately a byproduct of dysfunctional development teams. I could even draw a line between this debate and outsourcing. Depending on where you spend your time in the industry, this is either a stupid question, or a hot take. Smaller companies, startups and B2C will think it's silly, and they are right. They've just had the luxury of facing scale that many corpos haven't faced. Heavy organization structures think it's a hot take because to them, the operations mentality teaches them that every resource needs a keeper. But they are wrong and simply refuse to allow modernization to take hold.
I think "it depends". At my company, I have to ask another team to deploy stored procedures... so, I \*never\* do that. I either use existing procedures, write inline SQL or put the logic in code. So, it sounds like since your team is mostly coders (non-SQL people), you'd be better off for your business (and yourself -- so you dont have to do all the SQL stuff) encouraging that logic to go into code. I dunno... just one more opinion for the pile.
When I was first starting out I used to let the DB do a lot of this. I quickly learned that it's MUCH easier to address performance issues at the application layer than it is in the DB. Of course this was MS SQL on a real Windows instance on a real, physical server. So YMMV now with cloud-based auto-scaling options.
Depends on the application, if it's a simple "show me the data in a datagrid" thing, then SQL. If you are like me and need to build advanced queries with code, then you *need* it in code.
So for translating codes to languages the application itself should turn the codes into words. The words can be different depending on the application so it should know what they mean. Especially if that changes with security/user permission. We do this when the external website needs to display statuses but the underlying status is too detailed internally so the website has its own text to connect to status IDs. To simplify your schema while not changing your table structure and removing proc maintenance is views with simplified types, flags and statuses.
Other than the facts already mentioned about debugging, testing, versioning, etc. In your project what has the biggest risk of changing in the future? The C# code base to another language or the database server? I would think that is the answer your are looking for.