Which to use ORM or SQL?

Without a doubt, the ORM – Object Relational Mapping – model makes life easier for many developers. Its objective is to be the bridge between the world of OOP and the world of relational models.

But is he always the best choice? Which to use, ORM or SQL? Let’s check it out along with examples in Python and SQL!

ORM

The interface that the ORM provides to the developer is linked to calling methods and passing parameters. Like this code below in python:

stmt = select(User).where(User.name.in_(["juliana", 'sandy']) 

Not having to worry about the query to be performed is actually a great advantage of this approach.

On the other hand, performing a select would be like this:

SELECT * FROM User
                   WHERE name = "Julian"
                                  OR name = "sandy"

I could have used the like instead of the equals sign, but it is usually accompanied by special characters ( %, _ ) to determine a match.

OK… in this example it might not even be that easy. Imagine then creating a join:

stmt_join = select(User.fullname, Address.email_address).join_from(Address, User)

Your SQL version:

SELECT u.fullname, u.email_address
               FROM User AS u
               JOIN Address AS a

Since this is a natural join the join clause has been omitted, otherwise we could insert: ON u.id == a.id_user

The ORM – Object Relational Mapping – model provides transparency, in the construction of the SQL query, and consequently in the access to the database. Really for many scenarios it is the best approach, or the simplest

SQL CORE

But if they were just flowers, why then do we have the CORE version (like in SQLalchemy with python)?

sql_insert = text("insert into user values(2,'Mary','[email protected]','Ma')")
engine.execute(sql_insert)

For simple queries (most cases) the ORM makes our work easier. However, have you ever come across those giant queries? Well characteristics of the area of data analysis, for example?

Imagine the hypothetical scenario of checking the contact list of students enrolled in graduate school and adjunct students enrolled in classes in the graduate program

with students as (
       -- single_students
       SELECT id FROM user AS u
           INNER JOIN user_has_class AS uhc
               ON u.id == uhc.id_user
               AND uhc.date == date_trunc(year, current_date())
            WHERE u.active is true
UNION
       -- students_enrolled in the pos
       SELECT id FROM user AS u
            INNER JOIN user_has_program AS uhp
                 ON u.id == uhc.id_user
                 AND uhc.date == date_trunc(year, current_date())
            WHERE u.active is true
);

SELECT u.name AS 'Name',
        u.email,
        u.phone as 'Contact'
FROM User AS U WHERE u.id IN (students)
ORDER BY 1;

What to do? ORM or SQL?

In this scenario, the mapping to be done becomes much more complicated. Many times you will scour the ORM documentation to find the way to structure that query.

This one above was just an example, something much more complex than this one.

So… the time it may take you to adapt to the ORM, may not be worth it! This is one of the reasons why good old SQL is used over ORM.

Sometimes taking the time to learn a new skill, like SQL, can make your life easier!

For more information about the world of technology follow

– Juliana Mascarenhas
– Dalbert Mascarenhas
– Simplifying Networks/Programming
– Youtube channel: https://lnkd.in/dm_4uMyp
– Site/blog SR: www.simplificandoredes.com

Who better understand what databases are? Access the link to the SR article: Understand the which are database