Common SQL database mistakes

SQL databases are predominant in professional applications, even though NoSQL databases have gained popularity in recent years for some specific use cases. They allow data to be stored, organized, and manipulated reliably. However, even after several years of development, some mistakes keep occurring and can cause performance, maintenance, or reliability issues.

A poor database design may seem to work at first, then become a real problem as the amount of data increases. In this article, we will look at the most common mistakes.

Not using indexes correctly

Indexes are essential for improving SQL query performance. However, some developers completely forget about them or do not use them correctly.

Without an index, the database has to scan the entire table to find the requested data. And with a certain amount of data, slow queries can seriously impact application responsiveness.

Example:

SELECT *
FROM users
WHERE email = 'user@example.com';

If the table contains several million users and the email column has no index, the database will have to analyze every row. You will need to be patient.

An index can speed up this type of search:

CREATE INDEX idx_users_email
ON users(email);

However, you should not add indexes everywhere. Each index takes space and slows down write operations (INSERT, UPDATE, DELETE) because it also needs to be updated, which impacts performance as well.

The goal is therefore to create indexes on columns that are actually used for searches, joins, or sorting. To go further, you also need to choose the right type of index. For this, you can ask your favorite generative AI to explain the advantages and disadvantages of each type.

Misusing transactions

Transactions ensure data consistency through the ACID properties:

• Atomicity;
• Consistency;
• Isolation;
• Durability.

A common mistake is not using a transaction when an operation involves multiple changes.

Example:

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

If the second query fails, the money has been removed from the first account but never added to the second one.

With a transaction:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

In case of a problem, a ROLLBACK can cancel all modifications.

On the other hand, keeping transactions open for too long can also cause problems by blocking other queries. And this can quickly become a disaster in production 🙁 I have had production issues because of this...

Storing dates as text

A common mistake is storing dates in a text column:

date VARCHAR(50)

This makes searches, sorting, and calculations more complicated. For example, comparing dates stored as text can produce incorrect results depending on the format used.

It is better to use dedicated types provided by databases:

created_at TIMESTAMP

or:

created_at DATE

Databases can then correctly perform date operations. It also saves disk space.

It is also recommended to store dates in UTC when the application is used across multiple time zones.

Using VARCHAR everywhere

This paragraph is a bit redundant with the one just above, but I really wanted to have a dedicated paragraph about dates because this is a mistake I see very often.

Another common mistake is using VARCHAR for almost every column.

Example:

age VARCHAR(10)

when an age is a numeric value. Using the correct data types allows the database to better optimize storage and operations.

Some examples:

• INTEGER for integer values;
• DECIMAL for financial values;
• BOOLEAN for true/false values;
• DATE or TIMESTAMP for dates;
• TEXT for long content.

Choosing the right types also helps prevent inconsistent data.

For example, an INTEGER column will prevent accidentally storing:

"thirty years old"

in a column representing an age.

Poor handling of NULL values

NULL is often misunderstood by beginners. I made this mistake myself when I started.

NULL does not mean zero, an empty string, or a default value. It represents the absence of a value.

For example:

SELECT *
FROM users
WHERE phone IS NULL;

is correct.

However:

WHERE phone = NULL

will not work because NULL cannot be compared using the = operator. I have been caught by this behavior several times and lost quite a few hours because of it...

You also need to think carefully before allowing columns to accept NULL. A required column should generally be defined with:

email VARCHAR(255) NOT NULL

This prevents incomplete data from being stored.

Overusing ORMs without understanding the generated SQL

ORMs (Object-Relational Mapping) such as Doctrine, Hibernate, or Entity Framework allow developers to manipulate databases using objects instead of writing SQL directly. They are very useful, but they can also hide problems. A common mistake is forgetting about the SQL that is actually executed.

A classic example is the N+1 problem.

We retrieve a list of users:

users = User.objects.all()

Then for each user:

for user in users:
    print(user.orders)

The ORM may generate one SQL query to retrieve the users, then an additional query for each user to retrieve their orders.

With 10,000 users, this can generate 10,001 queries.

It is therefore important to understand the SQL generated by the ORM and use the available tools to analyze performance.

Personally, I have mixed memories of these tools. I found them quite heavy... But maybe I should have given them more time.

Conclusion

When you are just starting out, avoid using generative AI to create SQL queries without understanding them. First, use it to review and validate the queries you have written yourself. This will help you learn SQL concepts and improve your ability to solve problems.

Once you have gained more experience, do not hesitate to use AI tools to speed up your development process. They can be very useful for exploring different approaches, optimizing queries, or helping you solve more complex problems.

SQL databases are powerful tools; you just need to learn how to master them 🙂 With experience and curiosity, everyone can learn how to get the most out of their power!