For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of files... I'm totally guessing but there might be like 85% reads, 15% writes when restructuring or adding files), what would be a good database / schema to use? SQL with ..? NoSQL? GraphDB: OrientDB / Neo4j? I have a bit of experience with simple SQL using Postgres or MySQL (insert, select a from b where c, join / right outer join, etc) and otherwise generally have experience at application-level programming and with JSON.
If it matters, I plan on storing file paths to photos / videos and also storing related photo data (EXIF, etc) in either this database or a combo of databases (if that's a thing, like querying all photo paths from one db and then querying all data related to each of the files represented by those file paths in another db?)... I also was thinking I'd just use the underlying filesystem to read/write to/from photo/video files.
This is more of a personal project and I don't plan on scaling with distributed systems, etc. The actual photo / video storage space would probably span across several TiB of disk over time.
(Sorry if this seems vague. I'm just starting out and realized that my application suddenly seems a bit more complex since I feel the need to properly record my photo/video files and their place in the filesystem hierarchy in order to search over them and provide an API to my application to read/write from/to them (and to be able to re-order their positions in the filesystem hierarchy/tree).)
I didn't originally mention, but I plan on doing this self-hosted. I'm currently thinking that for my use-case (reading and possibly modifying hierarchy, and also inserting), I could simply use the OS filesystem and directly interface with it, starting out with syscalls (ie: ls, cd, mkdir, mv, chown, chmod, rm, login / exit, etc). This way I already get a decent tree structure to work with, backed by the OS, and I don't have to worry about syncing back with the original filesystem hierarchy on any modifications (since I'll be directly accessing and modifying some root directory on the filesystem)... I understand I'll also need to take precautions to harden security around my interface to the syscalls (basically I could start with parsing out any dangerous symbols, like ".." or "*" from an API query, but I'll look up better practices online, like implementing user-level OS restrictions)...
But using this method alone, I don't get to associate other data with each file, like commenting or 'like'ing a photo/video, or adding thumbnails, etc, so I'll probably need at least some other kind of DB for that (possibly Mongodb?)...
I'm now considering creating a replica of the folder hierarchy for each sub-folder in the original root folder. Then I'm thinking I'll keep a simple JSON file with a dictionary to store any extra data related to each photo / video file. Since I'm basically using a db for each folder, I decided to go with NoSQL / JSON as I might modify the schema of associated data for each image / video along the way, as I'm figuring things out. And I'm already using Node.js on the server-side, so JSON seems pretty simple to at least start out with. (I can also implement the concepts from EDIT above, and just create an API that synchronizes and maybe locks these two general data structures.)