[jameshart]

Well you still need to solve for what happens when a new version of your app (maybe with a new embedded version of SQLite) loads up an old data file saved by an old version of your app.

The old version might not contain all the tables you need, and the ones it has may not have the columns you expect. So you need to run some data migrations on the database. Now you no longer have a serialization problem but instead you have a schema versioning problem.

[bob1029]

https://www.sqlite.org/pragma.html#pragma_user_version

I use SQLite for game state management. It's just like any other database scenario. I write migrators that check the user_version of the database. It's just a for loop from user_version to current version. The migrators themselves can be arbitrary methods that sometimes modify game state to bring it up to date. The most common scenario is adding a new property to something, and then figuring out the appropriate defaults to assign for existing rows (typically null/0). But you can go all the way into the ETL rabbit hole.

I think the relational model via SQLite is the best way to manage state for the more complicated games like in the 4X and deck builder genres.

[freedomben]

I do the same thing, but I have increasingly found myself wanting to serialize json into columns because having a rigid schema can sometimes add a lot of friction. Experience has taught me though, that it's worth the extra effort to define a schema, because nine times out of 10, the flexible json will ossify into unexpected format that the code relies on anyway, but now the database doesn't help enforce integrity. I would definitely recommend defining a schema and doing it right the first time. It will save you time in the long run, and make for much fewer bugs.

[bob1029]

I am not against the JSON-in-columns hybrid path, but I have typically found it grows into a monster over time. In my experience, it caused performance problems more than anything else.

[flohofwoe]

> instead you have a schema versioning problem

That same versioning problem also exists with other approaches. Having a versioned schema of the savegame format around for version migrations is generally a good idea.

[cjonas]

Could solve this with a migration framework (I'm sure there is something for sqlLite). I've also done something similar with object/document storage. Store the version of the schema in the record and write a map function for each version from the previous.