When should you use REST over ORM/DBAL to do Database Operations

Some Background Study

Let’s say, you are architecting a scalable software. Obviously, you would utilize some kind of persistent data storage for your production. If you are developing some kind of transactional software like an ERP/Ecommerce, you are probably more focused into a relational database software like MySQL or MSSQL or PostgreSQL.

Now, one common difficulty for every software architect while working with relational databases, is the ‘Object Relational Impedance Mismatch’ problem. The idea is very straightforward. You design and model your software or programming concept based on Objects, while you store your data in relational tables. Now, how do you map them? As these are different data structures, mapping and utilizing them, is a programming difficulty here.

What is ORM?

In the software world, ORM (Object Relational Mapper) is a form of tool, that exist to resolve the problem. It basically is mapping your relational data into object. That allows you to think your software and data both are stored and operated in Objects. As data is not really stored in an Object, hence, ORM creates an abstraction layer to realize the difference to your software. Using ORM for software design is very useful from architectural point of view, because it takes and returns the same kind of data structure, that you use to design/model your software. It becomes easy to perceive the architecture.

How does DBAL fits here?

As ORM creates an abstraction layer for it’s purpose, it can also be called ‘Database Abstraction Layer’. This is merely conceptual. Now, one purpose of DBAL over ORM, is that DBAL creates the abstraction to keep your software independent from database software alone (Most ORM does the same or helps to achieve the same). So, if you use any of MySQL / MSSQL / Oracle SQL / PostgreSQL or any other SQL based DB engine, it will operate on the same syntax and keep your software compatible out of the box.

So, it’s great, then what’s the problem?

It is obviously conceptually great. But there are cases, when it is better not to use ORM/DBAL. One of the architectural sense of creating abstraction layer in software engineering is that, it creates imperative codes for your mapping. Now, what’s the problem with Imperative codes? If you are unware, Imperative programming refers to the style of programming where we are mainly focused on ‘HOW’ to do something. SQL or relational databases are essentially declarative style of programming, who are focused on ‘WHAT’ to do. For example, when we say ‘select * from users’, it directly gives you the data exists in the users table. We do not care about how does it do it, we only care about the results.

There obviously a catch of declarative code is that, it also creates some kind of abstraction, which uses imperative approach, but it’s underline. For example a Loop in Imperative C is obviously much faster than a loop in Imperative Python (Note: Python can be used in declarative way). But if your software’s imperative code is converting a declarative approach to an imperative piece, then sadly it’s going to be slow, sluggish and less able to scale. ORM or DBAL unfortunately suffers the same. ORM creates an abstraction layer or a slow imperative layer to run declarative SQL. Other cons on using ORM is that, it can duplicate or hobble database structure on your business logic as you are no longer thinking declarative, rather imperative while using ORM.

So where does REST fits in?

To have quicker, simpler and futuristic development, no developer is going to plugin RAW SQL codes in their software. An alternative is to use a declarative REST based api, that uses declarative approach for your database with minimal imperative codes. You can run and consume them as a service with the database software alone. Functional programming is closely related to declarative programming, hence for maximizing performance, you might want to choose a REST api tool that is written in some kind of functional programming language or done in a functional way like with Haskell.

Many database software already started giving HTTP as service protocol with the database software alone, that works in declarative approaches. MySQL comes with a MySQL Router API. PostgreSQL has a solid pREST written in Haskell available in Github. As C# can be written in declarative approach same like Python, there are numerous declarative REST tools written in C# for MSSQL/SQL.

Conclusion

Consuming Declarative API is more like a frontend topic these days since Javascript is taking more functional and declarative futures. React wants you to write codes in declarative approaches, and provides more tools in coming releases with declarative helpers. It is still an odd idea to utilize declarative programming to solve a long existing problem with Object Relational Impedance Mismatch. But then, in the era of millions of users, a declarative database access tool, can cut down a huge amount of cost for infrastructure.

How to Empty a Model In Odoo / Mass Delete in Odoo / Mass Unlink

In Odoo, we use unlink() ORM method to delete a record. But if you are trying to empty a model or wants to delete multiple records, the best way to do it, is to use two steps.

First, search the records:

record_set = self.env['your.model'].search([])

Second, unlink them all at once instead of looping through them:

record_set.unlink()

If you have a list of ids, search them using the list:

ids = [1, 2, 3, 10, 11, 12]
record_set = self.env['your.model'].search([('id', 'in', ids)])

and unlink:

record_set.unlink()

Remember, there is no need to loop through this iterable object, odoo unlink does it for you.

TIPS: If you want to get all the ids in a model directly in a list, you can use the following:

record_list = self.env['your.model'].search([]).ids