Altering database structure

[Kriso23]

Hi there, Wondering if there is any way for me to alter my database structure that is persistent? For example I'm making a game with future planned features and I want to be able to add columns or split tables into 2 etc. What methods are available to me to do this? I guess what I'm asking is how does migration work with this addon?

[Kriso23]

Because I could write a function which stores old data in dictionaries and adds it as rows to a new db but what if the user is 2 alterations behind? feels like it will get cumbersome quickly

[2shady4u]

Hello @Kriso23,

Other users have devised methods to update their databases depending on a version entry in their database. The best way, according to them, seems to be to update your outdated databases using so-called "migrations". (Django does this for example)

This can either be implemented in GDScript or in C++, the choice is yours.

Unfortunately this feature is not part of the scope of godot-sqlite and no native method will ever be implemented in this repository.

[cridenour]

@Kriso23 if you're looking for some code for migrations:

First have a migrations table.

CREATE TABLE migrations
(
    migration_name TEXT NOT NULL
        PRIMARY KEY,
    applied        TEXT NOT NULL
);

Second, where you manage your database, keep a GDScript Array of Dictionaries of migration names and functions.

var migrations: Array[Dictionary] = [
  {
    'name': '001_save_meta',
    'apply': migration_001_save_meta,
  },
  ...
]

Then after you load your database, you can check where that file has been migrated and move it along.

func migrate() -> void:
  # Checks what migrations we have applied and apply any missing
  var db_results := query('SELECT migration_name FROM migrations')
  var applied: Array[String] = []
  for result in db_results:
    applied.append(result['migration_name'])

  var ok: bool  
  for migration in migrations:
    if !applied.has(migration['name']):
      query('BEGIN TRANSACTION')
      ok = migration['apply'].call()
      if ok:
        query('INSERT INTO migrations (migration_name, applied) VALUES (?, ?)', [migration['name'], Time.get_datetime_string_from_system()])
        query('COMMIT TRANSACTION')
      else:
        query('ROLLBACK TRANSACTION')

        push_error('Could not upgrade save file. Failed at %s' % migration['name'])
        # TODO: Show user error
        return

In this case, query is a helper function over _db.query_with_bindings where _db is my SQLite variable.

And migration_001_save_meta and other migration functions just return true/false based on success. Most are just a bunch of query calls or running SQL from a file.

Hope that helps!