Somehow my first thought from the title was using sqlite as a format for applications. So like a replacement for ELF. I think this idea is both fascinating and horrifying.
I've been pondering something similar as a modern approach to fat binaries, basically around a table like
CREATE TABLE functions (name TEXT, arch TEXT, body BLOB);
The advantage would be that binaries could be partially fattened, i.e. every function would have at least one implementation in some cross-platform bytecode (like WASM), and then some functions would get compiled to machine code as necessary, and then the really-performance-dependent functions would have extra rows for different combinations of CPU extensions or compiler optimization levels or whatever — and you could store all of these in the same executable instead of having a bunch of executables for each target.
As a bonus, it'd be possible to embed functions' source code into the executable directly this way, whether for development purposes (kinda like how things are sometimes done in the old-school Smalltalk and Lisp worlds) or for debugging purposes (e.g. when printing stack traces).
Bit unrelated rant but I'm still not sure why ZIP has been adopted as an Application File Format rather than anything else. It is a remanent of a DOS era with questionable choices, why would you pick it over anything else?
Actually used it for a desktop blogging app a few years ago. It was great! I could set up a blog skeleton, send the file to a family member. They could focus on writing content and hitting deploy.
I remember someone mentioning the Acorn image editor on Mac uses sql files to store image data. It probably makes backwards compatibility much easier to work with.
SQLite is abolutely amazing as an app format! I couldn't list how many tools are available to read SQLite data, or how easy and friendly they are. Even its CLI does wonders when you're dealing with data with it. SQLite has been around for 20+ years and is one of the most heavily tested softwares in the world.
SQLite is very simple, yet very reliable and powerful. Using SQLite as file format might be the best decision an engineer can take when it comes to future-proofing preservation of data.
The only "downside" is that the format is an open spec, which allows anyone to modify the contents without going through the specific application. And it's only a downside if you are using the format as an obfuscation to prevent third-party compatibility/reverse engineering, or to lock in customers.
This approach has really helped me out in my work. I do something very similar using DuckDB to slurp output files anytime I write a custom hierarchical model. The single sql queryable file simplified my storage and analytics pipeline. I imagine SQLite would be especially ideal where long term data preservation is critical.
Something to consider when using SQLite as a file format is compression (correct me if I'm wrong!). You might end up with a large file unless you consider this, and can't/won't just gz the entire db. Nothing is compressed by default.
Sure. But if you have reasonably small files just compress the whole file, like MS Office or EPUB files do.
Or if your files are large and composed of lots of blobs, then compress those blobs individually.
Whereas if your files are large and truly database-y made of tabular data like integers and floats and small strings, then compression isn't really very viable. You usually want speed of lookup, which isn't generally compatible with compression.
We are developing using sqlite to transfer configurations from uat to production environment. Since the configurations are already saved in a postgres table in uat, moving some configs from uat to production an sqlite file is very easy. since it's a binary format, we are also saved from any inadvertent edits by people doing production deployment.
Also, another usecase is to export data from production to uat for testing some scenarios, it can be easily encoded in a sqlite file.
Most application's file formats are structured as a tree, not as flat tables. If your application's data is flat tables or name-value pairs then SQLite is an obvious choice. But if it is tree structured then it is less obvious. You can still save your tree in JSON format as a blob in a SQLite table but in this case the benefits are fewer. But if in addition to the JSON you have images or other binary data then once again SQLite offers benefits, because each of those binary files can be additional rows in the SQLite table. This is far easier to handle than storing them in ZIP format.
Maybe not as obvious for those without formal education in """database normalization""" but it's pretty trivial to convert from a tree structure to a flat table structure using foreign key relations. Recursive queries aren't even that difficult in SQLite, so self-referential data can be represented cleanly too, if not a bit more difficult to write. IME most applications "tree structures" aren't self-referential and are better formalized as distinct entities with one-to-one relationships (ie. a subtree gets a table).
There's always the lazy approach of storing JSON blobs in TEXT fields, but I personally shy away from that because you lose out on a huge part of the benefits of using a SQL DB in the first place, most importantly migrations and querying/indexing.
> There's always the lazy approach of storing JSON blobs in TEXT fields, but I personally shy away from that because you lose out on a huge part of the benefits of using a SQL DB in the first place, most importantly migrations and querying/indexing.
SQLite at least provides functions to make the “querying” part of that straightforward: https://sqlite.org/json1.html
I am not really classically trained on the subject but I think this is the idea behind relational storage, it is to have better extraction options, you don't have to treat your data as a single document at a time.
Naively, most data looks hierarchical and the instinctive reaction is to make your file format match. But if you think of this as a set of documents stacked on top of each other if you take the data as a bunch of 90 degree slices down through the stack now your data is relational, you loose the nice native hierarchical format, but you gain all sorts of interesting analysis and extraction options.
It is too bad relational data types tend to be so poorly represented in our programming languages, generally everything has to be mapped back to a hierarchical type.
I did this for MBTiles, for storing (at the time, raster) map tiles at Mapbox. I was working on the iPad wing of R&D early in the company and we were focusing on offline mapping for the iPad. Problem was, moving lots of tiny map tiles (generally 256px square PNGs) was tedious over USB and network. We had a thing called Maps on a Stick for moving things around by USB, but it just didn’t scale well to the iPad interface & file transfer needs.
Bundled the tiles into SQLite (I was inspired by seeing Dr. Hipp speak at a conference) and voila, things both easy to move and to checksum. Tiles were identified by X & Y offset at a given (Z)oom level, which made for super easy indexing in a relational DB like SQLite. On the iPad, it was then easy to give map bundles an application icon, associated datatype from file extension, metadata in a table, etc. At the time, I was fairly intimidated by the idea of creating a file format, but databases, I knew. And then making some CLI tools for working with the files in any language was trivial after that.
There seems to be no single software solution "out there" for mounting an SQLite DB (or an SQLite archive) as a file system, with or without per-record relative paths.
I have used SQLite file as the application itself. Almost. The tables would store the application features, UIs and logic. A generic kernel would bring up the application from the database.
31 comments
[ 3.4 ms ] story [ 48.2 ms ] thread> and is backwards compatible to its inception in 2004 and which promises to continue to be compatible in decades to come.
That is pretty amazing. You could do a lot worse.
- https://github.com/rumca-js/Internet-Places-Database
For UI I use HTML, because it already provides components with bootrap, and everybody can use it without installation of any software.
All data comes from a single SQLite that is easy read, and returns data.
My database is really big, so it takes time to browse it, I wanted to provide more meaningful way to limit scope of searching
As a bonus, it'd be possible to embed functions' source code into the executable directly this way, whether for development purposes (kinda like how things are sometimes done in the old-school Smalltalk and Lisp worlds) or for debugging purposes (e.g. when printing stack traces).
https://blog.project-daily.com/pages/file-format_3705.html
SQLite is very simple, yet very reliable and powerful. Using SQLite as file format might be the best decision an engineer can take when it comes to future-proofing preservation of data.
Or if your files are large and composed of lots of blobs, then compress those blobs individually.
Whereas if your files are large and truly database-y made of tabular data like integers and floats and small strings, then compression isn't really very viable. You usually want speed of lookup, which isn't generally compatible with compression.
Also, another usecase is to export data from production to uat for testing some scenarios, it can be easily encoded in a sqlite file.
There's always the lazy approach of storing JSON blobs in TEXT fields, but I personally shy away from that because you lose out on a huge part of the benefits of using a SQL DB in the first place, most importantly migrations and querying/indexing.
SQLite at least provides functions to make the “querying” part of that straightforward: https://sqlite.org/json1.html
Naively, most data looks hierarchical and the instinctive reaction is to make your file format match. But if you think of this as a set of documents stacked on top of each other if you take the data as a bunch of 90 degree slices down through the stack now your data is relational, you loose the nice native hierarchical format, but you gain all sorts of interesting analysis and extraction options.
It is too bad relational data types tend to be so poorly represented in our programming languages, generally everything has to be mapped back to a hierarchical type.
Bundled the tiles into SQLite (I was inspired by seeing Dr. Hipp speak at a conference) and voila, things both easy to move and to checksum. Tiles were identified by X & Y offset at a given (Z)oom level, which made for super easy indexing in a relational DB like SQLite. On the iPad, it was then easy to give map bundles an application icon, associated datatype from file extension, metadata in a table, etc. At the time, I was fairly intimidated by the idea of creating a file format, but databases, I knew. And then making some CLI tools for working with the files in any language was trivial after that.
I always wonder when people can sell ideas or products so effectively.