Insertions in prepared statements need to be finalized to persist

[sirikon]

Hi, just noticed that insertions in prepared statements won't persist to disk until the prepared statement is finalized.

Here's a snippet to reproduce the behavior:

// main.ts
import { Database } from "https://deno.land/x/sqlite3@0.6.1/mod.ts";

const REPETITIONS = 5;

const db = new Database("test.db");
db.exec(
  "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY, name STRING)",
);

for (let i = 1; i <= REPETITIONS; i++) {
  const stmt = db.prepare(
    "INSERT INTO people (name) VALUES (?) RETURNING id, name",
  );
  const [id, name] = stmt.value<[number, string]>("Johnny " + i)!;
  // stmt.finalize()
  console.log("Inserted:", id, name);
}

db.close();

deno run -A --unstable main.ts

Running multiple times the command above, will produce the same output:

$ deno run -A --unstable main.ts
Inserted: 1 Johnny 1
Inserted: 2 Johnny 2
Inserted: 3 Johnny 3
Inserted: 4 Johnny 4
Inserted: 5 Johnny 5
$ deno run -A --unstable main.ts
Inserted: 1 Johnny 1
Inserted: 2 Johnny 2
Inserted: 3 Johnny 3
Inserted: 4 Johnny 4
Inserted: 5 Johnny 5

When uncommenting the // stmt.finalize() line, insertions actually get persisted between executions:

$ deno run -A --unstable main.ts
Inserted: 1 Johnny 1
Inserted: 2 Johnny 2
Inserted: 3 Johnny 3
Inserted: 4 Johnny 4
Inserted: 5 Johnny 5
$ deno run -A --unstable main.ts
Inserted: 6 Johnny 1
Inserted: 7 Johnny 2
Inserted: 8 Johnny 3
Inserted: 9 Johnny 4
Inserted: 10 Johnny 5

I'm not sure if this is the correct behavior, but if it is, maybe it should be something explicit on the documentation for prepared statements?.

Thank you very much in advance :)

[DjDeveloperr]

It shouldn't be required. Seems like we should clean up all open statement handles when db.close is called (which will ensure that the data persists) instead of just relying on GC.

[sirikon]

That was quick :smile: thanks!!