Describe the bug
After upgrading to 2.0.15 I get these errors:

use of undeclared identifier sqlite3_enable_load_extension and use of undeclared identifier sqlite3_load_extension on iOS.

It seems that culprit is the use_frameworks! :linkage => :static on Podfile. I need static linkage for react-native-firebase. This is the PR that started causing the issue: #40

Versions:

• OS and version: iOS 17.2, Xcode 15.2
• op-sqlite version: 2.0.15
• RN version: 0.73.2 using expo-modules-core on expo@50

Reproducible example
https://github.com/efstathiosntonas/bare-react-native-expo-svg-transformer-issue , I had created it a while ago for a bug on react-native-svg-transformer so just ignore that.

What happened?

Entering to the app - sometimes it work, sometimes crashes.

FATAL EXCEPTION: mqt_native_modules

java.lang.UnsatisfiedLinkError: No implementation found for void com.op.sqlite.OPSQLiteBridge.clearStateNativeJsi() (tried Java_com_op_sqlite_OPSQLiteBridge_clearStateNativeJsi and Java_com_op_sqlite_OPSQLiteBridge_clearStateNativeJsi__) - is the library loaded, e.g. System.loadLibrary?

FATAL EXCEPTION: mqt_native_modules
Process: dev.world.oone.driverapp, PID: 23948
java.lang.UnsatisfiedLinkError: No implementation found for void com.op.sqlite.OPSQLiteBridge.clearStateNativeJsi() (tried Java_com_op_sqlite_OPSQLiteBridge_clearStateNativeJsi and Java_com_op_sqlite_OPSQLiteBridge_clearStateNativeJsi__) - is the library loaded, e.g. System.loadLibrary?
	at com.op.sqlite.OPSQLiteBridge.clearStateNativeJsi(Native Method)
	at com.op.sqlite.OPSQLiteBridge.clearState(OPSQLiteBridge.java:24)
	at com.op.sqlite.OPSQLiteModule.onCatalystInstanceDestroy(OPSQLiteModule.java:55)
	at com.facebook.react.bridge.BaseJavaModule.invalidate(BaseJavaModule.java:76)
	at com.facebook.react.bridge.ModuleHolder.destroy(ModuleHolder.java:110)
	at com.facebook.react.bridge.NativeModuleRegistry.notifyJSInstanceDestroy(NativeModuleRegistry.java:108)
	at com.facebook.react.bridge.CatalystInstanceImpl$1.run(CatalystInstanceImpl.java:359)
	at android.os.Handler.handleCallback(Handler.java:942)
	at android.os.Handler.dispatchMessage(Handler.java:99)
	at com.facebook.react.bridge.queue.MessageQueueThreadHandler.dispatchMessage(MessageQueueThreadHandler.java:27)
	at android.os.Looper.loopOnce(Looper.java:201)
	at android.os.Looper.loop(Looper.java:288)
	at com.facebook.react.bridge.queue.MessageQueueThreadImpl$4.run(MessageQueueThreadImpl.java:228)
	at java.lang.Thread.run(Thread.java:1012)

Version

2.0.3

Compiling, packaging and loading extensions is complex and error prone. It would be nice if we could take care of this for the most common and used libraries so people only need to turn on a flag an everything is taken care for them.

I've already started with cr-sqlite, you would only need to add OP_SQLITE_CRSQLITE=1 to your env.

I however need more time/effort since every extension has different compilation steps and targets.

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

I have simple insert statement
database.executeAsync('INSERT INTO Student (studentId,name, age) VALUES ("MSSV2016","JOHN",18)').then((result) => { console.log('Insert result:',result); });

When I clicked button then I got weird crash from c++

Stacktrace:

  2023-11-13 11:05:38.997   909-1034  libc                    com.op.sqlite.example                A  Fatal signal 5 (SIGTRAP), code 1 (TRAP_BRKPT), fault addr 0x7b490fc5ac in tid 1034 (mqt_js), pid 909 (.sqlite.example)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A  Cmdline: com.op.sqlite.example
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A  pid: 909, tid: 1034, name: mqt_js  >>> com.op.sqlite.example <<<
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #00 pc 00000000003e55ac  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libhermes_executor.so (BuildId: 5a3214274800ef73)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #01 pc 00000000003e5488  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libhermes_executor.so (BuildId: 5a3214274800ef73)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #02 pc 00000000003e5438  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libhermes_executor.so (BuildId: 5a3214274800ef73)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #03 pc 00000000003dc040  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libhermes_executor.so (BuildId: 5a3214274800ef73)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #04 pc 0000000000058d00  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #05 pc 00000000000aa61c  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #06 pc 0000000000070240  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #07 pc 000000000006b610  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (sqlite3_step+92) (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #08 pc 00000000000536b4  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (opsqlite::sqliteExecute(std::__ndk1::basic_string<char, std::__ndk1::char_traits<char>, std::__ndk1::allocator<char> >, std::__ndk1::basic_string<char, std::__ndk1::char_traits<char>, std::__ndk1::allocator<char> > const&, std::__ndk1::vector<std::__ndk1::variant<std::nullptr_t, bool, int, double, long, long long, std::__ndk1::basic_string<char, std::__ndk1::char_traits<char>, std::__ndk1::allocator<char> >, ArrayBuffer>, std::__ndk1::allocator<std::__ndk1::variant<std::nullptr_t, bool, int, double, long, long long, std::__ndk1::basic_string<char, std::__ndk1::char_traits<char>, std::__ndk1::allocator<char> >, ArrayBuffer> > >*, std::__ndk1::vector<opsqlite::DumbHostObject, std::__ndk1::allocator<opsqlite::DumbHostObject> >*, std::__ndk1::shared_ptr<std::__ndk1::vector<opsqlite::DynamicHostObject, std::__ndk1::allocator<opsqlite::DynamicHostObject> > >)+368) (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #09 pc 000000000005bed4  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #10 pc 000000000014f564  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (opsqlite::ThreadPool::doWork()+224) (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)
2023-11-13 11:05:39.312  1088-1088  DEBUG                   crash_dump64                         A        #11 pc 000000000014feb8  /data/app/~~27TdYvXzY46WXqaMacYAZw==/com.op.sqlite.example-8veDpapy9U-F3e-8TnBiRg==/lib/arm64/libop-sqlite.so (BuildId: 3de96ffdf27bbebbec7e29628b33278b00b3c68c)

Describe the bug
I am not able to build expo app with 5.0.5
https://github.com/Volland/op-sql-db-version-ios/tree/main

Versions:

• OS and version: iOS 17
• op-sqlite version: 5.0.5
• RN version: 0.73.6
• New Architecture: Yes

Reproducible example
Follow repo https://github.com/Volland/op-sql-db-version-ios/tree/main

Error :
error: Error Domain=NSCocoaErrorDomain Code=4 "The file “clang” doesn’t exist." UserInfo={NSFilePath=/usr/local/opt/llvm/bin/clang}

❌ error: Error Domain=NSCocoaErrorDomain Code=4 "The file “clang” doesn’t exist." UserInfo={NSFilePath=/usr/local/opt/llvm/bin/clang}

❌ error: Error Domain=NSCocoaErrorDomain Code=4 "The file “clang” doesn’t exist." UserInfo={NSFilePath=/usr/local/opt/llvm/bin/clang}

❌ error: Error Domain=NSCocoaErrorDomain Code=4 "The file “clang” doesn’t exist." UserInfo={NSFilePath=/usr/local/opt/llvm/bin/clang}

Hi Franco, thanks again for your recent fix that destructed ThreadPools upon restart! I noticed another minor threading issue that I wanted to report. I don't think it needs to be fixed with any urgency, but I still thought it worth reporting here, as it took some digging to figure it out, and may help uncover other latent issues.

When running multiple async executions in parallel, I hit a race condition where I'm receiving the error of another execution, actually receiving [react-native-quick-sqlite] SQL execution error: not an error which corresponds to SQLITE_OK. I think what happened is what's described at the bottom here:

When the serialized threading mode is in use, it might be the case that a second error occurs on a separate thread in between the time of the first error and the call to these interfaces. When that happens, the second error will be reported since these interfaces always report the most recent result. To avoid this, each thread can obtain exclusive use of the database connection D by invoking sqlite3_mutex_enter(sqlite3_db_mutex(D)) before beginning to use D and invoking sqlite3_mutex_leave(sqlite3_db_mutex(D)) after all calls to the interfaces listed here are completed.

I really love the bundling idea .
VSS https://github.com/asg017/sqlite-vss couild be also a good idea for ai powered app vector search is a critical feature

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Hi, I'm new in c++ and I just want to implement sqlite3_update_hook with bellow code:

   SQLiteCallbackData sqliteCallbackData{
        .rt = &rt,
        .onTableChanged = args[3].asObject(rt).asFunction(rt),
    };
    BridgeResult result = sqliteOpenDb(dbName, tempDocPath, memoryStorage);
    if (result.type == SQLiteError)
    {
        throw jsi::JSError(rt, result.message.c_str());
    }

    sqlite3 *db = sqliteGetDb(dbName);
    sqlite3_update_hook(db,  [](void *pArg, int event, char const *zDb, char const *tableName, sqlite3_int64 rowid){
        if (event == SQLITE_INSERT || event == SQLITE_UPDATE || event == SQLITE_DELETE)
        {
            // __android_log_print(ANDROID_LOG_DEBUG, "op-sqlite", "Table changed");
            
            auto callbackData = static_cast<SQLiteCallbackData *>(pArg);
           jsi::Object args  =jsi::Object(*callbackData->rt);
        }
    }, &sqliteCallbackData);`

But i got crash on this line jsi::Object args =jsi::Object(*callbackData->rt);
I see you have a plan to implement sqlite3_update_hook so can you tell me what I'm doing wrong
Many thanks @ospfranco

Describe the bug
When calling db.delete on a db with an absolute location path, the underlying database is not deleted because the path passed to the delete function is wrong.

Error: Exception in HostFunction: [op-sqlite]: Database file not found/Users/franz/Library/Developer/CoreSimulator/Devices/696292DB-6A10-499A-BE60-7466EF5DAF90/data/Containers/Data/Application/8AEFF1D1-1C41-4500-BCB4-C2A136093373/Library//Users/franz/Library/Developer/CoreSimulator/Devices/696292DB-6A10-499A-BE60-7466EF5DAF90/data/Containers/Data/Application/8AEFF1D1-1C41-4500-BCB4-C2A136093373/Library/UDB-User13518182+RemoteGraph.sql
    at delete (native)

Versions:

• OS and version: iOS simulator 17.0
• op-sqlite version: 3.0.7
• RN version: 0.71.14
• New Architecture: No

Reproducible example

• Open a the database specifying a location, e.g
  const db = open({ name: 'test.sql', location: Platform.OS === 'ios' ? IOS_LIBRARY_PATH : ANDROID_DATABASE_PATH });
• Invoke db.delete(), it will throw

By looking at the code, the offending lines seem to be these ones: if we specify an absolute path in the location, the delete method is going to concatenate it to the base path, creating an invalid path.

Describe the bug
When I use op-sqlite with react-native-firebase, it throws an error when I run expo run:ios. The error is a repetition of something like this:

› Compiling @op-engineering/op-sqlite Pods/op-sqlite » bindings.cpp

❌  (node_modules/@op-engineering/op-sqlite/cpp/sqlite3.h:729:8)

  727 | */
  728 | typedef struct sqlite3_file sqlite3_file;
> 729 | struct sqlite3_file {
      |        ^ redefinition of 'sqlite3_file'
  730 |   const struct sqlite3_io_methods *pMethods; /* Methods for an open file */
  731 | };
  732 | 

In my research, I found this issue GetStream/stream-chat-react-native#2123 that maybe related to this one.
As suggested in this issue, I tried to run OP_SQLITE_USE_PHONE_VERSION=1 npx pod-install, but it does not solve the error.

Versions:

• OS and version: iOS 17
• op-sqlite version: 3.0.7
• RN version: 0.73.6
• Expo version: 50.0.14
• New Architecture: Yes and no (both does not work)

Reproducible example

https://github.com/arthur-fontaine/repro-op-sqlite-firebase-incompatible

Steps:

• npm i
• npx expo prebuild --clean
• npm run ios

Dear Author, first of all thank you for creating this library.

Let me tell you about the problem I encountered：

A database created with op-sqlite will not work in flipper, but a database created with react-native quick-sqlite will work in flipper.

What do I need to do to get flipper to display a database created with op-sqlite?

When I click the Refresh button, the database will become corrupt.

Environment

Flipper version: Version 0.237.0
React Native version: 0.72.7

Describe the bug
I try to store a binary data to a table
As recomended i use

        let binaryData = new Uint8Array(2);
        binaryData[0] = 42;
    db.execute(
      `create table if not exists nodes (
        id varchar(36) primary key not null,
        label varchar,
        vectorLabel blob
      );`
    );
        const insert1 =  db.prepareStatement(
          'INSERT INTO nodes (id, label, vectorLabel) VALUES (?, ?, ?);'
        );
  insert1.bind([generateUUID(),'tea',binaryData])

I get error : [Error: Exception in HostFunction: Unknown JSI ArrayBuffer to variant value conversion, received object instead of ArrayBuffer]

I pass a ArrayBuffer as a parameter insead on Uint8Array and it solve issue but it is not align with a docs etc

Versions:

OS and version: iOS 17
op-sqlite version: 4.0.0-beta1
RN version: 0.73.6
New Architecture: Yes

Reproducible example
Create a minimal reproduction example, otherwise I won't take a look. I'm serious, no example = no solution.

It would be nice to have support for Web

I have a question regarding the screenshot and results posted on the README. I've gone ahead and run the example project on my iPhone 15 Pro (physical device) and could not reproduce similar results. The table below describes the results when running the example application.

I also created a sample project using Expo (react-native) and tested the same scenario vs react-native-quick-sqlite (v8.0.6). This was with the performance mode flag OP_SQLITE_PERF=1 enabled for op-sqlite. The results below are significantly different to the results shown in the README.

Could you please let me know what would cause the big difference in results?
I can provide more details about my environment and setup if needed.

Versions:

• OS and version: iOS 17.4
• op-sqlite version: 2.0.20
• RN version: 0.73.4
• New Architecture: Yes

Reproducible example
Followed the setup and tests in the example project.
https://github.com/OP-Engineering/op-sqlite/blob/main/example/src/Database.ts

Hi,

When I try to build this library with sqlite.swift

It fails on ci, with a bunch of errors on redefinition of sqlite3

fix error: redefinition of 'sqlite3_mutex_methods'

I have updated to the latest version of both op-sqlite and sqlite.swift
and added the following to my package.json
"op-sqlite": { "iosSqlite": false }

Any ideas on how to fix this issue would be appreciated. I really want the benefits of c++ op-sqlite
and the cleanliness of sqlite.swift in my ios modules

Hello! My team have been experimenting with SQLite libraries for React Native and stumbled upon this library. Thank you for you great work!

Our use case requires us to use multiple SQLite files at once and we notice that you have an 'attach' feature that allows you to attach multiple sqlite files for the same DB connection.

However, currently the db connection object does not return a 'executeSql' function that can be used as seen in the example. Is the example deprecated and if so, is there an updated example on how to use multiple SQLite files?

Hello

The doc says:

SQLite has a limit for attached databases: A default of 10, and a global max of 125

But I can't find a way to increase it with the library API.

Reading the SQLite doc, there is a function sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size). Is there a way to call it?

Thanks

I have query that create and insert data like this
When I called db.executeAsync(query) -> I got an error : [Error: [op-sqlite] SQL execution error: not an error]
I don't know it actually is but all tables and data were created successfully

CREATE TABLE segments ("distance" REAL NOT NULL, "endDate" INTEGER NOT NULL, "id" TEXT PRIMARY KEY, "index" INTEGER NOT NULL, "region" TEXT NOT NULL, "speed" REAL NOT NULL, "startDate" INTEGER NOT NULL, "tripId" TEXT NOT NULL, "startLat" REAL NOT NULL, "startLng" REAL NOT NULL, "endLat" REAL NOT NULL, "endLng" REAL NOT NULL) STRICT;

Error: [op-sqlite] SQLite code: 21 execution error: not an error
    at execute (native)
    at anonymous (http://localhost:8081/index.bundle//&platform=android&dev=true&minify=false&app=dev.world.oone.driverapp&modulesOnly=false&runModule=true:422897:26)
    at execute (http://localhost:8081/index.bundle//&platform=android&dev=true&minify=false&app=dev.world.oone.driverapp&modulesOnly=false&runModule=true:423039:32)
    at createSegmentsTable (http://localhost:8081/index.bundle//&platform=android&dev=true&minify=false&app=dev.world.oone.driverapp&modulesOnly=false&runModule=true:422692:19)

fails to execute table creation

I just realized that you manually need to convert a document back to a Uint8Array.

const result = db.execute('SELECT content FROM BlobTable');

const finalUint8 = new Uint8Array(result.rows!._array[0].content);

Do you think there is a change this could be done automatically? The only idea I have is prefixing the data with info of the type, but not happy about it.

Why? This is how other implementations like sql.js also work

It's possible that you would try to set a property on the resulting rows as they get referenced elsewhere in an application. Right now this is not possible and throws an error if you try rows[0].customProperty = 100.

The other solution might be to copy the host object values into a normal object but that means you lose he benefit of using HostObjects altogether.

Hi,
and first of all thanks for amazing work, it looks it's now faster than light! I currently of state when researching libraries for my new project and I have question about encryption.

Does op-sqlite support SQLCipher? I know this can impact performance but I don't have to much options because I need best security possible.

If it's not currently supported, can I try to do it myself and open PR?

Thanks.

Describe the bug

5.0.5 doesn't build for Android. 4.0.0-beta1 neither. 3.0.7 works fine.

Error is:

e: file:///home/jel-atolcd/Documents/AwesomeProject/node_modules/@op-engineering/op-sqlite/android/src/main/java/com/op/sqlite/OPSQLiteBridge.kt:5:46 Unresolved reference: FrameworkAPI
e: file:///home/jel-atolcd/Documents/AwesomeProject/node_modules/@op-engineering/op-sqlite/android/src/main/java/com/op/sqlite/OPSQLiteBridge.kt:7:8 Unresolved reference: FrameworkAPI
e: file:///home/jel-atolcd/Documents/AwesomeProject/node_modules/@op-engineering/op-sqlite/android/src/main/java/com/op/sqlite/OPSQLiteBridge.kt:7:8 An annotation argument must be a compile-time constant

FAILURE: Build failed with an exception.

Versions:

• OS and version: Android 13
• op-sqlite version: 5.0.5
• RN version: 0.72.7
• New Architecture: No

Reproducible example

Basic example from RN doc does not build:

npx [email protected] init AwesomeProject --version 0.72.7
npm i -s @op-engineering/op-sqlite

Sources available here: https://github.com/jel-atolcd/op-sqlite-issue

Thanks

I am currently seeking guidance on the process of reading SQLite database (.db) files from an SD Card within the context of an Android application.

I am managing multiple databases ranging in size from 1 GB to 3 GB, and it is my preference not to store these databases within the assets folder. Instead, I am interested in understanding how to dynamically reference and utilize these databases without the necessity of storing them in the assets directory. Additionally, I aim to implement a mechanism to download these databases initially and subsequently place them in a designated folder for further utilization. Could you provide assistance in accomplishing this task?

Describe the bug
I have this code:

const { rows } = await db.executeAsync(query, [
    id
]);

const data = [];

for (let i = 0; i < rows?.length; i++) {
  const item = rows.item(i);
  data.push({
    ...item,
    properties: JSON.parse(item.properties || '[]')
  })
}

return data;

Even though the item has many properties, when returning the data array the only property is properties. I tried with Object.assign and i received this error Exception in HostObject::set for prop 'properties': Unknown JSI ArrayBuffer to variant value conversion, received object instead of ArrayBuffer.

Versions:

• OS and version: Android 11
• op-sqlite version: latest
• RN version: 0.73.5
• New Architecture: No
  Reproducible example
  Create a minimal reproduction example, otherwise I won't take a look. I'm serious, no example = no solution.

Hi @ospfranco. Long time no see you! Glad to see that you are back :).
I've read the API, and seems to be a drop-in replacement to quick-sqlite. I'm right?
Did you check the new JSONB format from SQLite 3.45.0? Interested in a patch?

And the new numbers are amazing. I will read your code right now :). Thank you very mutch.

Description
When I attempt to use the op-sqlite to perform any operation, such as opening a connection to a db, it fails with this error:

 ERROR  Invariant Violation: TurboModuleRegistry.getEnforcing(...): 'OPSQLite' could not be found. Verify that a module by this name is registered in the native binary., js engine: hermes

This problem may be something that I am doing wrong, or a quirk of my dev env. It seems to be saying that something went wrong in the native binary.

Versions:

• MacOS Sonoma 14.3.1 on a MacBook Pro M1 Max (ARM)
• Simulator iOS version: 17.0
• op-sqlite version: 2.0.18
• RN version: 0.72.7

Reproducible example
When I import this code sample into my app.tsx file it causes the error.

import { open } from "@op-engineering/op-sqlite"
const db = open({ name: 'myDb.sqlite' })

Getting the following error on Expo Dev Client after reloading:

Error: Failed to install op-sqlite, the native initializer function does not exist. Are you trying to use OPSQLite from different JS Runtimes?, js engine: hermes

This issue has already been fixed in another library (react-native-mmkv) with the following PR:

https://github.com/mrousavy/react-native-mmkv/pull/602/files

Happy to make a PR for this library, or if you prefer to take care of it yourself, totally fine too.

package.json

{
  "dependencies": {
    ...
    "react-native": "^0.72.4",
    "@shopify/react-native-skia": "0.1.225",
    "@op-engineering/op-sqlite": "^1.0.14"
  }
}

Note: D:\Projects\work\Oone.World\fm-stransform\apps\international\node_modules\@shopify\react-native-skia\android\src\main\java\com\shopify\reactnative\skia\ViewScreenshotService.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
Note: Some input files use unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':app:mergeDebugNativeLibs'.
> A failure occurred while executing com.android.build.gradle.internal.tasks.MergeNativeLibsTask$MergeNativeLibsTaskWorkAction
   > 2 files found with path 'lib/arm64-v8a/liblog.so' from inputs:
      - D:\Projects\work\Oone.World\fm-stransform\apps\international\node_modules\@op-engineering\op-sqlite\android\build\intermediates\library_jni\debug\jni\arm64-v8a\liblog.so
      - D:\Projects\work\Oone.World\fm-stransform\apps\international\node_modules\@shopify\react-native-skia\android\build\intermediates\library_jni\debug\jni\arm64-v8a\liblog.so
     If you are using jniLibs and CMake IMPORTED targets, see
     https://developer.android.com/r/tools/jniLibs-vs-imported-targets

* Try:
> Run with --stacktrace option to get the stack trace.
> Run with --info or --debug option to get more log output.
> Run with --scan to get full insights.

* Get more help at https://help.gradle.org

First, of all, thank you for this awesome library!

Describe the bug
Sometimes, prepared statement reuse previous queries without replace parameters sent by bind, so the same query is runned.

Versions:

• OS and version: Android 12
• op-sqlite version: 2.0.13
• RN version: 0.73.2

Reproducible example

// Open database
const database = open({
  name: `../files/${databaseName}`,
});

// Create statement
const statement = database.prepareStatement(`
    select id_musica, titulo, letra, compositores, midias
      from musica
    where id_musica = ?
`);

// Bind and execute
statement.bind([123]);
console.log(statement.execute());

// Wait a random time, like close a screen and open again
// Bind and execute
statement.bind([321]);
console.log(statement.execute());

// do some iterations, sometimes the error doesn't occurs at first time
// but I don't noticed any pattern :/

I noticed that bind and execute both don't requires await. Is it expected?

I’m trying to implement the op-sqlite driver for Drizzle ORM, the biggest issue is that Drizzle requires a raw interface like better-sqlite3, it return rows as arrays instead of objects
https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md#rawtogglestate---this
https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/better-sqlite3/session.ts#L146

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Here is a proposal for an API that would allow to run reactive queries.

Besides your normal SQL string and argument, you can add two keys: tableName and rowID. Why? Because this two values allow you to subscribe to changes in the table using the native sqlite hook, plus the row (if ID is specified). With this it is possible to automatically re-run the query when an event is emitted with the matching criteria.

Then your query would be re-run. Not the most efficient way to get a diff between states but OP-sqlite already been as fast as it is, should cover most of the apps out there.

Samples

Here is an example of a reactive query for an entire table

// will trigger every time there is a change to the users table
db.reactiveExecute({
  query: 'SELECT * FROM Users',
  arguments: [],
  tables: ['Users'],
  callback: (users: any) => {
    console.log(users)
  }
})

Here is an example for a single row

// will trigger every time there is a change to the users table
db.reactiveExecute({
  query: 'SELECT * FROM Users',
  arguments: [],
  tables: ['Users'],
  callback: (user: any) => {
    console.log(user)
  }
})

With Mobx

Once you have a reactive query running, you can pair this with your favorite state management to get a fully reactive system

// on your state store
@observable
user: User

// later register the callback
db.reactiveExecute({
  query: 'SELECT * WHERE id = ? FROM Users',
  arguments: ['123'],
  tables: ['Users'],
  ids: ['123'],
  callback: (user: any) => {
    runInAction(() => {
      this.user = user
    }
  }
})

// finally your component is a normal mobx observer component
const myComp = observer(() => {
  const user = store.user;
// ..
  return (
    <Text>{user.name}</Text>
  )
}

This also means you can have your own complex logic in your query, joins, sorting, filtering, etc. Giving you a generic mechanism to add observability, allowing you to offload work to SQLite itself.

Let's do it

Unfortunately, this will require a big re-write of the library, so I will need people to sponsor this work

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Hello, thank you for this awesome project.

I would like to know if it is possible to use other collates.

SQLite has by default these three collates binary, nocase, rtrim. In my case, I would like to sort text that has accent. Android SQL has UNICODE and LOCALIZED support.

By the way, use default Android SQLite instead of op-sqlite would be just like a crazy decision due the amount of benefits using op-sqlite. :)

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Describe the bug

After upgrading op-sqlcipher from 1.0.8 to 1.0.18, compilation suddenly fails with:

A problem occurred configuring project ':op-engineering_op-sqlcipher'.
> Could not determine the dependencies of null.
   > Could not resolve all task dependencies for configuration ':op-engineering_op-sqlcipher:classpath'.
      > Could not find org.jetbrains.kotlin:kotlin-gradle-plugin:null.
        Searched in the following locations:
          - https://dl.google.com/dl/android/maven2/org/jetbrains/kotlin/kotlin-gradle-plugin/null/kotlin-gradle-plugin-null.pom
          - https://plugins.gradle.org/m2/org/jetbrains/kotlin/kotlin-gradle-plugin/null/kotlin-gradle-plugin-null.pom
        Required by:
            project :op-engineering_op-sqlcipher

Manually specifying a kotlinVersion in ./android/build.gradle fixes it.

buildscript {
    ext {
        kotlinVersion="1.8.10"
    }
}

It looks like this line (which didn't change between .8 and .18 from what I'm seeing) used to work, but now returns null.

I understand that this kind of gradle error can be a wack-a-mole, but putting this out here in case you have an idea what this is (or if it can help others).

Thank you for your time!

Versions:

• OS and version: Android, N/A (compilation error)
• op-sqlite version: 1.0.18 (latest at the time of writing)
• RN version: [e.g. 0.73.0] 0.72.4
• New Architecture: [Yes/No] No

Curious if theres any one else interested in having the ability to load sqlite extensions?

https://www.sqlite.org/loadext.html

(Personally im looking to experiment with https://github.com/vlcn-io/cr-sqlite)

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Describe the bug
When a db connection has a updateHook(), and you reload in metro the whole app crashes from a segfault.

Versions:

• OS and version: android 14
• op-sqlite version: latest 2.0.9
• RN version: 0.73.1 and 0.72.6

Reproducible example
https://github.com/Zoxive/op-sqlite/tree/UpdateHookCrashes

(Idk why cpp/sqlite3.h shows up in the diff, i tried creating the branch 2 times.. did it both times)

Basically just need a updateHook registered, and in metro if you press "R" to cause a reload it crashes.

Tested it on device, and in android simulator.

I get a very rough stacktrace in sentry:

libop-sqlite +0x06d44c <unknown> libop-sqlite +0x05d110 opsqlite::registerUpdateHook libop-sqlite +0x06d0ac <unknown> libhermes_executor +0x3e269c <unknown> libhermes_executor +0x3e25c4 std::__ndk1::function<T>::operator() libhermes_executor +0x3e2530 facebook::jsi::DecoratedHostFunction::operator() libhermes_executor +0x3e24b8 <unknown> libhermes_executor +0x3e2404 std::__ndk1::__invoke_void_return_wrapper<T>::__call<T> libhermes_executor +0x3e2340 <unknown> libhermes_executor +0x3e10d0 std::__ndk1::__function::__func<T>::operator()

It would be nice to have support for MacOS and Windows

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

I installed op-sqlite on this app: https://github.com/serenity-kit/Serenity/tree/main/apps/app

I follow the usual steps:

1. build simulator build with eas
2. download the build and install it on the simulator

Now receiving this error on the console and in the App:

When I use react-native-quick-sqlite it works: serenity-kit/Serenity@906e3da

I don't know what's different, but seems like eas is not including the package in the build? Could it be that react-native packages are only picked up if they are prefixed with react-native? Otherwise I'm not sure what could be different

Describe the bug
I getting back sqlite version 3.42 that do not support jsonb function

Versions:

• OS and version: iOS 17
• op-sqlite version: 5.0.1
• RN version: 0.73.6
• New Architecture: Yes

Reproducible example
I just open a new db and call

import {open} from '@op-engineering/op-sqlite'
    const dbname = `${ generateUUID()}-testdb.sqlite`

    const db = open({name: dbname})
    const version =  db.execute('select sqlite_version();')
    console.log('db name', dbname, 'version  ', version.rows)

on android symulator i see a correct version but on ios one i see :
sqlite version {"_array": [{"sqlite_version()": "3.42.0"}], "item": [Function anonymous], "length": 1}
LOG {"isSimulator": true, "os": "ios"}
ERROR [Error: Exception in HostFunction: [op-sqlite] SQL prepare statement error: no such function: jsonb]

Hi, is there any way to open an existing and populated database? I have the SQLite file inside of the iOS project, but neither using IOS_DOCUMENT_PATH nor IOS_LIBRARY_PATH worked since the file is part of the project.

Thanks!

I created a macos app using react-native-macos init and installed op-sqlite. When trying to run this project it is showing node_modules/@op-engineering/op-sqlite/cpp/utils.cpp:14:17 'get<bool, std::nullptr_t, bool, int, double, long, long long, std::string, ArrayBuffer>' is unavailable: introduced in macOS 10.13. I tried changing the deployment target, But that does not work.
Any workaround for this??

Libsql (https://github.com/tursodatabase/libsql) is an open contribution fork of sqlite. It adds a lot of features including server replication and vector search. Some people have expressed interest in sponsoring the work to get it working within op-sqlite, I would be willing to tackle the job but it looks like multi day work so I need to make time (= money) to reserve the time for it.

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

Thank you for your work. I do not see a option to ask question or any other forum so i decide to create and issue.
Could you guide me in one challenge. How to compile and load native extension to op-sql ?
I keen to use cr-sql https://github.com/vlcn-io/cr-sqlite and vss https://github.com/asg017/sqlite-vss extensions
So it is a 3 different extension that should be loaded.
I havent figure out how to compile and select a proper binaries on react native
Thanks in advance for your answer

Describe the bug
Hi @ospfranco, something is interfering with react-native-keys library and after building on [email protected] I get the following error:

dlopen failed: library "libcrypto.so" not found: needed by /data/app/~~lJuXe3_5lJkjj8LthPNdYA==/com.awesomeproject-QydFPfNIqGQkdQORPY-MSQ==/base.apk!/lib/arm64-v8a/libreact-native-keys.so in namespace clns-6

op-sqlite settings:

  "op-sqlite": {
    "sqlcipher": false,
    "crsqlite": false,
    "performanceMode": "2",
    "iosSqlite": false
  }

The moment I remove op-slite everything is back to normal, by removing react-native-keys and keeping only op-sqlite everything is back to normal too. Weird things 😅

Do you have any ideas what is happening? I believe at some point it strips out libcrypto no matter if sqlcipher is true or false.

Please note that in [email protected] everything is working fine, it only happens on 0.74.

Versions:

• OS and version: Android 14
• op-sqlite version: 6.0.1
• RN version: 0.74.0
• New Architecture: No

Reproducible example
Please build using Android Studio, not cli.

https://github.com/efstathiosntonas/0_74-react-native-keys-op-sqlit

What are the differences between this and the react-native-sqlite-storage package?
I saw that react-native-sqlite-storage hasn't been updated for a while but are there any
a) security
b) performance
c) etc.
differences that are important?

Library needs to support bridgeless since starting on 0.75 the default template will move to new arch + bridgeless. I need however to make time and follow up on the process of adjusting the lib.

Related facebook/react-native#43204

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

SQlite support an in memory version of the DB where data is not persisted https://www.sqlite.org/inmemorydb.html

This is useful for using Sqlite as a sync state management solution where data is not supposed to be persisted. E.g. Johannes is working livestore and the new version is using Sqlite in memory for fast reactive queries and a second connection to to persist the data - here an older video of the concept https://www.youtube.com/watch?v=qHSI5rxTp_Q

Due this always concatinating

it is not possible to use op-sqlite with in memory mode.

Btw would also be interesting to have benchmarks in comparison how much faster this is.

Hi @ospfranco

Many thanks for this new library, it looks very interesting.

I am using react-native-quick-sqlite with TypeORM which makes the DX a lot better.

Can I use their docs, patch, and the mentioned TypeORM driver too, or is that not ready yet?

Thanks

Hi hello,

Currently i trying to embedded op-sqlite library into the project that i'm working on in react native, typescript using expo format.
Which after npm install upon running via iOS, i got an error

ERROR Error: Base module not found. Maybe try rebuilding the app., js engine: hermes
ERROR Invariant Violation: "main" has not been registered. This can happen if:

React version: 18.2.0
Expo: 50.0.15

DB Table Value

Select Query Result

Did this bug ? Null will replaced into 0 ?
If I want to get null value instead 0 ?

Packages :

"@op-engineering/op-sqlcipher": "^1.0.15",
"react": "18.2.0",
"react-native": "0.72.6",