I am a big fan of SQLite. It is still one of my favorite databases for both production use and learning, which is why I included it in the Bexlite tech stack.

So the question is: why?

For me, there are three big reasons:

  1. It is simple and requires almost no setup.
  2. It is free.
  3. It is embedded.

The first two are already great. You can create a .db file and get started immediately. Easy.

But the most interesting part is the embedded nature of SQLite. That is what I want to focus on in this article, because it is a huge reason why SQLite can be so fast compared to traditional client-server databases.

Have you ever heard of the N+1 query problem? Let me give you a simple example.

Imagine I have a script like this:

const products = db.products.all().limit(20);

for (product of products) {
  const reviews = prouduct.review().limit(5);
}

Yes, that is the classic N+1 problem. But why is it such a problem?

Because individual queries are expensive, especially when you are using databases like PostgreSQL, MySQL, SQL Server, and other client-server systems.

When I say "expensive," I mean there are multiple components involved in each query.

Untitled

In this example, I am trying to fetch a single query. The total time is about 36.579ms.

The planning time is 0.043ms.

The execution time is 0.203ms.

The total includes CPU I/O, disk I/O, and network time.

If we add the planning and execution time together, we only get 0.246ms.

So why does the total reach 36.579ms?

The real problem is the network

Take a look at my sketch below.

Untitled

You might say, "Bruh, it is only 36ms. Why do you care?"

Because I care about every little thing I build. And once you combine that cost with an N+1 problem, the numbers get ugly very fast.

Look at this example:

const products = db.products.all().limit(20);

for (product of products) {
  const reviews = product.reviews().limit(5);
  for (review of reviews) {
    const author = review.author();
  }
}

The number of queries becomes:

  • 1 product query
  • 20 review queries
  • 100 author queries

That is 121 queries in total.

If each one costs about 36ms, then:

121 * 36ms = 4356ms

That is around 4.5 seconds, just to fetch the data. That is unacceptable for many cases.

So yes, individual queries are expensive, and a huge part of that cost comes from network overhead.

So how do we fix it?

One answer is simple: remove the network.

That is where embedded databases like SQLite become really interesting.

Here is an example using embedded SQLite, where there is no network hop at all.

Untitled

A single query takes about 0.7ms.

So:

121 queries * 0.7ms = 84.7ms

That is still under 100ms.

Of course, it is much faster. Why? Because there is no network cost in the middle.

Now, you might say: "But why would anyone fetch data like that? No one does that in real life."

That is fair. This is an intentionally simple scenario to show why embedded databases can be much faster than many people expect. Even before heavy optimization, SQLite can perform extremely well.

To be clear, I am not saying SQLite is the right answer for every case. Not at all.

What I am saying is that SQLite is underestimated far too often. People love to call it a toy database, but that is not true. SQLite is a very good choice for many real applications, especially hobby projects, starter products, local-first apps, and a lot more production workloads than people usually assume.