When working with databases in web applications or software, an important question arises: is it better to write SQL queries directly or use an abstraction layer such as SQLAlchemy? The answer depends on several factors, including security, performance, and ease of maintenance. In this article, we will explore both options so you can make the best decision for your needs.

Direct SQL Queries: What Are They?

A direct SQL query is one where you write SQL statements “by hand” in your code, using the Structured Query Language (SQL) to interact with the database. This gives you complete control over every aspect of the query.

Direct SQL Query Example

Imagine that you have a user database and need to retrieve the data for a specific user:

import sqlite3

conn = sqlite3.connect('mi_base_de_datos.db')
cursor = conn.cursor()

# Direct query
user_id = 1
query = f"SELECT * FROM usuarios WHERE id = {user_id}"
cursor.execute(query)

resultados = cursor.fetchall()

This SQL query is fairly simple, but it can become complicated or unsafe in more complex scenarios.

Advantages of Direct Queries

  1. Complete control: You have maximum control over how the query runs.

  2. Less abstraction: There are no intermediaries, which can translate into better performance when the query is well optimized.

  3. Flexibility: You can take advantage of advanced SQL features specific to the database system you are using.

Disadvantages of Direct Queries

  1. SQL Injection risk: If you concatenate values directly into queries without validation, you open the door to SQL Injection attacks, where an attacker can modify your query and even access or delete critical data.

  2. Difficult maintenance: Direct SQL queries tend to become difficult to manage as the project grows.

  3. Limited portability: If you change database systems, for example from MySQL to PostgreSQL, you will probably need to rewrite many queries.

Security in Direct Queries

To prevent SQL Injection attacks, the best practice is to use prepared statements or parameterized queries, where user data is handled safely.

query = "SELECT * FROM usuarios WHERE id = ?"
cursor.execute(query, (user_id,))

Here, ? is a placeholder that SQL handles safely, preventing an attacker from injecting malicious code.

Abstraction Layers: Using SQLAlchemy and Other ORMs

An ORM (Object-Relational Mapping) is a tool that lets you interact with a database using object-oriented code instead of writing SQL manually. SQLAlchemy is one of the most popular ORMs for Python.

SQLAlchemy Example

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker

# Database connection
engine = create_engine('sqlite:///mi_base_de_datos.db')
Session = sessionmaker(bind=engine)
session = Session()

# Query using SQLAlchemy
usuario = session.query(Usuario).filter_by(id=1).first()
print(usuario.nombre)

Instead of writing a SQL query manually, we use methods that the ORM translates into SQL for us.

Advantages of Using an ORM Such as SQLAlchemy

  1. Security: ORMs handle prepared queries automatically, which helps prevent SQL Injection attacks.

  2. Simple maintenance: Queries are easier to read and maintain, especially in large projects.

  3. Portability: Switching from one database to another is easier because you do not need to rewrite every SQL query.

  4. Relationship management: Databases often contain relationships between tables, and an ORM makes it easier to create and manage those relationships without writing complex SQL.

Disadvantages of Using an ORM

  1. Performance: Although SQLAlchemy is well optimized, writing SQL manually can be more efficient in some cases.

  2. Too much abstraction: You do not have as much control over queries, and optimizing very complex queries can be difficult.

Security in ORMs

One of the main advantages of using an ORM is that it handles security for you, protecting you from SQL Injection attacks without requiring much additional work.

Which Option Is Best?

The choice between direct SQL queries and an ORM such as SQLAlchemy depends on the type of project you are developing:

  • Direct SQL Queries are useful when you need complete control or when queries are simple and performance is critical. However, the security risk increases if you do not take appropriate precautions, such as using prepared statements.

  • ORMs such as SQLAlchemy are ideal for large projects where code readability and security matter more than extreme performance. The abstraction they provide also makes code easier to maintain and migrate between different databases.

Conclusion

If your priorities are security, readability, and scalability, using an abstraction layer such as SQLAlchemy is the best option. However, if you need to optimize a specific query or require complete control over every SQL query, writing it directly may be more appropriate, as long as you use security measures such as prepared statements to prevent SQL Injection.

In short, both options have their place, and understanding their strengths and weaknesses will help you make the best decision for your project.