Google Room and The Definitive Guide to SQLite

By: Jeremy W. Sherman. Published: . Categories: reading-notes. Tags: sqlite sql android.

Why read a book on SQLite?

The short answer: Because I needed to write a migration for a Google Room database.

Limited past exposure

I’ve mostly worked on Mac & iOS apps:

What do you mean, not the right tool?

Some folks reach for a database straight out of the gate. (Most backend frameworks, like Rails, sure seem pretty keen on baking in that architectural decision.)

But simple file serialization is often a great choice:

Most static blog generators seem to agree with this sentiment.

Even in cases where you think you might eventually need a database, if you don’t jump to conclusions, you may just never get pushed into that corner. One example is as Robert Martin tells of the acceptance testing tool FitNesse: they put off picking a DB for a couple years, then ultimately realized the project didn’t need one after all. It shipped as, and still is, a flat-file wiki.

Yes, SQLite would still have DBA headaches

You’d think it wouldn’t. You might think the same thing about a Berkeley DB key–value store. But the problems still sneak up on ya. Don’t take my word for it – consider MJD’s thoughts at the end of a bug hunt undertaken to get his blog generator populating the “subtopics” sidebar again:

I am sick of DB files. I am never using them again. I have been bitten too many times. From now on I am doing the smart thing, by which I mean the dumb thing, the worse-is-better thing: I will read a plain text file into memory, modify it, and write out the modified version whem I am done. It will be simple to debug the code and simple to modify the database.

What about CoreData?

Cocoa’s CoreData defaults to using SQLite as an implementation detail in its quest to simultaneously provide object graph persistence. But that’s an implementation detail: the file format is undocumented and subject to change at Apple’s whim. (As a bonus, you get platform data-lock from this. I’ve had cases where CoreData would have made sense, except that the file format needed to work outside of Apple platforms. Ah, well.) And the file format issues are in addition to the fun with thread containment, though that’s gotten to be substantially less of a problem over the years. So I had no reason to get cozy with SQLite to date, due to CoreData abstracting it entirely, and due to even CoreData winding up not being the right tool for me entirely too often (did I mention I like flat files and avoiding data lock-in?).

But Now, Room

But Google’s blessed persistence framework is Room. Unlike CoreData, Room is explicitly a SQLite wrapper. It aims to smooth some rough edges (writing DDL, un/marshaling data between rows and POJOs) and codify best practices around using SQLite on a (maybe pretty crummy) mobile device.

Some Rough Edges Smoothed

Some Best Practices Codified

Some Rough Edges Remain

It’ll generate the schema for you, but to move between schemas, you’ve gotta write the up-migration by hand. (You don’t have to write a down-migration. Room doesn’t support down-migration. Ever onward!)

So that’s what led me to read through a book on SQLite over a couple evenings. What follows are the notes I jotted down when I finished using LogsIt.

Aside: Room’s Design Is Swell

I really like Room’s design. It builds on top of a rock-solid and well-understood piece of tech (SQLite), there’s zero magic, you can readily dump the DB and poke around using the sqlite3 CLI tool to test and explore your queries, it codifies rather than prescribes best practice…

My favoritest thing, though, is that it vends POJOs. You don’t have to wed the heart of your app to a vendor framework just to get easy queries and streaming UI updates for free. You don’t have thread-bound crash-bombs lobbing through 99% of your app, or fight to keep it at arm’s length (as Dave DeLong advocates in point 8 of “The Laws of Core Data”) to avoid that. There’s no “will it fault? will it boom?” concern. They’re just objects. Plain, simple objects.

Anyway, on to the reading notes.

Reading Notes

Read a Book: November 14, 2018 at 21:57 Notes: Mike Owens, Grant Allen. The Definitive Guide to SQLite, Second Edition. Apress, November 2010. 9781430232254. Via Safari.

Read for background when writing migrations for Android Room.

Skipped in-depth coverage of C API, of other language bindings, and of the iOS & Android walkthroughs. Also kinda glazed over the shared cache stuff.

New to me: