I'm going back to the file system as a source of truth with static site generators.
The main reason is that I can use a variety of tools to view and manipulate files.
But other software can also mess with your "database", including the operating system itself. For example, Syncthing was breaking my static site by changing the unicode normalisation of file names. MacOS being case insensitive brought issues. Illegal filename characters brought issues.
This approach works well if you want to make data accessible to humans, but it's wildly inefficient if you expect machines to operate on that data, and comes with a few caveats.
This is exactly why I chose that approach for my websites. My website content and its backups are source-controlled, human-readable files. I can edit them with the tools I love, not just what's supplied with the content management system.
I'm now rebuilding my timeline thing [0] with the filesystem as the database. However, I still use an SQLite database as intermediate storage, because extracting metadata from thousands of photos is not cheap.
In other words, you'll need to build your own cache, and sync it with your filesystem. Making data human-readable makes it slower for machines to read.
For simple apps and app components, it's very convenient and manageable.
It becomes a problem when you: (1) scale up (2) have to deal with multiple relationships between objects. The "Database design" by Adrienne Watt posted in another comment covers the scale concerns well, but another scale problem she doesn't mention is hitting inode limit, at least if you're on a single machine. You can of course use a distributed filesystem as database, but at that point, you might want to use a database proper.
Btrfs inode limits is in a whole different league (whereas ext4's inodes are allocated at filesystem creation time and cannot be resized after creation, typically at 1-2 million, with a hard limit of 4 billion, btrfs's inodes are dynamically allocated as needed, and the hard limit is 2^64, around 18.4 quintillion.
Use a better file system with the ability to "scale up".
Attempting to predict and limit the max. number of allowed files at the time of creation is an unbelievably audacious yet hamstrung and self limiting design --- one that is totally unnecessary and as you point out, doomed to fail at some point --- often before storage and address space are depleted.
I find this particularly egregious in an era of constantly increasing storage demands, changing volume capacities and drive pooling in an OS often promoted for it's server prowess and flexibility.
All he did was iterate why large enterprises migrated toward the RDBMS.
A simple web service designed to address a single problem is not a "large enterprise". And applying an RDBMS to such problems is like using a hand grenade to kill a fly. It will work --- but there are better, more efficient and appropriate approaches.
In other words, not every problem needs to be generalized to encompass the global economy.
You're not getting that just from the file system though. Depending on the performance requirements and use case it may or may not be complicated. You are going down the rabbit hole of creating your own foundation for a database at that point.
I've been using a file based database at work for years. Data is stored in 1024 byte "blocks" with each file containing 1000 blocks. Each block refers to a single record and the data is delimited by the positions within those 1024 bytes (e.g. date/time created might be at position 38 in the block and run for 14 bytes so we reference it by its "token" of 380014).
Each file will be the first 4 characters of the 8 character long primary IDs of each block. E.g. file 1020.dat would contain records 10200000 -> 10209999. We then have all kinds of other files used for indexing and data locations that are built by tools over those files as well as overflow data files when those 1024 bytes just weren't enough.
I should point out this is a legacy system and for very good reasons we moved to an actual database a long time ago.
In your example you're kind of mixing a DB within a DB (the index.json file is a separate database contained within the main database).
A better structure would be something like
users/<user>/projects/<name>/data/... etc
Now your file system is just a NoSQL database. All that data you would dump into an index.json can be stored in the file tree.
That would actually work pretty well as long as you limit operations that are kind of meaningless and disable features like symlinks.
Mounting hierarchical data as a pseudo file system is actually pretty common. EG procfs, devfs, sysfs are all pseudo file system that present structured data to applications on Linux through the guise of a file tree.
> directory tree is not as easy to view and edit as JSON.
tree path/to
cat path/to/key
echo "1" > path/to/key
But my point is that you're not using the file system as a database. You're using it as an index, and haven't considered about multiple readers/writers to those individual JSON files that are doing the real work as databases. It's kind of like writing JSON into a SQL table. You can do it, but probably not to store important data within that JSON that always needs to be queried and ser/deserialized for any kind of read or write. If you need that, you probably want NoSQL.
For many years, I built most projects on top of a relational DB.
Then NoSQL happened.
After NoSQL became less popular, I started defaulting to JSON files, if I thought I could make it work.
One thing that helps a file-based DB is to make sure you put something useful in the filename (and/or path). Such as a search tag and/or category or owner.
I suspect that the majority of programs are like the ones I write in that they don't have a ton of demands on them, relatively speaking. Not in terms of load or scope or anything else.
In most of these "small" applications, I think you can literally use almost _any_ database or file structure you want and end up with basically equivalent results.
For one, with a 1 file per row (entity) design, any larger query would require reading from potentially millions of files, which I imagine would be nightmarishly inefficient.
Reminds me of WinFS, the Windows filesystem built on top of a relational database [0] that was supposed to ship with Longhorn (aka Windows Vista). Neat idea that never materialized.
The main reason is that I can use a variety of tools to view and manipulate files.
But other software can also mess with your "database", including the operating system itself. For example, Syncthing was breaking my static site by changing the unicode normalisation of file names. MacOS being case insensitive brought issues. Illegal filename characters brought issues.
This approach works well if you want to make data accessible to humans, but it's wildly inefficient if you expect machines to operate on that data, and comes with a few caveats.
Pick the right tool for the job.