Should you use Raw SQL or an ORM?

Should you use Raw SQL or an ORM?

SQL and ORM are two important tools for interacting with your database, but which is better for your project abstraction or power?

Interacting with databases is super crucial in becoming a badass developer. Whether you want to be a front-end, back-end or full-stack developer, you would encounter databases because most dynamic applications rely on user data. And you don’t want to be that guy who deletes an application table on the first day.

1_YkGgll6LxWTam1zH6TQafQ.jpeg

A database is an organised collection of structured information, typically stored electronically in a computer system. The database does not serve much use if you can not give, retrieve, update or delete data to enhance the user’s experience with the application. That’s where SQL(Do you pronounce it SEQUEL or ESS-CUE-EL ;) ?) comes in. It is a programming language created by Raymond Boyce and Donald Chamberlin in the ’70s (Yes, it’s that old!) to query, manipulate, define and provide access control used in nearly all relational databases even to this day. SQL acts as a middleman between your API and your database in some cases. The API tells SQL the actions it wants to perform, and then it goes to the database to carry out those actions and returns them to the API.

1.jpg A simple illustration of how SQL works

However, SQL comes with its downsides. One of the primary reasons I was hesitant about learning the programming language was how poor the interface was despite how straightforward it turned out to be. There are also many security flaws it is subject to because of how low the abstraction is. An inexperienced SQL programmer can easily fall prey to SQL Injections. A web security vulnerability issue allows an attacker to interfere with an application’s queries to a database, which often happens when passing parameters to a database. So you need to be skilled and highly aware of these vulnerabilities.

This is where ORMs come in, which lets you query and manipulate data from a database using object-oriented programming. This encapsulates the code needed to manage the data, so you do not have to write SQL anymore. The ORM(SQLAlchemy) generates the SQL statements, and then the database driver(pyscopg2) sends the SQL statements to the databa.se. The ORM heavily relies on the database driver to send the data to the database. ORMs are a more convenient option for people who are not comfortable with SQL because you can now manipulate data and define tables using Python objects(Or any other language OOP).

2.jpg An illustration on how Object Relational Mapper works

ORMs have become a popular option for most people, to the extent that it is integrated internally into some web frameworks(Django). They are powerful tools because they abstract most of the complicated parts of talking to a DB which reduces security attacks, and you would not need to write poorly structured SQL. But the high level of abstraction comes with its downsides because you would have less control, and ORMs are not lightweight tools, so it takes a colossal stab at performance gains which is a downside if you plan on scaling your project.

The choice between using an ORM or a Raw SQL is not as clear cut as you would think, and it depends on several factors from what you are comfortable with, the scale of application you are building, and what level of control you want over your app and so many others. I hope you found this article valuable, and happy coding ;).