[sqlite3_native_assets] How to enable loading extensions

[rodydavis]

I cannot figure out how to add custom extension loading when using the sqlite3_native_assets package and customizing the flags.

Unhandled exception:
SqliteException(21): Could not load extension

For example I added flags to support the SQLite Session extension:

import 'dart:convert';
import 'dart:io';

import 'package:archive/archive.dart';
import 'package:http/http.dart';
import 'package:logging/logging.dart';
import 'package:path/path.dart';
import 'package:native_assets_cli/native_assets_cli.dart';
import 'package:native_toolchain_c/native_toolchain_c.dart';

void main(List<String> args) async {
  await build(args, (input, output) async {
    final source = input.outputDirectory.resolve('sqlite3.c');
    final response = await get(
      Uri.parse('https://sqlite.org/2025/sqlite-amalgamation-3490100.zip'),
    );
    if (response.statusCode != 200) {
      throw 'Could not download sqlite3: ${response.statusCode} ${response.reasonPhrase} ${response.body}';
    }
    final archive = ZipDecoder().decodeBytes(response.bodyBytes);
    for (final file in archive) {
      if (posix.basename(file.name) == 'sqlite3.c') {
        await File(source.toFilePath()).writeAsBytes(file.content);
      }
    }

    final builder = CBuilder.library(
      name: 'sqlite3',
      assetName: 'sqlite3_native_assets.dart',
      sources: [source.toFilePath()],
      defines: collectDefines(),
      // flags: ['-lpthread', '-ldl', '-lm'],
    );

    await builder.run(
      input: input,
      // This adds a dependency on sqlite3.c, which confuses dependency tracking
      // because that file was also changed by this build script.
      output: _IgnoreSourceDependency(output),
      logger:
          Logger('')
            ..level = Level.ALL
            ..onRecord.listen((record) => print(record.message)),
    );
  });
}

final class _IgnoreSourceDependency extends BuildOutputBuilder {
  final BuildOutputBuilder _inner;

  _IgnoreSourceDependency(this._inner);

  @override
  EncodedAssetBuildOutputBuilder get assets => _inner.assets;

  @override
  void addDependencies(Iterable<Uri> uris) {
    super.addDependencies(uris.where((e) => url.extension(e.path) != '.c'));
  }
}

// Note: Keep in sync with https://github.com/simolus3/sqlite-native-libraries/blob/master/sqlite3-native-library/cpp/CMakeLists.txt
const _defines = '''
  SQLITE_ENABLE_DBSTAT_VTAB
  SQLITE_ENABLE_FTS5
  SQLITE_ENABLE_RTREE
  SQLITE_DQS=0
  SQLITE_DEFAULT_MEMSTATUS=0
  SQLITE_TEMP_STORE=2
  SQLITE_MAX_EXPR_DEPTH=0
  SQLITE_STRICT_SUBTYPE=1
  SQLITE_OMIT_AUTHORIZATION
  SQLITE_OMIT_DECLTYPE
  SQLITE_OMIT_DEPRECATED
  SQLITE_OMIT_PROGRESS_CALLBACK
  SQLITE_OMIT_SHARED_CACHE
  SQLITE_OMIT_TCL_VARIABLE
  SQLITE_OMIT_TRACE
  SQLITE_USE_ALLOCA
  SQLITE_UNTESTABLE
  SQLITE_HAVE_ISNAN
  SQLITE_HAVE_LOCALTIME_R
  SQLITE_HAVE_LOCALTIME_S
  SQLITE_HAVE_MALLOC_USABLE_SIZE
  SQLITE_HAVE_STRCHRNUL
+  SQLITE_ENABLE_SESSION
+  SQLITE_ENABLE_PREUPDATE_HOOK
''';

Map<String, String?> collectDefines() {
  final entries = <String, String?>{};
  for (final line in const LineSplitter().convert(_defines)) {
    if (line.contains('=')) {
      final [key, value] = line.trim().split('=');
      entries[key] = value;
    } else {
      entries[line.trim()] = null;
    }
  }
  return entries;
}

No matter what flags I add, it still passes the SQLITE_OMIT_LOAD_EXTENSION flag is being included but cannot find it in the source code.

Curious where I should look next to try to debug?

[simolus3]

I'm worried that something is linking another version of SQLite which might interfere with the native build. Is this on standalone Dart or on a Flutter app with plugin dependencies?

You could try adding this to your main() to print the version and actual flags? If it prints something other than 3.49.1 that's bad, but SQLITE_OMIT_LOAD_EXTENSION should not be printed if it's using the native-hooks compilation.

@ffi.DefaultAsset('package:sqlite3_native_assets/sqlite3_native_assets.dart')
library;

import 'dart:io';
import 'dart:ffi' as ffi;

import 'package:ffi/ffi.dart' as ffi;
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_native_assets/sqlite3_native_assets.dart';

@ffi.Native<ffi.Pointer<ffi.Utf8> Function(ffi.Int)>()
external ffi.Pointer<ffi.Utf8> sqlite3_compileoption_get(int i);

void main() {
  final sqlite3 = sqlite3Native;
  print('Using sqlite3 ${sqlite3.version}');

  ffi.Pointer<ffi.Utf8> option;
  for (var i = 0; (option = sqlite3_compileoption_get(i)).address != 0; i++) {
    print('Compile-option: ${option.toDartString()}');
  }

[rodydavis]

Running the code above does yield 3.43.2 as the version. I am running on stand alone Dart with a M3 Mac.

Also it has the flag in the list:

Compile-option: OMIT_LOAD_EXTENSION

Even running with --enable-experiment=native-assets does not change anything.

[rodydavis]

Is there a way to force a rebuild of the native assets? I tried with flutter clean thinking that would help with no avail

[simolus3]

That version sounds like it's using the SQLite library from the system (which can happen easily as a fallback when not using a custom sqlite3).

Is there a way to force a rebuild of the native assets? I tried with flutter clean thinking that would help with no avail

In my experience the cache is quite solid and gets invalidated for changes to build scripts. You can always remove the .dart_tool folder as a measure of last resort.

Even running with --enable-experiment=native-assets does not change anything.

What surprises me here is that it works without that flag at all, are you on Dart stable? For me, trying to run a Dart program that depends on a package with native assets fails immediately when I'm not enabling the experiment. So I think the tool may not have picked up the build hook?

[rodydavis]

This is weird, because the version for my system is even different than that:

3.49.0 2025-02-06 11:55:18 4a7dd425dc2a0e5082a9049c9b4a9d4f199a71583d014c24b4cfe276c5a77cde (64-bit)

This is my Dart and Flutter version:

Flutter (Channel stable, 3.29.1, on macOS 15.3.2 24D81 darwin-arm64, locale en-US)
Dart SDK version: 3.7.0 (stable) (Wed Feb 5 04:53:58 2025 -0800) on "macos_arm64"

So I think the tool may not have picked up the build hook?

That has to be what is happening. But what is so weird is that the session extension is supported (which requires extra flags), I just cannot load extensions. Although what ever version is detected might have it enabled.

[simolus3]

This is weird, because the version for my system is even different than that:

The PATH variable used to override executables does not affect library loading. If you run which sqlite3, it will probably point to something other than /usr/bin/sqlite3 which is the one that comes from macOS. For me, /usr/bin/sqlite3 --version prints 3.43.2.

But what is so weird is that the session extension is supported (which requires extra flags), I just cannot load extensions

Extra flags that Apple has, to my own surprise, enabled. Running /usr/bin/sqlite3 with

SELECT sqlite_compileoption_get(value) FROM generate_series(0, 500) where sqlite_compileoption_get(value) is not null;

will print both ENABLE_PREUPDATE_HOOK and ENABLE_SESSION.

[rodydavis]

Wow! I also is installed SQLite via homebrew since the one with macOS does not load extensions.

But I have to usually override it when loading in Dart. And I override it in zsh.

Native assets must be picking up the MacOS version instead...

How should I proceed on debugging? This is my first time with native assets, and I can reach out to the internal dart team if needed.

[rodydavis]

Also on the SQLite session extension I am proposing it is enabled by default for sqlite3.dart

It already is enabled for default WASM official SQLite build and Node.js official SQLite support!

I have a PR I am working on if you are interested with all the bindings wired up. FFI is done and working on WASM

[simolus3]

How should I proceed on debugging? This is my first time with native assets, and I can reach out to the internal dart team if needed.

I think that may be the best approach, I've not encountered the issue of build hooks not being recognized before.

I have a PR I am working on if you are interested with all the bindings wired up. FFI is done and working on WASM

That would be awesome 🚀 I'm also happy to help with WASM or anything else, I know the internal setup to get everything running is quite complex.

[rodydavis]

Here is the Session PR. Would love some help on the WASM side, the FFI side is working great for me!

#300

[simolus3]

Awesome, I'll take a look this weekend 🚀

[simolus3]

Thanks again for your work adding session support!

It would be interesting to see if you can reproduce the issue about extensions not being enabled / an older SQLite being used with a 3.x beta release of package:sqlite3. There aren't too many changes on the build hook itself compared to sqlite3_native_assets. But maybe something got fixed in Dart. Those versions also don't have the legacy DynamicLibrary loading logic anymore, so the code asset should be used consistently.