crsqlite_room_testing.md

cr-sqlite with Room Testing

This document covers the setup required to use cr-sqlite in Android instrumentation tests with Room.

Prerequisites

Before cr-sqlite can be tested in instrumentation tests, several APK packaging configurations are required.

Required Manifest Configuration

In AndroidManifest.xml, native libraries must be extracted to the filesystem:

<application
    android:extractNativeLibs="true"
    ...>

Without this, the native library directory will be empty at runtime, and dlopen will fail.

Required Gradle Configuration

In app/build.gradle, legacy packaging must be enabled:

packagingOptions {
    jniLibs {
        useLegacyPackaging = true
    }
    pickFirst '**/*.so'
}

• useLegacyPackaging = true is required when extractNativeLibs="true"
• pickFirst '**/*.so' resolves conflicts when multiple libraries have the same name

Library Naming: Why crsqlite_requery.so

The project has two cr-sqlite builds:

1. React Native's version (crsqlite.so) - bundled via op-sqlite/PowerSync for the RN side
2. Our custom build (crsqlite_requery.so) - built specifically for requery's SQLite version

These are not interchangeable. The React Native version is built against op-sqlite's embedded SQLite, while our custom version is built for requery's SQLite 3.45.0.

To avoid Gradle's pickFirst choosing the wrong one, we renamed our build to crsqlite_requery.so.

Loading cr-sqlite with Requery

✅ Correct: Use SQLiteCustomExtension

override fun createConfiguration(
    path: String?,
    @OpenFlags openFlags: Int
): SQLiteDatabaseConfiguration {
    val config = super.createConfiguration(path, openFlags)
    config.customExtensions.add(
        SQLiteCustomExtension("crsqlite_requery", "sqlite3_crsqlite_init")
    )
    return config
}

This uses requery's internal nativeLoadExtension() which works correctly.

❌ Wrong: Do NOT use load_extension() SQL

// DON'T DO THIS - fails with "error during initialization"
db.execSQL("SELECT load_extension('crsqlite_requery', 'sqlite3_crsqlite_init');")

Even though the library exists and is accessible, calling load_extension() from SQL fails with a generic "error during initialization" error. The root cause is unclear, but requery's native loading mechanism works while the SQL function does not.

Verifying cr-sqlite is Loaded

After the database is opened, verify cr-sqlite functions are available:

db.rawQuery("SELECT crsql_db_version()", null).use { cursor ->
    if (cursor.moveToFirst()) {
        Log.d(TAG, "cr-sqlite loaded! db_version = ${cursor.getString(0)}")
    }
}

Note: Use crsql_db_version(), not crsql_version() - the latter doesn't exist in this build.

Room Integration with RequerySQLiteOpenHelperFactory

We use requery's built-in Room support (docs):

Room → CrSqliteRoomFactory → RequerySQLiteOpenHelperFactory → cr-sqlite

Key classes:

• CrSqliteRoomFactory - Our factory that adds cr-sqlite via ConfigurationOptions
• CrSqliteFinalizeWrapper - Thin wrapper that calls crsql_finalize() before close
• RequerySQLiteOpenHelperFactory - Requery's built-in Room-compatible factory

How it works:

1. ConfigurationOptions.apply() adds SQLiteCustomExtension("crsqlite_requery", ...)
2. CrSqliteFinalizeWrapper ensures crsql_finalize() is called before close
3. underlyingDatabase exposes the requery SQLiteDatabase for cr-sqlite function access

Troubleshooting

"no such function: crsql_db_version"

The cr-sqlite extension isn't loading. Check:

1. extractNativeLibs="true" in AndroidManifest.xml
2. useLegacyPackaging = true in build.gradle
3. The .so file exists in jniLibs/<arch>/
4. Clean build: ./gradlew clean

"error during initialization" with load_extension()

Don't use SQL load_extension(). Use SQLiteCustomExtension instead (see above).

"dlopen failed: library not found"

The library isn't being extracted. Check extractNativeLibs and useLegacyPackaging settings.

Native library directory is empty

Same as above - the APK packaging isn't extracting native libraries.

Tests pass but use wrong cr-sqlite version

If you see unexpected behavior, verify the correct .so is packaged:

unzip -l app/build/outputs/apk/*/debug/*.apk | grep crsqlite

You should see crsqlite_requery.so in the lib directories.

"sqlite3_close failed: 5" / "unfinalized statements"

This warning appears during database close:

E SQLiteConnection: sqlite3_close(0x...) failed: 5
E SQLiteConnectionPool: unable to close due to unfinalized statements

This is expected and not a test failure. Room/Requery cache prepared statements for performance, and SQLite complains when closing with cached statements. The GC cleans these up.

CrSqliteSupportHelper tries to mitigate this by:

1. Setting SQL cache size to 0 before close
2. Catching the close exception gracefully

The important thing is that crsql_finalize() is called before close, which it is.