7 ms·
SQLite as an Application File Format
- stavarotti 11mo agoPreviously: https://news.ycombinator.com/item?id=23508923 https://news.ycombinator.com/item?id=23508923
- gjvc 11mo ago[flagged]
- lmshn 11mo agoThe previous discussion usually has useful and interesting conversations that are nice to revisit.
- gjvc 11mo ago[flagged]
- wat10000 11mo agoIt’s a helpful link, not a criticism.
- gjvc 11mo ago[flagged]
- wat10000 11mo agoIt makes it easy for me to find additional commentary for things I’m interested in, and I appreciate that. There’s no policing going on. If you find it off-putting, that’s your problem.
- gjvc 11mo agoas i said and you ignored, all it does is put people off from having fresh conversations.
- macintux 11mo agoYou do realize that dang himself frequently aggregates related discussions, and thanks people for doing so. And that the previous discussions of the same URL are readily available at the top of the topic, via the "past" link. So either HN itself is actively discouraging discussions, which seems unlikely, or your perception of this is askew.
- wat10000 11mo agoI directly addressed that in my last sentence.
- deleted 11mo ago[deleted]
- jrochkind1 11mo agoSearched for this topic: > 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.
- dist-epoch 11mo agoSame as .zip, .xml, .json and many others. Doesn't mean that whatever the app stores inside will remain backward compatible which is the harder problem to solve.
- QuadrupleA 11mo agoStill helpful!
- jrochkind1 11mo agoRight, but none of those are the working use file formats for a relational database.
- renegat0x0 11mo agoI think I use SQLite like that (to some extent): - https://github.com/rumca-js/Internet-Places-Database 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
- askl 11mo agoSomehow 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.
- trws 11mo agoI worked @fzakaria on developing that idea. It actually worked surprisingly well. The benefits are mostly in the ability to analyze the binary afterward though rather than any measurable benefit in load time or anything like that though. I don’t have the repo for the musl-based loader handy, but here’s the one for the virtual table plugin for SQLite to read from raw ELF files: https://github.com/fzakaria/sqlelf https://github.com/fzakaria/sqlelf
- deleted 11mo ago[deleted]
- gjvc 11mo agowonder if this would make hot-swap functions easier, if every function had its own section and every section was in the db
- kstrauser 11mo agoI think we could call it Library Internal Sequel Procedures.
- giancarlostoro 11mo agoForget elf, imagine having a SQLite file that stores elf, exe and DMG binaries. I would not mind working on something like this.
- actionfromafar 11mo agoNot that at all, but interesting in its own right - https://pypi.org/project/sqlelf/ https://pypi.org/project/sqlelf/ explore ELF via SQL.
- rtyu1120 11mo agoBit 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?
- tetraca 11mo agoBecause Windows can view and extract them out of the box without installing any additional applications. If it supported anything better out of the box I'd guess people would use that instead.
- lvh 11mo ago"The operating system makes it easy to mess with" doesn't seem like a particularly useful property for application file formats.
- TeMPOraL 11mo agoIt was, back when software development was run by hackers and not suits and security people. Easy access was a feature for users, too; back in those days, software was a tool that worked on data, it didn't try to own the data.
- mikkupikku 11mo agoIt works well enough. What could, for instance, epubs gain by having another base format instead?
- gus_massa 11mo agoI think most format use "gzip" instead of "zip".
- conradludgate 11mo agogzip is not an archive container. You're thinking of .tar.gz which is a "tape archive" format which is compressed using gzip. Zip is by itself both a compression and an archive format, and is what documents like epub or docx use
- scary-size 11mo agoActually 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. https://blog.project-daily.com/pages/file-format_3705.html https://blog.project-daily.com/pages/file-format_3705.html
- seanalltogether 11mo agoI 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.
- dchest 11mo agoIt does, here's a schema from an image I just saved with the latest version. Pretty simple. CREATE TABLE image_attributes ( name text, value blob); CREATE TABLE layers (id text, parent_id text, sequence integer, uti text, name text, data blob); CREATE TABLE layer_attributes ( id text, name text, value blob); Also, document-based apps that use Apple's Core Data framework (kinda ORM) usually use SQLite files for storage.
- setr 11mo agoMessages uses it too on Mac; was using it to do some convoluted text search on my history
- dchest 11mo agoNot as an application file format discussed in the link, though. Lots of software use it as a database (as intended) it's also a base for Apple's Core Data.
- joelwallis 11mo agoSQLite 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.
- psnehanshu 11mo agoI see no downside in using sqlite as an application file format.
- chii 11mo agoThe 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.
- crazygringo 11mo agoYup. You can strip headers from the file though and keep them in your application though, to keep the file from being easily usable. And/or encrypt it.
- nly 11mo agoSQLCipher + a hard-coded or generated key in your app.
- kianN 11mo agoThis 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.
- spdegabrielle 11mo agoI think the developers had the same idea https://fossil-scm.org/ https://fossil-scm.org/
- jansommer 11mo agoSomething 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.
- lateforwork 11mo agoIt can be compressed, see https://sqlite.org/sqlar.html https://sqlite.org/sqlar.html
- deleted 11mo ago[deleted]
- jansommer 11mo agoArchive Files is for blobs as far as I understand. All your other data remains uncompressed?
- nh2 11mo agoPlease do not use second resolution mtime (cannot represent the high accuracy mtime that modern OSs use, so packing and unpacking , or causes differences eg in rsync), or build anything new using DEFLATE (it is slow and cannot really be made fast).
- mort96 11mo agoThis seems completely orthogonal? This is an alternative to zip and tar built on SQLite: > An "SQLite Archive" is a file container similar to a ZIP archive or Tarball but based on an SQLite database. Your parent comment said that when you're using SQLite as an application format, the content in the database don't get compressed. These two things have nothing to do with each other.
- crazygringo 11mo agoSure. 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.
- abhashanand1501 11mo agoWe 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.
- lateforwork 11mo agoMost 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.
- elephantum 11mo agoYou do know, that you can create more than one table in SQLite and have references from one to another? Even recursive references work
- robrenaud 11mo agoI had some json data that I wanted an annotation interface for. So I asked codex to put it into sqlite and make a little annotation webserver. It worked quickly/easily and without hassle. Sqlite supports queries over json-like objects. Maybe a very simple document oriented db would have been better? My biggest gripe is that the sqlite cli is brutally minimal (makes sense given design), but I probably should have been using a nicer cli.
- fauigerzigerk 11mo agoWhat do you mean by "json-like objects"? My issue with SQLite's JSON implementation is that it cannot index arrays. SQLite indexes can only contain a single value per row (except for fulltext indexes but that's not what I want most of the time). SQLite has nothing like GIN indexes in Postgres.
- packetlost 11mo agoMaybe 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.
- incanus77 11mo agoI 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.
- jeffypoo 11mo agoabsolutely adore the mbtiles format! thank you for creating that.
- incanus77 11mo agoThanks. Shortly afterwords, I started collaborating with a few folks and it definitely was a team effort to push it forward (icon design, UTFGrid spec, node-sqlite & server-side adoption...). It's still a very satisfying solve for the problem.
- out_of_protocol 11mo agoAlso, tested .zip vs .tar vs sqlite vs file system. Of this bunch, sqlite was the most compact format with minimal overhead.
- euroderf 11mo agoThere 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.
- spdegabrielle 11mo agoIs there a software solution to mounting any DB as a filesystem?
- forgotpwd16 11mo agoThere's FUSE-using Sqlitefs & WebDAV-using Wddbfs.
- euroderf 11mo agoFUSE on Mac seems to be a kernel/permissions mess.
- crazygringo 11mo agoWhy would you want to do that?
- euroderf 11mo agoConvenience? Not cluttering up a directory with a transient file tree?
- crazygringo 11mo agoBut why would you want to use SQLite for that? On a Mac, you'd e.g. use and mount a disk image if you wanted to create a filesystem inside of a file. Windows has virtual hard drives, and you can do that kind of thing on Linux too. I don't understand why you'd ever want to use a relational database for that. It's a completely different paradigm. Although I also don't really understand why you're worried about cluttering up a directory. And if it's transient, it's that when temp dirs are for?
- born-jre 11mo agoi am taking it to new new extreme > https://github.com/blue-monads/potatoverse https://github.com/blue-monads/potatoverse
- actionfromafar 11mo agoPlanning on putting a license on it? I habitually ignore repos without a LICENSE file in them.
- born-jre 11mo agooh yeah, added now
- ejstembler 11mo agoThe Acorn macOS app uses SQLite in a similar way: https://flyingmeat.com/acorn/docs/technotes/ACTN002.html https://flyingmeat.com/acorn/docs/technotes/ACTN002.html
- itopaloglu83 11mo agoRecently reverse engineered the Money Pro backup format, it's a binary file with SQLite with some additional XML information backed in. It feels like they're purposefully making it harder for users to export their data in a useful format, especially after the changes they made to their financial model.
- incanus77 11mo agoYes! Gus (the developer) also has made & maintained FMDB for many years, a nice Cocoa wrapper for the SQLite bindings. https://github.com/ccgus/fmdb https://github.com/ccgus/fmdb
- SoKamil 11mo agoI remember when I was a child I used to open WinRAR and try to open random files in games and programs to find some „hidden” assets.
- ianberdin 11mo agoSold. Absolutely. I always wonder when people can sell ideas or products so effectively.
- zkmon 11mo agoI 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.