Is it reasonable to store images as BLOB in DB?

Question

Database design

I am developing a desktop application. The program will work with structured textual information and illustrations. Text information will perfectly fit into the local database (it is planned to use SQLite or H2). But the question of storing images is still open. It is expected from several tens to several thousand images.

One of the requirements for the application is the ability to recover data using third-party tools. Thus, an open database allows you to connect to it by a third-party client and pull out any data from there (the structure of the tables will be documented). It also requires the ability to easily back up data and restore it from a backup.

There is a temptation to add images to the database, just like text. To have everything in one file, or several files in a directory (H2 can keep the database in a ZIP archive, for example), if necessary, the database can be easily dumped and archived. And in the same way, recover from this archive with a dump.

On the other hand, everywhere I meet the recommendation not to use the database to store files. However, as you know, "only the Sith elevate everything to the absolute."

I ask for advice - what are the underwater rakes in this case in the case of storing images in the database? Is this decision justified in this case in your opinion?

Answer 1

A few thousand - you can also in the database
Here is what they write about storing small binaries in the SQLITE database

SQLite reads and writes small blobs (such as image thumbnails) up to 35% faster¹ than the same blobs that can be read from or written to separate files on disk using the fread() or fwrite() functions.
In addition, a single SQLite database containing 10K BLOBs uses approximately 20% less disk space than storing BLOBs in separate files.
The performance difference occurs (we believe) because when working with a SQLite database, the open() and close() system calls are called only once, while open() and close() are called once per blob when using BLOBs stored in separate files. It looks like the overhead of the open() and close() calls is more than the overhead of using the database. The size reduction is because individual files are padded to the next multiple of the file system block size, while blobs are packed more densely into the SQLite database.

https://www.sqlite.org/fasterthanfs.html

Answer 2

Among other things: if your application can display a picture without a temporary file for it, BLOB is better.

Answer 3

Well, that is, the logic of this question is that for some reason it is difficult to back up a file from the database, but the pictures that are in this file are vice versa
. Also, the "third-party tool" in the form of the copy command is generally incompatible with anything, and creating/expanding a "dump" by means specific to the database is available at every corner.
Not to mention access to these pictures by third-party means.
Still, a joke about Vovochka, "Where is the logic, where is the mind?" should be the motto of this site.