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!
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
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
Who better understand what databases are? Access the link to the SR article: Understand the which are database
Data Scientist and Master in Computer Modeling by LNCC.