SQL vs ORM - Just Stay With SQL!
I am a data guy. I know how to write Python code for Matplotlib, Pandas, and some general tips and tricks.
Coming from data, I also know SQL. But I don't know anything about web frameworks or backend development, and I could go on with this list for an hour.
I realised that if I want to build data apps, I need to learn some of those things. One of the first topics that came up is ORM.
What is an ORM?
ORM (Object Relational Mapping) is a programming technique that lets you interact with a relational database using the objects and classes in your programming language instead of writing raw SQL.
In the ORM context, classes are the tables, and objects are the rows from the table.
Here is an example table transformed into Python objects:
There are many options for ORM in Python:
SQLAlchemy
Peewee
The Django ORM
and a lot more...
Let's see some code examples:
If we want to query the data in SQL, we would do something like this:
SELECT * FROM users WHERE age > 30;
The same in ORM would look like this:
results = session.query(User).filter(User.age > 30).all()
Filter is our Where statement, All is the *, and Query is the Select from.
You can also replicate group by and use aggregate functions.
SELECT AVG(age) FROM users;
The above SQL is equal to the below ORM:
avg_age = session.query(func.avg(User.age)).scalar()
And similarly for Group by:
SELECT age, COUNT(*) FROM users
WHERE age > 25
GROUP BY age;
results = session.query(User.age, func.count()) \
.filter(User.age > 25) \
.group_by(User.age) \
.all()
As you can see, we can easily achieve the same thing with pure Python code, keeping the Object Oriented look and feel.
This is the biggest advantage of ORM. You don't need to jump languages, and it makes fast and clean development possible.
Also, if the underlying database were to change, for example, from SQLite to MySQL, ORM would still work the same way.
If you are super comfortable in Python and don't know a lot about SQL, it is a great alternative, but there are disadvantages as well.
With more complex queries, the syntax can be harder to write, and the execution may slow down. That's why it's a good idea to be ready to go back to raw SQL.
When we use ORM, we shift the data manipulation into the application itself, we do not handle it separately. It's not an issue if the app design is ok, but it can be chaotic.
Conclusion
I realised during reading and learning about ORMs that I should stay with raw SQL. I already know that well, and I am comfortable working with it.
I see the debate on the internet about which is better. Just like with everything else, there are advantages and disadvantages to both concepts. But the biggest advantage of SQL over ORM for me is that I know it already. Why would I learn the deep details of a new concept if I can easily replace it with SQL?
Coming from data, in this case, is good. On the other hand, if you are leaning towards ORMs, I would suggest to still learn SQL. ORM is great, but for complex problems, SQL will be superior. Also, the logic (and even the main syntax) is similar, so being familiar with SQL will not hurt.
The biggest conclusion for me was this:
Every shiny new framework or tool looks good in a simplified, small example.
Don’t fall for it and try to learn everything.
Focus on battle-tested tools that have stood the test of time.
In this case, it is SQL for me. It was great to learn the concept and basics of ORM, but it's not worth going deeper.