Compilation Clashes

If you have other packages that are dependent on sqlite (specially if they compile it from source) you will have issues.

Some of the known offenders are:

expo-updates
expo-sqlite
cozodb

You will face a duplicated symbols and/or header definitions, since each of the package will try to compile sqlite from sources. Even if they manage to compile, they might compile sqlite with different compilation flags and you might face threading errors.

Unfortunately, there is no easy 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 Gotchas

Strictness

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.

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).

Foreign constraints

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:

db.execute('PRAGMA foreign_keys = true')

Error codes

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.

Other Quirks

See the full list of SQLite quirks.

HostObjects Quirks

One of the reasons OP SQLite is so fast compared to the alternatives, is that it can return HostObjects via the executeWithHostObjects API, basically C++ classes exposed to the JS context. 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.

Closing a connection

If you use react-native-restart or your app has a non-standard lifecycle it’s important that you call db.close before to avoid crashes and memory leaks.