Forem: Athreya aka Maneshwar

What Building with MCP Taught Me About Its Biggest Gap

Athreya aka Maneshwar — Mon, 20 Apr 2026 16:58:14 +0000

I spent the last few weeks wiring up MCP at my org, stitching a handful of internal tools (GitHub, Slack, Datadog) into a shared layer that multiple teams' AI agents could call into.

Useful. Powerful. And, about a week in, slightly alarming.

The same four or five "wait, doesn't MCP handle this?" questions kept coming up. Who's allowed to call this tool? What happens if a tool returns 50MB of data? Where are we logging any of this? How do I give Team A read-only access when Team B needs write?

Turns out: MCP doesn't handle any of it. Not because it's broken, because that's not what it's for.

MCP standardizes how agents talk to tools. It says nothing about who gets to, how much they can pull, or whether anyone's keeping receipts.

I can't drop my org's internal code into a blog post, so I rebuilt the same shape of problem in a tiny public repo.

Three MCP servers, one Gemini-driven agent, one minimal gateway, all runnable in five minutes.

So, MCP. What is it, again?

A thirty-second version of MCP, straight from the official docs:

The mental model that finally made it click for me: MCP standardizes the plug, not the power grid.

Your agent speaks MCP.

Your tools (GitHub, Slack, Datadog, your database) speak MCP.

They meet in the middle and everything Just Works.

Well. Almost everything.

The demo: one agent, three MCP servers, a Gemini brain

To make this concrete, I built the smallest possible setup, a repo anyone can clone and run in five minutes:

A GitHub MCP server that exposes get_readme, get_latest_commit, get_repo_files
A Slack MCP server that exposes send_message
A SQLite MCP server that exposes log_event, get_logs
A Gemini-driven agent that picks a tool, calls it, summarizes the result, and posts to Slack

Five processes, one loop. Here's what that actually looks like on screen:

Point the agent at a repo and off it goes:

taco@TCSIND-4shZvZXk:~/mcp$ node agent/agent.js
[agent] starting one-loop run
[agent] chosen tool: github.get_readme
[agent] summary: Ragfolio is an AI-powered portfolio template that uses RAG to answer professional questions based on your resume data. It is built with a modern stack including React, FastAPI, and Google Gemini for high-performance retrieval and generation.
[agent] demo loop complete

Try a bigger, more famous repo? Same agent, no code change:

taco@TCSIND-4shZvZXk:~/mcp$ node agent/agent.js https://github.com/vercel/next.js
[agent] starting one-loop run
[agent] target repo: https://github.com/vercel/next.js
[agent] objective: Summarize the project highlights for a dev audience.
[agent] chosen tool: github.get_readme
[agent] summary: Next.js is a full-stack React framework designed for building high-performance web applications with integrated Rust-based tooling. It extends the latest React features while providing optimized build processes and a robust ecosystem for enterprise-scale development.
[agent] demo loop complete

Slack confirms the summaries landed:

Everything works. High-fives all around. I'm ready to ship this to teams.

And then I actually think about what I just built.

What MCP quietly does not give you

MCP is a protocol. That's wonderful and that's also exactly the problem. Out of the box, vanilla MCP has:

No auth. Anyone who can reach port 4001 can call every tool on the GitHub server. In prod, that's a problem.
No RBAC. Every caller gets every tool, or no tools.
No audit. Unless you add logging to every server, by hand, there is no record of who called what.
No guardrails on outputs. If a tool returns a 2MB README, your agent happily eats 2MB of its context window. If a tool returns rm -rf /, your agent happily executes it too.
No shared policy layer. Every team ends up copy-pasting the same "validate tool name, wrap in { output, error }" boilerplate, each with its own subtly different bugs.

This is not a knock on MCP.

USB-C also doesn't come with a surge protector.

Those are separate products for good reasons.

But if you're running agents in an environment where the blast radius of "oops" is meaningful, you need that separate product.

The obvious place for that product to live? A gateway, sitting between every agent and every MCP server.

Putting a tiny gateway in front of everything

In my demo repo, the gateway is a single 90-line Express file (gateway/gateway.js). It does three things. Together, they cover 80% of the complaints above.

1. An allowlist — capability control in one `Set`

Every tool call is namespaced (github.get_readme, slack.send_message, db.log_event).

The allowlist is quite literally a JS Set:

const TOOL_ALLOWLIST = new Set([
  TOOL_NAMES.GITHUB_GET_README,
  TOOL_NAMES.SLACK_SEND_MESSAGE,
  TOOL_NAMES.DB_LOG_EVENT
]);

If an agent (or a prompt-injected-into-misbehavior agent) tries to call github.delete_repo, it never reaches the GitHub server.

The gateway refuses in three lines, logs the attempt, and sends back a polite error.

Notice what this isn't: a prompt that says "please don't call delete_repo."

Prompts are suggestions.

Allowlists are rules.

2. A guardrail — the content contract

Some tools return unbounded blobs.

READMEs in particular love to be 40KB of badges and marketing copy.

The gateway has a hardcoded cap:

if (
  tool === TOOL_NAMES.GITHUB_GET_README &&
  serverResponse.output.content.length > 5000
) {
  return { output: null, error: createError("README content exceeded 5000 characters") };
}

Here's that guardrail earning its keep on a deliberately gnarly repo:

taco@TCSIND-4shZvZXk:~/mcp$ node agent/agent.js https://github.com/juice-shop/juice-shop 
[agent] starting one-loop run
[agent] target repo: https://github.com/juice-shop/juice-shop
[agent] objective: Summarize the project highlights for a dev audience.
[agent] chosen tool: github.get_readme
[agent] first tool call failed: {
  message: 'Guardrail blocked response: README content exceeded 5000 characters',
  details: null
}

Juice Shop's README is enormous.

Without the guardrail, my agent would've burned half its context on emoji-laden marketing.

With the guardrail, the agent got a clean "nope, try something else" and my context window stayed intact.

3. Logging — audit trail for free

Every single call through the gateway i.e success, failure, allowlist rejection, guardrail block gets recorded to SQLite via the db.log_event tool.

Best-effort, fire-and-forget, one await in the middleware.

Now when someone asks "what did the agent do yesterday?" the answer is a query, not a shrug.

That's it.

That's the whole governance layer.

An allowlist, a guardrail, a log roughly 200 lines of Node, no framework, readable in a single sitting.

But a toy gateway is still a toy

Here's where I have to be honest with myself.

My gateway works for the demo.

It would not survive contact with a real organization.

The allowlist is one Set shared by everyone. No per-team, per-agent, per-use-case scoping.
Guardrails are hardcoded conditionals. Adding a new one means a code change and a redeploy.
Authentication is nonexistent. Anyone who can curl :3000/mcp is an agent now.
Routing is three localhost URLs in a map. No service discovery, no health checks, no retries.
Adding a new tool means editing three files.

Solving each of those is a weekend project.

Solving all of them, operating them, and keeping them maintained as tools come and go across teams, that's a platform team's full-time job.

The feature I wish I'd built first: the Virtual MCP Server

While researching what a grown-up version of this gateway looks like, I came across TrueFoundry's MCP Gateway and specifically their concept of a Virtual MCP Server.

It's one of those ideas that's obvious in retrospect and I'm mildly annoyed I didn't think of it first.

The idea:

You have a bunch of real MCP servers, each exposing lots of tools.

Some tools are safe.

Some are dangerous.

Some are fine for one team and a footgun for another.

Rather than giving teams access to whole servers, you compose a Virtual MCP Server, a curated, custom, named collection of tools pulled from whichever upstream servers you want.

Concretely:

Your doc-summary-bot Virtual MCP Server exposes just github.get_readme and slack.send_message. That's the full surface area.
Your release-bot Virtual MCP Server exposes github.create_release, github.tag_commit, and slack.send_message — but not github.delete_repo, even though the upstream GitHub server technically supports it.

No new deployments.

The virtual server is just configuration on the gateway, and each one gets its own allowlist, its own guardrails, its own auth scope.

This matters because of the failure mode it quietly prevents.

Here's a solid demo video explaining Virtual MCP Server.

What this looks like in a real workflow

Let me walk through the kind of agent I'd actually want to run in production, a compliance automation bot, operating entirely through a TrueFoundry MCP Gateway endpoint:

A PR merges to main. A webhook wakes the agent.
The agent calls github.get_diff via its Virtual MCP Server. Authenticated, not with a bare PAT pasted into an env var, but with a service token the gateway issued and can rotate.
The diff comes back. The gateway's guardrail notices it's 12,000 lines, well over the "unsupervised review" threshold and pauses the run, requesting human approval before continuing. (Try getting that out of a lone MCP server.)
A reviewer approves. The agent writes the diff plus metadata to MongoDB via db.store_diff.
It opens a Jira ticket via jira.create_issue, linking back to the diff.
It posts a summary to Slack via slack.send_message.

Six tool calls.

Four different upstream MCP servers.

One endpoint. Every call authenticated. Every call logged to the audit trail.

The single dangerous tool the agent isn't supposed to touch, even if a prompt injection convinces it to try it isn't prompted against.

It's simply not in the Virtual MCP Server, so calling it is a 404, not a judgment call.

That, to me, is the jump from protocol to platform.

Wrapping up

MCP gave us a clean, shared language for AI agents to talk to tools.

That's a big deal, and it's easy to underrate how much of a pain this was before MCP existed.

But a shared language isn't a shared policy.

If you're running more than one agent, or letting more than one team build agents, you will need the thing that sits between "call the tool" and "did we mean to let it call the tool."

That thing is a gateway.

You can build a toy version in an afternoon.

My demo repo is proof.

But for anything real — auth, RBAC, audit, per-scope capability boundaries, and the Virtual MCP Server trick you want a platform that treats governance as the product, not the afterthought.

Take a look at TrueFoundry's MCP Gateway and the Virtual MCP Server feature if you're at the "I'm giving real agents real tools and someone in security wants to talk to me" stage.

If you build something interesting on top of either, I'd love to see it. Happy gatewaying.

Triggers in SQLite: Automating Logic Inside Your Database

Athreya aka Maneshwar — Tue, 14 Apr 2026 18:19:58 +0000

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

So far, we’ve looked at how SQLite lets you query smarter (subqueries), structure cleaner (views), and control IDs (autoincrement).

Now we move into something more powerful—something that lets your database react automatically.

That’s where triggers come in.

A trigger is code that runs automatically when something happens in your database.

You don’t call it.
You don’t manually execute it.
It just runs behind the scenes.

The Three Parts of a Trigger

Every trigger is built on three things:

1. Event (When it runs)

This is what activates the trigger:

INSERT
UPDATE
DELETE

2. Condition (Should it run?)

An optional check using a WHEN clause.

If the condition is false → trigger does nothing.

3. Action (What it does)

The actual SQL logic:

INSERT
UPDATE
DELETE
SELECT

Real-World Use Case: Preventing Invalid Data

Imagine you're managing a banking system.

You don’t want a user’s account balance to go negative accidentally.

Instead of relying only on application code, you can enforce this rule directly in the database using a trigger.

CREATE TRIGGER prevent_negative_balance
BEFORE UPDATE OF balance ON accounts
WHEN NEW.balance < 0
BEGIN
    SELECT RAISE(ABORT, 'Balance cannot be negative');
END;

What’s happening here?

The trigger fires before the balance is updated.
It checks if the new balance is less than zero.
If true, it aborts the operation with an error message.

This ensures your data remains consistent no matter where the update originates.

Keeping Track: Audit Logs with Triggers

Another powerful use of triggers is maintaining an audit trail.

For example, tracking changes made to sensitive data like salaries or account details.

CREATE TRIGGER log_salary_update
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO salary_audit(employee_id, old_salary, new_salary, change_date)
    VALUES (OLD.id, OLD.salary, NEW.salary, datetime('now'));
END;

Now every time a salary is updated:

The old and new values are recorded
You get a timestamp of the change
No manual logging required!

When Triggers Go Wrong

While triggers are powerful, they can also introduce complexity if not used carefully.

Here are a few pitfalls to watch out for:

Hidden logic: Since triggers run automatically, it can be hard to debug unexpected behavior.
Performance impact: Multiple triggers on large datasets can slow down operations.
Recursive triggers: A trigger that modifies a table may unintentionally fire another trigger, creating loops.

Best practice: Keep triggers simple, focused, and well-documented.

Triggers are like invisible guardians of your database, they watch, react, and enforce rules without being explicitly called.

When used wisely, they reduce redundancy, improve consistency, and make your system more robust.

But like any powerful tool, they demand discipline.

Overuse or misuse can lead to confusion and performance issues.

The key is balance: use triggers where they shine, and keep your logic transparent and maintainable.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

What's an AI Gateway and do you think you need one?

Athreya aka Maneshwar — Mon, 13 Apr 2026 15:52:46 +0000

It usually starts simple.

You're building a feature in your team with an AI feature in the product.

One API call to OpenAI. Maybe wrapped in a small helper function. You ship a feature. It works. Then things start creeping in.

Another team wants to use it. Someone adds a second model. Now you’ve got API keys sitting in different repos.

Nobody’s really tracking usage. Finance asks how much you're spending on AI this month, you don’t have a clear answer which team is using how much.

Then security shows up asking where prompts and responses are going what are the guardrails list and share it with us.

And at some point, a provider slows down or goes down, and suddenly your app is stuck waiting on a single dependency you don’t control.

Now you’ve got:

Multiple teams calling different models
No centralized control
No clear cost visibility
Zero guardrails

This is usually the moment people start searching:
“Do I actually need an AI gateway?”

What an AI Gateway Actually Is (Without the Buzzwords)

If you’ve worked with backend systems, you already know how an API Gateway works.

It sits in front of your services and handles things like routing, authentication, rate limiting, and observability, so your services don’t have to deal with that themselves.

An AI Gateway works in a very similar way.

But instead of sitting in front of microservices, it sits in front of your model providers.

At its core, an AI Gateway is just a layer between your application and the LLM APIs you’re calling.

Instead of your app directly hitting providers like OpenAI or Anthropic, every request goes through this gateway first.

That one change unlocks a lot.

Now you have a single place that can:

Route requests across different models
Handle authentication centrally
Enforce rate limits
Track usage and cost at a detailed level (tokens, not just requests)
Apply guardrails on inputs and outputs
Give you visibility into what’s actually happening

Most teams don’t start here though.

They usually go through this progression:

1. Raw SDKs

You use something like the OpenAI SDK. Quick to set up, works great, as long as it’s just one team and one use case.

2. Simple proxies (like LiteLLM)

You add a thin layer to route between models. Helps a bit, but governance, security, and cost tracking are still pretty limited.

3. AI Gateway

This is where things become structured. Instead of every team doing their own thing, you now have a centralized control plane managing how AI is used across your org.

The key difference isn’t just routing, it’s understanding.

An API Gateway can tell you:
“this service got 10,000 requests.”

An AI Gateway can tell you:
“this team used 4M tokens on GPT-4, spent $X, and triggered guardrails 3 times.”

Without it, your AI usage grows organically (read: chaotically).
With it, you can actually manage it.

So… do you actually need one?

This is the part most people overcomplicate.

You don’t need a framework. You don’t need a 10-step checklist.

You just need to be honest about how your setup actually looks today, not how you think it looks.

You probably don’t need one (yet)

If your world is still pretty contained, you’re fine.

One team building one feature, calling one model, with a bill that’s small enough that nobody’s asking questions, this kind of setup doesn’t need extra infrastructure yet.

Seriously.

Adding an AI Gateway here is like adding Kubernetes to a side project.
You can do it. You probably shouldn’t.

Just ship.

You do need one (or you’re about to)

Now flip it.

multiple teams are using LLMs independently
you’re juggling OpenAI + Anthropic (or thinking about it)
someone from compliance said words like “HIPAA”, “GDPR”, “SOC 2”, blah, blah, blah...

finance asked for a breakdown and you gave them… vibes
you’ve had that one moment where you thought: “wait… did we just send something sensitive to an LLM?”

And then there’s that subtle moment where you realize something could go wrong.

Maybe you don’t know exactly what’s being sent in prompts. Maybe logs are incomplete. Maybe you’re not sure how to stop a bad request before it reaches the model.

That’s usually the signal.

You don’t feel like you’re running “complex infrastructure,” but the problems you’re dealing with are already infrastructure problems.

What a production AI setup actually looks like

This is where things stop being “a few API calls” and start looking like a system.

Recently, I came across TrueFoundry while digging into how teams handle this at scale, and it’s a pretty good example of what this setup looks like in practice.

Instead of every team managing their own keys and integrations, everything goes through one layer. That one change removes a surprising amount of chaos.

So now:

there’s a single API key internally (teams don’t touch provider credentials anymore)
you can set budgets and rate limits per team so one experiment doesn’t accidentally burn your entire budget
if OpenAI slows down, you can fallback to Anthropic automatically instead of your feature just breaking
every request is tracked prompt, response, tokens, cost — all of it
you can add guardrails PII filtering, prompt injection checks, whatever your security team keeps asking about
and the whole thing can run in your own VPC / on-prem so data isn’t flying around random third-party infra

Performance wise, this isn’t some heavy layer either.

We’re talking about handling 350+ requests per second on a single vCPU with sub-3ms latency, which means it adds control without slowing things down in any meaningful way.

Also worth noting, this space is becoming real infra, not just hacks.

Tools like this are already showing up in places like the Gartner Market Guide for AI Gateways, which is usually a signal that “okay, this is a category now”.

The boring but real conclusion

Most teams don’t wake up and say:

“today we implement an AI Gateway”

They get pushed into it by problems.

If you read the earlier section and thought:

“yeah… we’re kinda there already”

Then you probably are.

And the tradeoff is simple:
You either spend a bit of time setting up structure now, or you keep paying for it later in the form of confusion, rising costs, and occasional fire drills that nobody enjoys dealing with.

Pick your pain.

Try it (if you’re already feeling the pain)

At this point, you can keep patching things together… or just try something like TrueFoundry and see what a structured setup actually feels like.

You can get it running in your own cloud pretty quickly, without needing a long setup process or even a credit card.

Even if you decide not to stick with it, going through the process once will give you a much clearer picture of what’s missing in your current setup.

AUTOINCREMENT in SQLite

Athreya aka Maneshwar — Sun, 12 Apr 2026 17:45:35 +0000

When working with SQLite, generating unique IDs is something you deal with almost immediately.

Most developers assume that AUTOINCREMENT is required for this, but SQLite already handles auto-incrementing behavior by default in a slightly different way.

Default Auto-Increment Behavior

If you define a column like this:

CREATE TABLE t1 (
    a INTEGER PRIMARY KEY
);

SQLite will automatically assign values to a whenever you insert NULL or don’t provide a value at all.

For example:

INSERT INTO t1 VALUES (NULL);

In most cases, SQLite assigns a value that is one greater than the current maximum in that column. If the table is empty, it starts from 1.

This makes it feel like standard auto-increment behavior, and for many applications, this is more than enough.

The Subtle Catch

The important detail is that SQLite only guarantees current uniqueness, not historical uniqueness.

If you:

Insert rows → 1, 2, 3
Delete row with ID 3
Insert again

SQLite may reuse 3.

This is not a bug. It is simply how SQLite optimizes ID generation.

It avoids keeping extra state and just looks at existing values.

For internal systems, this usually doesn’t matter. But if IDs are exposed outside (like APIs or logs), reuse can create confusion.

What AUTOINCREMENT Actually Does

When you explicitly use:

CREATE TABLE t1 (
    a INTEGER PRIMARY KEY AUTOINCREMENT
);

SQLite switches to a stricter strategy.

Now, instead of checking the current max value, it remembers the largest value ever used and always generates a new value greater than that.

So even if you delete rows, old IDs are never reused.

This gives you a stronger guarantee:

IDs are unique across the entire lifetime of the table
No accidental reuse

The Role of sqlite_sequence

To make this work, SQLite maintains a special internal table called sqlite_sequence.

It stores:

Table name
Highest ID ever used

This table is created automatically when you first insert into a table that uses AUTOINCREMENT.

Each new insert updates this value so SQLite always knows what comes next.

Why Not Always Use AUTOINCREMENT?

At first glance, AUTOINCREMENT seems like the safer option. But it comes with tradeoffs.

It adds a small performance overhead
It requires maintaining extra state (sqlite_sequence)
It prevents reuse, which may not always be necessary

Also, SQLite has a limit on integer values. If the maximum value is ever reached, further inserts will fail.

SQLite already gives you auto-incrementing IDs without needing AUTOINCREMENT.

The keyword is only for stricter guarantees, not basic functionality.

Instead of blindly adding AUTOINCREMENT, you choose it only when your system actually needs that level of consistency.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

Subqueries & Views in SQLite: Writing Smarter, Cleaner Queries

Athreya aka Maneshwar — Sat, 11 Apr 2026 19:52:43 +0000

In the previous discussion, we explored how PRAGMA gives you control over SQLite’s internal behavior.

Now, let’s see how you actually structure smarter queries using subqueries and views.

These aren’t just SQL features.

They’re tools that help you write cleaner logic, reduce duplication, and make your database easier to work with.

Subqueries: Queries Inside Queries

At its core, a basic SQL query looks like this:

SELECT x FROM y WHERE z;

x → columns
y → tables
z → condition

Now here’s where things get interesting:
That z (the condition) can itself contain another query.

That’s a subquery.

A Simple Subquery Example

SELECT name 
FROM Students 
WHERE sid IN (
    SELECT sid 
    FROM Admitted_to 
    WHERE doj = 'Jan 01, 2000'
);

What’s happening here?

Inner query → finds student IDs admitted on a specific date
Outer query → fetches names of those students

The inner query runs first, then feeds results into the outer query.

Where You Can Use Subqueries

Subqueries aren’t limited to WHERE. You can use them in:

WHERE clause (most common)
FROM clause (as derived tables)
HAVING clause (with grouped data)

They act like temporary datasets inside your query.

Correlated Subqueries: When Things Get Dynamic

Now we step into more advanced territory.

A correlated subquery is one that depends on the outer query.

Example:

SELECT name 
FROM Students 
WHERE EXISTS (
    SELECT * 
    FROM Admitted_to 
    WHERE sid = Students.sid 
    AND doj = 'Jan 01, 2000'
);

What’s different here?

The inner query references Students.sid
That means it cannot run independently
It depends on each row from the outer query

How Correlated Subqueries Actually Execute

This is where many people misunderstand what’s happening.

For each row in Students:

SQLite takes that row’s sid
Substitutes it into the subquery
Executes the subquery
Decides whether to include that row

So instead of running once, the subquery runs multiple times.

This makes correlated subqueries:

Powerful
But potentially slower

When to Use Subqueries (and When Not To)

Use them when:

You want clear, readable logic
You need intermediate filtering
You’re avoiding complex joins

Avoid them when:

Performance is critical
A join can do the same job more efficiently

Views: Virtual Tables That Simplify Everything

If subqueries are about embedding logic, views are about reusing it.

A view is basically a saved query that behaves like a table.

But here’s the key detail:

A view does not store data
It stores only the query definition

Creating a View

CREATE VIEW view1 AS 
SELECT name, sid 
FROM Students;

This doesn’t create a new table.

Instead, SQLite stores the query definition internally.

Using a View

Once created, you can query it like a normal table:

SELECT name 
FROM view1 
WHERE sid = 1001;

Behind the scenes:

SQLite runs the original query
Then applies your new query on top

Why Views Exist (And Why You Should Care)

Views solve real problems:

1. Simplify Complex Queries

Instead of repeating a long query everywhere:

Write it once as a view
Reuse it everywhere

2. Hide Complexity

You can expose only what users need:

Hide joins
Hide sensitive columns
Present clean datasets

3. Provide Schema Independence

If your base table changes:

Your view may still work unchanged
Applications using the view stay stable

Temporary Views

SQLite also allows temporary views:

CREATE TEMP VIEW temp_view AS 
SELECT name FROM Students;

Exists only during the session
Automatically deleted when connection closes

Useful for:

Testing
Intermediate processing
Session-based logic

The Catch: Views in SQLite Are Read-Only

Unlike some databases, SQLite does not allow direct updates on views.

So these won’t work:

INSERT INTO view1 ...
UPDATE view1 ...
DELETE FROM view1 ...

If you need updates:

You must use triggers on the view
Those triggers then modify base tables

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

SQLite PRAGMA: The Underrated Lever That Controls Your DB

Athreya aka Maneshwar — Fri, 10 Apr 2026 20:15:35 +0000

Most developers treat SQLite like a simple file-based database—create tables, run queries, move on.

But if you stop there, you’re missing one of its most powerful features: PRAGMA statements.

PRAGMA is where SQLite stops being “just a database” and starts becoming something you can actually tune, inspect, and control.

This isn’t just syntax—it’s control over how your database behaves under the hood.

What PRAGMA Really Is

A PRAGMA is a special SQLite command that lets you:

Peek into internal database metadata (stuff not stored in tables)
Change how SQLite behaves internally

It looks like SQL, but it doesn’t behave like typical SQL.

Example:

PRAGMA synchronous = OFF;

That single line changes how SQLite writes data to disk—trading safety for speed.

PRAGMA Doesn’t Always Run When You Think It Does

Unlike normal SQL, some PRAGMAs execute during the query preparation phase, not execution.

If you're using SQLite through APIs like:

sqlite3_prepare
sqlite3_step

Some PRAGMAs take effect before execution even begins.

So if something feels inconsistent, this might be why.

This Is SQLite-Only Territory

PRAGMAs are not portable.

If you:

Switch to PostgreSQL
Move to MySQL

Your PRAGMA-based logic? Gone.

So if portability matters, use them carefully and isolate them.

How PRAGMA Syntax Works

You’ve got two ways to use it:

PRAGMA name = value;

PRAGMA name(value);

To read a value:

PRAGMA name;

Boolean shortcuts:

ON, TRUE, YES → 1
OFF, FALSE, NO → 0

Case doesn’t matter.

Even worse:

Invalid values are silently treated as 1

The 4 Types of PRAGMA You Should Actually Care About

Instead of memorizing everything, think in categories.

1. Schema Inspection (Understanding Your Database)

These help you explore structure without guessing.

PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA foreign_key_list(users);

Use this when:

Debugging relationships
Reverse engineering a database
Building migration tools

2. Behavior Control (Where the Real Power Is)

This is where performance and behavior tuning happens.

Auto Vacuum

PRAGMA auto_vacuum = FULL;

Controls how SQLite reclaims unused space.

Options:

NONE → default, no cleanup
FULL → automatic cleanup
INCREMENTAL → manual cleanup

⚠️ You must set this before creating tables.
After that, it won’t change and SQLite won’t complain.

Cache Size

PRAGMA cache_size = 2000;

Controls how much data SQLite keeps in memory
Faster reads if increased

But:

It resets when the connection closes

To make it stick:

PRAGMA default_cache_size = 2000;

Synchronous Mode (Speed vs Safety)

PRAGMA synchronous = OFF;

FULL → safest, slowest
OFF → fastest, risky

Turning it off means:

Faster writes
Higher chance of corruption during crashes

This is a deliberate tradeoff, not a free optimization.

3. Versioning (For Migrations and Tracking)

User Version (Safe to Use)

PRAGMA user_version = 3;

This is for you, not SQLite.

Use it to:

Track schema versions
Manage migrations

4. Debugging and Integrity (Your Safety Net)

Integrity Check

PRAGMA integrity_check;

This scans the entire database and tells you if something is wrong.

Output:

"ok" → everything is fine
Error string → something is broken

Use this when:

You suspect corruption
After crashes
Before backups

Where People Usually Screw This Up

Misspelling PRAGMAs and assuming they worked
Using synchronous = OFF in production without understanding risk
Trying to change auto_vacuum after tables exist
Messing with schema_version
Relying on PRAGMAs in apps that need to support multiple databases

Why PRAGMA Actually Matters

If you ignore PRAGMA, you’re basically using SQLite in “default mode” forever.

That means:

Default performance
Default safety
Default behavior

But SQLite isn’t just a database—it’s configurable at a very low level.

PRAGMA is how you access that layer.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

How SQLite Executes Queries Through Its API Lifecycle

Athreya aka Maneshwar — Fri, 03 Apr 2026 19:50:31 +0000

Now that the internal structures and their interconnections are clear, the final piece is understanding how an application actually drives all of this through the SQLite API.

It starts with a simple call: sqlite3_open.

When this function is invoked successfully, SQLite allocates a sqlite3 object in the application’s memory space.

At this point, the connection is initialized, but it is still idle, no SQL has been compiled yet, and the pVdbe list (which holds compiled statements) is empty.

From SQL Text to Bytecode

The moment an application prepares a query using sqlite3_prepare, SQLite translates the SQL text into a compiled representation.

Internally, this creates a Vdbe object, which is essentially a bytecode program ready to be executed.

This object is then added to the pVdbe list inside the sqlite3 connection.

At this stage:

The query is compiled
No execution has happened yet
No cursors are open

It’s like having a program loaded but not yet run.

Binding and Execution

Before execution, the application can inject values into the query using sqlite3_bind_* functions.

These bindings replace placeholders in the prepared statement.

Execution begins when sqlite3_step is called.

This is where everything comes alive:

The VDBE starts interpreting bytecode
Cursors are created dynamically
Each cursor connects to a B-tree (table or index)
Data is read, filtered, or modified

These cursors exist only for the duration of execution—they are not permanent structures.

Cursor Lifecycle

Cursors are tightly scoped to execution.

Created when the VM starts executing
Used to traverse B/B+ trees
Destroyed when execution finishes

If the application calls sqlite3_reset, the statement is reset to its initial state, and any active cursors are closed and cleaned up.

This ensures that each execution starts fresh, without leftover state.

Cleaning Up Resources

Once a prepared statement is no longer needed, the application calls sqlite3_finalize.

This step is crucial—it:

Destroys the Vdbe object
Frees all associated resources
Removes it from the connection

Only after all prepared statements are finalized should the application close the connection using sqlite3_close.

When the connection is closed:

The sqlite3 object is destroyed
All associated handles become invalid
The session is fully terminated

The Complete Flow in One View

From start to finish, the lifecycle looks like this:

Open connection → sqlite3_open
Prepare query → sqlite3_prepare
Bind parameters → sqlite3_bind_*
Execute → sqlite3_step
Reset (optional) → sqlite3_reset
Finalize → sqlite3_finalize
Close connection → sqlite3_close

Closing Thoughts

What looks like a handful of API calls from the outside is actually orchestrating a deep chain of internal operations—compilation, cursor management, B-tree traversal, and disk interaction.

The sqlite3 object sits at the center of it all, acting as the control hub that binds together:

Prepared statements (Vdbe)
Execution flow (VM)
Storage (B-tree + Pager)
And application interaction

Once you see this lifecycle clearly, SQLite stops feeling magical—it becomes predictable, structured, and surprisingly elegant.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

How SQLite Internals Connect Into One Unified System

Athreya aka Maneshwar — Wed, 01 Apr 2026 17:32:36 +0000

In the previous section, we explored individual pieces of SQLite’s internal architecture—the sqlite3 structure, schema objects, tables, indexes, and execution engine.

Now, it’s time to zoom out and see how all of these components actually interact in a real system.

This is where things start to feel less like isolated structures and more like a living system.

The Big Picture: Application ↔ SQLite

At the highest level, an application interacts with SQLite through two primary handles:

sqlite3* → Represents a database connection
sqlite3_stmt* → Represents a compiled SQL statement

Every API call, whether it’s preparing a query, executing it, or fetching results—flows through these two interfaces.

SQLite doesn’t just passively execute queries either.

It allows applications to register custom callback functions.

When needed, SQLite invokes these callbacks, enabling logic to run inside the application’s space.

This is how features like custom functions, collations, or hooks are implemented.

Multiple Connections, Multiple Databases

One subtle but powerful feature of SQLite is that an application is not limited to a single connection.

An application can:

Open multiple sqlite3* connections
Connect each one to different databases—or even the same database

Within a single connection, multiple databases can coexist:

Main database
Temporary database
Attached databases

Each of these is represented internally by an entry in the Db array.

What’s important here is that each database is not just a logical entity—it has its own underlying storage machinery.

B-Tree and Pager: The Storage Backbone

Every database is accessed through a B-tree (or B+ tree) structure.

This is the core storage engine of SQLite.

Each database connection uses:

A dedicated Btree object → Handles structural access to data
A Pager object → Manages disk-level concerns

The Pager is where things get very real. It is responsible for:

Tracking database file state
Managing the journal (for transactions and rollback)
Handling locks
Maintaining the page cache

In shared cache mode, multiple B-tree instances can reuse the same Pager through a shared structure (BtShared).

This allows efficient memory usage when multiple connections access the same database file.

Transactions: Controlled and Isolated

Even though a connection may have multiple databases attached, SQLite enforces an important rule:

However, that transaction can span multiple databases within the same connection.

SQLite dynamically starts transactions on individual databases depending on what the SQL statement requires.

This keeps operations efficient while still ensuring consistency.

From SQL to Execution: The Role of `sqlite3_stmt`

Every SQL statement you write is compiled into a bytecode program.

That compiled form is represented by a sqlite3_stmt*, backed internally by a VDBE (Virtual Database Engine) program.

When executed:

The VDBE interprets the bytecode
It opens cursors to access data
It performs operations like scan, insert, update, or delete

Cursors: Navigating the Data

Cursors are the bridge between execution and storage.

Each SQL statement maintains a set of VdbeCursor objects.

These are runtime constructs used by the VM to interact with data.

Here’s how they work:

A VdbeCursor maps to a BtCursor
A BtCursor operates on a single B-tree (table or index)
It allows iteration over records

This design enables:

Multiple cursors on the same table or index
Independent traversal without interference

So even within a single query, SQLite can efficiently scan, filter, and join data using multiple cursors simultaneously.

Putting the Flow Together

Let’s trace a typical execution flow:

The application prepares a query → gets a sqlite3_stmt*
SQLite compiles it into VDBE bytecode
On execution:
- VDBE opens cursors
- Cursors access B-tree structures
- B-tree interacts with Pager
- Pager reads/writes pages from disk
Results flow back up to the application

All of this is coordinated through the sqlite3 connection object.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

SQLite Interface Handler: Understanding the sqlite3 Core Structure

Athreya aka Maneshwar — Tue, 31 Mar 2026 18:39:45 +0000

In earlier discussions, we explored several internal control data structures in isolation, each serving a specific purpose inside SQLite.

However, understanding SQLite in fragments can feel like looking at puzzle pieces without seeing the full picture.

This chapter ties everything together.

At the center of SQLite’s architecture lies the sqlite3 structure, which acts as the main interface between the application and the database engine.

By studying this structure and its relationships with other components, you get a complete, end-to-end understanding of how SQLite organizes and manages its internal state.

The `sqlite3` Structure

Whenever an application calls the sqlite3_open API, something important happens behind the scenes.

SQLite initializes a new database connection (also called a session) and prepares access to the database file.

This process results in the creation of a sqlite3 object, and the application receives a pointer to it.

That pointer is not just a reference—it represents the entire connection.

Every subsequent database operation performed by the application goes through this handle, until the connection is closed using sqlite3_close.

It’s important to note that the application should never directly modify the internal fields of this object.

It is strictly managed by SQLite itself.

Internal Composition of `sqlite3`

One of the most important members of the sqlite3 structure is aDb, which is an array of Db objects.

Each Db object represents a database associated with the connection.

Typically:

aDb[0] → Main database
aDb[1] → Temporary database

Additional entries appear when databases are attached dynamically during runtime.

Internally, SQLite does not refer to databases by name. Instead, it uses their index within the aDb array. This mapping from name to index is resolved during query compilation.

The nDb variable keeps track of how many databases are currently active in the connection.

The `Db` Structure

Each entry in the aDb array is a Db object, and it contains essential metadata about a database.

Key components include:

zName → Name of the database
pBt → Pointer to the B-tree structure (core storage engine)
inTrans → Current transaction state
pSchema → Pointer to schema information

The schema itself is a rich structure containing multiple hash tables and metadata used for query processing and validation.

Schema Organization

The schema is where SQLite keeps track of all structural elements of a database.

Important members include:

schema_cookie → Version of the schema
cache_size → Page cache configuration
tblHash → All tables and views
idxHash → All indexes
trigHash → All triggers
fkeyHash → All foreign keys
pSeqTab → Pointer to the sqlite_sequence table

When a database is opened, SQLite parses its schema and populates these hash tables.

This allows fast lookup and efficient query planning.

Table Representation in Memory

Each SQL table is represented internally as a Table object.

This object contains an array called aCol, which holds all column definitions. Each column is described by a Column object.

Index Representation

Indexes are also represented as structured objects in memory.

An Index object contains:

aiColumn → Array mapping index columns to table columns
tnum → Page number where the index root resides

This allows SQLite to efficiently locate and traverse indexed data during query execution.

Runtime State and Execution

The sqlite3 structure also keeps track of runtime execution details:

lastRowid → The most recent row ID generated by an INSERT
errCode / pErr → Error tracking
flags → Current runtime state
pVdbe → Collection of compiled SQL statements

Each compiled SQL statement is represented as a VDBE (Virtual Database Engine) program.

These are essentially bytecode instructions executed by SQLite’s internal engine.

Applications interact with these compiled statements using sqlite3_stmt pointers.

Putting It All Together

The sqlite3 structure is not just a container—it is the central hub that connects:

Database files (Db)
Storage engine (B-tree)
Schema metadata
Tables and indexes
Query execution engine (VDBE)

Every SQL query flows through this structure.

From parsing and compilation to execution and result retrieval, everything is orchestrated through sqlite3.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

From Queries to Bytecode: The Final Pieces of SQLite’s Frontend

Athreya aka Maneshwar — Sat, 28 Mar 2026 17:50:23 +0000

In the previous part, you saw how SQLite selects indexes and balances filtering with sorting.

Now we move into another important category of queries.

Aggregation and subqueries.

These introduce new challenges because SQLite is no longer just filtering rows.

It is grouping, transforming, and sometimes restructuring queries entirely.

How SQLite Executes GROUP BY

When you use a GROUP BY clause, SQLite introduces a special internal structure called an aggregator.

This is essentially a temporary table that stores:

A key → formed by GROUP BY columns
A value → aggregate data like COUNT, SUM, etc.

For example:

SELECT department, COUNT(*) FROM employees GROUP BY department;

SQLite processes this in two phases.

First, it scans rows and builds groups.

Then, it produces the final results.

The execution pattern looks like this:

where-begin  
    compute group-by key  
    focus on the group-by key  
    update aggregate terms  
where-end  
foreach group-by  
    compute result-set  
    send result to caller  
end-foreach

During the first phase, SQLite keeps updating aggregate values for each group.

During the second phase, it outputs the final computed results for each group

This approach ensures that rows are grouped efficiently without repeatedly scanning the same data.

The Problem with Subqueries in FROM

Now consider queries that use subqueries inside the FROM clause.

Example:

SELECT a FROM (
    SELECT x + y AS a FROM t1 WHERE z < 100
) WHERE a > 5;

The default way to execute this is:

Run the inner query
Store results in a temporary table
Run the outer query on that table
Delete the temporary table

This approach has a major drawback.

The temporary table has no indexes, so any filtering or joining done by the outer query becomes inefficient.

It also requires scanning data multiple times.

Subquery Flattening: A Smarter Approach

To avoid this overhead, SQLite uses an optimization called subquery flattening.

Instead of executing the subquery separately, SQLite merges it into the outer query.

The previous example becomes:

SELECT x + y AS a FROM t1 WHERE z < 100 AND a > 5;

Now the query can be executed in a single pass over the table.

This has two major benefits:

Eliminates temporary tables
Allows indexes on the base table to be used

This significantly improves performance

When Flattening Is Allowed

Flattening is not always possible.

SQLite applies this optimization only when a strict set of conditions is satisfied.

The subquery and the outer query do not both use aggregates.
The subquery is not an aggregate or the outer query is not a join.
The subquery is not the right operand of a left outer join.
The subquery is not DISTINCT or the outer query is not a join.
The subquery is not DISTINCT or the outer query does not use aggregates.
The subquery does not use aggregates or the outer query is not DISTINCT.
The subquery has a FROM clause.
The subquery does not use LIMIT or the outer query is not a join.
The subquery does not use LIMIT or the outer query does not use aggregates.
The subquery does not use aggregates or the outer query does not use LIMIT.
The subquery and the outer query do not both have ORDER BY clauses.
The subquery and outer query do not both use LIMIT.
The subquery does not use OFFSET.
The outer query is not part of a compound select or the subquery does not have both an ORDER BY and a LIMIT clause
The outer query is not an aggregate or the subquery does not contain ORDER BY
The sub-query is not a compound select, or it is a UNION ALL compound clause made up entirely of non-aggregate queries, and the parent query: • is not itself part of a compound select, • is not an aggregate or DISTINCT query, and • has no other tables or sub-selects in the FROM clause. The parent and sub-query may contain WHERE clauses. Subject to rules (11), (12) and (13), they may also contain ORDER BY, LIMIT and OFFSET clauses.
If the sub-query is a compound select, then all terms of the ORDER by clause of the parent must be simple references to columns of the sub-query.
The subquery does not use LIMIT or the outer query does not have a WHERE clause
If the sub-query is a compound select, then it must not use an ORDER BY clause.

Query flattening is an important optimization when views are used because each use of a view is translated into a su bquery.

Fast MIN and MAX Queries

Aggregation is not always expensive.

SQLite has a very efficient optimization for queries like:

SELECT MIN(age) FROM users;

SELECT MAX(age) FROM users;

If there is no index on the column, SQLite must scan the entire table.

But if an index exists, SQLite can do something much faster.

It directly navigates to:

The first entry in the index for MIN
The last entry in the index for MAX

Since indexes are stored as B-trees, this operation takes logarithmic time, not linear time.

If the column is an INTEGER PRIMARY KEY, SQLite can even use the table’s primary B+ tree directly.

This makes MIN and MAX queries extremely efficient when proper indexing is in place

Bringing It All Together

At this point, you have seen how SQLite handles:

Filtering using WHERE
Choosing indexes
Ordering joins
Grouping results
Flattening subqueries
Optimizing aggregations

All of this happens before execution, inside the frontend.

By the time the Virtual Machine runs, everything has already been carefully planned and optimized.

Final Thoughts on the Frontend

The SQLite frontend is a complete pipeline:

The tokenizer breaks SQL into tokens
The parser builds structured representations
The optimizer reshapes queries for efficiency
The code generator produces executable bytecode

All of this work is triggered by a single function call:

sqlite3_prepare()

Query optimization remains one of the most complex and delicate parts of any database system.

SQLite keeps things relatively simple by using heuristics instead of heavy statistical models, but still manages to achieve strong performance in most real-world scenarios

What’s Next

We have now covered the entire frontend pipeline of SQLite, from raw SQL to optimized bytecode.

In the next series, we will move beyond compilation and explore how SQLite interacts with the outside world.

We will start with the SQLite Interface Handler, where queries enter the system and results are returned.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

Which index should SQLite use?

Athreya aka Maneshwar — Fri, 27 Mar 2026 19:12:01 +0000

Even when indexes exist, choosing the wrong one can slow down a query significantly.

The optimizer’s job here is not just to use an index, but to use the right index.

One Table, One Index (Mostly)

For each table in a query, SQLite can typically use only one index.

There is one exception.

In OR-based queries, SQLite may use multiple indexes, but in most cases, it selects a single index per table.

Because of this limitation, index selection becomes a critical decision.

SQLite tries to ensure that at least one useful index is applied to each table whenever possible.

When Multiple Indexes Exist

Consider a table with multiple indexes:

CREATE TABLE table1(x, y, z);
CREATE INDEX i1 ON table1(x);
CREATE INDEX i2 ON table1(y);

Now look at this query:

SELECT z FROM table1 WHERE x = 5 AND y = 6;

SQLite has two choices:

Use index i1 to find rows where x = 5, then filter y = 6
Use index i2 to find rows where y = 6, then filter x = 5

Both approaches are valid, but they may have very different costs.

How SQLite Chooses the Best Index

SQLite estimates how much work each option will require and chooses the one with the lowest cost.

The decision is based on heuristics such as:

How many rows are expected to match
How selective the index is
How much filtering is needed after lookup

If statistical data is available, SQLite makes better decisions.

This is where the sqlite_stat1 table comes into play.

It stores information about how many rows are typically associated with a given column value.

Using this data, SQLite can estimate which index will reduce the result set the most.

The index that is expected to return fewer rows is usually preferred.

Forcing SQLite to Ignore an Index

Sometimes, you may want to override SQLite’s choice.

SQLite provides a subtle mechanism for this using the unary + operator.

Example:

SELECT z FROM table1 WHERE +x = 5 AND y = 6;

The + operator does nothing functionally, but it prevents SQLite from using the index on column x.

This forces the optimizer to consider other indexes, such as i2 on column y.

This is a lightweight way to guide the optimizer without changing query semantics.

Balancing WHERE and ORDER BY

Index selection is not only about filtering rows.

SQLite also considers sorting requirements.

For example:

SELECT * FROM table1 WHERE x = 5 ORDER BY y;

Now SQLite has a trade-off:

Use an index on x to filter efficiently
Use an index on y to avoid sorting

SQLite evaluates both options and chooses the one that results in the fastest overall execution.

This means sometimes SQLite may sacrifice a slightly less efficient filter to avoid an expensive sort operation.

When Sorting Cannot Use an Index

If no suitable index can satisfy the ORDER BY clause, SQLite must sort the results manually.

This is done using a temporary sorter, which behaves like a transient index.

The process looks like this:

open sorter
where-begin
    extract required columns
    build a record
    generate sort key
    insert into sorter
where-end
sort
for each sorted entry
    extract data
    return result
close sorter

This approach works, but it comes with a cost.

Additional memory usage
Extra processing for sorting
Potential performance overhead for large datasets

That is why SQLite always tries to use an index for ORDER BY whenever possible.

Why Index Selection Is So Important

At this stage, multiple layers of optimization come together:

WHERE clause determines candidate indexes
Join ordering decides when a table is accessed
Index selection decides how it is accessed
ORDER BY may influence which index is preferred

A single decision here can affect:

Number of rows scanned
Need for sorting
Overall execution time

Wrapping Up

Index selection is one of the most critical decisions in SQLite’s optimizer.

Even when multiple indexes are available, SQLite carefully evaluates which one minimizes total work.

It also balances filtering efficiency with sorting requirements to produce the fastest possible execution plan.

Understanding how SQLite makes this decision helps you design better indexes and write queries that align with the optimizer.

In the next part, we will explore GROUP BY and MIN/MAX optimizations, where SQLite applies additional strategies to aggregate and summarize data efficiently.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

Inside SQLite’s Frontend: Join Table Ordering

Athreya aka Maneshwar — Thu, 26 Mar 2026 20:45:48 +0000

Even if your WHERE clause is perfectly optimized, a bad join order can still make your query slow.

How SQLite Executes Joins

SQLite uses a very simple but effective strategy for joins.

It always executes joins as nested loops.

That means for a query like:

SELECT * FROM A JOIN B;

SQLite will do something like:

for each row in A:
    for each row in B:
        process the combination

The important detail here is that the order of tables determines how these loops are nested.

The first table becomes the outer loop
The last table becomes the inner loop

So the order in your FROM clause directly affects execution.

Why Order Matters

Nested loops can be very expensive if the outer loop has too many rows.

For example, if table A has 1 million rows and table B has 1 million rows:

A outer → B inner = 1M × 1M operations
B outer → A inner = also large, but may differ depending on indexes

The goal is to reduce the number of iterations as early as possible.

SQLite Doesn’t Always Follow Your Order

Although the default behavior follows the FROM clause order, SQLite can reorder tables if it finds a better execution plan.

It uses a greedy algorithm to decide the order.

Instead of trying all possible combinations, it builds the order step by step:

First, pick the table that is cheapest to process
Then pick the next cheapest
Continue until all tables are placed

If two options are equally good, SQLite falls back to the original order in the query.

What “Cheapest” Means

SQLite estimates cost using several factors:

Availability of indexes
How selective those indexes are
Whether sorting can be avoided
Estimated number of rows to scan

For example:

An index that reduces 1 million rows to 1 row is extremely valuable
An index that reduces 1 million rows to 900,000 rows is not very useful

This is where the ANALYZE command becomes important. It collects statistics so SQLite can better estimate how effective an index is.

Index Nested Loop Joins

If a table has an index on the join column, SQLite often places it as the inner loop.

This allows efficient lookups for each row from the outer loop.

This strategy is called an index nested loop join.

Instead of scanning the entire inner table, SQLite uses the index to quickly find matching rows.

Special Cases: INNER vs OUTER Joins

Inner joins are flexible.

SQLite can freely reorder tables in inner joins because the result does not depend on order.

Outer joins are different.

A LEFT OUTER JOIN is not commutative, meaning order matters for correctness. Because of this, SQLite does not reorder outer joins.

However, tables involved in inner joins around an outer join may still be reordered if it improves performance.

WHERE Clause Integration

If you use ON or USING clauses in joins, SQLite internally converts them into additional WHERE clause conditions before optimization.

This means all the WHERE clause optimizations you saw earlier still apply here.

Forcing Join Order

In most cases, SQLite’s automatic reordering works well and you do not need to worry about it.

But if you want to force a specific order, you can use a CROSS JOIN.

Example:

SELECT * FROM table1 CROSS JOIN table2;

Here:

table1 will always be the outer loop
table2 will always be the inner loop

This gives you manual control when needed.

In the next part, we will look at index selection, where SQLite decides exactly which index to use when multiple options are available.

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

git-lrc-intro-60s.mp4

Why

🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
🔁 Build a…

View on GitHub

Forem: Athreya aka Maneshwar

What Building with MCP Taught Me About Its Biggest Gap

So, MCP. What is it, again?

The demo: one agent, three MCP servers, a Gemini brain

What MCP quietly does not give you

Putting a tiny gateway in front of everything

1. An allowlist — capability control in one Set

2. A guardrail — the content contract

3. Logging — audit trail for free

But a toy gateway is still a toy

The feature I wish I'd built first: the Virtual MCP Server

What this looks like in a real workflow

Wrapping up

Triggers in SQLite: Automating Logic Inside Your Database

The Three Parts of a Trigger

1. Event (When it runs)

2. Condition (Should it run?)

3. Action (What it does)

Real-World Use Case: Preventing Invalid Data

Keeping Track: Audit Logs with Triggers

When Triggers Go Wrong

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

See It In Action

Why

What's an AI Gateway and do you think you need one?

What an AI Gateway Actually Is (Without the Buzzwords)

1. Raw SDKs

2. Simple proxies (like LiteLLM)

3. AI Gateway

So… do you actually need one?

You probably don’t need one (yet)

You do need one (or you’re about to)

What a production AI setup actually looks like

The boring but real conclusion

Try it (if you’re already feeling the pain)

AUTOINCREMENT in SQLite

Default Auto-Increment Behavior

The Subtle Catch

What AUTOINCREMENT Actually Does

The Role of sqlite_sequence

Why Not Always Use AUTOINCREMENT?

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

See It In Action

Why

Subqueries & Views in SQLite: Writing Smarter, Cleaner Queries

Subqueries: Queries Inside Queries

A Simple Subquery Example

Where You Can Use Subqueries

Correlated Subqueries: When Things Get Dynamic

Example:

What’s different here?

How Correlated Subqueries Actually Execute

When to Use Subqueries (and When Not To)

Views: Virtual Tables That Simplify Everything

Creating a View

Using a View

Why Views Exist (And Why You Should Care)

1. Simplify Complex Queries

2. Hide Complexity

3. Provide Schema Independence

Temporary Views

The Catch: Views in SQLite Are Read-Only

HexmosTech / git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

git-lrc

Free, Unlimited AI Code Reviews That Run on Commit

See It In Action

Why

SQLite PRAGMA: The Underrated Lever That Controls Your DB

What PRAGMA Really Is

PRAGMA Doesn’t Always Run When You Think It Does

This Is SQLite-Only Territory

How PRAGMA Syntax Works

Boolean shortcuts:

1. An allowlist — capability control in one `Set`

From SQL to Execution: The Role of `sqlite3_stmt`

The `sqlite3` Structure

Internal Composition of `sqlite3`

The `Db` Structure