SQLite Secrets I Learned the Hard Way: ATTACH, CTEs, and Transactional Tricks

Introduction

When I built an SMS system that required balance checks, conditional updates, and multi-database operations, I thought I knew SQLite, but reality proved me wrong.

I wrestled with merging two databases (ATTACH DATABASE), avoided redundant subqueries with CTEs (WITH clauses), and learned the hard way why BEGIN IMMEDIATE it matters for transactions. Along the way, I fixed missteps (like misusing changes()) and discovered that SQLite’s "hidden" features aren’t really advanced, just underused.

If you’ve ever copy-pasted a SQL query thinking "This works… but why?", this post is for you. Let’s break down the lessons that transformed my struggles with SQLite into confidence.

This blog isn’t a tutorial. It’s a breakdown of the things I learned the hard way and why they matter.

---

Attaching a Database with ATTACH DATABASE

What it is: A way to query across multiple SQLite database files in a single SQL script.

How it works: You attach another SQLite file and give it an alias. Then you can query it like a schema:

ATTACH DATABASE 'path/to/user.sqlite' AS user_db;
SELECT * FROM user_db.sms_logs;

When to use it: Anytime you’re working with related data split across multiple SQLite files (like system and user records).

What I didn’t know: I didn’t even realize you could work across DBs this way until I needed to keep system-level SMS logs separate from user-specific ones. This simple clause unlocked so much power.

---

Writing Cleaner Logic with CTEs (Common Table Expressions)

What they are: Temporary result sets defined with a WITH clause that can be reused in the rest of your SQL query.

Why they're powerful:

• Makes SQL more readable

• Helps eliminate repeated subqueries

• Can improve performance

Example:

WITH balance_cte AS (
  SELECT balance FROM sms_accounts WHERE name = 'MainSystemAccount'
)
SELECT * FROM balance_cte;

When to use them:

• When a value needs to be referenced in multiple places

• When trying to simplify deeply nested queries

My takeaway: CTEs are the best-kept secret in SQL for maintainable code. I use them now by default anytime I find myself repeating logic.

---

Transactions: Using BEGIN IMMEDIATE

Why transactions matter: When you’re updating multiple tables and records, you don’t want your database ending up in a half-finished state if something goes wrong. Transactions ensure it’s all or nothing.

What I learned: BEGIN IMMEDIATE TRANSACTION in SQLite grabs a write-lock immediately, helping prevent race conditions in high-traffic environments.

Where I used it: My SMS logic involved deducting and adding balances across user and system accounts. Without a transaction, one insert or update could fail halfway, and the data would go out of sync.

---

Updating Conditionally with CASE

What it is: SQL’s version of an if-else block inside a query.

Example:

UPDATE sms_logs
SET status = CASE
  WHEN balance > previous_balance THEN 'SENDING'
  ELSE 'PAUSED'
END;

Best practice: Always include an ELSE to avoid unexpected NULLs.

Use case: I used this to change SMS status depending on whether the system account was successfully topped up.

---

WHERE EXISTS vs WHERE IN

What I learned: EXISTS is better for checking if a row exists without caring about the actual data. It stops scanning as soon as it finds one match.

INSERT INTO ...
SELECT ...
WHERE EXISTS (
  SELECT 1 FROM sms_logs WHERE status = 'ACCEPTED'
);

When to use it: When your condition is based on presence, not values.

---

What I Got Wrong

1. Misused changes(): I thought I could use SELECT changes() inside a subquery to see if a row had changed. Nope. changes() only returns the count of affected rows from the last statement and can’t be used like that.

2. Repeated subqueries: I found myself repeating the same SELECT balance ... multiple times. I later replaced these with CTEs for readability and performance.

3. String interpolation in raw SQL: Using ${} directly in query strings is risky. I’ve since moved to parameterised queries to avoid SQL injection.

---

Final Thoughts

When I started this project, I thought I knew SQLite until it showed me how much I didn’t. But that’s the beauty of real-world coding: the messy, "Why isn’t this working?!" moments are where the best learning happens.

Now, ATTACH DATABASE Feels like second nature. CTEs save me from query spaghetti. And BEGIN IMMEDIATEA lifesaver for data integrity. But more than any syntax trick, this experience taught me that SQL’s real power isn’t in memorising commands, it’s in understanding how they solve problems.

So if you’re staring at a SQL query thinking "There’s gotta be a better way", there probably is. Dig deeper. Test weird ideas. Break things (safely, in a transaction!). Because the features you “discover” today will be the tools you can’t live without tomorrow.

SQLite kicked my butt but now we’re cool. What SQL lesson hit you hardest? Drop it below!

Stay curious,
Muna