Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite as an Application File Format

sqlite.org

Post reply on HN
Print view View original

81–90 of 108 posts

Re: SQLite as an Application File Format

#81

Post by incanus77 »

jeffypoo wrote: post #57
incanus77 wrote: post #43

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 interfac…

absolutely adore the mbtiles format! thank you for creating that.

Thanks. 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.

Re: SQLite as an Application File Format

#82

Post by packetlost »

lateforwork wrote: post #80
packetlost wrote: post #58

Earlier quoted context omitted.

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 a…

What problem are you trying to solve with this approach? Unless your document is huge and you need the ability to read or update portions of it, it is better to just read and write JSON.

There's a laundry list of benefits that all add up, not like one specific killer feature. Some applications really do have very complex configuration needs, but it's sorta situation dependent on whether embedding a scripting language or a database is the right solution (for really simple cases I'm more likely to reach for TOML).

An incomplete list of benefits of using SQLite:

- Runtime config changes for free

- type safety

- strong migration support

- incorrect configurations can be unrepresentable (or at least enforced with check constraints)

- interactable from text-based interfaces and strong off-the-shelf GUI support

Re: SQLite as an Application File Format

#83

Post by lateforwork »

packetlost wrote: post #82
lateforwork wrote: post #80

Earlier quoted context omitted.

What problem are you trying to solve with this approach? Unless your document is huge and you need the ability to read or update portions of it, it is better to just read and write JSON.

There's a laundry list of benefits that all add up, not like one specific killer feature. Some applications really do have very complex configuration needs, but it's sorta situation dependent on whether embedding a scripting language or a database is the right solution (for really simple cases I'm more likely to reach for TOML). An incomplete list of benefits of using SQLite: - Runtime config changes for free - type…

Most frameworks can serialize and deserialize JSON from strongly typed classes. For example, Newtonsoft in .NET. The rest isn't worth the effort for most people. Your scenario may be unusual.

Re: SQLite as an Application File Format

#84

Post by packetlost »

lateforwork wrote: post #83
packetlost wrote: post #82

Earlier quoted context omitted.

There's a laundry list of benefits that all add up, not like one specific killer feature. Some applications really do have very complex configuration needs, but it's sorta situation dependent on whether embedding a scripting language or a database is the right solution (for really simple cases I'm more likely to reach for TOML). An incomplete list of benefits of using SQLite: - Runtime config changes for free - type…

Most frameworks can serialize and deserialize JSON from strongly typed classes. For example, Newtonsoft in .NET. The rest isn't worth the effort for most people. Your scenario may be unusual.

I've certainly had some unusual contents in the past where we had approximately 10,000 configurable properties on the system, but we didn't use SQLite for that. Regardless, you ignored 3 of the 4 (I'll ignore the last one, it applies to JSON too) other points I made. My use cases aren't that weird and I'm not saying reach for SQLite every time, it's one option out of many. Migrations and runtime configuration change alone justify it for me in many cases.

Re: SQLite as an Application File Format

#85

Post by dogline »

packetlost wrote: post #58
lateforwork wrote: post #42

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 a…

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 a…

Until just now, I've been trying to figure out why people think that JSON is necessary in the database? Yes, lots of data is hierarchical, and you just normalize it into tables and move on. The fact that some people don't work this way, and would like to put this data as it stands into a JSON tree hadn't occurred to me.

What problem does normalization solve? You don't have to parse and run through a tree every time you're looking for data. You would, however, need to rebuild the tree through self joins or other references in other cases, I suppose. It depends how far you break down your data. I understand that we all see data structures a bit differently, however.

Re: SQLite as an Application File Format

#86

Post by chii »

psnehanshu wrote: post #31

I see no downside in using sqlite as an application file format.

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.

Re: SQLite as an Application File Format

#88

Post by crazygringo »

jansommer wrote: post #38

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.

Re: SQLite as an Application File Format

#90

Post by crazygringo »

chii wrote: post #86
psnehanshu wrote: post #31

I see no downside in using sqlite as an application file format.

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.

Yup. 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.
Post reply on HN