The Foundations of Backend Engineering — Understanding Databases

The Warehouse of Your Data

Since you’ve come this far, you must have understood by now everything about HTTP requests, how your browser communicates with the backend, what happens when you click login, and stuff like that.

But now another question should bother you.

How does a website remember you?

No, I am not talking about authentication or authorization.

I’m asking you how the backend, which is technically just a server, stores information related to you.

Your cart items.

Your favourite movies.

Your liked videos.

Your watch history.

When you log in to your Netflix account, you must have noticed that it somehow remembers the movies you watched last week, or the movie you started but never finished.

Where does all of this information go?

That somehow is actually using databases.

A database is basically a system used to store, organize, and retrieve data.

But why do we even need databases?

I just told you why.

Imagine User A logs into your application and performs some task A, like adding a note.

Then User B logs into the same application and performs some task B, like editing a blog.

There must be some place where we can store all these operations and information so that when the users come back again, their data and state are still there.

This was the fundamental need for databases.

ACID Properties

Since databases are so important for an application to work properly, there must be some rules that databases should follow to keep our data safe and reliable, right?

ACID properties.

You will encounter this term in almost every textbook that talks about databases.

But before scaring you with its abbreviations, I’m simply going to ask you a few questions.

You will answer them yourself and develop the understanding.

Let’s start with A.

Atomicity

Have you ever done an online transaction?

If you have, you must know that either your money is debited completely or the transaction fails.

There is no such thing as:

“₹500 was supposed to be transferred, but only ₹250 got transferred.”

A transaction should either happen completely or not happen at all.

The same thing happens with databases.

Imagine you’re buying a Netflix subscription.

The database needs to:

Charge User
    ↓
Create Subscription
    ↓
Activate Subscription

What if the money is deducted but the subscription isn’t activated?

That’s a problem.

Atomicity ensures that the transaction is treated as one indivisible unit.

Either all required operations succeed…

Or the transaction is rolled back.

That’s Atomicity.

Consistency

Now imagine Netflix stores the number of movies you’ve watched.

Your current count is:

Movies Watched = 10

You start watching your 11th movie.

The database updates the count from 10 → 11.

But something goes wrong during the transaction.

The database should not end up with some invalid state like:

Movies Watched = -50

or violate some other rule defined for the data.

Consistency means that a transaction must take the database from one valid state to another valid state while respecting all defined rules and constraints.

So if the database was valid before the transaction, it should remain valid after the transaction.

That’s Consistency.

Isolation

Now imagine you’re watching Movie A on your Netflix account while your friend, who also has access to your account, starts watching Movie B at almost exactly the same time.

Two transactions are happening concurrently.

The database has to make sure that these transactions don’t interfere with each other in a way that produces corrupted or inconsistent results.

Ideally, the result should be as if the transactions were processed in some valid order.

Transaction A
      │
      ▼
Transaction B

or

Transaction B
      │
      ▼
Transaction A

The important thing is that one transaction should not see or create an invalid intermediate state caused by another concurrent transaction.

This property is called Isolation.

Durability

Now imagine you just finished watching a movie.

The database successfully stored it in your watch history.

And then…

BOOM.

Power goes out.

When the server comes back online, should Netflix suddenly forget that you watched the movie?

Obviously not.

Once a transaction has been successfully committed, its data should survive system crashes or power failures.

That’s what Durability means.

The database makes sure that committed data is persisted and isn’t simply lost when the system crashes.

So congratulations!

You have understood the four ACID properties.

A → Atomicity   → All or Nothing
C → Consistency → Valid State → Valid State
I → Isolation   → Transactions don't interfere incorrectly
D → Durability  → Committed Data Survives

Not that scary, right?

Types of Databases

Now that we know why databases are needed, another question naturally arises.

What types of databases are there?

Think about warehouses.

Some warehouses organize everything neatly into shelves and racks.

Others might store things in completely different structures depending on what they are storing.

Databases are somewhat similar.

At a very high level, we can divide them into:

1. SQL Databases

SQL databases, also called relational databases, store data in tables consisting of rows and columns.

Think of something like an Excel sheet.

Users

┌────┬──────────┬─────┐
│ ID │ Name     │ Age │
├────┼──────────┼─────┤
│ 1  │ Sachin   │ 22  │
│ 2  │ Rahul    │ 24  │
│ 3  │ Aman     │ 21  │
└────┴──────────┴─────┘

They have a predefined structure and are excellent when your data has clear relationships and consistency requirements.

2. NoSQL Databases

NoSQL databases don’t follow the traditional relational table structure.

They can store data in different models such as:

• Documents
• Key-value pairs
• Graphs
• Wide-column structures

For example, a document database might store a user like this:

{
    "name": "Sachin",
    "age": 22,
    "movies": [
        "Titanic",
        "Interstellar"
    ]
}

NoSQL databases generally provide more flexibility in how data is structured and can be useful for different types of workloads.

Neither SQL nor NoSQL is universally better.

Each has different use cases and should be chosen according to the requirements of your application.

Database Migrations

Now imagine you’re building a brand-new application.

You decide that your users table should contain:

age: number
name: string
email: string

This structure is called a schema.

Think of the schema as the blueprint of how your data is organized.

But here’s the problem.

Your application is growing.

Today you have:

name
age
email

Tomorrow you realize:

“Wait… I also need to store the user’s date of birth.”

So you need to change the database schema.

But can you simply modify the database whenever you feel like it?

NO.

Database changes are much more dangerous than changing a CSS file.

Imagine you decide to delete a column.

users.email

But another table depends on that column.

Or your application code still expects it to exist.

Now you’ve potentially broken your application.

And if you’re dealing with millions of records…

Good luck fixing that manually. 💀

That’s why we use Database Migrations.

A database migration is basically a controlled way of moving your database from its current state to a desired state.

For example:

Current Database

Users
├── name
├── age
└── email

        │
        │ Migration
        ▼

Desired Database

Users
├── name
├── age
├── email
└── date_of_birth

Migrations can involve changes such as:

• Creating tables
• Adding columns
• Removing columns
• Modifying constraints
• Creating indexes

NOTE: Database migration usually refers to changing the schema or structure of your database. Moving data from one database system to another is a different problem, although that can also involve migration work.

Specialized migration tools help developers manage these changes safely.

They also maintain a version history of database changes.

Think of it like Git…

But for your database schema.

Migration 001
     ↓
Migration 002
     ↓
Migration 003
     ↓
Migration 004

This allows developers to know exactly which changes have been applied and reproduce the same database structure across development, testing, and production environments.

There are different approaches to database migrations, including:

1. State-based migrations
2. Change-based migrations

Their detailed differences deserve an article of their own, so I’ll leave that rabbit hole for another day.

Indexing

Now comes one of my favourite database concepts.

Imagine your Netflix database has 100 million movies.

You want to find a movie with a particular ID.

Without an index, the database may have to scan a large portion of the table to find the matching row.

Imagine searching for a particular name in a book by reading every single page.

Phewww...

That is extremely inefficient.

Is that how developers actually do it?

OF COURSE NOT.

Developers always find a way to make existing technology faster.

But how?

Have you ever read a book?

What do you find at the beginning of a book?

Yes…

After the abstract…

INDEX.

YAYYY!

If you want to read Chapter 20, you don’t start from page 1 and turn every page until you reach Chapter 20.

You simply check the index, find the page number, and jump directly there.

Databases use a similar idea.

We create an index, which is a separate data structure that helps the database find rows much faster.

A common index structure is a B-tree.

Imagine our table:

Movies

ID       Movie
1        Titanic
2        Interstellar
3        Inception
...

If we frequently search using movie_id, we can create an index on that column.

movie_id → INDEX

1  → Titanic
2  → Interstellar
3  → Inception

Now the database can use the index to locate the required row much more efficiently instead of scanning the entire table.

But now another question.

Which column should we index?

There isn’t a magical formula that says:

“Always index this column.”

You look at how your application actually queries the database.

For example, if your application constantly searches users using their email:

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

then an index on email might make sense.

Similarly, if you’re constantly searching students using their roll number, indexing roll_number would be useful.

And yes…

You can index multiple columns.

You can even create a composite index involving multiple columns when your queries commonly filter or sort using those columns together.

But remember…

Indexes aren’t free.

They consume storage and can make INSERT, UPDATE, and DELETE operations more expensive because the indexes also need to be updated.

So you don’t simply index every column.

You index the columns that actually benefit your workload.

While researching this topic, I came across a wonderful and useful data structure called Bloom Filters.

Go read about them yourself.

It’s amazing.

Database Connection Pooling

Now let’s say your backend needs to talk to the database.

A database connection isn’t free.

Establishing a connection involves work such as authentication, network setup, and protocol negotiation.

So imagine you have 1,000 users constantly performing operations that require database queries.

If your application creates a brand-new database connection for every single query and then closes it immediately…

That would be extremely inefficient.

So how did developers solve this?

Like every other piece of technology…

They made it more efficient.

This is where Database Connection Pooling comes into the picture.

Instead of creating a new connection for every query, the application creates a pool of reusable database connections.

For example:

Application
     │
     ▼
┌──────────────────────┐
│   Connection Pool    │
│                      │
│  Connection 1  ✓     │
│  Connection 2  ✓     │
│  Connection 3  ✓     │
│  Connection 4  ✓     │
│  Connection 5  ✓     │
└──────────────────────┘
     │
     ▼
   Database

Suppose our backend has 10 connections in the pool.

A user sends a request that requires a database query.

The backend takes an available connection from the pool.

Connection Pool
      │
      │ Borrow connection
      ▼
   Query DB
      │
      │ Query finished
      ▼
Return connection
      │
      ▼
Connection Pool

The connection isn’t destroyed.

It simply goes back into the pool and waits for another request.

This prevents the application from repeatedly opening and closing database connections.

So this is what Database Connection Pooling is.

It is a strategy for reusing database connections and avoiding the expensive overhead of establishing a new connection for every database operation.

Wrapping Up

And with that…

We’ve completed a high-level overview of databases.

Of course, we can’t cover everything in one article.

Databases are an entire subject in themselves.

But for now, this is enough.

We’ve covered:

• Why databases are needed
• ACID properties
• SQL vs NoSQL
• Database migrations
• Indexing
• Connection pooling

And we’ve seen a few strategies that make databases faster and more reliable.

But wait…

We’ve been talking about making our database faster and faster.

What happens when our application suddenly gets millions of users?

Can a single database handle all of that traffic?

What happens when thousands of users are reading data at the same time?

What if the database itself becomes the bottleneck?

Are we just going to buy a bigger server and call it a day?

HAHAHAHA.

I LOVE SPEED.

Every developer does.

And so should you.

So in the next article, we’ll go one step further and explore strategies and technologies that allow our backend and database to handle massive amounts of traffic while staying fast and stable.

Till then…

Be curious. Be amazing.