New API for `query` and `prepareQuery`

[dyedgreen]

Closes #132

This is a re-design of the API, to make it easier to used, and more performant. Feedback is very welcome!

See the full API here.

cc @jeremyBanks @uki00a @halvardssm @andykais

To Do

• [x] Finalize API design (write examples to see how the new API works in practice)
• [x] Write docs for new functions
• [x] Update benchmarks to measure performance in a more meaningful way
• [x] Add tests for the new API surface (query, queryAll, queryOne, and execute should be tested explicitly)

[dyedgreen]

Performance tests (preliminary)

Trying to run the comparison from: https://github.com/dyedgreen/deno-sqlite/issues/14#issuecomment-847119039

// Deno
import { DB } from "./mod.ts";

const selectCount = 13000;
const db = new DB("test.db");

db.query(
  "CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)",
);

db.query("begin");
const insert = db.prepareQuery("INSERT INTO people (name) VALUES (?)");
for (let i = 0; i < selectCount; i++) {
  insert.execute([i]);
}
db.query("commit");

const select = db.prepareQuery("SELECT * FROM people WHERE name = ?");
console.time(`select ${selectCount} times`);
for (let i = 0; i < selectCount; i++) {
  const _row = select.queryOne([i]);
}
console.timeEnd(`select ${selectCount} times`);

// Node / better-sqlite
const db = require("better-sqlite3")("test.db");

const select_stmt = db.prepare("SELECT * FROM people WHERE name = ?");
const select_count = 13000;
console.time(`select ${select_count} times`);
for (let i = 0; i < select_count; i++) {
  select_stmt.get(i);
}
console.timeEnd(`select ${select_count} times`);

results deno: select 13000 times: 14446ms
results node: select 13000 times: 23.505s

[dyedgreen]

A thought about the api: most people will name their prepared query objects 'query'. E.g.

const userQuery = db.prepareQuery("…");

This can make the names of the query methods awkward to use eg userQuery.queryOne(). To make this less awkward they could be renamed to not include 'query'. I would propose:

• query -> iter
• queryAll -> all
• queryOne -> one
• execute -> is already good

Which would make the call above userQuery.one().

[andykais]

I'm not so sure those benchmarks are accurate. I ran the two benchmarks (modifying them slightly so nodejs doesnt depend on deno sqlite to instantiate the db) and better-sqlite3 performs faster:

better-sqlite3

benchmark code

```ts const DB = require('better-sqlite3') const row_count = 13000; const db = new DB("test.db"); db.exec("DROP TABLE people"); db.exec("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)"); console.time(`insert ${row_count} times`); db.transaction(() => { const insert = db.prepare("INSERT INTO people (name) VALUES (?)"); for (let i = 0; i < row_count; i++) { insert.run([i]); } })() console.timeEnd(`insert ${row_count} times`); const { count } = db.prepare('SELECT COUNT(1) as count FROM people').get() console.log(`${count} rows in table`) const select = db.prepare("SELECT * FROM people WHERE name = ?"); console.time(`select ${row_count} times`); for (let i = 0; i < row_count; i++) { const _row = select.get([i]); } console.timeEnd(`select ${row_count} times`); ```

insert 13000 times: 25.069ms
13000 rows in table
select 13000 times: 17.103s

deno sqlite

benchmark code

```ts import { DB } from "https://raw.githubusercontent.com/dyedgreen/deno-sqlite/rework_api/mod.ts"; const row_count = 13000; const db = new DB("test.db"); db.query("DROP TABLE people"); db.query("CREATE TABLE people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)"); console.time(`insert ${row_count} times`); db.query("begin"); const insert = db.prepareQuery("INSERT INTO people (name) VALUES (?)"); for (let i = 0; i < row_count; i++) { insert.execute([i]); } db.query("commit"); console.timeEnd(`insert ${row_count} times`); const count = db.query('SELECT COUNT(1) FROM people') console.log(`${count} rows in table`) const select = db.prepareQuery("SELECT * FROM people WHERE name = ?"); console.time(`select ${row_count} times`); for (let i = 0; i < row_count; i++) { const _row = select.one([i]); } console.timeEnd(`select ${row_count} times`); ```

insert 13000 times: 76ms
13000 rows in table
select 13000 times: 27486ms

I'd like to point out that this pr is still a huge win for this library, ergonomically and speedwise, better-sqlite3 just may have a few more performance tricks up its sleeve is all 🙂

[dyedgreen]

Right, if you so the insert and select in the same connection sqlite will cache some of it, so better-sqlite is still faster. But the speed up for us is still significant 😅

[andykais]

one neat typing suggestion would be to tie a table to a prepared statement. Then statements could be reused without the need to cast variables each time. E.g.

interface PhoneNumber {
  id: string
  telephone: number
}

// type param would default to any of course (db.prepareQuery<any>)
const phone_number_select = db.prepareQuery<PhoneNumber>('SELECT * FROM phone_number')

// row is of type 'PhoneNumber' without manually casting it
const row = phone_number_select.one()

essentially all this does is push the type casting up from the query execution to the statement prepares.

// without typed statements, we need to cast on each query execute
const row: PhoneNumber = phone_number_select.one()

[dyedgreen]

one neat typing suggestion would be to tie a table to a prepared statement. Then statements could be reused without the need to cast variables each time.

That’s already how it works: Both query and prepareQuery accept a type parameter which you can set to reflect your select statements:

const emails = db.query<[string]>("SELECT email FROM users");

const query = db.prepareQuery<[string, number]>("SELECT name, age FROM users WHERE country = :country");
for (const [name, age] of query.iter({ country }) {
  // …
}