6 ms·

Naive question: is there a reason why SQLite wouldn’t work for something like this?

nonethewiser 2y ago
I wonder the same thing. Perhaps less portable? IE can't package that up in a binary (I have absolutely no idea just spitballing)? And very cursory search suggests maybe there is nothing to that guess: https://www.reddit.com/r/golang/comments/tqffv2/packaging_an_sql_database_in_a_go_binary/ https://www.reddit.com/r/golang/comments/tqffv2/packaging_an... It's an interesting question because I've run into some datascientists that were so used to working in memory with dataframes and similar that they moved mountains to do things like de-duplicate csv's in memory (that they couldn't all fit in at once) where-as they could have done so trivially with sqlite.
jameshart 2y ago
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.
- cjonas 2y ago
  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.
- flohofwoe 2y ago
  > 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.
- bob1029 2y ago
  https://www.sqlite.org/pragma.html#pragma_user_version 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 2y ago
    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 2y ago
      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.
nearbuy 2y ago
You could use it, but it's not really solving the same problem. For a game, you generally don't need the relational database features. You aren't doing queries. You just want to load an entire level into memory, or save an entire level. For the serialization and persistence aspect, I don't see an advantage of SQLite over just calling JsonSerializer.Serialize(). The author's system then adds a bunch of features like version tolerance, AOT compilation of class metadata for iOS, polymorphic serialization, support for List<> and Dictionary<>, integration with the Godot game engine, etc. As far as I know, SQLite doesn't help you with any of that. Anything that can write data to disk can ultimately save and load your game data; it's just a question of how easily.
- nitwit005 2y ago
  While you don't need the relational features, some games do need the ability to make partial updates to make auto-save performant. Do a search for something like "Minecraft save game size", and you'll see some people have multi-gigabyte saves. Similar issues crop up with some Paradox Interactive games.
  - nearbuy 2y ago
    Games are hugely varied. No doubt there are games out there for which SQLite is perfect. But I wouldn't use it for making partial updates in something like Minecraft. It's not practical to store individual Minecraft blocks as table entries, so if you were using SQLite, you'd likely just store chunks (e.g. 16x16x16 blocks) as binary blobs. Then you'd rewrite entire chunks on save. It's not really taking advantage of what SQLite offers. There are a lot of serializers and frameworks out there you could choose from, but even something as simple as just writing one map region per file and overwriting modified regions on save would be better than SQLite.
rishav_sharan 2y ago
For simpler games with simple state which can be expressed in relationships, it is definitely a good solution. However, as games get more complex, modeling the game state in just relations is harder. Its much simpler to model state in an object like structure. At least for me.
flohofwoe 2y ago
We actually used SQLite in a couple of singleplayer RPGs (the Drakensang games). The initial world state was baked into tables in an SQLite database file, and savegames were just mutated SQLite files (we kept a record of created, mutated and deleted database rows, and periodically flushed those changes into SQLite). It worked well, but was overkill because we didn't actually make use of any advanced SQL features (just simple search over an object-id column). It would have been easier to cut SQL out of the loop and just write a simple table-based persistency system.
superfist 2y ago
Because that would require additinal step: object graph conversion to relational database representation when saving and reverse process when loading. It is simpler to save graph right away.
jayd16 2y ago
When N=1 normalizing and denormalizing the data would be slower and more cumbersome than just reading and writing the whole blob. You could use DB schema upgrade tooling to accomplish some of what's done by this library but now you're at SQLite+<some other middlewear>. If you have a tool you already like then that's perfectly ok.

I wonder the same thing. Perhaps less portable? IE can't package that up in a binary (I have absolutely no idea just spitballing)? And very cursory search suggests maybe there is nothing to that guess: https://www.reddit.com/r/golang/comments/tqffv2/packaging_an_sql_database_in_a_go_binary/ https://www.reddit.com/r/golang/comments/tqffv2/packaging_an... It's an interesting question because I've run into some datascientists that were so used to working in memory with dataframes and similar that they moved mountains to do things like de-duplicate csv's in memory (that they couldn't all fit in at once) where-as they could have done so trivially with sqlite.

jameshart 2y ago

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.

cjonas 2y ago
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.
flohofwoe 2y ago
> 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.
bob1029 2y ago
https://www.sqlite.org/pragma.html#pragma_user_version 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 2y ago
  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 2y ago
    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.

nearbuy 2y ago

You could use it, but it's not really solving the same problem. For a game, you generally don't need the relational database features. You aren't doing queries. You just want to load an entire level into memory, or save an entire level. For the serialization and persistence aspect, I don't see an advantage of SQLite over just calling JsonSerializer.Serialize(). The author's system then adds a bunch of features like version tolerance, AOT compilation of class metadata for iOS, polymorphic serialization, support for List<> and Dictionary<>, integration with the Godot game engine, etc. As far as I know, SQLite doesn't help you with any of that. Anything that can write data to disk can ultimately save and load your game data; it's just a question of how easily.

nitwit005 2y ago
While you don't need the relational features, some games do need the ability to make partial updates to make auto-save performant. Do a search for something like "Minecraft save game size", and you'll see some people have multi-gigabyte saves. Similar issues crop up with some Paradox Interactive games.
- nearbuy 2y ago
  Games are hugely varied. No doubt there are games out there for which SQLite is perfect. But I wouldn't use it for making partial updates in something like Minecraft. It's not practical to store individual Minecraft blocks as table entries, so if you were using SQLite, you'd likely just store chunks (e.g. 16x16x16 blocks) as binary blobs. Then you'd rewrite entire chunks on save. It's not really taking advantage of what SQLite offers. There are a lot of serializers and frameworks out there you could choose from, but even something as simple as just writing one map region per file and overwriting modified regions on save would be better than SQLite.

rishav_sharan 2y ago

For simpler games with simple state which can be expressed in relationships, it is definitely a good solution. However, as games get more complex, modeling the game state in just relations is harder. Its much simpler to model state in an object like structure. At least for me.

flohofwoe 2y ago

We actually used SQLite in a couple of singleplayer RPGs (the Drakensang games). The initial world state was baked into tables in an SQLite database file, and savegames were just mutated SQLite files (we kept a record of created, mutated and deleted database rows, and periodically flushed those changes into SQLite). It worked well, but was overkill because we didn't actually make use of any advanced SQL features (just simple search over an object-id column). It would have been easier to cut SQL out of the loop and just write a simple table-based persistency system.

superfist 2y ago

Because that would require additinal step: object graph conversion to relational database representation when saving and reverse process when loading. It is simpler to save graph right away.

jayd16 2y ago

When N=1 normalizing and denormalizing the data would be slower and more cumbersome than just reading and writing the whole blob. You could use DB schema upgrade tooling to accomplish some of what's done by this library but now you're at SQLite+<some other middlewear>. If you have a tool you already like then that's perfectly ok.