'use strict';
const { spawnPromisified, skipIfSQLiteMissing } = require('../common');
skipIfSQLiteMissing();
const { Database, constants } = require('node:sqlite');
const { suite, test } = require('node:test');
const { pathToFileURL } = require('node:url');
const { nextDb } = require('../sqlite/next-db.js');
suite('accessing the node:sqlite module', () => {
test('cannot be accessed without the node: scheme', (t) => {
t.assert.throws(() => {
require('sqlite');
}, {
code: 'MODULE_NOT_FOUND',
message: /Cannot find module 'sqlite'/,
});
});
test('can be disabled with --no-experimental-sqlite flag', async (t) => {
const {
stdout,
stderr,
code,
signal,
} = await spawnPromisified(process.execPath, [
'--no-experimental-sqlite',
'-e',
'require("node:sqlite")',
]);
t.assert.strictEqual(stdout, '');
t.assert.match(stderr, /No such built-in module: node:sqlite/);
t.assert.notStrictEqual(code, 0);
t.assert.strictEqual(signal, null);
});
});
test('ERR_SQLITE_ERROR is thrown for errors originating from SQLite', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
const setup = db.exec(`
CREATE TABLE test(
key INTEGER PRIMARY KEY
) STRICT;
`);
t.assert.strictEqual(setup, undefined);
const stmt = db.prepare('INSERT INTO test (key) VALUES (?)');
t.assert.deepStrictEqual(stmt.run(1), { changes: 1, lastInsertRowid: 1 });
t.assert.throws(() => {
stmt.run(1);
}, {
code: 'ERR_SQLITE_ERROR',
message: 'UNIQUE constraint failed: test.key',
errcode: 1555,
errstr: 'constraint failed',
});
});
test('in-memory databases are supported', (t) => {
const db1 = new Database(':memory:');
const db2 = new Database(':memory:');
const setup1 = db1.exec(`
CREATE TABLE data(key INTEGER PRIMARY KEY);
INSERT INTO data (key) VALUES (1);
`);
const setup2 = db2.exec(`
CREATE TABLE data(key INTEGER PRIMARY KEY);
INSERT INTO data (key) VALUES (1);
`);
t.assert.strictEqual(setup1, undefined);
t.assert.strictEqual(setup2, undefined);
t.assert.deepStrictEqual(
db1.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
t.assert.deepStrictEqual(
db2.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
});
test('sqlite constants are defined', (t) => {
t.assert.strictEqual(constants.SQLITE_CHANGESET_OMIT, 0);
t.assert.strictEqual(constants.SQLITE_CHANGESET_REPLACE, 1);
t.assert.strictEqual(constants.SQLITE_CHANGESET_ABORT, 2);
});
test('PRAGMAs are supported', (t) => {
// WAL journal mode requires an on-disk database.
const db = new Database(nextDb());
t.after(() => { db.close(); });
t.assert.deepStrictEqual(
db.prepare('PRAGMA journal_mode = WAL').get(),
{ __proto__: null, journal_mode: 'wal' },
);
t.assert.deepStrictEqual(
db.prepare('PRAGMA journal_mode').get(),
{ __proto__: null, journal_mode: 'wal' },
);
});
test('Buffer is supported as the database path', (t) => {
const db = new Database(Buffer.from(nextDb()));
t.after(() => { db.close(); });
db.exec(`
CREATE TABLE data(key INTEGER PRIMARY KEY);
INSERT INTO data (key) VALUES (1);
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
});
test('URL is supported as the database path', (t) => {
const url = pathToFileURL(nextDb());
const db = new Database(url);
t.after(() => { db.close(); });
db.exec(`
CREATE TABLE data(key INTEGER PRIMARY KEY);
INSERT INTO data (key) VALUES (1);
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
});
suite('URI query params', () => {
const baseDbPath = nextDb();
const baseDb = new Database(baseDbPath);
baseDb.exec(`
CREATE TABLE data(key INTEGER PRIMARY KEY);
INSERT INTO data (key) VALUES (1);
`);
baseDb.close();
test('query params are supported with URL objects', (t) => {
const url = pathToFileURL(baseDbPath);
url.searchParams.set('mode', 'ro');
const readOnlyDB = new Database(url);
t.after(() => { readOnlyDB.close(); });
t.assert.deepStrictEqual(
readOnlyDB.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
t.assert.throws(() => {
readOnlyDB.exec('INSERT INTO data (key) VALUES (1);');
}, {
code: 'ERR_SQLITE_ERROR',
message: 'attempt to write a readonly database',
});
});
test('query params are supported with string', (t) => {
const url = pathToFileURL(baseDbPath);
url.searchParams.set('mode', 'ro');
// Ensures a valid URI passed as a string is supported
const readOnlyDB = new Database(url.toString());
t.after(() => { readOnlyDB.close(); });
t.assert.deepStrictEqual(
readOnlyDB.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
t.assert.throws(() => {
readOnlyDB.exec('INSERT INTO data (key) VALUES (1);');
}, {
code: 'ERR_SQLITE_ERROR',
message: 'attempt to write a readonly database',
});
});
test('query params are supported with Buffer', (t) => {
const url = pathToFileURL(baseDbPath);
url.searchParams.set('mode', 'ro');
// Ensures a valid URI passed as a Buffer is supported
const readOnlyDB = new Database(Buffer.from(url.toString()));
t.after(() => { readOnlyDB.close(); });
t.assert.deepStrictEqual(
readOnlyDB.prepare('SELECT * FROM data').all(),
[{ __proto__: null, key: 1 }]
);
t.assert.throws(() => {
readOnlyDB.exec('INSERT INTO data (key) VALUES (1);');
}, {
code: 'ERR_SQLITE_ERROR',
message: 'attempt to write a readonly database',
});
});
});
suite('SQL APIs enabled at build time', () => {
test('math functions are enabled', (t) => {
const db = new Database(':memory:');
t.assert.deepStrictEqual(
db.prepare('SELECT PI() AS pi').get(),
{ __proto__: null, pi: 3.141592653589793 },
);
});
test('percentile is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE TABLE t1 (x INTEGER);
INSERT INTO t1 (x) VALUES (1), (2), (3), (4), (5);
`);
t.assert.deepStrictEqual(
db.prepare('SELECT percentile(x, 50) AS p50 FROM t1;').get(),
{ __proto__: null, p50: 3 },
);
});
test('dbstat is enabled', (t) => {
const db = new Database(':memory:');
t.after(() => { db.close(); });
db.exec(`
CREATE TABLE t1 (key INTEGER PRIMARY KEY);
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM dbstat WHERE name = \'t1\'').get(),
{
__proto__: null,
mx_payload: 0,
name: 't1',
ncell: 0,
pageno: 2,
pagetype: 'leaf',
path: '/',
payload: 0,
pgoffset: 4096,
pgsize: 4096,
unused: 4088
},
);
});
test('fts3 is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING fts3(content TEXT);
INSERT INTO t1 (content) VALUES ('hello world');
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
[
{ __proto__: null, content: 'hello world' },
],
);
});
test('fts3 parenthesis is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING fts3(content TEXT);
INSERT INTO t1 (content) VALUES ('hello world');
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM t1 WHERE content MATCH \'(groupedterm1 OR groupedterm2) OR hello world\'').all(),
[
{ __proto__: null, content: 'hello world' },
],
);
});
test('fts4 is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING fts4(content TEXT);
INSERT INTO t1 (content) VALUES ('hello world');
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
[
{ __proto__: null, content: 'hello world' },
],
);
});
test('fts5 is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING fts5(content);
INSERT INTO t1 (content) VALUES ('hello world');
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
[
{ __proto__: null, content: 'hello world' },
],
);
});
test('rtree is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING rtree(id, minX, maxX, minY, maxY);
INSERT INTO t1 (id, minX, maxX, minY, maxY) VALUES (1, 0, 1, 0, 1);
`);
t.assert.deepStrictEqual(
db.prepare('SELECT * FROM t1 WHERE minX < 0.5').all(),
[
{ __proto__: null, id: 1, minX: 0, maxX: 1, minY: 0, maxY: 1 },
],
);
});
test('rbu is enabled', (t) => {
const db = new Database(':memory:');
t.assert.deepStrictEqual(
db.prepare('SELECT sqlite_compileoption_used(\'SQLITE_ENABLE_RBU\') as rbu_enabled;').get(),
{ __proto__: null, rbu_enabled: 1 },
);
});
test('geopoly is enabled', (t) => {
const db = new Database(':memory:');
db.exec(`
CREATE VIRTUAL TABLE t1 USING geopoly(a,b,c);
INSERT INTO t1(_shape) VALUES('[[0,0],[1,0],[0.5,1],[0,0]]');
`);
t.assert.deepStrictEqual(
db.prepare('SELECT rowid FROM t1 WHERE geopoly_contains_point(_shape, 0, 0)').get(),
{ __proto__: null, rowid: 1 },
);
});
});