5 ms·

What if I need to store binary data like images or PDFs? Is storing them as blobs an acceptable method?

anonymouzz 8y ago
Yup, 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 ago
  Is 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 ago
    That'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 ago
      Counterpoint: 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)
      - fatboy 8y ago
        We just transitioned our tiled images from using a directory structure to SQLite. Having a single file per very large image (for mobile) makes transferring them to the phone over USB around ten times as fast. I wish I’d done it years ago.
  - jamougha 8y ago
    Storing large objects in database rows impacts performance and scalability but this shouldn't be a problem unless your files are extraordinarily large.
  - marcosdumay 8y ago
    Creating huge files is usually a bad idea. It does not matter if they are a sequence of lines of characters, a gziped collecion of XML data, a binary format that requires 6000 pages of documentation, or a SQLite database. If you can spread your data among many files, you probably should. Not everybody can.
combatentropy 8y ago
In SQLite, a database file can be 140 terabytes, https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html And reading and writing can be even faster than if they were stored directly in the filesystem, https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html
- devmunchies 8y ago
  So does that mean serving static files from a SQLite file would be faster than something like NGINX?
  - oscargrouch 8y ago
    No, because of the sendfile syscall, where the kernel pass all the file data without switching back to userspace. Even if you can in theory save some time with small files with sqlite, you would be back to read/write IO and userspace/kernel byte buffer switching. And it probably wouldn't be faster than something like a mmaped file anyway (unless the SQLite Db is also mmaped).
    - fiddlerwoaroof 8y ago
      The thing is SQLite handles a lot of hard problems that you might not realize you have: like “was my data actually written to the disk”
- qwerty456127 8y ago
  Why don't we replace entire filesystems with SQLite then?
  - darkpuma 8y ago
    >"This file contains some example code demonstrating how the SQLite vfs feature can be used to have SQLite operate directly on an embedded media, without using an intermediate file system." https://www.sqlite.org/src/doc/trunk/src/test_onefile.c https://www.sqlite.org/src/doc/trunk/src/test_onefile.c https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html IIRC, during one of his talks D. Richard Hipp mentions this actually being done for real.
wongarsu 8y ago
Yes. The MBTiles Format [1][2] is essentially just an SQLite database storing map tiles (thousands of square images of parts of a map). In that case it's mostly done because SQLite stores small files much more efficiently than a general purpose file system in default settings. 1: https://github.com/mapbox/mbtiles-spec https://github.com/mapbox/mbtiles-spec 2: https://wiki.openstreetmap.org/wiki/MBTiles https://wiki.openstreetmap.org/wiki/MBTiles

Yup, 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 ago
Is 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 ago
  That'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 ago
    Counterpoint: 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)
    - fatboy 8y ago
      We just transitioned our tiled images from using a directory structure to SQLite. Having a single file per very large image (for mobile) makes transferring them to the phone over USB around ten times as fast. I wish I’d done it years ago.
- jamougha 8y ago
  Storing large objects in database rows impacts performance and scalability but this shouldn't be a problem unless your files are extraordinarily large.
- marcosdumay 8y ago
  Creating huge files is usually a bad idea. It does not matter if they are a sequence of lines of characters, a gziped collecion of XML data, a binary format that requires 6000 pages of documentation, or a SQLite database. If you can spread your data among many files, you probably should. Not everybody can.

combatentropy 8y ago

In SQLite, a database file can be 140 terabytes, https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html And reading and writing can be even faster than if they were stored directly in the filesystem, https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html

devmunchies 8y ago
So does that mean serving static files from a SQLite file would be faster than something like NGINX?
- oscargrouch 8y ago
  No, because of the sendfile syscall, where the kernel pass all the file data without switching back to userspace. Even if you can in theory save some time with small files with sqlite, you would be back to read/write IO and userspace/kernel byte buffer switching. And it probably wouldn't be faster than something like a mmaped file anyway (unless the SQLite Db is also mmaped).
  - fiddlerwoaroof 8y ago
    The thing is SQLite handles a lot of hard problems that you might not realize you have: like “was my data actually written to the disk”
qwerty456127 8y ago
Why don't we replace entire filesystems with SQLite then?
- darkpuma 8y ago
  >"This file contains some example code demonstrating how the SQLite vfs feature can be used to have SQLite operate directly on an embedded media, without using an intermediate file system." https://www.sqlite.org/src/doc/trunk/src/test_onefile.c https://www.sqlite.org/src/doc/trunk/src/test_onefile.c https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html IIRC, during one of his talks D. Richard Hipp mentions this actually being done for real.

wongarsu 8y ago

Yes. The MBTiles Format [1][2] is essentially just an SQLite database storing map tiles (thousands of square images of parts of a map). In that case it's mostly done because SQLite stores small files much more efficiently than a general purpose file system in default settings. 1: https://github.com/mapbox/mbtiles-spec https://github.com/mapbox/mbtiles-spec 2: https://wiki.openstreetmap.org/wiki/MBTiles https://wiki.openstreetmap.org/wiki/MBTiles