After a hiatus, I’m back trying to make steady progress on adding features and fixes to Basemix, my app for rat breeders. In my app rats and litters get profile pages, and for a while people have been asking me about adding photos to rat profile pages. I’ve liked this idea in theory but always worried (maybe a bit too much) about how to implement it elegantly. I’ve finally released this feature and I think I’ve done it in a way that isn’t common, maybe to my detriment but time will tell, and I wanted to write about what I did and why here.

The “Problem”

Basemix is fully offline and stores everything on your local device in a SQLite database. Our own rattery’s records are in a ~80kb database to date which is neat and small. Adding photos in any form completely tips the scales on the data estate into megabytes easily, especially because breeders… tend to have a lot of rats over time (and assuming the average use case is someone taking a photo with their smartphone and putting it straight in the app unedited, that’s going to be quite high resolution data - ~5mb per photo on my phone today).

As it’s fully offline, this can quickly start to bloat the app data, and we really don’t need highest resolution for a record keeping app - it isn’t a personal photo repository, it’s just there to help people visually tie their rats to the records or make the experience a bit nicer. It most importantly makes backups a more painful - people can export their one database and import it as a single file, and it has been so light and easy to move. If we’re suddenly producing 100s of mbs (or even 1gb+) as a backup file that takes a lot longer to pull together, restore from and move around.

One option always worth considering is to choose to just ignore this and leave people to their own devices with a warning to self-manage things sensibly (the ethos of the app is to let you handle your own data after all), but I don’t want to let people fend for themselves - I want to make the default path low friction and unlikely to cause issues.

It also wasn’t clear how to best actually handle images - we have to pull a copy into the app somehow, make it easy to backup, and load it into the UI. Putting the images directly into the database will bloat the single file which, though I’ve not tested it hard, feels like it’ll start to have a negative impact on overall query performance as the bulk of the file on disk will be image data that I won’t be doing anything with and will need to query past - no analytical queries over image data just direct lookups. And storing images in a database is a bit odd - nobody does that - are there subtle issues?

So the two top-level problems are:

  1. Functionality: Where to store the images, how to back them up, and how to render them?
  2. Performance: How to stop image size bloat becoming an issue?

Initial research

Most initial research points to “Yes, you shouldn’t store photos in your database, you should store them in a directory and reference them with <img src="path/to/file"> which is all well and good but the difficult thing about googling this is I am not building an applications server which is what most recommendations are targeting, I’m just building an offline app that ridiculously uses web tech for the UI (as many choices these days do) and the database is really just a file with a clever library around it. I can still take this approach though - store a bunch of images in a path relative to my database, store the image path against each rat, then just use the img path to reference it.

The things I didn’t quite like about that solution were:

  1. We’ve gone from backing up a single database to a database and a subfolder which isn’t quite as nice and simple.
  2. If I want to store metadata about images (which I think I might do) I have to invent something bespoke for that. Nothing major, maybe just a photo_name.metadata.json file in the same directory
  3. Any data manipulation I may need or chose to do today or in future will have to be very manual and filesystem based rather than sqlite and memory based. Features like exporting individual rats, migrating data, etc.

So I started to wonder if I could make use of SQLite still, but with a bit more sensibility around it. SQLite does natively handle BLOBs as a supported datatype, and the maximum length of a BLOB is 2GB - well beyond what I need (though the maximum practical length is another matter). Further, there’s some benchmarks on performance that are nicely documented around this very issue - storing things internally or externally - so it’s far from an unknown. In either case it didn’t look unworkable but the main issue was still database bloat, though another issue would be if I’m storing in the database and reading into memory, how do I reference that if it’s not a local filepath? Write to disk temporarily? Turns out data urls exist and can let you embed binary data straight in a base64-encoded string to render it, so that’s an option.

A clear way to drastically cut down bloat would be to not store anything 1:1 with the source - I am only showing profile photos which don’t need to be incredibly high resolution. This means I have some freedom to manipulate images to the benefit of the application. Filesize, bloat etc all kinda comes down to one thing: can I reduce image sizes with lowered quality and compression? In my testing, the biggest gains came from scaling down sizewise (kind of obviously, it’s sheer data loss), a 1080px max width/height and a proportional scale-down to that size chopped megabytes into kilobytes - I didn’t fancy layering lossy image compression on top of that too and I didn’t want to be too opinionated about file format (if someone decided to add hand drawn illustrations for their rats, wild idea, I didn’t want to force these to jpeg and make them look awful) but lossless compression was possible and shaved a little more off the total filesize. Decompression could be computationally expensive if the photos are too large, but that’d be against the very point of what I’m considering here.

Putting it all together

Testing and researching lead me to a few decisions:

  1. I would store things in sqlite but in a separate database. This slightly increases backup complexity but to two files (which I’ll zip together) rather than a file system.
  2. All images would be downscaled and then compressed with the deflate algorithm, some metadata would be stored in the database alongside the image to note that this has occurred.
  3. Users will be able to change the downscale and compression settings, if they want to, so if I’m wrong and quality does matter, or auto-compression is causing problems, users can override it.
  4. Data URIs aren’t great but so long as they’re workable I don’t have to think too hard about them. And they are workable.

Storing things across two databases (or external to the database) obviously introduces some challenges - first being how things are referenced and second how we resolve issues without transactional certainty - what if the application fails during a photo upload, will the photo persist to disk with no rat pointing to it and become orphaned? A simple solution: The media database is just intended as a key-value lookup store and every image should be keyed on some deterministically generated id - that way we can save the pointer from the rat to the image on the rat immediately, then if the photo fails to save we at worst render an “Image not found” graphic and let the user add it again. It is actually possible to run transactional queries across multiple files in sqlite but I figured I would keep it simple for now as I don’t strictly need that certainty today - the main thing is a deterministic id lookup doesn’t run the risk of orphaning photographs and having “dead data” taking up space.

Final thoughts

All the solutions I went through were workable, arguably I’ve gone for a bit of an esoteric choice but I’m not unhappy with it today and it’s not like I cannot migrate to another solution in time. I didn’t find much writing on solutions exactly like this despite a lot of the piecemeal surrounding documentation leading me to conclude its feasibility which is why I wanted to publish my own post to tie it all together. Time will tell how well this works and I always build my app with forward compatibility in mind - being able to migrate all of this to a completely different choice down the line easily enough should I find major flaws.

I’m happy that users are already telling me it works good and they enjoy it - no issues with performance or image quality so far which were my main concerns that are hard to test across the userbase. One user fed back that a cropping tool would be useful to choose what part of the photo to center in the profile (as I effectively render it as a square) so I’ve since added that, which will also only ever result in trimmed filesizes once again working in favour of my desires here.

There’s a small part of me constantly flirting with rebuilding this app in another technology, something with more native controls (heck, MAUI again would work if I dumped the blazor webview, or something like flutter) or with an ecosystem that doesn’t constantly seem to upset me. In either case I’m pleased that my choices here all enable migration to a whole other framework easily should that ever happen (it won’t).