'use strict';
const { skipIfSQLiteMissing } = require('../common');
skipIfSQLiteMissing();
const { Database } = require('node:sqlite');
const { suite, test } = require('node:test');
suite('data binding and mapping', () => {
test('supported data types', (t) => {
const u8a = new TextEncoder().encode('a☃b☃c');
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(`
CREATE TABLE types(
key INTEGER PRIMARY KEY,
int INTEGER,
double REAL,
text TEXT,
buf BLOB
) STRICT;
`);
t.assert.strictEqual(setup, undefined);
const stmt = db.prepare('INSERT INTO types (key, int, double, text, buf) ' +
'VALUES (?, ?, ?, ?, ?)');
t.assert.deepStrictEqual(
stmt.run(1, 42, 3.14159, 'foo', u8a),
{ changes: 1, lastInsertRowid: 1 },
);
t.assert.deepStrictEqual(
stmt.run(2, null, null, null, null),
{ changes: 1, lastInsertRowid: 2 }
);
t.assert.deepStrictEqual(
stmt.run(3, Number(8), Number(2.718), String('bar'), Buffer.from('x☃y☃')),
{ changes: 1, lastInsertRowid: 3 },
);
t.assert.deepStrictEqual(
stmt.run(4, 99n, 0xf, '', new Uint8Array()),
{ changes: 1, lastInsertRowid: 4 },
);
const query = db.prepare('SELECT * FROM types WHERE key = ?');
t.assert.deepStrictEqual(query.get(1), {
__proto__: null,
key: 1,
int: 42,
double: 3.14159,
text: 'foo',
buf: u8a,
});
t.assert.deepStrictEqual(query.get(2), {
__proto__: null,
key: 2,
int: null,
double: null,
text: null,
buf: null,
});
t.assert.deepStrictEqual(query.get(3), {
__proto__: null,
key: 3,
int: 8,
double: 2.718,
text: 'bar',
buf: new TextEncoder().encode('x☃y☃'),
});
t.assert.deepStrictEqual(query.get(4), {
__proto__: null,
key: 4,
int: 99,
double: 0xf,
text: '',
buf: new Uint8Array(),
});
t.assert.deepStrictEqual(
stmt.run(5, true, false, true, null),
{ changes: 1, lastInsertRowid: 5 }
);
t.assert.deepStrictEqual(
query.get(5),
{ __proto__: null, key: 5, int: 1, double: 0, text: '1', buf: null }
);
t.assert.deepStrictEqual(
stmt.run(6, undefined, undefined, undefined, undefined),
{ changes: 1, lastInsertRowid: 6 }
);
t.assert.deepStrictEqual(
query.get(6),
{ __proto__: null, key: 6, int: null, double: null, text: null, buf: null }
);
});
test('undefined is bound as NULL', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE types(key INTEGER PRIMARY KEY, val INTEGER) STRICT;'
);
t.assert.strictEqual(setup, undefined);
const stmt = db.prepare('INSERT INTO types (key, val) VALUES ($k, $v)');
const query = db.prepare('SELECT * FROM types WHERE key = ?');
// An explicit `undefined` and an omitted named parameter are equivalent.
t.assert.deepStrictEqual(
stmt.run({ k: 1, v: undefined }),
{ changes: 1, lastInsertRowid: 1 }
);
t.assert.deepStrictEqual(
stmt.run({ k: 2 }),
{ changes: 1, lastInsertRowid: 2 }
);
t.assert.deepStrictEqual(
query.get(1),
{ __proto__: null, key: 1, val: null }
);
t.assert.deepStrictEqual(
query.get(2),
{ __proto__: null, key: 2, val: null }
);
t.assert.deepStrictEqual(
db.prepare('SELECT ? AS a').get(undefined),
{ __proto__: null, a: null }
);
});
test('undefined is not treated as the named parameters argument', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
// `undefined` is not an object, so it is bound as an anonymous parameter
// rather than being treated as the named parameters argument.
t.assert.throws(() => {
db.prepare('SELECT $k AS a').get(undefined);
}, {
code: 'ERR_SQLITE_ERROR',
message: /column index out of range/,
});
t.assert.throws(() => {
db.prepare('SELECT ? AS a').get(1, undefined);
}, {
code: 'ERR_SQLITE_ERROR',
message: /column index out of range/,
});
});
test('large strings are bound correctly', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE data(key INTEGER PRIMARY KEY, text TEXT) STRICT;'
);
t.assert.strictEqual(setup, undefined);
t.assert.deepStrictEqual(
db.prepare('INSERT INTO data (key, text) VALUES (?, ?)').run(1, ''),
{ changes: 1, lastInsertRowid: 1 },
);
const update = db.prepare('UPDATE data SET text = ? WHERE key = 1');
// > 1024 bytes so `Utf8Value` uses heap storage internally.
const largeAscii = 'a'.repeat(8 * 1024);
// Force a non-one-byte string path through UTF-8 conversion.
const largeUnicode = '\u2603'.repeat(2048);
const res = update.run(largeAscii);
t.assert.strictEqual(res.changes, 1);
t.assert.strictEqual(
db.prepare('SELECT text FROM data WHERE key = 1').get().text,
largeAscii,
);
t.assert.strictEqual(update.run(largeUnicode).changes, 1);
t.assert.strictEqual(
db.prepare('SELECT text FROM data WHERE key = 1').get().text,
largeUnicode,
);
});
test('unsupported data types', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE types(key INTEGER PRIMARY KEY, val INTEGER) STRICT;'
);
t.assert.strictEqual(setup, undefined);
[
() => {},
Symbol(),
/foo/,
Promise.resolve(),
new Map(),
new Set(),
].forEach((val) => {
t.assert.throws(() => {
db.prepare('INSERT INTO types (key, val) VALUES (?, ?)').run(1, val);
}, {
code: 'ERR_INVALID_ARG_TYPE',
message: /Provided value cannot be bound to SQLite parameter 2/,
});
});
t.assert.throws(() => {
const stmt = db.prepare('INSERT INTO types (key, val) VALUES ($k, $v)');
stmt.run({ $k: 1, $v: () => {} });
}, {
code: 'ERR_INVALID_ARG_TYPE',
message: /Provided value cannot be bound to SQLite parameter 2/,
});
});
test('throws when binding a BigInt that is too large', (t) => {
const max = 9223372036854775807n; // Largest 64-bit signed integer value.
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE types(key INTEGER PRIMARY KEY, val INTEGER) STRICT;'
);
t.assert.strictEqual(setup, undefined);
const stmt = db.prepare('INSERT INTO types (key, val) VALUES (?, ?)');
t.assert.deepStrictEqual(
stmt.run(1, max),
{ changes: 1, lastInsertRowid: 1 },
);
t.assert.throws(() => {
stmt.run(1, max + 1n);
}, {
code: 'ERR_INVALID_ARG_VALUE',
message: /BigInt value is too large to bind/,
});
});
test('statements are unbound on each call', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE data(key INTEGER PRIMARY KEY, val INTEGER) STRICT;'
);
t.assert.strictEqual(setup, undefined);
const stmt = db.prepare('INSERT INTO data (key, val) VALUES (?, ?)');
t.assert.deepStrictEqual(
stmt.run(1, 5),
{ changes: 1, lastInsertRowid: 1 },
);
t.assert.deepStrictEqual(
stmt.run(),
{ changes: 1, lastInsertRowid: 2 },
);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM data ORDER BY key').all(),
[{ __proto__: null, key: 1, val: 5 }, { __proto__: null, key: 2, val: null }],
);
});
test('omitted named parameters are unbound on each call', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(
'CREATE TABLE data(key INTEGER PRIMARY KEY, val INTEGER) STRICT;'
);
t.assert.strictEqual(setup, undefined);
const named = db.prepare('INSERT INTO data (key, val) VALUES ($k, $v)');
const mixed = db.prepare('INSERT INTO data (key, val) VALUES ($k, ?)');
named.run({ k: 1, v: 5 });
named.run({ k: 2 });
named.run({ k: 3, v: 6 });
named.run({ k: 4, $k: 4 });
mixed.run({ k: 5 }, 7);
mixed.run({ k: 6 });
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM data ORDER BY key').all(),
[
{ __proto__: null, key: 1, val: 5 },
{ __proto__: null, key: 2, val: null },
{ __proto__: null, key: 3, val: 6 },
{ __proto__: null, key: 4, val: null },
{ __proto__: null, key: 5, val: 7 },
{ __proto__: null, key: 6, val: null },
],
);
});
});