How to write tests with database that reads from file? Do I even need it?

[cervonwong]

Hi,

I have a table icon_colors which updates as more color options are added to the app. I select from the icon_colors table to show to the users in the UI the possible color options they have to change their icons. Some tables also have foreign key constraints to this table.

Diagram

Code

// In icon_colors_table.dart
class IconColors extends Table {
  IntColumn get id => integer()();

  TextColumn get name => text().customConstraint('NOT NULL UNIQUE')();

  @override
  Set<Column> get primaryKey => {id};
}

I insert records to icon_colors table when the database is created for the first time:

// In my_database.dart
@override
MigrationStrategy get migration {
  return MigrationStrategy(
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');

      if (!details.wasCreated) return;

      await batch(
        (batch) {
          batch.insertAll(
            iconColors,
            IconColorConstants.values.entries
              .map((entry) => IconColorModel(id: entry.key, name: entry.value))
              .toList(),
          );
        }
      );
    }
  );
}

When I want to add new colors to the app, I go to my IconColorConstants class in a dart file which stores all the colors in a map, and change the version constant.

// In icon_color_constants.dart
class IconColorConstants{
  static const int version = 1; // Increment this to two when values changes

  static const values = {
    1: 'red',
    2: 'orange',
    3: 'green',
    4: 'yellow',
  };
}

I am not showing the code because I have not written it yet, but in beforeOpen of MigrationStrategy, I will compare IconColorConstants.version with a table in my database which I used to store the version of the colors called icon_colors_version. If I see that the IconColorConstants.version is higher than the version in icon_colors_version table, that means that there are new colors that I added in code that the database have not updated yet. I will then create/update records in icon_colors table in beforeOpen. This way, I can add new colors in IconColorConstants, and increment the version there. When the database opens again, and sees that IconColorConstants.version has increased, it will make sure it has the new colors in the icon_colors table.
Mini-question: Is this a good way of handling changing this kind of data?

I want to test the logic of beforeOpen of MigrationStrategy checking IconColorConstants.version and updating the icon_colors table. But, I come into a problem.

// In my_database_test.dart
void main() {
  late MyDatabase db;

  setUp(
    db = MyDatabase(VmDatabase.memory());
  );
}

I am using VmDatabase.memory which does not persist any data. What I think I need to do is to:

1. Give the database version 1 of icon colors.
2. Verify that the icon_colors table is filled with icon colors from version 1.
3. Close the database???
4. Open the database again so that beforeOpen runs again??? Do I need to read from a file to load the previous data in the tables?
5. Give the database version 2 of icon colors.
6. Verify that the icon_colors table is being updated to have the new colors from version 2.
   But I'm not sure about how to do this.

Question: How do I test this kind of logic? Or is there a better way to do this?

I know this is kind of a long question, I also wrote this at night and my brain is not working well so reply if you need clarification or more code. Thanks for spending your time to read this. :D

[simolus3]

Mini-question: Is this a good way of handling changing this kind of data?

I don't see anything wrong with it. I'm not fully sure why you need to store colors in a db if you have them as constants in your app, but your approach of writing data with version updates sounds reasonable to me. You could also use moor's schemaVersion for this instead of writing that information to a new table, but both approaches should be fine here.

Question: How do I test this kind of logic? Or is there a better way to do this?

In moor_generator, we actually have something similar for verified schema migrations. You can find its implementation here, but it's likely more complicated than what you need.

You can use named in-memory databases to test this behavior. Unfortunately thee setup is a bit complicated, something like this should work:

import 'dart:math';

import 'package:moor/fii.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:test/test.dart';

void main() {
  late String connectionUri;

  setUp(() {
    connectionUri = 'file:mem${_randomString()}?mode=memory&cache=shared';
  });

  test('inserts new colors after a version upgrade', () async {
    // open the database in a mode where it thinks items are at version 1 (how
    // you do that is left up to you :D)
    var db =
        YourDatabaseClass(VmDatabase.opened(sqlite3.open(connectionUri, uri: true)));
    // make sure the database is opened and migrations ran
    await db.doWhenOpened((e) {});
    await db.close();

    // Create another database, this time with the new version constant
    db = YourDatabaseClass(VmDatabase.opened(sqlite3.open(connectionUri, uri: true)));
    // Ensure we have new colors...
    await expectLater(
        db.select(db.categories).get(), completion(hasLength(12)));
    await db.close();
  });
}

final Random _random = Random();

String _randomString() {
  const charCodeLowerA = 97;
  const charCodeLowerZ = 122;
  const length = 16;

  final buffer = StringBuffer();
  for (var i = 0; i < length; i++) {
    buffer.writeCharCode(
        _random.nextInt(charCodeLowerZ - charCodeLowerA) + charCodeLowerA);
  }

  return buffer.toString();
}

In the next moor version you can simplify this and work with a regular in-memory database:

void main() {
  late Database rawDb;

  setUp(() {
    rawDb = sqlite3.openInMemory();
  });

  tearDown(() {
    rawDb.dispose();
  });

  test('inserts new colors after a version upgrade', () async {
    // open the database in a mode where it thinks items are at version 1 (how
    // you do that is left up to you :D)
    var db =
        e.Database(VmDatabase.opened(rawDb, closeUnderlyingOnClose: false));
    // make sure the database is opened and migrations ran
    await db.doWhenOpened((e) {});
    await db.close();

    // Create another database, this time with the new version constant
    db = e.Database(VmDatabase.opened(rawDb, closeUnderlyingOnClose: false));
    // Ensure we have new colors...
    await expectLater(
        db.select(db.categories).get(), completion(hasLength(12)));
    await db.close();
  });
}

[cervonwong]

Thanks a lot! I will try your method (I will mark as answer once I get it working).

The reason I stored the colors in the db is because other tables can have foreign key constraints to it.

And the reason I did not use schema versions was because I was adding new records to the table and not modifying columns. However, I will be taking a second look at schema versions to see if it will work better.

:D

[cervonwong]

I compared the benefits of both solutions and decided that using schemaVersion would be more appropriate here. However, since my app is still in the developmental phase, I will not be able to test schema migrations until I change the icon colors in production, which is fine.

Thanks for your perspective and teaching me how to use URIs like that 🙏🏻