I wrote my first SQLite article in 2011 while debugging an Adobe AIR app. The problem was a quoting edge case; the solution was a one-liner. SQLite was a curiosity — a file-based database you used when you didn’t want to run a server.
Fifteen years later, SQLite is having a moment that few databases get to have. It’s not just embedded tooling anymore. It’s in the browser, at the edge, in production analytics pipelines, and increasingly, in vector search. The same engine, radically new contexts.
R has a reputation as a statistics tool — the thing you reach for when you need a regression or a publication-quality plot. That framing undersells it. The part of R that earns its place in a practical engineering workflow is its I/O story: the breadth of data sources it can connect to, and the consistency of the analysis layer once data arrives in a data frame regardless of where it came from.
An interesting quirk about SQLite table primary keys. Coming from MySQL I had been using ID INTEGER PRIMARY KEY AUTOINCREMENT as a habit, but it turns out SQLite primary keys only behave as a rowid alias under specific conditions.
This is worth getting right. Primary keys return SELECT and ORDER BY queries roughly twice as fast as a normal column search. SQLite creates a separate ROWID column by default for all tables that don’t have an integer primary key. Only when another column is explicitly declared as INTEGER PRIMARY KEY ASC will SQLite skip creating that default rowid column.