I'm thinking about building a hybrid SQL database/cache. Would anyone use something like this?
So the impetus behind this is that at my job I work a lot with MySQL (AWS RDS) but I don't have access to administrative things like backups, sharding, or replication. So keeping a dev mysql server in sync with the production database would be difficult for me and would consume too much storage space if it were local. Having to run queries against the production database is nerve wracking.
Having said that, does anyone know of any current solutions for a problem/situation like this? I was thinking if I did make something, that the hypothetical service would poll the tables you tell it to and sync it to its internal server. On top of that I was thinking about implementing auto backups in a way that individual committed transactions could be reverted within a reasonable time window--in case you accidentally run a query with a mistake. So basically the service would be focused on providing a safe and convenient dev database environment. I was also thinking about having an add-on in-memory caching feature that would allow you to query the cache in SQL--but that's still tentative. Another possible feature would be SQL queries you can turn into webhooks for when the records in the query change.
Does all that sound like a fever dream or does anyone think doing this would be worth it?
Is your concern around having long-running queries being executed on your production database? I know that FlyData (https://www.flydata.com/) addresses that. You can use it to sync your DB to Redshift (which is actually more appropriate for long-running, analytics queries).
Polling tables and dealing with diffs could also get pretty hairy, for MySQL I think looking into working with the binary log would be a better approach (https://dev.mysql.com/doc/refman/8.0/en/binary-log.html)
Auto backups are interesting. However, I don't see how you'd be able to do this effectively for large databases, as every potentially destructive change would result in a copy and backup?
MySQL supports MEMORY tables, which is effectively in-memory caching, so I don't see much value there.
Webhooks for when query results change is interesting.
I don't mean to sound too negative. I think you've put forward a few, fairly broad ideas. I think it might help to focus in on what's a real use-case / pain-point for you? and is it something you see other developers facing?
Hmmm... there are many things a developer on my team could do to make me unhappy. The list includes:
Consider using some form of test double, or having scripts to pre-populate test databases with test data - in-memory DBs are good for this, as you can't accidentally re-use them, and so you're aways starting from a known point.