Against unnecessary databases [see within blog graph]

rexport is a script to export user's Reddit data like saved post/comments/submissions and upvotes.

• export layer

  If you ignore the argparse boilerplate, the export itself is very simple, just a matter of calling API methods (I'm using existing bindings) and combining the results into a JSON object. After that, data is immediately persisted on a disk.

  Reddit API is restricted to the 1000 latest items, so to have a full view of my data, I need to

  • export it regularly (which I do)

  • save each exported data slice in a new file, which I also do

    Using timestamp as a filename is a natural choice, I'm using arctee tool to do this for all of my exports.

• data access layer

  The data access layer provides simple Python classes that make it pleasant to work with the data.

  My bindings simply expose saved JSON data via Python's properties: e.g. Comment.

  • if I ever need to extract something else from the raw JSON (e.g. upvotes), that would only be a matter of adding three lines of code into Comment class
  • in contrast, had I kept the data in a database, that would have required a database migration for Comments table at the very least.

  Now, remember I mentioned that Reddit only gives you the latest 1000 items, so I end up with overlapping data slices? To get a full view of my data, I'm simply going through individual JSON files in chronological order and merging together in accumulate method.

• [optional] caching layer

  I've got Reddit data way into past and export it every day, so merging together all these files during data access can indeed take a noticeable time.

  I'm overcoming this by using a @cachew annotation on the comments() method.

  One annotation, that's it. Look ma, no SQL! Okay, okay, there is quite a bit of SQL involved, but it's all hidden inside cachew. It would also work for any other source of personal data.

Yes, however in most cases your data export will take few megabytes anyway. If you compress it, it's hundreds of kilobytes.

Storage saved by using a database instead of plaintext is marginal and not worth the effort.

Only exceptions I can think of are:

• instant messaging apps: number of text messages approaches millions

• continuous data like room temperature, CO2 levels, etc

  If you have a minute by minute stream of uniform data, a database is probably the way to go.

The same point applies as in the previous section. If a your data export contains several thousand items, you won't notice the difference.

In addition, there are ways of getting most of the database benefits without having to sacrifice simplicity and without writing SQL boilerplate.

SQL is extremely powerful and helps you to interact with your data without distracting on low-level retrieval details.

That's a very good point, but query language doesn't necessarily mean a database. E.g. see pandas which is capable of what SQL is capable of, and even more convenient than SQL for our data exploration purposes.

In addition, see the counterargument about the restrictions of the relational model.

And finally, nothing prevents you from providing a database as an additional interface to your data.

Example: exposing jawbone sleep data as a dataframe.

That's kind of a subjective point (although I'd rather agree).

However, raw rows, while good enough for simple queries, can only get you so far.

While Sqlite is more pleasant to query than JSON (but still, do check out out jq), once you start using your data repeatedly, you want to abstract away from the low-level rows and encode with proper datatypes (in your favorite target programming language). E.g. you want to use strings that have the familiar methods, datetime types, data classes, etc.

I want an abstract interface to my data, and I'm not alone, ORMs exist for a reason.

So, in either case, you need to write data bindings. Say, you want to export some data from an API.

We're comparing two options:

• option 1

  during export:

  • retrieve the JSON from the API
  • keep it on the disk as is. Trivial step, just save in the file.

  during data access:

  • parse JSON
  • map data onto the abstract interface

• option 2

  during export:

  • retrieve the JSON from the API
  • parse JSON, transform and normalize data to conform to the database
  • insert in the database. Often not a trivial step due to transactions and multiple insert/update statements.

  during data access:

  • make a SQL query
  • map data onto the abstract interface

Note that you have fewer steps if you follow the first approach. And it's not only about the time saved on implementing: the less you transform the data, the less the chance you screw something up. I'll elaborate with a specific example of a royal screw up later.

Even though schemas in Sqlite are annotated with types, that doesn't give you strong typing guarantees in runtime, a concept that is gently called "flexible typing". In other words, your data is implicitly coerced which may be convenient but might cause some unanticipated failures later.

SQL constraints are a potentially great way to guarantee intrinsic consistency of your data. If you encode consistency checks in your target SQL, you will be protected from corrupting the logical state, even if you try to mess with the database manually.

This is great in theory, however, doesn't work so well on practice for personal data:

• when you use something like PostgreSQL, you've got PL/pgSQL at hand, a real programming language, to encode your constraints
• when you use Sqlite, it's a bit more tedious, there is a C API to interface with custom functions. Otherwise, you're limited by native Sqlite functions, hence encoding constraints might be very awkward. Needless to say, very few people would (and do) bother using it unless it's absolutely vital to implement such checks.

In addition, with regards to personal data, it's easier to assume a "single writer" model and encode constraint checking in the programming language, during data access.

My observation is: if you don't own the schema, you won't get it right. The services break their APIs, underdocument, or return utter garbage all the time.

The alternative I suggest, storing raw data as is, means that at least:

• you won't make it any more inconsistent than it already is
• if you misinterpret the data, that's just a code change, data is not corrupted
• you will massively save on your time and mental resources

That's a good point, also somewhere along the lines of 'better data interfaces'.

Exporting data is one part, and it doesn't particularly matter who implemented it and how, as long as it works for you.

Accessing is different, the realities of the modern FFI are such that typically, it's easier to reimplement the data bindings from scratch on the programming language you prefer.

• if the data is kept as is (e.g. as unnormalized JSON), that means reimplementing the data extraction and parsing logic from scratch

• if the data is kept in Sqlite, it's already to some extent structured and normalized

  That means that data bindings could be simply a matter of writing datatype definitions and using some database bindings, which is fairly straightforward.

I have to admit, I don't have much to counter this. This is a tradeoff I have accepted while writing tools for export.

I want other people to use them, so we have a collective stake in maintaining them. I write in Python, so hopefully, it's covering enough people.

That said, I'll repeat my point that you can still use a database as an intermediate layer, without relying on it as a primary data storage.

These days we often just say 'database' when we mean 'relational database'. Relational databases can be vaguely thought of as "a set of tables, each table storing typed data tuples". Such simplicity allows for query language expressiveness and enables the optimizer to reason about data retrieval.

At the same time, this simplicity is a curse and imposes structure that might be incompatible with the format that you receive data in. Often, the relational model requires you to rearrange data before inserting, which might break data consistency.

Kind of similar to the points on types and the relational model, often you need first-hand knowledge of the API to come up with a correct and reasonable schema.

Schemas use a fixed number of fields, which requires you to scrupulously inspect the data and make sure you've covered everything by your schema.

Inevitably, APIs change and extend, often without you knowing that (or not having time to follow the API news). If the API starts returning more data, that means the database scheme needs to change.

Depending on the way you implemented it, that means one of two things:

• best-case scenario: you implement checks for the API schema and fail your export script on a mismatch.

  Now you are aware of API changes, but your export script stopped working and requires attention and fixing Depending on the specific service and on how much do you value your data, it might require a fast fixing response.

• worst-case scenario: you had no checks for schema changes and you're not even aware of it.

  Sometimes it's fine, but sometimes that means losing data forever.

Consider the following scenarios and how we handle them:

As I mentioned, Chrome only keeps the history for the last 90 days. Imagine you want to retain all your past browsing history. And no, you can't rely on Takeout for that, or you might not be using Google at all.

So you want a script that exports your history, say, every week and maintains full browsing history over the years.

Chrome keeps the history in an sqlite file, and exporting it is trivial! It's just a matter of copying ~/.config/google-chrome/Profile/History.

Imagine you want to come up with a master database schema and merge together weekly history databases. That requires some careful inspection of the database in order to find out how it keeps the data.

So you do it and it seems that a subset of schema that History database is already using would make sense:

TABLE   urls(
    id    INTEGER PRIMARY KEY, 
    url   STRING,
    title STRING
)
TABLE visits(
    id         INTEGER PRIMARY KEY,
    url_id     INTEGER, // foreign key referring to urls.id
    visit_time STRING
)

Pseudocode for your export script would look like:

chunk_db = "~/.config/google-chrome/Profile/History"
master_db  = "/exports/chrome/full_history.db"

for row in query(chunk_db, 'SELECT id, url, title from urls'):
    insert_or_ignore(master_db, 'urls', row)

for row in query(chunk_db, 'SELECT id, url_id, visit_tile from visits'):
    insert_or_ignore(master_db, 'visits', row)

Note: this code already smells because:

• accessing an opened (by Chrome) sqlite database would fail – you need to copy the database file first and work with a copy
• it's also easiest to copy to ensure transactional reads: no one guarantees you Chrome updates the database in a transaction
• you need a write transaction on master_db, otherwise, someone who reads it might see inconsistent data

• we have to use insert_or_ignore (instead of simple insert), because the chunks are overlapping on data, hence primary keys will conflict

  It's kind of an arbitrary choice; the alternative could be using insert_or_update. Hopefully, they should be equivalent, although it's unclear which one is morally correct. E.g. what happens if URL's title change? But hopefully, that doesn't matter much anyway, right?

  As we'll see this very bit will turn out to be very problematic.

But anyway, let's carry on. Fast forward few exports and merges, your full history database looks like:

visit_time is in epoch microseconds in the actual chrome history database, but it's easier to illustrate with strings here.

Note that ids don't start with 0, we had already been using Chrome for more than 90 days at the time we started exporting, so sadly some data is gone forever.

Now, imagine you've reinstalled your operating system.

What happens to the Chrome databases? They reset, no one guaranteed you that ids would persist. The table ids start all over from 0 again.

~/.config/google-chrome/Profile/History now looks like:

Let's see what it means for our full history database /exports/chrome/full_history.db. What happens after you merge the new chunk of history?

Hmm.

• google.com ended up twice with different ids, but okay, why not
• however, the real problem is that stackoverflow.com is gone due to the use of insert_or_ignore.

And it's actually worse. Let's look at the visits table in the full_history.db:

The visit with id 900, that should point at stackoverflow.com, now points at google.com, so we ended up with some fake browsing history.

How could we have avoided it? Note that using insert_or_update wouldn't have helped: that way we'd just overwrite history in the master database and make it inconsistent again

Why did that happen? The schema was wrong. We shouldn't have relied on ids: they are kind of an artifact of the relational model. One way of getting around it is "unnormalizing":

TABLE   visit_with_url(
    visit_time STRING
    url        STRING
    title      STRING
)

When we update the database, we use something like:

for row in query(chunk_db, 'SELECT visit_time, url, title FROM urls JOIN visits ON url.id = visits.url_id'):
    insert_or_ignore(master_db, row)

Note that by the time you do realize that and fix it, you may have already lost (and even worse, corrupted) older history.

TLDR: cachew lets you cache function calls into an sqlite database on your disk in a matter of single decorator (similar to functools.lru_cache). The difference from functools.lru_cache is that cached data is persisted between program runs, so next time you call your function, it will only be a matter of reading from the cache. The cache is invalidated automatically if your function's arguments change, so you don't have to think about maintaining it.

In order to be cacheable, your function needs to return (an Iterator, that is generator, tuple or list) of simple data types:

• primitive types: str / int / float / datetime
• NamedTuples
• dataclasses

That allows to automatically infer the schema from type hints (PEP 526) and not think about serializing/deserializing.

The readme also contains an example of using cachew for exporting temperature sensor data.

In essence, filenames along with their modification times are used as the key for caching:

• when the exported data is updated, files and their modification times change, which makes the cache invalidate. It's it's updated automatically on the next data access
• in between data updates, data is read straight from the cache and the access is pretty much instantaneous: databases are indeed fast