What happened to storing blobs in a database being giant rookie mistake? I'm currently dealing with this at the office, where now a SQLServer database has grown to unmanageable size from helpdesk software attachments.
Storing huge blobs then becomes a matter of "is this my data, or is this general data that I'm merely also making use of". Examples abound of both:
- Adobe Lightroom (a non-destructive photo editor) goes with "these are not my images" and leaves them out of its sqlite "lrcat" files, instead maintaining a record of where files can be found on-disk.
- Clip Studio (a graphics program tailored for digital artists) on the other hand stores everything, because that's what you want: even if you imported 20 images as layers, that data is now strongly tied to a single project, and should absolutely all be in a single file.
So the key point here is that, yes: sqlite files are database, but because they're also single files on disk, their _use_ extends far beyond what a normal database allows. As application file format, the rules for application files apply, not the rules for standard databases.
Is having everything in one gigantic .db file an actual downside? What makes a database's size "unmanageable"? Presumably, you'd have to store that business information somewhere anyways, right? I don't understand how 1 unified file is unmanageable, but splitting that concern up into 2 different domains magically makes it better.
As a simple example, Word documents are just zipped XML text files (try unzipping a .docx and looking inside). Instead of using this, you could a SQLite .db file (probably with a different extension), translating the XML files into tables, and folders into databases. The OpenOffice case study has more details: https://sqlite.org/affcase1.html
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);Sometimes all that is known about data is it exists - in that case, into the database as a blob it shall go. If it can be decomposed it probably should be.
Your Helpdesk example used SQLServer problematically because a SQL database shouldn’t be used as an arbitrary file store. But if you know what the file structure is and have a reasonable grasp for how it might scale (that each binary blob is small, that each user only adds one row to the database, etc), there are huge advantages to “a SQLite table with lots of binary and text columns” versus “a folder with lots of binary and text files.” And if those text files are just small key-value pairs then maybe they should also go in SQLite.
We don't really create too many new file formats these days, and if we did they're highly performance specialized (parquet, arrow).
Just wondering aloud, what recent file format would have benefited from being based on sqlite?
Suppose, for example, that you were going to make your own implementation of Git. The normal implementation has a bunch of blobs under one directory. There's not much use in manipulating this directory separately from everything else under .git. The blobs and most of the non-blob stuff are only useful together anyway, so you already manage them as a single collection.
It could create problems for certain backup tools, so that's a disadvantage. But it also simplifies application development. So it's not a slam dunk one way or the other.
Storing blobs on the DB makes the data more consistent (the database is always in sync with the file content), and allows you to use advanced features of the DB on these files (transactions, SQL queries, data rollback ...). You also only have to backup the DB.
Storing links to the objects is usually more scalable, as your DB will not grow as fast. DBs are usually harder to scale, and also more expensive (at least 10x per GB).
It really depends on the project, but I'm favoring more and more storing the data in BLOBs, as it makes backups easier and as I can use SQL queries directly on the data. Databases as a service also make it easy to scale DBs up to +/- 2TB. But the cost might still be an issue.
Expensive in what way? Memory/Compute? It can't be licensing money since SQLite is public domain.
In my view, storing blobs in sqlite doesn't have a huge disadvantage tied to it. Sqlite grows pretty linearly and as it stores the whole db in a single file, hosting provider doesn't even have to know about you using it.