Store file notes in the filesystem rather than the database

[alexdachin]

Describe feature

Currently Trilium stores the contents of a File note type embedded inside the SQLite database.

While this is extremely nice for simplicity and portability, it can become problematic once you start uploading many reference files (for example some large quality images, large PDFs, videos etc). While in theory SQLite supports huge databases, storing many files in a single database makes the database file harder to backup, increases database lock times and increases the database corruption risk slightly.

I propose storing the files in the filesystem and storing a reference to them and extra metadata in the database. The files can be stored in the trilium-data directory to begin with for simplicity, but it also opens the gate for storing them in other places in the future (like S3 compatible storage systems). This keeps the SQLite database small and performant, enables faster incremental backups (like rsync), reduces the risk of database corruption and still allows Trilium to manage things like note hierarchy with cloning.

Would love to hear your thoughts on weather this approach could be added as a feature or if you feel it might complicate the setup too much.

Additional Information

No response

[eliandoran]

Trilium was implemented with a database in order to store the meta information that doesn’t always map well to a file system, such as being able to place a note in multiple locations (this can be partially supported at FS level via symlinks or hardlinks but FS support can be janky or non-portable).

Another important aspect is that notes in Trilium can also have children, whereas folders in a file system don’t.

Rewriting the entire approach to storing the notes would be too problematic at this stage of the application, however we can consider syncing a subset (similar to how shared notes work), which is described here: #6351

The problem is that it’s a very complicated problem and the workload to do it is too high at the moment.

[alexdachin]

Thanks for looking into this and for all the effort you put into this project! I think what I mentioned was misunderstood, I was not referring to storing every note as a file and I totally understand why the architectural decision is the way it is for Trilium, it provides a lot of strengths.

I was rather referring only to the content of the actual uploaded File note types (for example pdfs, images and videos). They are now stored as a blob in the database, but they can simply be stored flatly or partitioned in the file system with filename being either note id or a UUID (and a reference to this file in the database). Edit: It is similar to this issue which was opened slightly after I commented this, but with a smaller scope (I promise I don't know the author, it was only a coincidence we posted close to each other 😄)

I believe this shouldn't require a big refactoring and shouldn't impact the ability of notes to be in multiple locations and have children. If you would accept a PR for this, I can give it a go, but let me know if you still believe this will add too much complexity.

[eliandoran]

@alexdachin ,

You're right, I guess it was too late in the evening when I looked at the bug.

I'm pretty sure this was requested before, in some form or the other, but I can't find it yet.

Anyway, it's certainly more feasible than having the entire note structure but it comes with some troubles as well. For example, syncing might be affected.

If you wanna give it a try:

• I think the best approach is to work at BLOB level at SQLite level
• Make sure not to do it for all the blobs, but based on a threshold as defined here: https://www.sqlite.org/intern-v-extern-blob.html
• We need handling in case the image is no longer accessible (e.g. a "broken" image placeholder as we have now should suffice).

PRs are welcome.

[alexdachin]

Thanks for being open to this and for the tips! It totally makes sense to separate the blobs by threshold.

I think the best approach is to work at BLOB level at SQLite level

Is there a built-in SQLite feature you are thinking of for BLOBs or are you referring to working at the blobs table level in Becca?

[eliandoran]

@alexdachin , yup, BLOBs are an SQLite feature and we are using it to store the content of notes & attachments.

[dosubot]

To clarify: SQLite supports storing BLOBs (binary large objects) directly inside the database file, but it does not natively support storing BLOBs externally on the filesystem. The "internal vs external BLOB" terminology in the SQLite docs refers to how SQLite manages BLOB data within the database file itself for performance reasons, not to storing files outside the database.

In Trilium, all file note contents (like images, PDFs, videos) are stored as BLOBs in the SQLite database via the blobs table, and there is currently no built-in support for storing these files externally on disk. If you want to implement external file storage, it would need to be handled at the application level: Trilium would store a reference to the file in the database and manage the file itself on disk. This would require architectural changes, especially in the blob management services, since all current logic assumes BLOBs are stored in the database file itself. For more on this, see the relevant SQLite documentation and related Trilium discussions.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[CobriMediaJulien]

I´m using a workaround and I´m linking to to the files in my file system. So the files are openened in a native app. It´s not what you want but just to mention it..