SQLite Extension Loading for PowerSync

PowerSync requires the ability to load SQLite extensions. This document describes the requirements and approaches for enabling SQLite extension loading across different platforms.

Requirements

PowerSync has no special requirements other than requiring the ability for our SQLite extension to be loaded.

SQLite Documentation

For detailed information about SQLite extension loading, refer to the official SQLite documentation:

Checking Extension Loading Support

Most system SQLite libraries (especially those shipped with Android and iOS) are built with limited or no support for dynamic extension loading. Whether extension loading is available depends on how the library was compiled. You can check this at runtime using:

SELECT
  sqlite_compileoption_used ('OMIT_LOAD_EXTENSION');

If it returns 1, then dynamic loading is disabled (SQLITE_OMIT_LOAD_EXTENSION).

Static Registration (Possible on iOS/macOS with the system library)

On Apple platforms, sqlite3_auto_extension() is deprecated with system SQLite because process-global extensions are not supported.

However, when using SQLCipher (such as with Capacitor community SQLite), sqlite3_auto_extension() can be used. This is the approach used in the PowerSync Capacitor plugin for iOS. See the iOS implementation for an example:

int register_powersync(void) {
    return sqlite3_auto_extension((void(*)(void))sqlite3_powersync_init);
}

With system SQLite, extensions must be statically linked and initialized per connection. Each extension defines its own init function, typically named sqlite3_<extname>_init, which must be called manually after opening a database connection:

import SQLite3

var db: OpaquePointer?
if sqlite3_open("mydb.sqlite", &db) == SQLITE_OK {
    sqlite3_mypowersync_init(db, nil, nil)
}

This works for system SQLite on both iOS and macOS.

Important: To statically link our extension, your library would need to include our extension in its build. This is probably not ideal for your setup, as it couples your library to our code.

Dynamic Loading (Possible on Android)

Android system SQLite may support dynamic extension loading, but this needs to be verified since the system library may be older and not have this capability. Android SQLCipher does support this and it's enabled by default for all connections from version 4.10.

Even if dynamic loading is technically supported, relying on it is probably not recommended for production, due to variability in SQLite versions across devices.

If dynamic loading is available, it can be enabled using the C API:

sqlite3_enable_load_extension(db, 1);
sqlite3_load_extension(db, "path/to/extension.so", 0, &errMsg);

Alternatively, you can load extensions dynamically using an SQL statement (as used in the PowerSync Capacitor plugin for Android). See the Android implementation for an example:

SELECT
  load_extension ('libpowersync.so', 'sqlite3_powersync_init');

This SQL approach requires that extension loading is already enabled for the connection (which SQLCipher does by default from version 4.10). The first parameter is the path to the extension library, and the second parameter is the entry point function name.

Recommendation for Cross-Platform Support

Ideally, your library should expose a mechanism to register a dynamically loaded extension. This avoids statically linking our extension (to your library).

kobiebotha/powersync-extension-loading.md