Should you use Raw SQL or an ORM?
Interacting with databases is super important 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 are highly reliant on user data. And you don’t want to be that guy who deletes an application table on the first day.
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 on 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, then it goes to the database to carry out those actions and returns them to the API.
However, SQL comes with its downsides. One of the primary reasons I was hesitant about learning the programming language was because of how poor the interface is 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 ORM’s come in, a tool that 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. This is 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).
ORM’s have become a popular option for most people, to the extent, 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 ORM’s 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 a number factors from what you are comfortable with, the scale of application you are building, what level of control you want over your app and so many others. I hope you found this article useful and happy coding ;).
