16 ms·
SQLite as an Application File Format (2014)
- saagarjha 8y agoCoreData has an option to write to a SQLite database, so a lot of data on iOS and macOS apps ends up being written to disk this way.
- thechao 8y agoIf you code in c/c++, and have even a modest OS/RTOS available, you should use SQLite as your persistence layer, full stop. I switched to this methodology 5 years ago & never looked back. You know what happens when another engineer gets your on disk format & asks how to look at it? You say “open it in SQLite browser”; all further questions disappear.
- romwell 8y agoSeconding this. One thing though: last time I did that, I ended up writing my own ORM/serialization library (with templates and all), but I wish I didn't have to. Is there anything freely available out there that would allow one to store object hierarchies with the ease of boost::serialization, but into an SQLite EAV scheme?
- thechao 8y agoI always trace rather than log. That means I have one table per ‘tracing function’, and just record the arguments to each table. Recording an object means recording the function calls that generated the object; regenerating the object is just playing those functions back. I’ll admit that this works far better in C than C++, as C++ tends to treat new types as opaque primitives.
- trampi 8y agoSounds like https://martinfowler.com/eaaDev/EventSourcing.html https://martinfowler.com/eaaDev/EventSourcing.html
- ramraj07 8y agoWhat if I need to store binary data like images or PDFs? Is storing them as blobs an acceptable method?
- anonymouzz 8y agoYup, see no reason why not. This is answered in the post: > Any application state that can be recorded in a pile-of-files can also be recorded in an SQLite database with a simple key/value schema like this: CREATE TABLE files(filename TEXT PRIMARY KEY, content BLOB); > If the content is compressed, then such an SQLite Archive database is the same size (±1%) as an equivalent ZIP archive, and it has the advantage of being able to update individual "files" without rewriting the entire document. Also look at the related post which suggests doing precisely that - storing images inside SQLite blobs: https://www.sqlite.org/affcase1.html https://www.sqlite.org/affcase1.html
- nodamage 8y agoIs this really a good idea though? I thought it was generally preferred practice to store large binary files outside of the database. Otherwise the database file will end up growing pretty quickly.
- manigandham 8y agoThat's your call. If you're going to be storing 100gb of images then put them somewhere else, but this post is about using SQLite as a file format so storing binary files inside is not a problem.
- out_of_protocol 8y agoCounterpoint: tiled maps, which are basically loads and loads of images, can be stored on disk as - Direcory structure - Zip file - Tar file - Sqlite db Out of these, sqlite is the most compact, by far (also the fastest)
- 8y ago
- tedunangst 8y agoI believe ZIP supports updates about as well as SQLite. You append the new file to the end (and write the "header" after that). This leaves a hole in the middle, but that can be reused by a later addition. There's some details about internal page size and fragmentation, but I think it could amortize to about the same. There's something to be said about quality of implementation, but that's not an argument about the file format per se.
- darkpuma 8y agoAre there zip libraries that facilitate this usage pattern? Either way, I tend to think you'd probably be better off with sqlite since it would give you more flexibility (for instance if you later realize that your application possesses relational data.)
- manigandham 8y agoHow? There's a massive difference in usability and performance with a SQL database and all the data querying and manipulation it provides compared to a ZIP file that just straps together a bunch of compressed payloads with a key/value file structure. The linked page even has a case study of the OpenDocument file format that shows exactly how SQLite is better than a Zip file: https://www.sqlite.org/affcase1.html https://www.sqlite.org/affcase1.html
- tedunangst 8y agoIf you're using SQLite as a pile of files, you're not doing much querying if the compressed data inside. > Newer machines are faster, but it is still bothersome that changing a single character in a 50 megabyte presentation causes one to burn through 50 megabytes of the finite write life on the SSD. That's funny, since Firefox would burn through gigabytes of writing to SQLite. All comes down to quality of implementation.
- manigandham 8y agoThe point is that you don't just use it as a pile-of-files when you have SQL access to the data and instead use a high-level data model. This is not possible at all with ZIP.
- LeoNatan25 8y ago> Single-File Documents. An SQLite database is contained in a single file, which is easily copied or moved or attached. The "document" metaphor is preserved. Is this still true by default? With write-ahead (wal) and shared memory (shm) enabled, it is no longer safe to consider only the database file. Pragmas exist to disable those, but people should be familiar of these features and use with care. The document should be updated to introduce these concepts.
- kccqzy 8y agoIt's mentioned on the WAL docs on https://sqlite.org/wal.html https://sqlite.org/wal.html > There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as an application file-format.
- adzm 8y agoWhy would you realistically need wal or shm for an application file format anyway? That's for high concurrency in general.
- gumby 8y agoThe article explicitly mentions concurrent access.
- marton78 8y agoThat's true, however it's possible to remove the wal file after closing the database: > The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().
- imh 8y agoCan you expand on that? How do those two features break things?
- darkpuma 8y agoIt's not so much that they break things, but if those features are enabled sqlite will be writing multiple files instead of just one, which somewhat nullifies the "single file" benefit of sqlite.
- anonymouzz 8y agoI wish more people would publish data as SQLite databases (if the size permits, of course; usually it does). It's so much more reliable than CSVs, which have at least a few dimensions of significant differences (quoted/unquoted, comma vs semicolon vs tab vs space, headers/no headers, comments). Not to mention that initial data exploration can be done right in an SQLite explorer/browser tool.
- icelancer 8y agoWhen I open my data I usually publish both CSV dumps (for those who prefer using awk/sed and command line tools; this is usually easier) and MariaDB reconstructive files. I'll consider SQLite databases from now on, I like that idea.
- blitmap 8y agoI wish webpages were 'archived' as sqlite databases. :x I wish a lot of metadata were defined as a database schema, and sqlite lends itself so willingly to becoming the archive/header. Does sqlite do internal gunzip compression? I do understand we have MHTML: https://en.wikipedia.org/wiki/MHTML https://en.wikipedia.org/wiki/MHTML
- voltagex_ 8y agoWARC is the "standard" now for web archiving.
- olalonde 8y agoReminds me of this HN submission: https://news.ycombinator.com/item?id=16809963 https://news.ycombinator.com/item?id=16809963 Apparently CSV is actually quite hard to parse.
- mirimir 8y agoIt's not that CSV is hard to parse. It's that there's no guarantee that you'll get proper CSV. For example, it may literally be "CSV", without quotes. And that's fatal if values contain commas. I've even seen CSV with values that contain ``","``!
- Const-me 8y agoIt's a good idea unless you're exchanging these files. I'd love couple of my clients switch from HDF5 to SQLite, HDF5 is horrible.. But if you exchange them, it's too easy for app developers to misuse the library causing user's data to be leaked to third parties, in free DB pages, and even in the way how exactly the DB is fragmented.
- romwell 8y agoWhat you suggest sounds like security by obscurity.
- naniwaduni 8y agoBased on the mention of free DB pages, I suspect the issue is information leaking because it isn't scrubbed after being deleted, so that it's still in the db file when it's sent off in a third party, just not "accessible" via SQLite APIs. For example, a redaction procedure which consists of deleting one table that contains all the sensitive data doesn't necessarily _erase the data_; you'd probably want to VACUUM or reconstruct the database from a dump. Moreover, it's a general issue with file formats that can encode the same data in multiple ways that, in addition to the data itself, the data file also incidentally encodes information about the process by which the data was encoded. This metadata is usually ignored and abstracted out by APIs for working with the data, so application developers tend to overlook it as a place where sensitive data can leak. For comparison, you might consider fingerprinting API clients based on the order in which they send HTTP headers or keys in JSON objects, which can often be correlated with language or library versions in environments where the most convenient map structure is an arbitrarily ordered hash. It's not necessarily easy to think of nefarious uses for this sort of information, but SQLite database fragmentation can reveal, albeit in rather rough detail, the order in and frequency with which changes were made. Quite the opposite of "security by obscurity", in fact.
- justinclift 8y agoThe SECURE_DELETE pragma sounds like it'd be useful for this: https://sqlite.org/pragma.html#pragma_secure_delete https://sqlite.org/pragma.html#pragma_secure_delete Needs to be manually enabled though, as that doc says it's generally not enabled by default. :/
- tommoor 8y agoSketch (https://sketchapp.com https://sketchapp.com) actually used a zipped SQLite database as it's file format until the past year.
- glhaynes 8y agoAny idea why they changed?
- tommoor 8y agoThey changed to a zip of JSON files, I believe the main reason was likely hackability – one of the main advantages Sketch has in the design ecosystem is it's plugin architecture and the array of tools that exist around it. If you make your file format easy to work with then it only widens this moat.
- qlk1123 8y agoWhen using kubernetes, I always wonder if we can transform all the tedious "kubeconfig"s into a collection that can be easily queried, modified, saved and retrieved, rather than maintinaing small text pieces in some directories. More ambitiously, maybe include container/image metadata as well? After reading this document, well, I think I will give it a try. It might not be a perfect fit, but I don't quite like yamls.
- tyingq 8y agoLxd uses distributed sqlite for it's config: https://github.com/lxc/lxd/blob/master/doc/database.md https://github.com/lxc/lxd/blob/master/doc/database.md And there's this simplified fork of K8S that replaces etcd with sqlite: https://github.com/ibuildthecloud/k3s/blob/master/README.md https://github.com/ibuildthecloud/k3s/blob/master/README.md
- cyphar 8y agoIt should be noted that the distributed SQLite is a patchset written by the LXD folks. Last time I talked to hem they said they're working on upstreaming it but want to be sure that everything is sane (SQLite has crazy backwards-compatibility requirements, not to mention the testing).
- revskill 8y agoYaml is stupid. JSON is fine, because it could be `import` from javascript as a real javascript object. In general, make any configuration as a Javascript Object and you can program it whatever you want.
- mbrumlow 8y agoBut what do you do when dealing with large numbers across platforms? Represent them as a string? I have recently been moving to toml.
- revskill 8y agoConsider using all config files as `app.config.js` , you can just return a JS object and done.
- adwn 8y ago> Consider using all config files as `app.config.js` , you can just return a JS object and done. It's kind of sad that you seem to think that JavaScript is the only relevant language in existence.
- revskill 8y agoIt's because my projects are universal application, that's why Javascript. But JS is not the only choice. What i mean is, we want a programmable config file, not "static one"
- sly010 8y agoThis topic interests me. I am very interested to hear what the specific issues are with "non programmable" config files in your case. Generally I find that people often choose config languages because of syntax preference, where in reality they should be thinking if what they are trying to express is data or behavior? Ansible should have been just python. Yaml makes for an ugly programming language. Whereas python setup.py files should have been anything but python. It's a terrible source of metadata. There are cases of course where the distinction is not that obvious. Is your nginx config data or a program/behavior? How about your CI config?
- romwell 8y agoI have migrated a binary file format to an SQLite-based ORM schema at one of my jobs. It worked out amazingly well, with (limited) forward and backward compatibility for nearly free. A lot of hairy if-else code was made unnecessary. SQLite is IMHO the best option for data serialization/persistence/interchange in many cases, especially when some data is a blob, or, say, a huge array of floating-point numbers that you need to store with full precision (which makes text-based formats clunky to deal with).
- zubairq 8y agoUsing sqlite as our format has worked well for us at dannea. It also means we can have web only documents since sqlite can be embedded in a web page
- tenken 8y ago> since sqlite can be embedded in a web page howso ? ... beyond querying the database server-side ...
- lioeters 8y agoPerhaps they're using SQLite compiled to JS via Emscripten: https://github.com/kripken/sql.js/ https://github.com/kripken/sql.js/
- zubairq 8y agoYes that is correct, at Dannea we use sqlite compiled to JS. We can even load the data from the same Html page so it allows us to have sqlite powered web apps which can be emailed to others and is true server less
- nafey 8y agoI was thinking of using SQLite for creating game saves. Does anyone have experience with that? The more I think about it the more I wonder why we don't see SQLite in games more often.
- justinclift 8y agoIt has been used for game saves before. Not sure how commonly, but we (sqlitebrowser.org) do have links to our website from various places giving tips on how to change things (in the saves). ;)
- imtringued 8y agoCubeworld used SQLite to store the procedurally generated world.
- qwerty456127 8y agoYeah, I have always felt this is a great idea for almost anything that is not really so small and simple that plain text would make more sense. BLOB-by-nature data like multimedia, pictures etc come as the only reasonable exception.
- sjwright 8y agoThe third great file format (after text and SQLite) is the zip file with custom suffix. That is ideal for BLOB-by-nature data.
- qwerty456127 8y agoI didn't really study the formats myself but as far as I know 7z container format is a way better than zip.
- sjwright 8y agoVery likely true however zip has somehow become a de-facto go-to standard for this stuff. From Java (JAR files) to Microsoft (.docx et al) and numerous open formats including ODF and EPUB.
- CGamesPlay 8y agoFunnily, SQLite also describes itself as better than the FileSystem in this case as well: https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html
- deleted 8y ago[deleted]
- geocar 8y agoYou can't stream SQLite files.
- SQLite 8y agoI'm not sure what you mean by "stream" here. However, there are two APIs designed to facilitate sending SQLite database files over a socket. The sqlite3_serialize() interface (https://www.sqlite.org/c3ref/serialize.html https://www.sqlite.org/c3ref/serialize.html) takes a database and converts it into a blob of contiguous memory that can be sent over a wire. The sqlite3_deserialize() interface (https://www.sqlite.org/c3ref/deserialize.html https://www.sqlite.org/c3ref/deserialize.html) does the opposite, accepting a blob and using it as a database file. These interfaces were created for the purpose of "streaming" database files across network connections. Depending on your specific needs, they might work for you.
- shaklee3 8y agoI'm assuming they mean some parts in the file require parts that appear farther on, so you'd have to receive the whole file?
- geocar 8y ago> I'm not sure what you mean by "stream" here. However, there are two APIs designed to facilitate sending SQLite database files over a socket. I already have APIs that send SQLite database files over a socket: send(), sendfile(), writev(), and so on. What I don't have is the ability to receive SQLite database files incrementally (stream): I always need enough disk and/or memory to store an SQLite database. I can however, receive an XLS file (or even XLSX) incrementally and implement a streaming parser for it.
- phreack 8y agoI've used SQLite for a distributed project lately where different offline teachers in rural schools would back up their app data to a thumb drive as file databases, take it back to a town with connectivity and upload that data to the app server, so they'd get extra functionality and updates. It was incredibly straightforward to do and worked wonders!
- xte 8y agoMh, > Simplified Application Development It's depend "application development" is a vast area... > Single-File Documents Not much related to SQlite, can be or cannot be done with nearly any format you like. Also remember as a good advise how worse go Catia v6 R2009 (Catia is the best CAD/CAE/CAM suite in the world, used anywhere from automotive to aerospace&defence, it's sole real competitor are Creo (formerly Pro-e) and Nx) when it start to offer single-file PLMs for "light usage"... > High-Level Query Language Depending on the format you choose you may have the best/highest level query language: your own apps programming language itself, so IMO may not be an SQLite plus at all > Accessible Content Any binary content is FAR LESS accessible than text > Better Applications Sorry, it have NO mining at all. I like SQLite for many aspects but I certainly not look at it as a file format for most applications I use or imaging, for some usage is really nice, for others usages {Tokyo,Kioto}Cabinet is another nice format, for MANY other usage text files in various format, binary files in various formats (including classic pickle) may be better...
- hyperpallium 8y agoWhy not a text serialization format? That SQLite can read/write. e.g. tables and schema in JSON. A table as a list of row-objects, of attributes. (or more efficiently, an object of lists. Or, following the achema, just a list of rows, each a list of attribue's values).
- MikusR 8y agohttps://news.ycombinator.com/item?id=18685296 https://news.ycombinator.com/item?id=18685296
- oconnor663 8y agoI'm surprised this is the only comment in the thread that mentions this security issue. It's only a week old!
- hestefisk 8y agoI have done this on a large .NET Win32/XAML client / server application in two ways: 1) Saving application state and config/settings (window layout, ... very easy to marshall XAML stage to an SQLite file) 2) Caching of common data sets to avoid unnecessary polling to the server of data that is accessed all the time (ie customer master data) It works beautifully.
- henridf 8y agoFor those wishing to see Sqlite used to publish data sets: Datasette (https://github.com/simonw/datasette https://github.com/simonw/datasette) is a new tool to publish data on the web. It uses SQLite under the hood.
- derefr 8y agoCool fact you might not know: SQLite has direct support for “SQLite archives” (i.e. an SQLite database containing a conventional table of {file metadata, BLOB data} records representing archived files) built into its CLI client. This functionality lives under the `sqlite ar` subcommand, which exposes similar command-line functionality to tar(1). Read more at: https://www.sqlite.org/sqlar.html https://www.sqlite.org/sqlar.html The nice thing about this is that, since this “sqlar” table is just one table in the file, and the commands work whether or not other such tables exist, a file can be an “SQLite archive” while also being a regular SQLite database containing other tables at the same time. It’s sort of like how Fireworks used to add special chunks to PNG—the file was still a PNG, but now it was also a Fireworks project. (But, in this case, the “chunks” aren’t opaque binaries, but rather are SQL tables you can manipulate using regular SQL queries.) And, of course, the SQLar “standard” isn’t all that complex—it’s designed so that you can easily construct your own SQLite archive files just by issuing regular DDL+DML queries through the SQLite binding of your language of choice. (Though, if you want support for inserting files compressed—which the `sqlite ar` command supports extracting—you’ll need a zlib binding as well.)