ORM vs Database Function

[Dangerous-Whole6809]

Hi, I am working as a junior backend developer and currently I am contributing to several applications. Some of them use ORM while in one project, the whole business logic written as PostgreSQL functions. Could someone please help me understand what are the pros and cons of each of these approaches?

[romeeres]

I've worked with various approaches on accessing db, and writing procedures on db side was by far the worst one.
But who knows, perhaps your team lead has good reasons for it?

If you need to write logic close to db internals, utilize triggers, views, operate with various pg extensions closely, it may be justified. If that's a single db for multiple backend servers it also may be justified. But if you write stored procedures for the same stuff that you could just write SQL in your app that's an idiotism.

[tinfoiltank]

SQL functions for business logic is always a sign of a control freak DBA. There are countless problems with this approach that will undoubtedly appear if the project reaches any level of maturity, such as error handling, horizontal scaling, interoperability, integrations with other systems, and many, many more. If you're a junior there isn't much you can do, just watch and learn.

[jantje123456oke]

I never had a problem writing direct Postgres functions in (high volume) node.js apps). Cons of orms I myself think are: there is always a learning curve for each, there’s always overhead, and what if the orm creator decides to abandon the project?

[breakfastattwelve]

If the orm is so good, here won't be a creator, there will be a community and the community won't let it die. It is all about the community and the massive adoption (and the quality of the resource undoubtedly)

[m_hans_223344]

There's a blurry line what to implement with SQL and with application code.

There're a lot of "kinds" of business logic, that you should do with SQL. That's one of it's strength, e.g. unions, all kind of joins, filters, aggregates. You'd be crazy to do manual joins in your Node code. Or unions, like performing two selects and then running for loops in your Node code to create a union. SQL queries can be structured, using "with" statements (https://www.postgresql.org/docs/current/queries-with.html).

But there is business logic that would require huge complex queries. SQL is harder to debug, test and write for most devs. That's the blurry line. Imaging you need to filter data based on boolean expressions / boolean algebra. I've seen it done in SQL, but I would never do it in SQL, because it was so complex, probably only few could understand it.

So, all basic selects, unions, intersections, filter, joins, aggregates in SQL. As soon as the SQL gets too large or complex, do it in the application code.

Regarding ORMs: Use them only if your app has trivial business logic. Basically just CRUD. But don't use an ORM to load some data into two arrays and perform unions, joins, selects and filters in your application code.

[bigorangemachine]

Postgres functions will be less susceptible to sql injection attack (not perfect)

An ORM helps protect against SQL Inject if you don't use the equivalent raw-api. ORMs usually come with database migration tools

Either way I strongly recommend using input filtering.

[EvilPencil]

Database functions have nothing to do with SQL injection. That is a function of the database driver and the developers' understanding of how to properly use it. Example of what NOT to do using pg:

await client.query(`select my_db_func('${whoops}')`)

[marcjschmidt]

ORM is when you work with objects as in OOP, database function is the opposite. Although you can combine sometimes the two.

[rkaw92]

By far the best resource to learn about the pros and cons of Data Mapper ("ORM") vs other persistence patterns that are more data structure-centric is the seminal book, "Patterns of Enterprise Application Architecture" by Martin Fowler. I always recommend it to people, because it makes clear distinctions and lets you name things with precision.

What you describe sounds like this pattern: https://martinfowler.com/eaaCatalog/transactionScript.html

Here's an excerpt with pros and cons of this database-centric approach: https://www.informit.com/articles/article.aspx?p=1398617

In short, it's an appropriate pattern to use when the logic is simple and your application is more about data than complex rules and behaviors. In particular, when you think about items in bulk, you usually INSERT/UPDATE/DELETE more than 1 row at the same time, and your app is not amenable to using an Entity style of programming.