Repository: denoland/deno
File: tests/unit_node/sqlite_test.ts (TypeScript)

Code before:
  sourceDb.exec("CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)");
  targetDb.exec("CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)");

  const session = sourceDb.createSession();

  const insert = sourceDb.prepare(
    "INSERT INTO data (key, value) VALUES (?, ?)",
  );
  insert.run(1, "hello");
  insert.run(2, "world");

  const changeset = session.changeset();
  targetDb.applyChangeset(changeset, {
    filter: (e) => e === "data",
    onConflict: () => sqlite.constants.SQLITE_CHANGESET_ABORT,
  });

  const stmt = targetDb.prepare("SELECT * FROM data");
  assertEquals(stmt.all(), [
    { key: 1, value: "hello", __proto__: null },
    { key: 2, value: "world", __proto__: null },
  ]);
});

Deno.test("[node/sqlite] exec should execute batch statements", () => {
  const db = new DatabaseSync(":memory:");
  db.exec(`CREATE TABLE one(id int PRIMARY KEY) STRICT;
CREATE TABLE two(id int PRIMARY KEY) STRICT;`);

  const table = db.prepare(
    `SELECT name FROM sqlite_master WHERE type='table'`,
  ).all();
  assertEquals(table.length, 2);

  db.close();
});

Deno.test("[node/sqlite] query should handle mixed positional and named parameters", () => {
  const db = new DatabaseSync(":memory:");
  db.exec(`CREATE TABLE one(variable1 TEXT, variable2 INT, variable3 INT)`);
  db.exec(
    `INSERT INTO one (variable1, variable2, variable3) VALUES ("test", 1 , 2);`,
  );

  const query = "SELECT * FROM one WHERE variable1=:var1 AND variable2=:var2 ";
  const result = db.prepare(query).all({ var1: "test", var2: 1 });
  assertEquals(result, [{
    __proto__: null,
    variable1: "test",
    variable2: 1,
    variable3: 2,

Review comment (suggestion):
You might need to bump the local version of `@types/node` in tests/registries/npm

Code after:
        "too many attached databases - max 0",
      );
      db.close();
    }
  },
});

Deno.test("[node/sqlite] applyChangeset across databases", () => {
  const sourceDb = new DatabaseSync(":memory:");
  const targetDb = new DatabaseSync(":memory:");

  sourceDb.exec("CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)");
  targetDb.exec("CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)");

  const session = sourceDb.createSession();

  const insert = sourceDb.prepare(
    "INSERT INTO data (key, value) VALUES (?, ?)",
  );
  insert.run(1, "hello");
  insert.run(2, "world");

  const changeset = session.changeset();
  targetDb.applyChangeset(changeset, {
    filter: (e) => e === "data",
    // @ts-ignore: types are not up to date
    onConflict: () => sqlite.constants.SQLITE_CHANGESET_ABORT,
  });

  const stmt = targetDb.prepare("SELECT * FROM data");
  assertEquals(stmt.all(), [
    { key: 1, value: "hello", __proto__: null },
    { key: 2, value: "world", __proto__: null },
  ]);
});

Deno.test("[node/sqlite] exec should execute batch statements", () => {
  const db = new DatabaseSync(":memory:");
  db.exec(`CREATE TABLE one(id int PRIMARY KEY) STRICT;
CREATE TABLE two(id int PRIMARY KEY) STRICT;`);

  const table = db.prepare(
    `SELECT name FROM sqlite_master WHERE type='table'`,
  ).all();
  assertEquals(table.length, 2);

  db.close();
});

Deno.test("[node/sqlite] query should handle mixed positional and named parameters", () => {
  const db = new DatabaseSync(":memory:");