SQLite: The Underappreciated Database

Every time I mention that Black SEO Analyzer stores crawl data in SQLite, I get the same face. The one where someone is trying to be polite about your life choices. “Oh. Interesting. Not Postgres?”

No. SQLite. A single file on disk, and it works great.

Why SQLite actually works here

A crawl is a write-once, read-many workload. You fire off the crawler, it collects URLs, status codes, response headers, links, schema, Core Web Vitals signals, and a bunch of other stuff. Then you, the human, poke at that data for the next week trying to figure out why the site tanked.

SQLite is built for exactly this. It’s an embedded database that reads and writes from a file, with no server process, no port conflicts, no pg_hba.conf to fight with. A crawl lands as a single file you can copy to a coworker’s machine. Try doing that with a Postgres cluster.

And it’s fast. Not “fast enough.” Actually fast. SQLite can read millions of rows on a laptop and barely notices. The query planner is genuinely good, and most dismissals of SQLite skip that part entirely. Add indexes on the columns you actually query and you’ll outrun a lot of “real” databases on the same hardware.

What ends up in the crawl file

Every crawl writes a SQLite file with everything BSA collected:

• Every URL, status code, request and response headers, and the page HTML
• Every page’s outgoing links
• Redirect targets
• The full analysis for every page as JSON: canonicals, hreflang, robots directives, structured data, Web Vitals checks, and every warning

You don’t have to run one report, then another, then VLOOKUP them into something coherent. It’s all in one file: a pages table, plus an analyzer_outputs table joined to it by foreign key.

Querying it yourself

Here’s the part that makes programmers happy. You don’t need BSA to read BSA’s data. Open the file with anything:

sqlite3 crawl-2024-11-14.db

Want every 404 that’s linked from more than one page?

SELECT l.value AS destination_url, COUNT(DISTINCT p.url) AS inbound_count
FROM pages p, json_each(p.outgoing_links) l
JOIN pages d ON d.url = l.value
WHERE d.status_code = 404
GROUP BY l.value
HAVING inbound_count > 1
ORDER BY inbound_count DESC;

That’s it. No API key. No rate limit. No “upgrade to the Agency plan to export more than 500 rows.” If you want a purpose-built view of that same data, the broken links report that shows the exact source page is worth a look alongside raw SQL queries.

Comparing crawls over time

Since each crawl is its own file, diffing them is straightforward. ATTACH DATABASE two crawls in the same SQLite session and you can run joins across them. Find URLs that were 200 last week and are 500 today. Find pages where the canonical changed. Find links that disappeared after a CMS update.

ATTACH DATABASE 'crawl-last-week.db' AS prev;
SELECT u.url, u.status_code AS now, p.status_code AS before
FROM pages u
JOIN prev.pages p ON p.url = u.url
WHERE u.status_code != p.status_code;

Three lines of SQL and you have a regression report. Try getting that out of most SaaS crawlers without clicking through six screens. Tools like OnCrawl struggle with timed-out exports and poor scripting options at this kind of scale, which is exactly where a local SQLite file earns its keep.

The honest reason people reach for bigger databases

A lot of “real database” choices are about looking serious, not being serious. SQLite handles gigabytes of crawl data on a laptop, gives you full SQL, and keeps everything in a file you can back up with cp. For a write-once, read-many crawl workload, SQLite is not a compromise. It’s just the right call.

Want to run this audit on your own site? Free 14-day BSA trial — no credit card.

-Sethers