`bun:sqlite` (#167)

* :scissors:

* Add the slow version

* draw the rest of the owl

* Fix crash when allocating lots of memory

* [Bun.Transipiler] Support passing objects

* [JS Parser] Support passing objects to macros via Bun.Transpiler

* Update JSSQLStatement.cpp

* Embed SQLite

* Add SQLite to Dockerfile

* [sqlite] Add quick one-off queries without creating a whole object

* [sqlite] Add `columnsCount`, rename raw() to `values()`, remove `rebind`

* Implement `bun:sqlite`

* return null

* Fix updating query

* Update bun.d.ts

* more tests

* Support variadic arguments, write tests and add types

* Update sqlite.d.ts

* Update sqlite.d.ts

* latest

* Implement `Database.loadExtension` and `Database.setCustomSQLite`

* Support `require.resolve`

* [napi] Improve string performance

* [bun.js] Support some of `node:module`

* another test

* [sqlite] Support serialize & deserialize

* [`bun:ffi`] Implement `CFunction` and `linkSymbols`

* [bun.js] Fix crash in `Buffer.from`

* Update sqlite.test.js

* Document linkSymbols

* docs

* Update README.md

6 files changed, 510 insertions, 1 deletions

diff --git a/integration/bunjs-only-snippets/buffer.test.js b/integration/bunjs-only-snippets/buffer.test.js
index 54c4c3943..cbe204e63 100644
--- a/integration/bunjs-only-snippets/buffer.test.js
+++ b/integration/bunjs-only-snippets/buffer.test.js
@@ -45,6 +45,8 @@ it("Buffer", () => {
     expect(Array.from(new Buffer(input)).join(",")).toBe(good[i].join(","));
     gc();
     expect(Buffer.byteLength(input)).toBe(good[i].length);
+    gc();
+    expect(Buffer.from(input).byteLength).toBe(Buffer.byteLength(input));
   }
 });
 
diff --git a/integration/bunjs-only-snippets/ffi.test.js b/integration/bunjs-only-snippets/ffi.test.js
index 9198df3f7..6698ae51b 100644
--- a/integration/bunjs-only-snippets/ffi.test.js
+++ b/integration/bunjs-only-snippets/ffi.test.js
@@ -11,6 +11,7 @@ import {
   toArrayBuffer,
   FFIType,
   callback,
+  CFunction,
 } from "bun:ffi";
 
 it("ffi print", async () => {
@@ -424,6 +425,13 @@ function ffiRunner(types) {
   expect(identity_ptr(cptr)).toBe(cptr);
   const second_ptr = ptr(new Buffer(8));
   expect(identity_ptr(second_ptr)).toBe(second_ptr);
+
+  var myCFunction = new CFunction({
+    ptr: return_a_function_ptr_to_function_that_returns_true(),
+    returns: "bool",
+  });
+  expect(myCFunction()).toBe(true);
+
   // function identityBool() {
   //   return true;
   // }
@@ -436,7 +444,7 @@ function ffiRunner(types) {
   //   identityBool
   // );
   // expect(
-  //   cb_identity_true(return_a_function_ptr_to_function_that_returns_true())
+  // cb_identity_true()
   // ).toBe(true);
 
   // expect(cb_identity_true(first)).toBe(true);
diff --git a/integration/bunjs-only-snippets/import-meta.test.js b/integration/bunjs-only-snippets/import-meta.test.js
index c0d00f4c1..0520be3a5 100644
--- a/integration/bunjs-only-snippets/import-meta.test.js
+++ b/integration/bunjs-only-snippets/import-meta.test.js
@@ -1,4 +1,5 @@
 import { it, expect } from "bun:test";
+import * as Module from "node:module";
 import sync from "./require-json.json";
 
 const { path, dir } = import.meta;
@@ -7,10 +8,20 @@ it("import.meta.resolveSync", () => {
   expect(
     import.meta.resolveSync("./" + import.meta.file, import.meta.path)
   ).toBe(path);
+  const require = Module.createRequire(import.meta.path);
+  expect(require.resolve(import.meta.path)).toBe(path);
+  expect(require.resolve("./" + import.meta.file)).toBe(path);
+
+  // check it works with URL objects
+  expect(
+    Module.createRequire(new URL(import.meta.url)).resolve(import.meta.path)
+  ).toBe(import.meta.path);
 });
 
 it("import.meta.require", () => {
   expect(import.meta.require("./require-json.json").hello).toBe(sync.hello);
+  const require = Module.createRequire(import.meta.path);
+  expect(require("./require-json.json").hello).toBe(sync.hello);
 });
 
 it("import.meta.dir", () => {
diff --git a/integration/bunjs-only-snippets/sql-raw.test.js b/integration/bunjs-only-snippets/sql-raw.test.js
new file mode 100644
index 000000000..ea7f72bd6
--- /dev/null
+++ b/integration/bunjs-only-snippets/sql-raw.test.js
@@ -0,0 +1,71 @@
+import { expect, it } from "bun:test";
+
+var SQL = globalThis[Symbol.for("Bun.lazy")]("sqlite");
+
+it("works", () => {
+  const handle = SQL.open("/tmp/northwind.sqlite");
+
+  const stmt = SQL.prepare(
+    handle,
+    'SELECT * FROM "Order" WHERE OrderDate > date($date)'
+  );
+  expect(stmt.toString()).toBe(
+    `SELECT * FROM "Order" WHERE OrderDate > date(NULL)`
+  );
+
+  expect(
+    Array.isArray(
+      stmt.all({
+        // do the conversion this way so that this test runs in multiple timezones
+        $date: new Date(
+          new Date(1996, 8, 1, 0, 0, 0, 0).toUTCString()
+        ).toISOString(),
+      })
+    )
+  ).toBe(true);
+  expect(stmt.toString()).toBe(
+    `SELECT * FROM "Order" WHERE OrderDate > date('1996-09-01T07:00:00.000Z')`
+  );
+
+  var ran = stmt.run({
+    $date: new Date(
+      new Date(1997, 8, 1, 0, 0, 0, 0).toUTCString()
+    ).toISOString(),
+  });
+  expect(Array.isArray(ran)).toBe(false);
+  expect(ran === undefined).toBe(true);
+  expect(stmt.toString()).toBe(
+    `SELECT * FROM "Order" WHERE OrderDate > date('1997-09-01T07:00:00.000Z')`
+  );
+
+  expect(
+    Array.isArray(
+      stmt.get({
+        $date: new Date(
+          new Date(1998, 8, 1, 0, 0, 0, 0).toUTCString()
+        ).toISOString(),
+      })
+    )
+  ).toBe(false);
+  expect(stmt.toString()).toBe(
+    `SELECT * FROM "Order" WHERE OrderDate > date('1998-09-01T07:00:00.000Z')`
+  );
+  expect(stmt.paramsCount).toBe(1);
+  expect(stmt.columnsCount).toBe(14);
+  expect(stmt.columns.length).toBe(14);
+  stmt.finalize();
+  SQL.close(handle);
+});
+
+it("SQL.run works", () => {
+  const handle = SQL.open("/tmp/northwind.sqlite");
+  expect(typeof handle).toBe("number");
+
+  expect(
+    SQL.run(handle, 'SELECT * FROM "Order" WHERE OrderDate > date($date)', {
+      $date: new Date(1996, 8, 1).toISOString(),
+    })
+  ).toBe(undefined);
+
+  SQL.close(handle);
+});
diff --git a/integration/bunjs-only-snippets/sqlite.test.js b/integration/bunjs-only-snippets/sqlite.test.js
new file mode 100644
index 000000000..3f1e6c5db
--- /dev/null
+++ b/integration/bunjs-only-snippets/sqlite.test.js
@@ -0,0 +1,383 @@
+import { expect, it } from "bun:test";
+import { Database, constants } from "bun:sqlite";
+
+var encode = (text) => Buffer.from(text);
+
+it("Database.open", () => {
+  // in a folder which doesn't exist
+  try {
+    Database.open(
+      "/this/database/does/not/exist.sqlite",
+      constants.SQLITE_OPEN_READWRITE
+    );
+    throw new Error("Expected an error to be thrown");
+  } catch (error) {
+    expect(error.message).toBe("unable to open database file");
+  }
+
+  // in a file which doesn't exist
+  try {
+    Database.open(
+      `/tmp/database-${Math.random()}.sqlite`,
+      constants.SQLITE_OPEN_READWRITE
+    );
+    throw new Error("Expected an error to be thrown");
+  } catch (error) {
+    expect(error.message).toBe("unable to open database file");
+  }
+
+  // in a file which doesn't exist
+  try {
+    Database.open(`/tmp/database-${Math.random()}.sqlite`, { readonly: true });
+    throw new Error("Expected an error to be thrown");
+  } catch (error) {
+    expect(error.message).toBe("unable to open database file");
+  }
+
+  // in a file which doesn't exist
+  try {
+    Database.open(`/tmp/database-${Math.random()}.sqlite`, { readwrite: true });
+    throw new Error("Expected an error to be thrown");
+  } catch (error) {
+    expect(error.message).toBe("unable to open database file");
+  }
+
+  // create works
+  {
+    var db = Database.open(`/tmp/database-${Math.random()}.sqlite`, {
+      create: true,
+    });
+    db.close();
+  }
+
+  // this should not throw
+  // it creates an in-memory db
+  new Database().close();
+});
+
+it("creates", () => {
+  const db = Database.open(":memory:");
+  db.exec(
+    "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, created TEXT, deci FLOAT, blobby BLOB)"
+  );
+  const stmt = db.prepare(
+    "INSERT INTO test (name, value, deci, created, blobby) VALUES (?, ?, ?, ?, ?)"
+  );
+
+  stmt.run([
+    "foo",
+    1,
+    Math.fround(1.111),
+    new Date(1995, 12, 19).toISOString(),
+    encode("Hello World"),
+  ]);
+  stmt.run([
+    "bar",
+    2,
+    Math.fround(2.222),
+    new Date(1995, 12, 19).toISOString(),
+    encode("Hello World"),
+  ]);
+  stmt.run([
+    "baz",
+    3,
+    Math.fround(3.333),
+    new Date(1995, 12, 19).toISOString(),
+    encode("Hello World"),
+  ]);
+
+  stmt.finalize();
+
+  const stmt2 = db.prepare("SELECT * FROM test");
+  expect(JSON.stringify(stmt2.get())).toBe(
+    JSON.stringify({
+      id: 1,
+      name: "foo",
+      value: 1,
+      created: new Date(1995, 12, 19).toISOString(),
+      deci: Math.fround(1.111),
+      blobby: encode("Hello World"),
+    })
+  );
+
+  expect(JSON.stringify(stmt2.all())).toBe(
+    JSON.stringify([
+      {
+        id: 1,
+        name: "foo",
+        value: 1,
+        created: new Date(1995, 12, 19).toISOString(),
+        deci: Math.fround(1.111),
+        blobby: encode("Hello World"),
+      },
+      {
+        id: 2,
+        name: "bar",
+        value: 2,
+        created: new Date(1995, 12, 19).toISOString(),
+        deci: Math.fround(2.222),
+        blobby: encode("Hello World"),
+      },
+      {
+        id: 3,
+        name: "baz",
+        value: 3,
+        created: new Date(1995, 12, 19).toISOString(),
+        deci: Math.fround(3.333),
+        blobby: encode("Hello World"),
+      },
+    ])
+  );
+  expect(stmt2.run()).toBe(undefined);
+
+  // not necessary to run but it's a good practice
+  stmt2.finalize();
+});
+
+it("typechecks", () => {
+  const db = Database.open(":memory:");
+  db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
+  db.exec('INSERT INTO test (name) VALUES ("Hello")');
+  db.exec('INSERT INTO test (name) VALUES ("World")');
+
+  const q = db.prepare("SELECT * FROM test WHERE (name = ?)");
+
+  var expectfail = (val) => {
+    try {
+      q.run([val]);
+      throw new Error("Expected error");
+    } catch (e) {
+      expect(e.message !== "Expected error").toBe(true);
+      expect(e.name).toBe("TypeError");
+    }
+
+    try {
+      q.all([val]);
+      throw new Error("Expected error");
+    } catch (e) {
+      expect(e.message !== "Expected error").toBe(true);
+      expect(e.name).toBe("TypeError");
+    }
+
+    try {
+      q.get([val]);
+      throw new Error("Expected error");
+    } catch (e) {
+      expect(e.message !== "Expected error").toBe(true);
+      expect(e.name).toBe("TypeError");
+    }
+  };
+
+  expectfail(Symbol("oh hai"));
+  expectfail(new Date());
+  expectfail(class Foo {});
+  expectfail(() => class Foo {});
+  expectfail(new RangeError("what"));
+  expectfail(new Map());
+  expectfail(new Map([["foo", "bar"]]));
+  expectfail(new Set());
+  expectfail(new Set([1, 2, 3]));
+});
+
+it("db.query supports TypedArray", () => {
+  const db = Database.open(":memory:");
+
+  db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, blobby BLOB)");
+
+  const stmt = db.prepare("INSERT INTO test (blobby) VALUES (?)");
+  stmt.run([encode("Hello World")]);
+  stmt.finalize();
+
+  const stmt2 = db.prepare("SELECT * FROM test");
+  expect(JSON.stringify(stmt2.get())).toBe(
+    JSON.stringify({
+      id: 1,
+      blobby: encode("Hello World"),
+    })
+  );
+
+  const stmt3 = db.prepare("SELECT * FROM test WHERE (blobby = ?)");
+
+  expect(JSON.stringify(stmt3.get([encode("Hello World")]))).toBe(
+    JSON.stringify({
+      id: 1,
+      blobby: encode("Hello World"),
+    })
+  );
+
+  expect(
+    JSON.stringify(
+      db
+        .query("SELECT * FROM test WHERE (blobby = ?)")
+        .get([encode("Hello World")])
+    )
+  ).toBe(
+    JSON.stringify({
+      id: 1,
+      blobby: encode("Hello World"),
+    })
+  );
+
+  expect(stmt3.get([encode("Hello World NOT")])).toBe(null);
+});
+
+it("supports serialize/deserialize", () => {
+  const db = Database.open(":memory:");
+  db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
+  db.exec('INSERT INTO test (name) VALUES ("Hello")');
+  db.exec('INSERT INTO test (name) VALUES ("World")');
+
+  const input = db.serialize();
+  const db2 = new Database(input);
+
+  const stmt = db2.prepare("SELECT * FROM test");
+  expect(JSON.stringify(stmt.get())).toBe(
+    JSON.stringify({
+      id: 1,
+      name: "Hello",
+    })
+  );
+
+  expect(JSON.stringify(stmt.all())).toBe(
+    JSON.stringify([
+      {
+        id: 1,
+        name: "Hello",
+      },
+      {
+        id: 2,
+        name: "World",
+      },
+    ])
+  );
+  db2.exec("insert into test (name) values ('foo')");
+  expect(JSON.stringify(stmt.all())).toBe(
+    JSON.stringify([
+      {
+        id: 1,
+        name: "Hello",
+      },
+      {
+        id: 2,
+        name: "World",
+      },
+      {
+        id: 3,
+        name: "foo",
+      },
+    ])
+  );
+
+  const db3 = new Database(input, { readonly: true });
+  try {
+    db3.exec("insert into test (name) values ('foo')");
+    throw new Error("Expected error");
+  } catch (e) {
+    expect(e.message).toBe("attempt to write a readonly database");
+  }
+});
+
+it("db.query()", () => {
+  const db = Database.open(":memory:");
+  db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
+
+  expect(db[Symbol.for("Bun.Database.cache.count")]).toBe(0);
+
+  var q = db.query("SELECT * FROM test WHERE name = ?");
+  expect(q.get("Hello") === null).toBe(true);
+
+  db.exec('INSERT INTO test (name) VALUES ("Hello")');
+  db.exec('INSERT INTO test (name) VALUES ("World")');
+
+  var rows = db.query("SELECT * FROM test WHERE name = ?").all(["Hello"]);
+
+  expect(JSON.stringify(rows)).toBe(JSON.stringify([{ id: 1, name: "Hello" }]));
+
+  rows = db.query("SELECT * FROM test WHERE name = ?").all(["World"]);
+
+  // if this fails, it means the query caching failed to update
+  expect(JSON.stringify(rows)).toBe(JSON.stringify([{ id: 2, name: "World" }]));
+
+  rows = db.query("SELECT * FROM test WHERE name = ?").all(["Hello"]);
+  expect(JSON.stringify(rows)).toBe(JSON.stringify([{ id: 1, name: "Hello" }]));
+
+  // check that the query is cached
+  expect(db[Symbol.for("Bun.Database.cache.count")]).toBe(1);
+
+  db.clearQueryCache();
+
+  // check clearing the cache decremented the counter
+  expect(db[Symbol.for("Bun.Database.cache.count")]).toBe(0);
+
+  q.finalize();
+  try {
+    // check clearing the cache decremented the counter
+
+    q.all(["Hello"]);
+    throw new Error("Should have thrown");
+  } catch (e) {
+    expect(e.message !== "Should have thrown").toBe(true);
+  }
+
+  // check that invalid queries are not cached
+  // and invalid queries throw
+  try {
+    db.query("SELECT * FROM BACON", ["Hello"]).all();
+    throw new Error("Should have thrown");
+  } catch (e) {
+    expect(e.message !== "Should have thrown").toBe(true);
+    expect(db[Symbol.for("Bun.Database.cache.count")]).toBe(0);
+  }
+
+  // check that it supports multiple arguments
+  expect(
+    JSON.stringify(
+      db
+        .query("SELECT * FROM test where (name = ? OR name = ?)")
+        .all(["Hello", "Fooooo"])
+    )
+  ).toBe(JSON.stringify([{ id: 1, name: "Hello" }]));
+  expect(
+    JSON.stringify(
+      db
+        .query("SELECT * FROM test where (name = ? OR name = ?)")
+        .all("Hello", "Fooooo")
+    )
+  ).toBe(JSON.stringify([{ id: 1, name: "Hello" }]));
+
+  // throws if insufficeint arguments
+  try {
+    db.query("SELECT * FROM test where (name = ? OR name = ?)").all("Hello");
+  } catch (e) {
+    expect(e.message).toBe("Expected 2 values, got 1");
+  }
+
+  // named parameters
+  expect(
+    JSON.stringify(
+      db
+        .query("SELECT * FROM test where (name = $hello OR name = $goodbye)")
+        .all({
+          $hello: "Hello",
+          $goodbye: "Fooooo",
+        })
+    )
+  ).toBe(JSON.stringify([{ id: 1, name: "Hello" }]));
+
+  db.close();
+
+  // Check that a closed database doesn't crash
+  // and does throw an error when trying to run a query
+  try {
+    db.query("SELECT * FROM test WHERE name = ?").all(["Hello"]);
+    throw new Error("Should have thrown");
+  } catch (e) {
+    expect(e.message !== "Should have thrown").toBe(true);
+  }
+
+  // check that we can call close multiple times
+  // it should not throw so that your code doesn't break
+  db.close();
+  db.close();
+  db.close();
+});
diff --git a/integration/bunjs-only-snippets/transpiler.test.js b/integration/bunjs-only-snippets/transpiler.test.js
index 3d2064f72..61024e400 100644
--- a/integration/bunjs-only-snippets/transpiler.test.js
+++ b/integration/bunjs-only-snippets/transpiler.test.js
@@ -629,6 +629,40 @@ describe("Bun.Transpiler", () => {
       );
     });
 
+    describe("Bun.js", () => {
+      it("require -> import.meta.require", () => {
+        expectBunPrinted_(
+          `export const foo = require('bar.node')`,
+          `export const foo = import.meta.require("bar.node")`
+        );
+      });
+
+      it("require.resolve -> import.meta.resolveSync", () => {
+        expectBunPrinted_(
+          `export const foo = require.resolve('bar.node')`,
+          `export const foo = import.meta.resolveSync("bar.node")`
+        );
+      });
+
+      it('require.resolve(path, {paths: ["blah"]}) -> import.meta.resolveSync', () => {
+        expectBunPrinted_(
+          `export const foo = require.resolve('bar.node', {paths: ["blah"]})`,
+          `export const foo = import.meta.resolveSync("bar.node", { paths: ["blah"] })`
+        );
+      });
+    });
+
+    describe("Browsers", () => {
+      it('require.resolve("my-module") -> "/resolved/my-module"', () => {
+        // the module resolver & linker doesn't run with Bun.Transpiler
+        // so in this test, it becomes the same path string
+        expectPrinted_(
+          `export const foo = require.resolve('my-module')`,
+          `export const foo = "my-module"`
+        );
+      });
+    });
+
     it("define", () => {
       expectPrinted_(
         `export default typeof user_undefined === 'undefined';`,