Purely additive migrations

[edwardaux]

I've noticed that, by default, adding a new table to my app will fail with a "table not found" error. During development this is fine, as I just blow away my database and start again... however, once I ship, then I know I'm going to need to write a migration.

I'm just thinking about a very common case for me which is to add some new tables (no modifications to any existing tables). I was wondering if rather than writing a bunch of migrations to specifically add the new tables whether there's a way I can just say "lazily create the table" - either the first time the table is referenced or perhaps when the database is opened.

I see that there's a createAll function on the migrator that suggests that it could be used to do this. Would this work? Is it a bad idea?

Thanks again for a great library. One of the best I've used.

[simolus3]

Doing createAll() should be fine because tables and views are created with CREATE IF NOT EXISTS, but isn't necessarily true for triggers or indices. A safer approach would be to read existing schema entities and only create new ones. I haven't tried it, but I think this should work:

// or use beforeOpen if you don't want to bother bumping the schemaVersion either :)
onUpgrade: (m, from, to) async {
  final existingTables =
      await customSelect('SELECT name FROM sqlite_schema')
          .map((row) => row.read<String>('name'))
          .get();

  // Create all schema entities that don't exist yet.
  for (final entity in allSchemaEntities) {
    if (entity is! OnCreateQuery &&
        !existingTables.contains(entity.entityName)) {
      await m.create(entity);
    }
  }
},

Of course, this still wouldn't work when adding a new column to an existing table for instance.

[edwardaux]

Ah, neato. Thanks I'll give that a shot. I may end up just writing migrations anyway... I'm just thinking of ways to make my life a bit easier. Thanks again for such great support.