By Oscar Franco
Twitter • YouTube • [Email](mailto:[email protected]?subject=I%20need%20a%20Freelancer)
SQLite being a C library and React Native being a JS framework with native parts some times create conflicts. Here are the most common problems.
JavaScript represents every number internally as a double. This means you can only have integers represented up to 2^53 (Number.MAX_SAFE_INTEGER). Although sqlite supports long long (2^64) the numbers will be truncated when you query a value bigger than what a JS number can represent. If you need to store larger numbers you should use a bigint, however, such a type is not natively supported by sqlite, so you will have to serialize and deserialize from/to bigint when you do your queries:
// When inserting, convert bigint into a string
await db.execute("INSERT INTO NumbersTable VALUES (?)", [bigint("123").toString()]);
// When retrieving, convert string into bigint
let res = await db.execute("SELECT * FROM NumbersTable");
let myBigint = BigInt(res.rows[0].number);
If you have other packages that are dependent on sqlite you will have issues.
Some of the known offenders are:
expo-updatesexpo-sqlitecozodbYou will face a duplicated symbols and/or header definitions, since each of the packages will try to compile sqlite from sources or link it on build time. Even if they manage to compile, they might compile sqlite with different compilation flags and you might face runtime errors.
For expo-updates and expo-sqlite you can use the iOS embedded version of sqlite (they both use the OS version, that’s why they clash when op-sqlite compiles sqlite from sources), in your package.json use the following flags:
"op-sqlite": {
"iosSqlite": true
}
This means however, you will be used whatever version the phone is running, which might be outdated and it also does not support extension loading. There is no way around this.
For other conflicts and compilation errors there is no easy solution (Is there a solution?). You need to get rid of the double compilation by hand, either by patching the compilation of each package so that it still builds or removing the dependency on the package.
On Android you might be able to get away by just using a pickFirst strategy (here is an article on how to do that). On iOS depending on the build system you might be able to patch it via a post-build hook, something like:
pre_install do |installer|
installer.pod_targets.each do |pod|
if pod.name.eql?('expo-updates')
# Modify the configuration of the pod so it doesn't depend on the sqlite pod
end
end
end
SQLite by default does not strictly check for types. if you want true type safety when you declare your tables you need to use the STRICT keyword.
await db.execute('CREATE TABLE Test (id INT PRIMARY KEY, name TEXT) STRICT;');
If you don't set it, SQLite will happily write whatever you insert in your table, independetly of the declared type (it will try to cast it though, e.g. a "1" string might be turned to a 1 int).
When SQLite evaluates your query and you have forgein key constraints, it keeps track of the satisfied relations via a counter. Once your statement finishes executing and the counter is not 0, it throws a foreign key constraint failed error. Unfortunately, this simple design means it is impossible to catch which foreign constraint is failed and you will receive a generic error. Nothing op-sqlite can do about it, it's a design flaw in SQLite.
In order to catch foreign key errors, you also need to execute the pragma when you open your connection:
await db.execute('PRAGMA foreign_keys = true')
Sometimes you might be using valid SQL syntax for other engines or you might be doing something else wrong. The errors returned by op-sqlite contain the raw error code returned by SQLite and you should check the reference for more detailed information.
See the full list of SQLite quirks.
op-sqlite can return HostObjects via the executeWithHostObjects API, basically C++ instances exposed to the JS context. They are super fast to create at the cost of runtime access. However, this means some operations won't work.
You can write single properties with scalars, for example:
let results = await db.executeWithHostObjects("SELECT * FROM USER;");
results._array[0].newProp = "myNewProp"
As for trying to assign any object to a property, unfortunately, won't work.
results._array[0].newProp = { foo: "bar" }
On the C++ side properties need to be stored and cast to C++ types. Mostly to prevent race conditions and de-allocation between the JS Runtime and C++. Basically, not a bug, but rather a limitation of HostObjects.
You might want to try to create a completely new pure JS object to achieve this:
let newUser = {...{}, ...results._array[0], newProp: { foo: "bar" }}
Sometimes {...item, blah: 'foo'} gets transpiled to Object.assign(item, {blah: 'foo'}, so that’s why you might need to use the quirky ...{} at the beginning.
If you use react-native-restart or your app has a non-standard lifecycle and “reloads” itself at some point during runtime. It’s important that you call db.close() before to avoid crashes and memory leaks.