what is the purpose of business login in api layer when all of it can be done in sql functions?-CodePudding

I've been programming api/font end apps for a while now with dotnet webapi & entity framework for orm then springboot with jpa, because of this my native sql query skill's are medicore so i recently ditched using any orm's in my side projects and retorted to calling native sql function's/view's/crud qurie's, after playing with these for a while i have so many questions,after using sql function's my api business layer barely has any logic, most of the codes only do data validation and pass the parameter's to the sql function via jdbc or jpa native query,

is sql functions doing most of the business logic normal and is it a good practice?
how do you handle error/success message return's to the client, right now iam returning json directly from pgsql but i feel like it is not the best practice.
what is faster? orm or using orm to call native sql scripts?

CodePudding user response：

Using SQL directly for CRUD operations is perfectly fine, I think.

ORM handles things like tracking which fields to update, in which order to do queries, mapping relations between objects, etc. But you can write maintainable and performant applications without it.

In theory, if you write your own SQL, it can be at least as fast or even faster than what an ORM does, but you need to remember to optimize things that an ORM would do out-of-the-box. Things like session caching, 2nd level caching, reusing prepared statements, batch processing, eager/lazy loading, etc.

There are some things that are much harder to implement and maintain in SQL, however. If your application is just CRUD on entities one at a time, then there is not much 'Business Logic' involved, and your SQL functions can perfectly handle this with simple INSERT/UPDATE/UPSERT/DELETE commands, and for read logic, I'd even recommend creating your own SELECT statements if you're good with it.

What do I consider 'Business Logic':

non-trivial validation
updates on multiple rows at once
updates that span different tables
conditional operations that relate to multiple rows or multiple tables
interaction with the user: present a non-trivial view, follow a flow, give feedback, ...

For use cases like this, you should first write the CRUD operations, either with SQL or ORM, and then write the actual use case in a bit of code that is independent of the CRUD layer. That way it's easier later if you need to change anything: you know where to look for what functionality.

CodePudding user response：

A question like this, gets answers based on opinions. Here's mine:

Business logic written in SQL is normal, but what is your definition of "normal"? In databases like Oracle, PostgreSQL and SQL Server you can write simple functions and procedures to do whatever it takes for your business.
Good practice is imho to a technology you and your team understand really well. When it's Python, you use Python, when it's SQL, use SQL, etc.
In PostgreSQL you can use exceptions when errors occur, your application can translate these to something useful for the end user. That's something you always need, no matter where you do the business logic.
"faster" can be related to development, to maintenance, but also to usage. Development and maintenance depends on your skills. In usage ORM is always slower, but the difference can be so futile that nobody cares. And an ORM can be much faster for development and maintenance.

We have almost all logic in the database, in SQL and PL/pgSQL, for raw speed where every millisecond counts. We just can't afford any network overhead. Is it the best? I don't know. Can we maintain it? Yes. Does it work? Yes. Is the customer happy? Yes, and that's the only thing that counts.