sqlite: bind ArrayBuffer

[mike-git374]

Problem

You can bind Buffer or TypedArray (added in #56384) but ArrayBuffer doesn't work:

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');

db.exec('create table t (c)');

const insert = db.prepare('insert into t values (?)');

insert.run(new Uint8Array([ 1, 2, 3 ]));
insert.run(new Uint16Array([ 4, 5, 6 ]));
insert.run(new Uint8Array([ 7, 8, 9 ]).buffer); // ArrayBuffer
insert.run(Buffer.from([ 10, 11, 12 ]));

console.log(db.prepare('select * from t').all());

// [
// { c: Uint8Array(3) [ 1, 2, 3 ] },
// { c: Uint8Array(6) [ 4, 0, 5, 0, 6, 0 ] },
// { c: null },
// { c: Uint8Array(3) [ 10, 11, 12 ] }
// ]

Related: Boolean should also be allowed to bind, see SQLite bind booleans

Proposal

Allow binding ArrayBuffer

insert.run(arrayBuffer);

Alternatives

Convert to Uint8Array or Buffer before binding, but this should not be required:

insert.run(Buffer.from(arrayBuffer));

insert.run(new Uint8Array(arrayBuffer));

[geeksilva97]

I don't know if that's possible.

ArrayBuffer is the underlying sequence of bytes. To get the valuues (needed for binding) of that sequence we need to add a view on top of it.

[mike-git374]

These primitive JS values like ArrayBuffer and Boolean with clear SQLite equivalents should not be throwing errors or silently failing, the driver should convert these as needed

[geeksilva97]

These primitive JS values like ArrayBuffer and Boolean with clear SQLite equivalents should not be throwing errors or silently failing, the driver should convert these as needed

I don't get it. To which of the views should an ArrayBuffer be converted to?

[mike-git374]

Uint8Array

[geeksilva97]

Uint8Array

why that one?

[mike-git374]

SQLite BLOB is a series of bytes, ArrayBuffer is also a series of bytes, if you need an ArrayBufferView you can take new Uint8Array(arrayBuffer) which is similar to Buffer.from(arrayBuffer). The SQLite BLOB should end up with the same series of bytes

[geeksilva97]

I cannot see how this would be helpful since, as you pointed, it's a matter of passing a view, which can be easily done as you pointed in the examples.

[mike-git374]

Why is it silently failing when it should at least throw an error? Why throw an error when the driver could handle the ArrayBuffer case?

[geeksilva97]

Why is it silently failing when it should at least throw an error? Why throw an error when the driver could handle the ArrayBuffer case?

Throwing an error sounds reasonable to me. I'll work on this.

[mike-git374]

I had a library passing an ArrayBuffer which I was inserting into SQLite table, I figured directly handling the ArrayBuffer in the sqlite driver might be more efficient than creating a garbage collected Uint8Array. I ended up needing a subarray of that ArrayBuffer so currently I am doing the Uint8Array, so this issue is not directly affecting me currently. But if you are interested, I consider this Boolean binding issue even more important, the driver should definitely be able to accept Boolean values: #57862