4 ms·

TLDR; don't do it. I've used SQLite blob fields for storing files extensively. Note that there is a 2GB blob maximum: https://www.sqlite.org/limits.html https

TLDR; don't do it. I've used SQLite blob fields for storing files extensively. Note that there is a 2GB blob maximum: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html To read/write blobs, you have to serialize/deserialize your objects to bytes. This process is not only tedious, but also varies for different objects and it's not a first-class citizen in other tools, so serialization kept breaking as my dependencies upgraded. As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path and other folder-level metadata in sqlite so that I could work with it in Python. E.g. `os.listdir(my_folder)`. Also, if you want to interact with other systems/services, then you need files. sqlite can't be read over NFS (e.g. AWS EFS) and by design it has no server for requests. so i found myself caching files to disk for export/import. SQLite has some settings for handling parallel requests from multiple services, but when I experimented with them I always wound up with a locked db due to competing requests. For one reason or another, you will end up with hybrid (blob/file) ways of persisting data.

knighthack 2y ago
The idea to emulate hierarchical folder-like functionality ala filepaths is quite brilliant - I might try it out.
- thunderbong 2y ago
  Storing Hierarchical Data in Relational Databases https://medium.com/@rishabhdevmanu/from-trees-to-tables-storing-hierarchical-data-in-relational-databases-a5e5e6e1bd64 https://medium.com/@rishabhdevmanu/from-trees-to-tables-stor...
stavros 2y ago
Can you describe how you stored the paths in sqlite? I'm not entirely getting it.
- Kalanos 2y ago
  just a string field that points to the file path
formerly_proven 2y ago
> I've used SQLite blob fields for storing files extensively. Note that there is a 2GB blob maximum: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html Also note that SQLite does have an incremental blob I/O API (sqlite3_blob_xxx), so unlike most other RDBMS there is no need to read/write blobs as a contiguous piece of memory - handling large blobs is more reasonable than in those. Though the blob API is still separate from normal querying.
- Kalanos 2y ago
  That sounds nice for chunking, but what if you need contiguous memory? E.g. viewing an image or running an AI model
  - mort96 2y ago
    You can always use a chunked API to read data into a contiguous buffer. Just allocate a large enough contiguous block of memory, then copy chunk by chunk into that contiguous memory until you've read everything.
- fnordlord 2y ago
  Do you have or know of a clear example of how to do this? I have to ask because I spent half of yesterday trying to make it work. The blob_open command wouldn't work until I set a default value on the blob column and then the blob_write command wouldn't work because you can't resize a blob. It was very weird but I'm pretty confident it's because I'm missing something stupid.
  - formerly_proven 2y ago
    I’m pretty sure you can’t resize blobs using this API. The intended usage is to insert/update a row using bind_zeroblob and then update that in place (sans journaling) using the incremental API. It’s a major limitation especially for writing compressed data.
    - fnordlord 2y ago
raverbashing 2y ago
> As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path in sqlite and work with it in Python. E.g. `os.listdir(my_folder)` This makes total sense and it is also "frowned upon" by people who take a too purist view of databases (Until it comes a time to backup, or extract files, or grow a hard drive etc and then you figure out how you shot yourself in the foot)
- Kalanos 2y ago
  To make it more queryable, you can have different classes for dataset types with metadata like: file_format, num_files, sizes
jorams 2y ago
> To read/write blobs, you have to serialize/deserialize your objects to bytes. This process is not only tedious, but also varies for different objects and it's not a first-class citizen in other tools, so things kept breaking as my dependencies upgraded. I'm confused what you mean by this. Files also only contain bytes, so that serialization/deserialization has to happen anyway?
- Demiurge 2y ago
  Maybe you need to encode bytes as text for sql?
  - arianvanp 2y ago
    Writing and reading bytes in sqlite is very easy: https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html
- Kalanos 2y ago
  The tools associated with every file type you support have to support reading/writing a buffer/bytestream or whatever it is called. For example, `pd.read_parquet` accepts "file-like objects" as its first argument: https://pandas.pydata.org/docs/reference/api/pandas.read_parquet.html https://pandas.pydata.org/docs/reference/api/pandas.read_par... However, this is not the case for fringe tools
floam 2y ago
> so i found myself caching files to disk for export/import Could use a named pipe. I’m reminded of what I often do at the shell with psub in fish. psub -f creates and returns the path to a fifo/named pipe in $TMPDIR and writes stdin to that; you’ve got a path but aren’t writing to the filesystem. e.g. you want to feed some output to something that takes file paths as arguments. We want to compare cmd1 | grep foo and cmd2 | grep foo. We pipe each to psub in command substitutions: diff -u $(cmd1 | grep foo | psub -f) $(cmd2 | grep foo | psub -f) which expands to something like diff -u /tmp/fish0K5fd.psub /tmp/fish0hE1c.psub As long as the tool doesn’t seek around the file. (caveats are numerous enough that without -f, psub uses regular files.)
- tzot 2y ago
  > I’m reminded of what I often do at the shell with psub in fish. ksh and bash too have this as <(…) and >(…) under Process Substitution. An example from ksh(1) man page: paste <(cut -f1 file1) <(cut -f3 file2) | tee >(process1) >(process2)
- jayknight 2y ago
  bash (at least) has a built-in mechanism to do that diff <(cmd1 | grep foo) <(cmd2 | grep foo)
  - deleted 2y ago
    [deleted]
OskarS 2y ago
> As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path and other folder-level metadata in sqlite so that I could work with it in Python. E.g. `os.listdir(my_folder)`. This is a silly argument, there's no reason to recreate the full hierarchy. If you have something like this: CREATE TABLE files (path TEXT UNIQUE COLLATE NOCASE); Then you can do this: SELECT path FROM files WHERE path LIKE "./some/path/%"; This gets you everything in that path and everything in the subpaths (if you just want from the single folder, you can always just add a `directory` column). I benchmarked it using hyperfine on the Linux kernel source tree and a random deep folder: `/bin/ls` took ~1.5 milliseconds, the SQLite query took ~3.0 milliseconds (this is on a M1 MacBook Pro). The reason it's fast is because the table has a UNIQUE index, and LIKE uses it if you turn off case-sensitivity. No need to faff about with hierarchies. EDIT: btw, I am using SQLite for this purpose in a production application, couldn't be happier with it.
- Kalanos 2y ago
  cool. i don't want to recreate a filesystem in my app logic
  - OskarS 2y ago
    If that SELECT query is too much for you, I agree, SQLite is maybe not meant for you. Not a very solid argument against SQLite, though.
    - Kalanos 2y ago
      stop making personal attacks. i'm sure your suggestion exhibits creative step-one thinking, but i've described several reasons why it doesn't make sense to recreate a filesystem in sqlite, the combination of which should make it clear why doing so is naive
      - jazzyjackson 2y ago
        calling someone's idea naive is also kind of a personal attack fwiw
out_of_protocol 2y ago
> As my app matured, I found that I often wanted hierarchical folder-like functionality (1) Slim table "items" - id / parent_id / kind (0/1 file folder) integer - name text - Maybe metadata. (2) Separate table "content" - id integer - data blob There you have file-system-like structure and fast access times (don't mix content in the first table) Or, if you wish for deduplication or compression, add item_content (3)
qbane 2y ago
> As my app matured, I found that I often wanted hierarchical folder-like functionality. In the process of prototyping some "remote" collaborating file systems, I always wonder whether it is a good idea maintaining a flat map from path concatenated with "/" like an S3 to the file content, in term of efficiency or elegancy.

knighthack 2y ago

The idea to emulate hierarchical folder-like functionality ala filepaths is quite brilliant - I might try it out.

thunderbong 2y ago
Storing Hierarchical Data in Relational Databases https://medium.com/@rishabhdevmanu/from-trees-to-tables-storing-hierarchical-data-in-relational-databases-a5e5e6e1bd64 https://medium.com/@rishabhdevmanu/from-trees-to-tables-stor...

stavros 2y ago

Can you describe how you stored the paths in sqlite? I'm not entirely getting it.

Kalanos 2y ago
just a string field that points to the file path

formerly_proven 2y ago

> I've used SQLite blob fields for storing files extensively. Note that there is a 2GB blob maximum: https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html Also note that SQLite does have an incremental blob I/O API (sqlite3_blob_xxx), so unlike most other RDBMS there is no need to read/write blobs as a contiguous piece of memory - handling large blobs is more reasonable than in those. Though the blob API is still separate from normal querying.

Kalanos 2y ago
That sounds nice for chunking, but what if you need contiguous memory? E.g. viewing an image or running an AI model
- mort96 2y ago
  You can always use a chunked API to read data into a contiguous buffer. Just allocate a large enough contiguous block of memory, then copy chunk by chunk into that contiguous memory until you've read everything.
fnordlord 2y ago
Do you have or know of a clear example of how to do this? I have to ask because I spent half of yesterday trying to make it work. The blob_open command wouldn't work until I set a default value on the blob column and then the blob_write command wouldn't work because you can't resize a blob. It was very weird but I'm pretty confident it's because I'm missing something stupid.
- formerly_proven 2y ago
  I’m pretty sure you can’t resize blobs using this API. The intended usage is to insert/update a row using bind_zeroblob and then update that in place (sans journaling) using the incremental API. It’s a major limitation especially for writing compressed data.
  - fnordlord 2y ago

raverbashing 2y ago

> As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path in sqlite and work with it in Python. E.g. `os.listdir(my_folder)` This makes total sense and it is also "frowned upon" by people who take a too purist view of databases (Until it comes a time to backup, or extract files, or grow a hard drive etc and then you figure out how you shot yourself in the foot)

Kalanos 2y ago
To make it more queryable, you can have different classes for dataset types with metadata like: file_format, num_files, sizes

jorams 2y ago

> To read/write blobs, you have to serialize/deserialize your objects to bytes. This process is not only tedious, but also varies for different objects and it's not a first-class citizen in other tools, so things kept breaking as my dependencies upgraded. I'm confused what you mean by this. Files also only contain bytes, so that serialization/deserialization has to happen anyway?

Demiurge 2y ago
Maybe you need to encode bytes as text for sql?
- arianvanp 2y ago
  Writing and reading bytes in sqlite is very easy: https://www.sqlite.org/c3ref/blob_open.html https://www.sqlite.org/c3ref/blob_open.html
Kalanos 2y ago
The tools associated with every file type you support have to support reading/writing a buffer/bytestream or whatever it is called. For example, `pd.read_parquet` accepts "file-like objects" as its first argument: https://pandas.pydata.org/docs/reference/api/pandas.read_parquet.html https://pandas.pydata.org/docs/reference/api/pandas.read_par... However, this is not the case for fringe tools

floam 2y ago

> so i found myself caching files to disk for export/import Could use a named pipe. I’m reminded of what I often do at the shell with psub in fish. psub -f creates and returns the path to a fifo/named pipe in $TMPDIR and writes stdin to that; you’ve got a path but aren’t writing to the filesystem. e.g. you want to feed some output to something that takes file paths as arguments. We want to compare cmd1 | grep foo and cmd2 | grep foo. We pipe each to psub in command substitutions: diff -u $(cmd1 | grep foo | psub -f) $(cmd2 | grep foo | psub -f) which expands to something like diff -u /tmp/fish0K5fd.psub /tmp/fish0hE1c.psub As long as the tool doesn’t seek around the file. (caveats are numerous enough that without -f, psub uses regular files.)

tzot 2y ago
> I’m reminded of what I often do at the shell with psub in fish. ksh and bash too have this as <(…) and >(…) under Process Substitution. An example from ksh(1) man page: paste <(cut -f1 file1) <(cut -f3 file2) | tee >(process1) >(process2)
jayknight 2y ago
bash (at least) has a built-in mechanism to do that diff <(cmd1 | grep foo) <(cmd2 | grep foo)
- deleted 2y ago
  [deleted]

OskarS 2y ago

> As my app matured, I found that I often wanted hierarchical folder-like functionality. Rather than recreating this mess in db relationships, it was easier to store the path and other folder-level metadata in sqlite so that I could work with it in Python. E.g. `os.listdir(my_folder)`. This is a silly argument, there's no reason to recreate the full hierarchy. If you have something like this: CREATE TABLE files (path TEXT UNIQUE COLLATE NOCASE); Then you can do this: SELECT path FROM files WHERE path LIKE "./some/path/%"; This gets you everything in that path and everything in the subpaths (if you just want from the single folder, you can always just add a `directory` column). I benchmarked it using hyperfine on the Linux kernel source tree and a random deep folder: `/bin/ls` took ~1.5 milliseconds, the SQLite query took ~3.0 milliseconds (this is on a M1 MacBook Pro). The reason it's fast is because the table has a UNIQUE index, and LIKE uses it if you turn off case-sensitivity. No need to faff about with hierarchies. EDIT: btw, I am using SQLite for this purpose in a production application, couldn't be happier with it.

Kalanos 2y ago
cool. i don't want to recreate a filesystem in my app logic
- OskarS 2y ago
  If that SELECT query is too much for you, I agree, SQLite is maybe not meant for you. Not a very solid argument against SQLite, though.
  - Kalanos 2y ago
    stop making personal attacks. i'm sure your suggestion exhibits creative step-one thinking, but i've described several reasons why it doesn't make sense to recreate a filesystem in sqlite, the combination of which should make it clear why doing so is naive
    - jazzyjackson 2y ago
      calling someone's idea naive is also kind of a personal attack fwiw

out_of_protocol 2y ago

> As my app matured, I found that I often wanted hierarchical folder-like functionality (1) Slim table "items" - id / parent_id / kind (0/1 file folder) integer - name text - Maybe metadata. (2) Separate table "content" - id integer - data blob There you have file-system-like structure and fast access times (don't mix content in the first table) Or, if you wish for deduplication or compression, add item_content (3)

qbane 2y ago

> As my app matured, I found that I often wanted hierarchical folder-like functionality. In the process of prototyping some "remote" collaborating file systems, I always wonder whether it is a good idea maintaining a flat map from path concatenated with "/" like an S3 to the file content, in term of efficiency or elegancy.