Sqlite 3.36 locally but Sqlite 3.32 on CodeMagic

[svarlet]

Hi,

I need help to fix my integration tests. They pass locally but fail on our build server.
An exception is thrown when we use INSERT RETURNING. I immediately knew that was related to the version of sqlite.
A few print statements later, I confirmed that my integration tests create an (in memory) sqlite db with sqlite 3.36. That supports the RETURNING clause. But the same print statements reveal that sqlite version 3.32.3 is used instead on our CodeMagic build server.
I'm confused because I've just migrated from moor to drift+drift native+sqlite3_flutter_libs and expected the latter would automagically ensure the latest sqlite3 is used. It does not.
What am I missing?

Our pubspec.yaml matches https://drift.simonbinder.eu/docs/getting-started/#adding-the-dependency

Our build.yaml:

targets:
  $default:
    sources:
      - lib/*
      - lib/src/**
      - lib/vfs/database/*
      - lib/vfs/entities/*
      - pubspec.*
      - lib/$lib$
      - $package$
      - test/test_helpers.dart
    builders:
      drift_dev:
        options:
          apply_converters_on_variables: true
          generate_values_in_copy_with: true
          named_parameters: true
          new_sql_code_generation: true
          sqlite:
            version: "3.35"
            modules:
              - fts5

How we create the db:

LazyDatabase _openConnection(Logger _log) {
  return LazyDatabase(() async {
    return NativeDatabase(await databaseFile(), logStatements: true,
        setup: (db) {
      db.execute('PRAGMA journal_mode = WAL');
      db.execute('PRAGMA recursive_triggers = true');
      db.execute('PRAGMA case_sensitive_like = true;');
      _log.fine('executed PRAGMA statements');
    });
  });

@DriftDatabase(include: {'tables.moor'})
class SyncDatabase extends _$SyncDatabase {
  //...

  SyncDatabase({
    MigrationListener migrationListener = const _NoopMigrationListener(),
    QueryExecutor? queryExecutor,
  })  : _migrationListener = migrationListener,
        super(queryExecutor ?? _openConnection(_log));

  // ...
}

[kuhnroyal]

The test are run on the host OS (linux/mac?) and not on a device. You need to ensure the version is installed there.
I am manually compiling it for my Linux Github runners and use brew install sqlite3 for MacOS runners.
Not sure if you can customize CodeMagic runners.

And add this to your flutter_test_config.dart

import 'package:sqlite3/open.dart';
import 'package:sqlite3/sqlite3.dart';

....


/// This is required in order to support the `RETURNING` keyword
/// which was added in 3.35.0 which is not available by default on MacOS.
///
/// Sqlite needs to be installed via brew.
Future<void> _configureSqlite3Library() async {
  late File library;
  late OperatingSystem os;
  if (Platform.isMacOS) {
    os = OperatingSystem.macOS;
    library = File('/usr/local/opt/sqlite/bin/sqlite3');
  } else if (Platform.isLinux) {
    os = OperatingSystem.linux;
    library = File('/usr/local/lib/libsqlite3.so');
  } else {
    throw AssertionError('Linux or MacOS is required to run database tests!');
  }

  if (!library.existsSync()) {
    final general = "The sqlite3 lib was not found in '${library.path}' - please ";
    if (Platform.isMacOS) {
      throw AssertionError("$general run 'brew install sqlite'!");
    } else {
      throw AssertionError('$general follow https://www.osradar.com/install-the-latest-version-sqlite-ubuntu-20-04/ to install!');
    }
  }
  open.overrideFor(os, () => DynamicLibrary.open(library.absolute.path));

  if (sqlite3.version.versionNumber <= 3035000) {
    final general = "The sqlite3 version in '${library.path}' is too old - please ";
    if (Platform.isMacOS) {
      throw AssertionError("$general run 'brew update && brew upgrade'!");
    } else {
      throw AssertionError('$general follow https://www.osradar.com/install-the-latest-version-sqlite-ubuntu-20-04/ to install!');
    }
  }
}

[svarlet]

I just added a brew install sqlite to my codemagic config and confirmed that the installed version is now 3.38.
Because the macOS instance already provides an old (3.32) version, homebrew does not symlink and I must update my $PATH in the .zshrc file to also include the /usr/local/opt/sqlite folder.
Yet, when the tests run, they still report that the db created is v3.32.
How can I tell dart to use the one I just installed? if not with $PATH, how? what differs from my personal Mac laptop and this macOS-on-codemagic machine?

[svarlet]

This docs section explains how I could ship a .so with my app but doesn't explain how to proceed in the context of testing with flutter test.

[kuhnroyal]

You are right, I forgot that part. Added a code snippet which I run before each test.

[kuhnroyal]

Ok so the sqlite3_flutter_libs provides a recent sqlite3 version for android and iOS only, right?

And MacOS, but only if you package it as an app. Which is not the case when running the tests, that is why the correct sqlite3 library is not available.

[simolus3]

Already started writing so I'm adding to what @kuhnroyal posted :D

Ok so the sqlite3_flutter_libs provides a recent sqlite3 version for android and iOS only, right?

Also macOS, but just like you can't use say platform channels in flutter test, sqlite3_flutter_libs can't do anything there too. These native plugins are only applied for actual apps or integration tests ran with a test driver. flutter test won't run your tests in a Flutter app, they're run in a standard Dart VM.

How can I tell dart to use the one I just installed? if not with $PATH, how?

$PATH is for binaries. On macOS, $LD_LIBRARY_PATH is used to look up dynamic libraries. But unfortunately the name of the dylib from the sqlite3 formula (or whatever it's called) is libsqlite3.0.dylib and I couldn't figure out how to load it with LD_LIBRARY_PATH.

This works though:

import 'package:sqlite3/open.dart';

void main() {
  open.overrideFor(OperatingSystem.macOS, () {
    return DynamicLibrary.open('/opt/homebrew/Cella/sqlite/3.38.0/lib/libsqlite3.0.dylib');
  });

  // ...
}

But the snippet @kuhnroyal posted looks way more reliable, doing that in a setupAll flutter_test_config.dart is probably the best approach here.

what differs from my personal Mac laptop and this macOS-on-codemagic machine?

That's an interesting question. Perhaps the codemagic machines are using an older version of macOS? My 12.0.1 mac comes with sqlite 3.36.0.

[svarlet]

Side note, this docs page says:

If you need a custom sqlite3 library, or want to make sure that your app will always use a specific sqlite3 version, you can also ship that version with your app. When depending on sqlite3_flutter_libs, drift will automatically use that version which is usually more recent than the sqlite3 version that comes with macOS.

and I think it's either misleading, incorrect, or unspecific.

[simolus3]

This is a good point, thanks. In hindsight I see how that page must be really confusing with your use-case of running sqlite3 in tests. I've updated the page to point this out in 64c5083.