I hope I have chosen the right forum topic for this.
I have a question for your opinion.
I want to build an automatic app update script for the database. This is a SQLLite database directly in the app locally.
I want to update the database at will to deliver new content or to fix errors in the database.
We already have a server that we use to send and receive data. I would also use this server for this purpose.
The app would query the server to see if there are any updates that can be installed. Of course with all the necessary checks. And if so, the server would directly issue an SQL command that can then be executed in the app. If the SQL query can be executed, then the update would be marked as successful.
Do you see any security concerns or other issues here?
EDIT:
I just want to add something to my question, maybe it will change your mind, maybe not.
The project we are working on is a remake of our other games that were based on a different game engine. We had developed a completely separate system for the maintenance of the content, which imports the content completely into a SQLLite table. The game lives exclusively from the content, it is a pure UI game and the SQLLite table had to be used as before - in the same and identical form. was virtually the default for budget reasons.
I wouldn’t touch that approach with a ten-foot pole.
Just use Addressables or Asset Bundles for content updates, or even just use Firebase Remote Config if you’re just twiddling values after shipping the game (eg, game tuning).
Otherwise, integrating and maintaining your own on-app database won’t be easy, especially on iOS/Android.
Databases just aren’t a Unity Way™ of doing things so you’d be responsible for pretty much all of it.
There is a real concern here: you have no control over what happens to the app and the user’s data if the execution of that SQL command fails, or worse, partially fails or does not do the thing you want it to do but it returns that it ran successfully.
The problem is that from your (the server’s) point of view the app has been successfully updated. But this may not be the case, leaving the app broken.
Imagine you already made one such update and it ran fine, now you are sending out the second update but now you have to account for users with the original database, and the ones with the updated database. Do you send two different SQL commands now? Do you send each update consecutively until the app is completely up to date? Would this approach still be suitable after 10 or more updates?
Honestly, I would simply send the entire database file with the update, unless it is prohibitively large (in which case it should be split up).
If the app also writes to the database, for example to persist user data, I would split that up into a read-only and a writable database file so you can simply replace the former without breaking the latter.
Just forget about sending/receiving SQL Commands to/from a server… it’s just a minefield, like others already said.
Just send your data as Json from the server (API), that’s all you need. It’s safer, a lot easier to do, and easily debuggable as a bonus (plain text data files). That’s what lot of big games with frequent content updates do (Fortnite, GTA Online…).
The game on start just checks if there’s new data/content (it sends its latest update version/date/id… whatever) and the server is responsible of getting the new data and generating the Json. This is in the case of frequent and HUGE data updates. If it’s just small updates (< 256 kb Json file), just don’t bother about versioning and diff’ing and just send the whole latest Json data. You can output caching on server + compression (gzip/brotli…) to make the Json data even smaller (Json is highly compressible since it’s text).