Very Popular
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536import { assertAlmostEquals, assertEquals, assertInstanceOf, assertThrows,} from "https://deno.land/std@0.154.0/testing/asserts.ts";
import { DB } from "../mod.ts";import { Status } from "./constants.ts";import { SqliteError } from "./error.ts";
const TEST_DB = "test.db";const LARGE_TEST_DB = "build/2GB_test.db";
async function dbPermissions(path: string): Promise<boolean> { const query = async (name: "read" | "write") => (await Deno.permissions.query({ name, path })).state === "granted"; return await query("read") && await query("write");}
const TEST_DB_PERMISSIONS = await dbPermissions(TEST_DB);const LARGE_TEST_DB_PERMISSIONS = await dbPermissions(LARGE_TEST_DB);
async function deleteDatabase(file: string) { try { await Deno.remove(file); } catch { /* no op */ } try { await Deno.remove(`${file}-journal`); } catch { /* no op */ }}
Deno.test("execute multiple statements", function () { const db = new DB();
db.execute(` CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO test (id) VALUES (1); INSERT INTO test (id) VALUES (2); INSERT INTO test (id) VALUES (3); `); assertEquals(db.query("SELECT id FROM test"), [[1], [2], [3]]);
// table `test` already exists ... assertThrows(function () { db.execute(` CREATE TABLE test2 (id INTEGER); CREATE TABLE test (id INTEGER); `); });
// ... but table `test2` was created before the error assertEquals(db.query("SELECT id FROM test2"), []);
// syntax error after first valid statement assertThrows(() => db.execute("SELECT id FROM test; NOT SQL ANYMORE"));});
Deno.test("foreign key constraints enabled", function () { const db = new DB(); db.execute(` CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT); CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, user INTEGER, FOREIGN KEY(user) REFERENCES users(id)); `);
db.query("INSERT INTO users (id) VALUES (1)"); const [{ id }] = db.queryEntries<{ id: number }>("SELECT id FROM users");
// user must exist assertThrows(() => db.query("INSERT INTO orders (user) VALUES (?)", [id + 1]) ); db.query("INSERT INTO orders (user) VALUES (?)", [id]);
// can't delete if that violates the constraint ... assertThrows(() => { db.query("DELETE FROM users WHERE id = ?", [id]); });
// ... after deleting the order, deleting is OK db.query("DELETE FROM orders WHERE user = ?", [id]); db.query("DELETE FROM users WHERE id = ?", [id]);});
Deno.test("json functions exist", function () { const db = new DB();
// The JSON1 functions should exist and we should be able to call them without unexpected errors db.query(`SELECT json('{"this is": ["json"]}')`);
// We should expect an error if we pass invalid JSON where valid JSON is expected assertThrows(() => { db.query(`SELECT json('this is not json')`); });
// We should be able to use bound values as arguments to the JSON1 functions, // and they should produce the expected results for these simple expressions. const [[objectType]] = db.query(`SELECT json_type('{}')`); assertEquals(objectType, "object");
const [[integerType]] = db.query(`SELECT json_type(?)`, ["2"]); assertEquals(integerType, "integer");
const [[realType]] = db.query(`SELECT json_type(?)`, ["2.5"]); assertEquals(realType, "real");
const [[stringType]] = db.query(`SELECT json_type(?)`, [`"hello"`]); assertEquals(stringType, "text");
const [[integerTypeAtPath]] = db.query( `SELECT json_type(?, ?)`, [`["hello", 2, {"world": 4}]`, `$[2].world`], ); assertEquals(integerTypeAtPath, "integer");});
Deno.test("date time is correct", function () { const db = new DB(); // the date/ time is passed from JS and should be current (note that it is GMT) const [[now]] = [...db.query("SELECT STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')")]; const jsTime = new Date().getTime(); const dbTime = new Date(`${now}Z`).getTime(); // to account for runtime latency, a small difference is ok const tolerance = 10; assertAlmostEquals(jsTime, dbTime, tolerance); db.close();});
Deno.test("SQL localtime reflects system locale", function () { const db = new DB(); const [[timeDb]] = db.query("SELECT datetime('now', 'localtime')"); const now = new Date();
const jsMonth = `${now.getMonth() + 1}`.padStart(2, "0"); const jsDate = `${now.getDate()}`.padStart(2, "0"); const jsHour = `${now.getHours()}`.padStart(2, "0"); const jsMinute = `${now.getMinutes()}`.padStart(2, "0"); const jsSecond = `${now.getSeconds()}`.padStart(2, "0"); const timeJs = `${now.getFullYear()}-${jsMonth}-${jsDate} ${jsHour}:${jsMinute}:${jsSecond}`;
assertEquals(timeDb, timeJs);});
Deno.test("database has correct changes and totalChanges", function () { const db = new DB();
db.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)", );
for (const name of ["a", "b", "c"]) { db.query("INSERT INTO test (name) VALUES (?)", [name]); assertEquals(1, db.changes); }
assertEquals(3, db.totalChanges);
db.query("UPDATE test SET name = ?", ["new name"]); assertEquals(3, db.changes); assertEquals(6, db.totalChanges);});
Deno.test("last inserted id", function () { const db = new DB();
// By default, lastInsertRowId must be 0 assertEquals(db.lastInsertRowId, 0);
// Create table and insert value db.query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
const insertRowIds = [];
// Insert data to table and collect their ids for (let i = 0; i < 10; i++) { db.query("INSERT INTO users (name) VALUES ('John Doe')"); insertRowIds.push(db.lastInsertRowId); }
// Now, the last inserted row id must be 10 assertEquals(db.lastInsertRowId, 10);
// All collected row ids must be the same as in the database assertEquals( insertRowIds, [...db.query("SELECT id FROM users")].map(([i]) => i), );
db.close();
// When the database is closed, the value // will be reset to 0 again assertEquals(db.lastInsertRowId, 0);});
Deno.test("close database", function () { const db = new DB(); db.close(); assertThrows(() => db.query("CREATE TABLE test (name TEXT PRIMARY KEY)")); db.close(); // check close is idempotent and won't throw});
Deno.test("open queries block close", function () { const db = new DB(); db.query("CREATE TABLE test (name TEXT PRIMARY KEY)");
const query = db.prepareQuery("SELECT name FROM test"); assertThrows(() => db.close()); query.finalize();
db.close();});
Deno.test("open queries cleaned up by forced close", function () { const db = new DB(); db.query("CREATE TABLE test (name TEXT PRIMARY KEY)"); db.query("INSERT INTO test (name) VALUES (?)", ["Deno"]);
db.prepareQuery("SELECT name FROM test WHERE name like '%test%'");
assertThrows(() => db.close()); db.close(true);});
Deno.test("invalid bind does not leak statements", function () { const db = new DB(); db.query("CREATE TABLE test (id INTEGER)");
for (let n = 0; n < 100; n++) { assertThrows(() => { // deno-lint-ignore no-explicit-any const badBinding: any = [{}]; db.query("INSERT INTO test (id) VALUES (?)", badBinding); }); assertThrows(() => { const badBinding = { missingKey: null }; db.query("INSERT INTO test (id) VALUES (?)", badBinding); }); }
db.query("INSERT INTO test (id) VALUES (1)");
db.close();});
Deno.test("serialize / deserialize round-trips correctly", function () { const db = new DB(); db.execute(` CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT); INSERT INTO test (id, value) VALUES (1, 'Hello'), (2, 'World'); `);
const original = db.serialize();
db.query( "INSERT INTO test (id, value) VALUES (3, 'Added after serialize')", ); assertEquals( [["Hello"], ["World"], ["Added after serialize"]], db.query("SELECT value FROM test"), );
const modified = db.serialize();
db.deserialize(original); assertEquals([["Hello"], ["World"]], db.query("SELECT value FROM test"));
db.execute("ATTACH DATABASE ':memory:' AS modified"); db.deserialize(modified, { schema: "modified" }); assertEquals([["Hello"], ["World"]], db.query("SELECT value FROM main.test")); assertEquals( [["Hello"], ["World"], ["Added after serialize"]], db.query("SELECT value FROM modified.test"), );
const copyOfModified = db.serialize("modified"); assertEquals(modified, copyOfModified);});
Deno.test("can't modify a database deserialized as read-only", function () { const db = new DB(":memory:", { mode: "write" }); db.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)"); const data = db.serialize();
// before calling deserialize inserts are okay db.execute("INSERT INTO test (id, value) VALUES (1, 'This works!')");
db.deserialize(data, { mode: "read" });
// after calling deserialize inserts throw assertThrows(() => db.execute("INSERT INTO test (id, value) VALUES (2, 'This fails!')") );});
Deno.test("can't serialize an unknown database", function () { const db = new DB(); assertThrows(() => db.serialize("unknown-schema"), (err: Error) => { assertInstanceOf(err, SqliteError); assertEquals(err.code, Status.Unknown); assertEquals(err.message, "Failed to serialize database 'unknown-schema'"); });});
Deno.test("can't deserialize into temp database", function () { const db = new DB(); db.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)"); const data = db.serialize(); assertThrows(() => db.deserialize(data, { schema: "temp" }), (err: Error) => { assertInstanceOf(err, SqliteError); assertEquals(err.code, Status.SqliteError); assertEquals(err.message, "Failed to deserialize into database 'temp'"); });});
Deno.test("transactions can be nested", function () { const db = new DB(); db.query("CREATE TABLE test (id INTEGER PRIMARY KEY)");
db.transaction(() => { db.query("INSERT INTO test (id) VALUES (1)"); try { db.transaction(() => { db.query("INSERT INTO test (id) VALUES (2)"); throw new Error("boom!"); }); } catch (_) { /* ignore */ } });
assertEquals([{ id: 1 }], db.queryEntries("SELECT * FROM test"));});
Deno.test("transactions commit when closure exists", function () { const db = new DB(); db.query("CREATE TABLE test (id INTEGER PRIMARY KEY)");
db.transaction(() => { db.query("INSERT INTO test (id) VALUES (1)"); }); assertThrows(() => db.query("ROLLBACK"));
assertEquals([{ id: 1 }], db.queryEntries("SELECT * FROM test"));});
Deno.test("transaction rolls back on throw", function () { const db = new DB(); db.query("CREATE TABLE test (id INTEGER PRIMARY KEY)");
assertThrows(() => { db.transaction(() => { db.query("INSERT INTO test (id) VALUES (1)"); throw new Error("boom!"); }); });
assertEquals([], db.query("SELECT * FROM test"));});
Deno.test( "persist database to file", { ignore: !TEST_DB_PERMISSIONS, permissions: { read: true, write: true }, sanitizeResources: true, }, async function () { const data = [ "Hello World!", "Hello Deno!", "JavaScript <3", "This costs 0€ / $0 / £0", "Wéll, hällö thėrè¿", ];
// ensure the test database file does not exist await deleteDatabase(TEST_DB);
const db = new DB(TEST_DB); db.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)", ); for (const val of data) { db.query("INSERT INTO test (val) VALUES (?)", [val]); }
// open the same database with a separate connection const readOnlyDb = await new DB(TEST_DB, { mode: "read" }); for ( const [id, val] of readOnlyDb.query<[number, string]>( "SELECT * FROM test", ) ) { assertEquals(data[id - 1], val); }
await Deno.remove(TEST_DB); db.close(); readOnlyDb.close(); },);
Deno.test( "temporary file database read / write", { ignore: !TEST_DB_PERMISSIONS, permissions: { read: true, write: true }, sanitizeResources: true, }, function () { const data = [ "Hello World!", "Hello Deno!", "JavaScript <3", "This costs 0€ / $0 / £0", "Wéll, hällö thėrè¿", ];
const tempDb = new DB(""); tempDb.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)", ); for (const val of data) { tempDb.query("INSERT INTO test (val) VALUES (?)", [val]); }
for ( const [id, val] of tempDb.query<[number, string]>("SELECT * FROM test") ) { assertEquals(data[id - 1], val); }
tempDb.close(); },);
Deno.test( "database open options", { ignore: !TEST_DB_PERMISSIONS, permissions: { read: true, write: true }, sanitizeResources: true, }, async function () { await deleteDatabase(TEST_DB);
// when no file exists, these should error assertThrows(() => new DB(TEST_DB, { mode: "write" })); assertThrows(() => new DB(TEST_DB, { mode: "read" }));
// create the database const dbCreate = new DB(TEST_DB, { mode: "create" }); dbCreate.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)", ); dbCreate.close();
// the default mode is create await deleteDatabase(TEST_DB); const dbCreateDefault = new DB(TEST_DB, { mode: "create" }); dbCreateDefault.execute( "CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)", ); dbCreateDefault.close();
// in write mode, we can run INSERT queries ... const dbWrite = new DB(TEST_DB, { mode: "write" }); dbWrite.query("INSERT INTO test (name) VALUES (?)", ["open-options-test"]); dbWrite.close();
// ... which we can read in read-only mode ... const dbRead = new DB(TEST_DB, { mode: "read" }); const rows = [...dbRead.query("SELECT id, name FROM test")]; assertEquals(rows, [[1, "open-options-test"]]);
// ... but we can't write with a read-only connection assertThrows(() => dbRead.query("INTERT INTO test (name) VALUES (?)", ["this-fails"]) ); dbRead.close(); },);
Deno.test( "create / write mode require write permissions", { ignore: !TEST_DB_PERMISSIONS, permissions: { read: true, write: false }, sanitizeResources: true, }, function () { // opening with these modes requires write permissions ... assertThrows(() => new DB(TEST_DB, { mode: "create" })); assertThrows(() => new DB(TEST_DB, { mode: "write" }));
// ... and the default mode is create assertThrows(() => new DB(TEST_DB));
// however, opening in read-only mode should work (the file was created // in the previous test) (new DB(TEST_DB, { mode: "read" })).close();
// with memory flag set, the database will be in memory and // not require any permissions (new DB(TEST_DB, { mode: "create", memory: true })).close();
// the mode can also be specified via a URI flag (new DB(`file:${TEST_DB}?mode=memory`, { uri: true })).close(); },);
Deno.test( "database larger than 2GB read / write", { ignore: !LARGE_TEST_DB_PERMISSIONS, permissions: { read: true, write: true }, sanitizeResources: true, }, function () { // generated with `cd build && make testdb` const db = new DB(LARGE_TEST_DB, { mode: "write" });
db.query("INSERT INTO test (value) VALUES (?)", ["This is a test..."]);
const rows = [ ...db.query("SELECT value FROM test ORDER BY id DESC LIMIT 10"), ]; assertEquals(rows.length, 10); assertEquals(rows[0][0], "This is a test...");
db.close(); },);