sqlite: db.prepare(sql, options)

[mike-git374]

Problem

Currently you need multiple js statements to declare a single SQLite statement with options like returnArrays, readBigInts etc. This requires multiple lines and can be inefficient to call c++ multiple times:

const q = db.prepare(sql);
q.setReturnArrays(true);
q.setReadBigInts(true);
q.setAllowBareNamedParameters(true);
q.setAllowUnknownNamedParameters(true);

Proposal 1

Allow chaining statement.set* functions by returning the sql statement instead of undefined. This allows declaring a sql statement with 1 JS statement. This still requires multiple function calls but is slightly better DX developer experience using the existing set* functions:

// before
const q = db.prepare(sql);
q.setReturnArrays(true);

// after
const q = db.prepare(sql).setReturnArrays(true);

// more example
const q = db.prepare(sql).setReturnArrays(true).setReadBigInts(true);

PR: #61263

Proposal 2

Add options argument: db.prepare(sql[, options])

// before
const q = db.prepare(sql);
q.setReturnArrays(true);

// after
const q = db.prepare(sql, { returnArrays: true });

// more example
const q = db.prepare(sql, { returnArrays: true, readBigInts: true });

In this case I recommend to remove the statement.set* functions to simplify the API, you only need db.prepare(sql[, options])

Proposal 3

Do both Proposal 1 and Proposal 2. Currently I am leaning towards Proposal 2 and removing set* functions

[Renegade334]

I think your original proposal (an options argument to db.prepare()) is more consistent with existing node:sqlite patterns, and with Node.js conventions on the whole. That being said, is your usage case already covered by passing defaults to the DatabaseSync constructor?

[mike-git374]

Status of this issue: Need to create PR to add db.prepare(sql[, options]) and remove statement.set* functions. If anyone is interested in looking at this it is available to open PR: DatabaseSync::Prepare