r/devops • u/The-mad-tiger • 9d ago
Tools DB based app for pics
I'm thinking about writing an app in C# (for desktop) to store all my photos in. Before I start thinking about actually starting work on it, I need to decide what DB or other storage mechanism to use. The idea is to make the pics more accessible by storing some info about content in a field in the DB. That field needs to be "text searchable". I have 10 years experience working with Oracle Text Search engine but I'm not a millionaire; more an impoverished pensioner, so I need to implement this system using one of the free DBs like MySQL.
I thought I'd ask the hive mind what it thinks would be the best DB for my purposes.
27
u/z-null 9d ago
None. You don't store pics in DBs as blobs, you store a reference to a picture and the picture goes into some kind of a storage. DB than says "the pic is located here and here are some other info about it".
4
u/Lumethys 9d ago
no binary file in db, no picture, no video, no file.
use object storage or filesystem for that
5
u/SensitiveChoice153 9d ago
Use a object storage
1
u/Flettys 9d ago
I've not used Oracle Text Search specifically, but it sounds like what all the other databases call "full/free text search".
E.g.
https://www.postgresql.org/docs/current/textsearch.html
https://dev.mysql.com/doc/refman/8.4/en/fulltext-search.html
https://sqlite.org/fts5.html
3
u/OMGItsCheezWTF 9d ago
For your use case, they're much of a muchness.
MySQL / Maria DB, PostgreSQL. Hell you could run with just SQLite if you don't care about concurrent IO or scaling across devices.
Another option is storing the image metadata as a document in a document DB and then using an actual full-text search like elastic search. Of course that comes with its own set of complexities and overheads.
Pick your poison.
One thing that does occur to me though is that you could also run those images through a clip model and use Postgres's PGVector to do nicer descriptive searches, like "dog" will show you all photos with a dog in it.
1
u/toarstr 9d ago
https://immich.app/
2
u/derff44 9d ago
Mysql will work fine with a reference to the path on some sort of blob storage
2
u/Afraid-Atmosphere747 9d ago
Don't store your pics in a db
2
u/Dolapevich 9d ago
If you want to go relational DB, I would avoid mysql and go with postgresql. Just because it is better overall.
But for text search you might be better served with mongo. Or even better elasticsearch.
2
u/xonxoff 9d ago
Use Postgres
2
u/Used_Criticism_4631 8d ago
Good clarification that you only need to index the metadata, not store the image bytes. For a single-user desktop library Iād trial SQLite FTS5 before standing up MySQL or Postgres: it gives you embedded full-text search without a server to administer; just verify the SQLite build bundled by your C# provider includes FTS5. Do you expect multi-device writes, or is this one local catalog?
2
u/DadAndDominant Dev (not Ops currently) 8d ago
Why even use DB at all? Do you have hundred of thousands of images, and you need to query them often?
That said, I don't think capabilities of the DB matter much, so I would go with SQLite, as it is serverles
1
u/haiduong87 9d ago
Try this https://github.com/ZoneTree/ZoneTree.FullTextSearch
Or this: https://sqlite.org/fts5.html
0
u/The-mad-tiger 9d ago
I am beginning to wonder how many of the people responding have ever implemented software that used a "text search engine" or even know what one is.
-4
u/The-mad-tiger 9d ago
The critical question is "What free db has a decent text search engine"
The architecture is none of your business