Testing utilities

[pulyaevskiy]

Hey, thanks for this great plugin! Just curious if there were any plans on providing testing utilities like some sort of in-memory stub implementation. Or if you have some other suggestions in regards to testing. I'd really like to avoid mocking platform channels.

[alextekartik]

Thanks for the note. Testing is still a pain since there is no convenient way to run unit tests on iOS/Android (required here to have SQLite). I'm open to suggestion on how to do it:

• binding to some native workstation SQLite lib (so far my experience with native code is near zero and most attempts unsuccessful) when running regular flutter unit test
• Setup a test environment (group/test) in a regular flutter application
• mocking platform channel to send to some SQLite server
• mocking platform channel and build an SQL interpreter to write to some file database/in-memory
• Waiting for flutter sdk to provide an easy way to run "connected" tests (non-UI)

Most of my tests involves running a flutter app on iOS or Android. Plugin and native code testing is still not perfect but usable.
Testing application usage of sqflite is however mostly inexistent. I'm doing experiments but nothing valuable so far

[pulyaevskiy]

OK, thanks for the info. I'll see if I can come up with something. Just started to playing with your plugin today. Will post here when I have more ideas.

[alextekartik]

I ended up writing a custom solution. Not very nice but it works for testing SQL commands in a flutter unit test (which unfortunately has become painfully slower since dart2).

Basically it requires running an app (a SQFlite server) on either ios and android and running test that uses sqflite, forwarding plugins calls to web socket call to the server (json rpc 2 is easy to map with plugin invoke command). It kinds of work. Some info here:

https://github.com/tekartik/sqflite_more/blob/dart2/sqflite_server/README.md

It is a work in progress (no doc, some caveat)

[kdaker]

You can run sqlflite test just like you debug widget tests:

flutter run test/my_sqflite_test.dart

This will launch the device and run this particular test file. Still a pain as I still have to run these tests separately from the other unit tests.

[gmcdowell]

A working example from @kdaker would be amazing as this is a real pain point. Any chance you could put something up?

[kdaker]

@gmcdowell sure, here is a sample from my test file below.

# run the test
flutter run test/sqlite_test.dart

Some caveats:

• You have to specify the filename, the run command doesn't seem to take a directory
• The run command doesn't exit after the test completes
• Recreating the DB in the setUp function results in SQLite errors

The code below is abstracted a bit, basically the SQLite class wraps the db object and is responsible for setting it up.

// test/sqlite_test.dart

// skip this file to avoid getting errors when running your unit tests
@Skip("sqflite cannot run on the machine.")

import 'dart:io';
import "package:test/test.dart";
import 'package:sqflite/sqflite.dart';
import 'package:myapp/models/path.dart';
import 'package:myapp/dao/sqlite_paths_dao.dart';
import 'package:myapp/database/sqlite.dart';

final testDBPath = "somepath";
SQLitePathsDao dao;
SQLite sqlite;


/// Runs SQLite database tests. Assumes running in a fresh database all the time
/// Database is NOT reset between tests, tests should assume data exists from other test
/// runs.
/// Database is removed once all tests are complete
/// TODO: ideally I'd like to create a new DB before every test
void main() {
  group("SQLiteDao tests", () {

    setUp(() async {
      sqlite = SQLite( (await getDatabasesPath()) + "/" + testDBPath);

      await sqlite.setup();
      dao = SQLitePathsDao(sqlite);
    });

    // Delete the database so every test run starts with a fresh database
    tearDownAll(() async {
      await deleteDatabase( (await getDatabasesPath()) + "/" + testDBPath);
    });

    test("should insert and query path by id", () async {
      Path p = Path(name: "my path", isArriveAt: true, time: DateTime.now());
      p = await dao.insertPath(p);
      expect(p.id, isNotNull);

      var qp = await dao.getPath(p.id);

      expect(qp.name, equals(p.name));
      expect(qp.isArriveAt, equals(p.isArriveAt));
      expect(qp.id, equals(p.id));
    });

    test("sample sqflite test code", () async {
      var result = await sqlite.db.query('sampleTableName', where: "id = ?", whereArgs: ['123']);
      expect(result.length, equals(0));
    });
  });
}

[GanZhiXiong]

You can run sqlflite test just like you debug widget tests:

flutter run test/my_sqflite_test.dart

This will launch the device and run this particular test file. Still a pain as I still have to run these tests separately from the other unit tests.

@kdaker How do you debug breakpoints when you run this command?

[alextekartik]

@GanZhiXiong
From the command line you can use

flutter run -t test/my_sqflite_test.dart

in Android studio, you can create a flutter configuration and specify the target (Dart entrypoint) to point to test/my_sqflite_test.dart instead of lib/main.dart, that should allow debugging.

[debuggdapps]

Is there any work planned for this ticket that allows the testing to be done independently of the platform. I want to be able to run unit test to test out my data access functionality without running it on the emulator. Is that going to be possible?

[alextekartik]

Hi @debuggdapps I have a temporary solution which is based on moor_ffi. The work is done separately from this depot and can be used as a git dev dependency. Take a look here: https://github.com/tekartik/sqflite_more/tree/master/sqflite_ffi_test

It supports unit testing on Windows, Mac and Linux in regular flutter test (no emulator/simulator).

[debuggdapps]

@alextekartik, Thanks for this info. Does this allow to connect to a real sqlite database. Is it possible that i can create a test database and use that as a resource to run my unit test scenarios?

[alextekartik]

Unfortunately I'm not sure. I would guess yes since I don't know about any binary incompatibility on the version installed so far. There has not been any format change since 3.0.0 according to this https://www.sqlite.org/formatchng.html.

[alextekartik]

sqflite_common_ffi v1.0.0 has been published allowing using ffi on the desktop. It is based on moor_ffi (!) that provides a nice wrapper around sqlite libraries