tndrle / node-sqlite3-wasm Goto Github PK

It would be nice to have d.ts file (maybe adapted sqlite3.d.ts) to omit warnings in VSC.
Or maybe @types/node-sqlite3-wasm ?

It seems that large-ish blobs (bigger than ~64kb) can't be put into a table, I'm not sure where the error is, but this should reproduce the error:

import * as Sqlite3Wasm from 'node-sqlite3-wasm';
const Database = Sqlite3Wasm.default.Database;

const SAMPLE = new TextEncoder ().encode ( 'a'.repeat ( 100_000 ) );

const wdb = new Database ( ':memory:' );

await wdb.exec ( `CREATE TABLE example ( id INTEGER PRIMARY KEY, data BLOB )` );
await wdb.prepare ( `INSERT INTO example VALUES( ?, ? )` ).run ([ 1, SAMPLE ]);

const read = await wdb.all ( `SELECT data FROM example WHERE id=1` );

await wdb.exec ( `DELETE FROM example WHERE id=1` );

And error is this:

wasm://wasm/0039d32e:1


RuntimeError: table index is out of bounds
    at wasm://wasm/0039d32e:wasm-function[285]:0x5341e
    at wasm://wasm/0039d32e:wasm-function[411]:0x79401
    at wasm://wasm/0039d32e:wasm-function[784]:0xc1ddc
    at ccall (/Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:29808)
    at /Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:30193
    at Statement._bindValue (/Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:12466)
    at Statement._bindArray (/Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:11500)
    at Statement._bind (/Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:10208)
    at Statement.run (/Users/fabio/Code/fabiospampinato/tiny-sqlite3/node_modules/node-sqlite3-wasm/dist/node-sqlite3-wasm.js:8:9263)
    at file:///Users/fabio/Code/fabiospampinato/tiny-sqlite3/tasks/blob.js:61:60

Node.js v18.12.0

Could this be fixed?

I would like to use it with TypeScript. Is there ESM version (or some other way to use it with TS)

Hello, just to make sure - does the SQLite3 needs to be installed on target PC? As I understand, the goal of this library is to make it unnecessary, hence the WASM module. But just in case, I'm asking. And are all pragmas supported? WAL mode etc?

Thank you

It would be nice to have a .iterate() method that returns the rows one at a time, without loading the whole result set into memory.

For example,

for (const val of db.iterate("SELECT * FROM entries ORDER BY rowid")) { ... }
// OR
const stmt = db.prepare("SELECT * FROM entries ORDER BY rowid")
for (const val of stmt.iterate()) { ... }

Some example tests: https://github.com/WiseLibs/better-sqlite3/blob/ae23e690b02c00d075d543c66ae7e26c98c46f74/test/23.statement.iterate.js

Great work! Solves so many headaches with electron!

Have you any plans to add support for returning an array of arrays (rather than objects) and column metadata ?

better-sqlite3 does this via a raw method to enable it, and then a columns method to fetch the column metadata.

https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#rawtogglestate---this

My use case for the array of arrays is dealing with overlapping column names, a contrived example is:

select 'short' as 'long', 'test' as 'long';

The meta data is to be able to handle better typing in the returned results.

Some other node based database drivers will return it in one call rather than needing two like better does... but I have not dug into see if it is something specific to SQLite that requires the two methods or just their API approach.

Hi,

First of all thank you for this library - we had serious issues with better-sqlite3 on older linux machines and we have decided to use this one in Publii.

Unfortunately we have discovered one important issue on Windows 11 - it seems that your library doesn't properly close the database connection. In result our app is unable to remove website folder while restoring website from backup - the db.sqlite file cannot be removed (even by rimraf). The EPERM error is returned. File is removed after closing the app so definitely it is connected with some unclosed handles to the db.sqlite file.

The problem occurs only on Windows - we didn't observed it on mac OS or Linux. We have even tried to test it outside protected directories (on desktop) and with EV-signed app version, but the problem still occurs, so most probably it is not caused by Windows itself. Also app based on better-sqlite3 properly closes the DB connection and there is no issue with removing db.sqlite file.

I have even tried to create a custom build and use close() instead of close_v2() as I have seen that better-sqlite3 uses close(), but without success, so there must be some additional operations to do during closing the connection with DB.

Thanks for your great work!

I'm writing a dialect for Kysely, that needs get lastInsertRowid, changes and rows when the sql have returing, but in my current(v0.8.0) try:

import sqlite from "node-sqlite3-wasm";

const db = new sqlite.Database(':memory:')

db.run('create table test1(a INTEGER, b TEXT)')

console.log(db.run('insert into test1(a, b) values (1, \'test\') returning *'))
// { changes: 0, lastInsertRowid: 1 }

console.log(db.exec('insert into test1(a, b) values (1, \'test\') returning *'))
// undefined

console.log(db.prepare('insert into test1(a, b) values (1, \'test\') returning *').all())
// [{ a: 1, b: 'test' }], but no changes and lastInsertRowid

maybe expose a new API or just expose sqlite.changes()?

Hi,

We have got another report about issue which didn't happen while using better-sqlite3 native module in our Electron app.

User reports that he gets often the following error:

A JavaScript error occurred in the main process
Uncaught Exception:
SQlite3Error: database is locked
at Database._handleError

It disappears when Dropbox synchronization is disabled.

It is possible that your library is more sensitive for files locked by Dropbox sync process than native SQLite3 module?

It would be great if the SQLite build could include the FTS5 extension to support use cases that need fulltext search support.
It should be enough include -DSQLITE_ENABLE_FTS5 in SQLITE_FLAGS

Hi, that pretty comfortable to use knex as query builder for sql databases
seems default sqlite3 driver is not compatible with node-sqlite3-wasm
is there any way to connect node-sqlite3-wasm with knex?

I'm creating an electron App then i want to use node-sqlite3-wasm
I was using better-sqlite3 before, to switch to node-sqlite3-wasm, I just changed the lines:

const Database = require('better-sqlite3');
const db = new Database('foobar.db', { verbose: console.log });

to

const { Database } = require("node-sqlite3-wasm");
const db = new Database('breach.db', { fileMustExist: true });

of course I also changed the request methods like db.prepare() to equivalents...

but when i run my app, i get this error :

failed to compile wasm module: RangeError: WebAssembly.Compile is disallowed on the main thread, if the buffer size is larger than 4KB. Use WebAssembly.compile, or compile on a worker thread.
instantiateSync @ VM167 node-sqlite3-wasm.js:8
VM161 renderer_init:2 Unable to load preload script: /Path/to/my-app/src/preload.js
(anonymous) @ VM161 renderer_init:2
VM161 renderer_init:2 RangeError: WebAssembly.Compile is disallowed on the main thread, if the buffer size is larger than 4KB. Use WebAssembly.compile, or compile on a worker thread.
    at instantiateSync (VM167 node-sqlite3-wasm.js:8:18906)
    at createWasm (VM167 node-sqlite3-wasm.js:8:19965)
    at VM167 node-sqlite3-wasm.js:8:33718
    at Object.<anonymous> (VM167 node-sqlite3-wasm.js:15:5)
    at Object.<anonymous> (VM167 node-sqlite3-wasm.js:23:3)
    at Module._compile (VM126 loader:1269:14)
    at Module._extensions..js (VM126 loader:1324:10)
    at Module.load (VM126 loader:1124:32)
    at Module._load (VM126 loader:965:12)
    at f._load (VM158 asar_bundle:2:13330)

i included the module from preload.js and serve it to index.html via contextBridge.exposeInMainWorld

can sameone tell me how to solve this problem please ?

Thank you! Amazing work!
No one has done this until now, and using SQLite with node was a headache.

I was wondering if you could add official support for the Drizzle ORM.

Drizzle is a new, popular yet simple ORM
https://orm.drizzle.team/

Your API is very similar to better-sqlite3, so it may be already fully supported.
I am sure that if you mention that many people will know and support your work.

Good Luck :)

I use esbuild to build main.ts to main.js, electron with "type":"modules" in package.json ,esbuild format is "esm"

Then run electron will be error:
Error: Dynamic require of "node:path" is not supported

Maybe error line is here