better-sqlite3 tries to load regex0.so.so instead of regex0.so on debian

[jonathanweibel]

https://github.com/asg017/sqlite-regex/blob/7fb4ab9dfbe8c136f7089c87524743e85a23cc13/npm/sqlite-regex/src/index.js#L43

Thank you for this very useful module which allows us to manage the sqlite-regex extension using Node.js.

I encountered an issue while running e2e tests for a NestJS application in a debian CI runner (docker image node:18).

Our application relies on @mikro-orm/better-sqlite@5.7.0. Under the hood, this is better-sqlite3@8.5.0. When running conn.loadExtension(getLoadablePath()) to add sqlite-regex to the sqlite db, the shared lib file is not found because sqlite tries to load regex0.so.so.

I coded a workaround in my project to remove the extension when platform is 'linux', then it works. Would it be suitable for all linux systems? If so, I can create a PR to contribute to the project.

[asg017]

@jonathanweibel thanks for reporting!

So I'm guessing getLoadablePath() returns something like:

/home/user/path/to/regex0.so

If so, if you hardcode that path to .loadExtension("/home/user/path/to/regex0.so"), will it still throw the same regex0.so.so error? And if you run .loadExtension("/home/user/path/to/regex0"), does that work?

I've seen the .so.so error when the extension itself fails to load. SQLite will try a few different paths when loading an extension, and sometimes if regex0.so fails, it'll try regex0.so.so (adding the .so itself based on the current OS).

Here's a test I pulled together:

Dockerfile:

FROM node:18

WORKDIR /app
RUN npm init -y
RUN npm install better-sqlite3 sqlite-regex

COPY test.mjs /app/test.mjs

test.mjs:

import Database from "better-sqlite3";
import {getLoadablePath} from "sqlite-regex";
console.log(getLoadablePath());
const db = new Database(":memory:");
db.loadExtension(getLoadablePath());

Then I built with:

docker build . -t sqlite-regex-node
docker run --rm -it sqlite-regex-node node /app/test.mjs

And it appears to work, logging out /app/node_modules/sqlite-regex-linux-x64/lib/regex0.so.

Does that script match what your code looks like?

[jonathanweibel]

@asg017 Thanks for your detailed answer! Sorry for the delay.

tl;dr

I tested your code on my CI environment and I confirm it works, so the wrong path I got was not related to sqlite-regex and we can close this issue.

More details

In test.mjs, to be sure the extension is actually loaded, I added a few lines to test the regex function:

import Database from "better-sqlite3";
import { getLoadablePath } from "sqlite-regex";
console.log(getLoadablePath());
const db = new Database(":memory:", { verbose: console.log });
db.loadExtension(getLoadablePath());

db.prepare("CREATE TABLE users(name VARCHAR)").run();
db.prepare("INSERT INTO users(name) VALUES('foo')").run();

const row1 = db.prepare("SELECT name FROM users").get();
console.log(row1.name);

const row2 = db
  .prepare("SELECT name FROM users WHERE name REGEXP ?")
  .get("foo");
console.log(row2.name);

The full output is:

/workspace/node_modules/sqlite-regex-linux-x64/lib/regex0.so
CREATE TABLE users(name VARCHAR)
INSERT INTO users(name) VALUES('foo')
SELECT name FROM users
foo
SELECT name FROM users WHERE name REGEXP 'foo'
foo

I could reproduce this behaviour both on my MacOS m2 machine and on the node:18 CI runner. So the wrong file extension I got was probably a side effect, I will investigate further. Thank you for your support, I close this issue.