sqlite2dir

sqlite2dir exposes the contents of an SQLite 3 database as a collection of plain-text files. It's intended use case is not for database backups -- the view provided is intended to allow humans to more easily inspect and track changes to an SQLite database. The output format is chosen so that tools designed to operate on plain-text files, like diff and git should work well.

To allow for change tracking, sqlite2dir supports committing the tree of files resulting from the database export directly to a bare git repository, which allows inspecting the history of changes using regular git tools.

Note that sqlite2dir is currently in its initial development phase, and hasn't even been deployed by its author. The usual caveats apply.

Documentation

The documentation for sqlite2dir comes in the form of man page. The markdown file can be turned in to troff format for viewing with the man command using pandoc. Note that to the markdown source is tailored toward producing good output when fed through pandoc, and will not be rendered nicely on github or alike, and is not ideal to read in plain, either.

Generate and view the man page using the Unix man command:

pandoc -s -t man sqlite2dir.1.md -o sqlite2dir.1
man -l sqlite2dir.1

You can also find a pandoc HTML rendering of the manpage online.

Installation

As sqlite2dir is written in Rust, you need a Rust toolchain. Rust 1.37 or newer is required. To obtain the latest release from crates.io, use:

cargo install sqlite2dir

Alternatively, you can run it directly from the source checkout:

cargo run -- --help
cargo run -- db.sqlite3 db-contents

To install from locally checked-out source, use cargo install --path ., which will end up installing the executable in ~/.cargo/bin/sqlite2dir, which should already be in your PATH environment variable, if you followed the Rust toolchain installations instructions.

Static build

For deployment to a Linux target, an attractive option is to create a statically linked binary using Rust's MUSL target. This will result in a completely standalone binary, which depends only on the Linux kernel's system call ABI.

In this case, you need to enable the vendored-sqlite feature flag to link against an embedded, newly-compiled, copy of libsqlite3:

# If you haven't installed the MUSL target already, let's do that now:
rustup target add x86_64-unknown-linux-musl
# Build using a compiled-in copy of libsqlite3
cargo build --target x86_64-unknown-linux-musl --features vendored-sqlite --release
# Let's check it's really a static binary
file target/x86_64-unknown-linux-musl/release/sqlite2dir \
  | grep -q 'statically linked' || echo "nope"

Usage

Create a dump of an sqlite3 database to a directory:

sqlite2dir db.sqlite3 db-contents

Inside the newly created db-contents directory, you will find a collection of SQL files containing the database Schema, and a JSON file per table with the table contents.

The format of the SQL table data files is a stream of JSON arrays, each row being a single line containing a stand-alone JSON array containing the column data for a single database row. This format has been chosen to fulfill the following criteria:

• Reasonable diff output, while preserving the type of the values. In particular, NULL values are represented as JSON null, and so can be disambiguated from a "NULL" string or an empty string.
• Allow streaming creation and consumption with JSON parsers and serializers that operate on whole values.

Note that the SQLite "blob" data type is not yet supported, and the database dump will be aborted if a blob is encountered. See "Planned features" below for details.

Planned features

These features are planned, roughly in the order of the author's perceived importance. During development, items will be moved from below into the changelog upon completion.

• An option to generate a short report, suitable as an email message body.
• A test harness including some basic smoke tests.
• Support for the SQLite "blob" data type. A basic implementation would be to hash the blob content, and spit it out disk as its own file. The DB column would then contain a reference like {"blob-sha3-256": "SHA-3-here"}. An improvement would be to base64-encode small blobs, and store them inline.

Possible future features

• Add support for a --run argument, to specify a config file allowing for multiple DB extractions in a single run.
• With --run, add possibility for multi-threaded operation.
• Additional database backends. I don't anticipate having the need for this feature, so I probably won't add it myself. Pull requests welcome!

Non-features

sqlite2dir is not, is not intended to be, and will, in all likelihood, never become a database backup tool. SQLite provides the .dump and .backup meta-commands its command-line tool, these should be used instead. That way, it is even possible to restore the data!

Example use case

This is the scenario which prompted the development of sqlite2dir.

The PowerDNS (aka pdns) authoritative nameserver provides several database backend, in addition to the "bind" backend, which operates on plain-text zone files. The use of a database backend is more flexible, but prevents easily tracking changes to the zone content. When using plain text zone files, change tracking is easily achieved by just putting the zone files into a git repository. Using sqlite2dir, you can recover that functionality when using the SQLite pdns backend.

The following command will extract the database and commit to a bare git repository:

sqlite2dir --git-name="Clara Root" --git-email="root@localhost" \
    /var/lib/pdns/pdns.sqlite3 /var/lib/pdns/pdns.git

By adding a periodic job executing the above command, e.g., via cron or systemd timers, one can accumulate history in a bare git repository, which can be cloned and inspected for troubleshooting or other analysis.

Licensing

The code and documentation in the sqlite2dir crate is free software, licensed under the GNU GPL, version 3.0 or later, at your option.