DEV Community: Hamza Junaid

What actually is ACID in databases?

Hamza Junaid — Sun, 17 Aug 2025 12:57:27 +0000

Working with databases we often see the flashy headline on the websites of database vendors claiming their database is ACID compliant. Sure, we do learn a thing or two about ACID properties in Database courses as well. Let's take a peak into what is actually possible and if the ACID claims really fits all the time?

It all comes down to the concept of transaction and if we assume that the idea of database executing a transaction was not there then ACID would not really mean anything.

Transaction means that if I tell the database to perform an operation then it is not considered done until it is fully completed and committed. Consider an example where you tell the database to give your top 5 users a discount of 10% and it goes ahead updating the records in the table. If due to any reason all 5 users are not updated, the transaction will not be completed and it will be considered as if no update request was ever recorded. The database should stand at where it was before the user executed the command.

If you grasp the idea of a transaction then understanding the 4 properties in the acronym of ACID becomes simple. So let's take a look one by one.

1- Atomicity

In chemistry terms we an atom is an entity which is the smallest possible unit (well not true entirely because we know about neutrons, protons, electrons etc. etc. but lets just assume it is). When we say that a database is Atomic it simply implies that any operation executed will be treated as an indivisible unit which will mean all or nothing. It is exactly what is discussed above when we were talking about a transaction.

2- Consistency

This one is a bit controversial. Lets discuss why I say that. Consistency in simpler terms mean that our data is always in a state of correct condition. We discussed earlier that databases that support transactions already ensures that operations performed always follow "all or nothing" sort of pattern. How come consistency come into picture when atomicity is already there for a low level guarantee. If we think carefully then maintaining and ensuring consistency usually falls into the responsibility of the application. Database is keeping its promise of keeping its state perfect but its up to us to provide that data. If I don't keep the correct track of invariants(constraints) then eventually the data stored will be inconsistent. We cannot put the burden of our definition of consistency onto the database even though it gives us the ability to put those checks to maintain a state of data stored onto it.

3- Isolation

The guarantee that an operations is done in a manner such that it treats itself as the only operation happening at the moment is called Isolation. Lets simplify this. In case of reading and updating a value, a transaction will assume that there is no other transaction meddling with this data. Database providers use different techniques to achieve this and there are several levels they use to support isolation. It is indeed a challenging task and there are various studies on this. We can discuss in a different article.

4- Durability

It is simply the promise that anything assumed to have been given to the database will be considered available and logged onto it. It would not be the expectation that we give something (execute a command to insert a record in a table) to it and we are not able read it back. The database who is ACID compliant will make sure of the durability and will take care of the reads and writes properly getting committed onto permanent disks without any problems.

Conclusion

The much advertised ACID compliance of databases is less of a flashy feature and more of a set of fundamental promises that make reliable data systems possible. Atomicity, Consistency, Isolation, and Durability together form the foundation, but they are not magic bullets because application design will still play a significant role in keeping data truly meaningful. A database may guarantee transactions and durability, but it is up to us to define the right invariants and enforce them correctly. Understanding what each property really means allows us to better appreciate where the database ends and where our responsibility begins. With this clarity, ACID stops being just an acronym and becomes a practical guide for building dependable systems.

Raw SQL or ORMs? Why ORMs are a preferred choice

Hamza Junaid — Sat, 02 Aug 2025 19:19:25 +0000

When I started working as a Software Engineer about two and a half years ago, I had a rough idea of what ORMs were but didn’t fully appreciate their value. Like most people coming out of university or jumping into CS career, I was more familiar with raw SQL. Writing "SELECT * FROM Users WHERE id = ..." felt natural. It’s what we did in class, in side projects, and in tutorials.

Fast forward to now after working on multiple projects using Entity Framework Core in ASP.NET Core and Django’s ORM, I’ve realized that ORMs are not just a convenience but essential tools in modern software development. Tbh I can't imagine building a serious application without one.

My first project (a serious one) involved working on APIs using ASP.NET Core where I was introduced to Entity Framework Core. I remember being a bit skeptical at first. It felt odd not writing SQL directly. I thought "Is this really doing what I think it's doing under the hood? What if it generates terrible queries?"

But over time, I started to see the benefits. I didn’t have to write boilerplate SQL. I didn’t have to worry about SQL injection (because ORMs handle that automatically). And my code became easier to read and maintain. Everything just clicked.

Then I worked on Django. Same story, Django’s ORM was powering almost everything behind the scenes. But this time I knew the drill and leaned into it quite easily.

So, Why Do I (and almost everyone in the Software Industry) Prefer ORMs?

1. Safety Comes Built In

One of the biggest advantages of using an ORM is the built in protection against SQL injection which is a very serious threat in real world applications.

With Django’s ORM, query parameterization happens automatically. You’re not directly injecting values into SQL strings but instead the ORM safely escapes and handles them under the hood.

Let's see the difference.

Say we want to get a user by email, and we’re accepting the email value from a request. Take a look at what a risky SQL query looks like:

def get_user_by_email_raw(email):
    query = f"SELECT * FROM users WHERE email = '{email}'"
    with connection.cursor() as cursor:
        cursor.execute(query)  # SQL Injection is possible here
        result = cursor.fetchone()
    return result

If someone passes email = "test@example.com' OR '1'='1", this query becomes

SELECT * FROM users WHERE email = 'test@example.com' OR '1'='1'

Boom!! every user could be exposed. This is a pretty common SQL injection vulnerability.

Yes, you can use parameterized queries with cursor.execute(), but the problem is that you have to remember to do it. Every single time.

Now here’s how Django ORM handles the same logic,

def get_user_by_email_orm(email):
    return User.objects.filter(email=email).first()

This is not only cleaner, but also safe by default. The ORM automatically parameterizes the query behind the scenes no matter what value email holds, it won’t break the query or expose data.

And here’s the actual SQL Django generates (with safe placeholders):

SELECT * FROM users WHERE email = %s

2. Migrations Save You Headaches

I didn’t fully appreciate this until I had to manage schema changes on a live project with real users and a team pushing updates every sprint.

With any ORM, you could just update the model, generate a migration, and apply it .No need to manually write ALTER TABLE statements or keep track of which SQL script ran where.

Let’s say I needed to add a PhoneNumber field to the User model:

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
}

I would just need to run the following commands,

dotnet ef migrations add AddPhoneNumber
dotnet ef database update

That’s it. EF Core handled generating the SQL, versioned the change, and I could share that migration with my team via source control. Zero manual SQL, zero risk of “hey, did you run that script yet?” in staging or production.

Now compare that with raw SQL:
ALTER TABLE Users ADD PhoneNumber NVARCHAR(20);

On its own, it looks harmless. But now I’m responsible for tracking it, making sure it runs in all environments, and writing rollback scripts in case something goes wrong. Multiply that by multiple changes per sprint, and you see how quickly things get messy.

Schema changes are tracked alongside your code, they’re repeatable, and you avoid the classic "it works on my machine" when the database isn't in sync.

3. Readable Code Matters

When you revisit code after a few months or when someone else has to pick up your work, readable code helps. ORM queries are usually easier to understand at a glance than long SQL statements buried in strings.

Let me show you what I mean.

Suppose I need to get all users who placed an order in the last 30 days, with the total amount spent, sorted by their spend:

query = """
    SELECT u.id, u.email, SUM(o.amount) as total_spent
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.created_at >= NOW() - INTERVAL '30 days'
    GROUP BY u.id, u.email
    ORDER BY total_spent DESC
    LIMIT 10;
"""

with connection.cursor() as cursor:
    cursor.execute(query)
    result = cursor.fetchall()

Does it work? Yes. Is it efficient? Probably. But someone reading this will have to parse through raw SQL syntax, figure out aliases, and mentally map it back to the models. Trust me I had to do this while writing this example.

Django ORM Version
Here’s the same logic using Django ORM with annotations and aggregation:

from django.db.models import Sum, F
from django.utils.timezone import now, timedelta

thirty_days_ago = now() - timedelta(days=30)

top_spenders = (
    User.objects
    .filter(order__created_at__gte=thirty_days_ago)
    .annotate(total_spent=Sum('order__amount'))
    .order_by('-total_spent')[:10]
)

This is more readable, especially for developers familiar with the model structure. You immediately knew that we were dealing with Users, Orders, and that we’re calculating total spending in a time window.

Key Takeaways

ORMs save time, reduce bugs, and handle a lot of repetitive work like query building, input sanitization, and database migrations. More importantly, they help you focus on solving business problems instead of worrying about SQL syntax or database versioning.

At the end of the day, both raw SQL and ORMs are tools and like any tool, it’s about using the right one at the right time. But if we are building anything that needs to scale and be maintained by more than one person, I’m reaching for the ORM first. Every time.