Deliberate rollback in tests

[svarlet]

Hi,

As many tests hit the same database, I chose to wrap each test in a transaction to effectively isolate each test against the changes performed by all other tests. So I have code like this:

  // setUpAll to create an in-memory database

  // tearDownAll to db.close()

    test('something', () async {
      await db.transaction(() async {
        await db.createSomething(x, y, z);
        final aThing = await db.getSomethingByX(x);
        expect(aThing?.y, y);
      });
    });

I'd like to end each test with a rollback, how do I do that?
I know I can throw an exception and that will trigger the rollback, but that will also make my test fail.

[svarlet]

I guess an alternative is to have each test spawn its own exclusive in-memory database, but my intuition is that it's not going to scale well.

[simolus3]

Actually I don't think this will become a bottleneck at all. Even if you start from scratch every time and run the onCreate migration steps, sqlite3 is fast enough that the duplicate overhead will be negligible (especially since you're not accessing the file system at all). This is the approach I'm using in internal drift tests and all my projects as well.

If you go with this approach, you just have to make sure to close the database in a tear-down all the time. Otherwise you start leaking memory.

[svarlet]

Thanks for the prompt answer.

Also, if spawning a db became expensive, say it needs a gigabyte of data to be loaded, this approach wouldn't work anymore so what would you do instead?

[svarlet]

Or can I do that instead?

  // setUpAll to create an in-memory database

  // tearDownAll to db.close()

    test('something', () async {
      final transactionExecutor = db.executor.beginTransaction();
      
      await db.createSomething(x, y, z);
      final aThing = await db.getSomethingByX(x);
      expect(aThing?.y, y);
      
      await transactionExecutor.rollback();
    });

Then I suppose I could also move the begin/rollback code to setUp() and tearDown() functions?

[simolus3]

Be aware that sqlite3 doesn't support nested transactions, so if you go with this approach you can't use nested transactions in your database. You could use checkpoints manually with custom statements, but drift has no builtin support for them.

Either way, I wouldn't use beginTransaction / rollback manually. In that case, methods not called on transactionExecutor will not run on the transaction (in this case it's probably just a deadlock?). Drift uses zones to ensure that everything in a transaction() block does use the right executor, but there's no public API to pass your own transaction executor (and I don't think there should be, this is a drift-internal matter).

Instead, use customStatement('BEGIN TRANSACTION') in setUp and customStatement('ROLLBACK') in tearDown.

[svarlet]

How would we distinguish 2 concurrent transactions? for example, when 2 concurrent tests run?

[simolus3]

This isn't really possible inside the same executor, sqlite3 doesn't support concurrent transactions for a single connection.

[simolus3]

I've just remembered that this is possible, I think it should clear all your concerns:

void main() {
  const name = 'file:tests_1?mode=memory&cache=shared'; // maybe use a unique name per file name

  late Database _keepOpen;
  late TodoDb db; // Your generated database class

  setUpAll(() async {
    final db = sqlite3.open(name, uri: true);
    _keepOpen = sqlite3.open(name, uri: true);

    final driftDb = TodoDb(NativeDatabase.opened(db));
    await driftDb.customSelect('SELECT 1').get();
    await driftDb.close();
  });

  tearDownAll(() => _keepOpen.dispose());

  setUp(() async {
    db = TodoDb(NativeDatabase.opened(sqlite3.open(name, uri: true)));

    await db.customStatement('BEGIN TRANSACTION');
  });

  tearDown(() async {
    await db.customStatement('COMMIT TRANSACTION');
    await db.close();
  });

  test('first test', () async {
    await db.customSelect('SELECT 1').get();
  });
}

Basically, this uses one single in-memory database that is created once (assuming that the schema migration is super expensive because e.g. you insert tons of data), but gives every test an independent connection to that database which also runs in a transaction so that different tests don't interfere with each other. This way, every test is sharing common data created in setUpAll, but two tests don't interfere with each other even when ran concurrently (but sqlite may complain about the database being locked - it's just not really built for this). You could also use some magic with ATTACH to give every test a truly independent database that copies from a main database in setUpAll.