How to upgrade sqlite version fast?

[mateoKutnjak]

I am getting this error

SqliteException(1): near "RETURNING": syntax error, SQL logic error (code 1)

because my sqlite version is 3.31.0 and I want to upgrade.

I put sqlite3_flutter_libs: ^0.5.12 to my pubspec.yaml but it does not help.

Btw I need this for testing where I initialize my database like this:

setUp(() async {
    db = AppDatabase(NativeDatabase.memory(logStatements: true, setup: (database) => print(database.select("select sqlite_version();")),));
  });

[simolus3]

I put sqlite3_flutter_libs: ^0.5.12 to my pubspec.yaml but it does not help.

I assume you're running those tests as unit tests? In that case, the sqlite3 version from your operating system will be used (and we have no way of changing that currently - sqlite3_flutter_libs can only bundle a version of sqlite3 with your app at runtime).

To test drift itself, I'm using this file and I'm calling preferLocalSqlite3() as the first line of main() in test files that use sqlite3. Then, this file is responsible for downloading and compiling a recent sqlite version.

[simolus3]

On which operating system are you on though? Windows doesn't bundle sqlite3, so you have probably downloaded it and can download a more recent version. macOS tends to ship somewhat recent sqlite3 builds too, and there's an easy way to in install up-to-date sqlite3 libraries for almost all Linux distributions as well.

So maybe there's a way to update sqlite3 on your system and avoid the local workarounds.

[mateoKutnjak]

It is Ubuntu 20.04 LTS but accoding to this post it is not so safe. I guess that with newer destribution comes newer sqlite version. I will stick to the file you provided for now

[hcanyz]

I also ran into this problem on macos, and couldn't upgrade the user's macos version. Is there any way to specify the runtime version of sqlite3 on macos? @simolus3

[simolus3]

Using sqlite3_flutter_libs should link the latest sqlite3 with your macOS application if you're using Flutter.

[hcanyz]

Using sqlite3_flutter_libs should link the latest sqlite3 with your macOS application if you're using Flutter.

I tried to add this, but the result is that the value obtained by sqlite3.version is still the version number of sqlite that comes with macOS.

And I also tried using brew to install a new version of sqlite and set environment variables. However, the runtime uses either the sqlite that comes with the system.

[simolus3]

If you run this integration test (with flutter run integration_test/ on macOS), does that also print the verison of sqlite3 shipped with the system for you? For me it uses the sqlite3 version linked by sqlite3_flutter_libs.

If it's only broken in your app, I wonder if this snippet works for you?

[hcanyz]

Sorry, I can't test it on that machine at the moment.

[simolus3]

That makes it kind of hard for me to help diagnose the problem. I don't see a general issue with sqlite3_flutter_libs since it works on macOS for me (and I'm not aware of widespread reports about it not working). So my best guess is that it's something related to your setup. You could check if adding the entries to your podspect works, otherwise I'll likely need more information though.

[kuhnroyal]

MacOS has been always been a bit more complicated with brew and now even more especially on Apple silicon.
But this is only relevant if you don't get sqlite3_flutter_libs to work for whatever reason.

I am using code like this:

  if (Platform.isMacOS) {
    os = OperatingSystem.macOS;

    /// First check if the Arm64 binary installed via Homebrew exists.
    /// The homebrew binary path is different for x86 and Arm64
    library = File('/opt/homebrew/opt/sqlite/bin/sqlite3');
    if (!library.existsSync()) {
      /// Next check the x86 Homebrew binary.
      library = File('/usr/local/homebrew/opt/sqlite/bin/sqlite3');
    }
    if (!library.existsSync()) {
      /// If Homebrew binaries are not found, lastly check the system default.
      /// This may be an outdated version on older MacOS systems.
      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!');
  }

  open.overrideFor(os, () => DynamicLibrary.open(library.absolute.path));

[hcanyz]

This seems like a good idea. But if it is in the production release version, rather than doing integration testing, more robustness processing may be required. thanks~