sqlite: add DatabaseSync.prototype.pragma()

[thisalihassan]

What is the problem this feature will solve?

node:sqlite already supports PRAGMA statements through raw SQL but using them is more awkward than it needs to be.
Today users need to know which API shape to use for each case:

db.exec('PRAGMA foreign_keys = ON');
const mode = db.prepare('PRAGMA journal_mode').get().journal_mode;
const columns = db.prepare('PRAGMA table_info(users)').all();

This works, but it is not very discoverable and it forces users to switch between exec(), get() and all()

A dedicated pragma() helper would make common sqlite configuration and introspection easier to read, easier to teach and closer to what users may already expect from libraries like better-sqlite3.

What is the feature you are proposing to solve the problem?

Add a database.pragma() helper to node:sqlite, initially on DatabaseSync.
Example shape:

db.pragma('foreign_keys = ON');
const mode = db.pragma('journal_mode', { simple: true });
const columns = db.pragma('table_info(users)');

Possible behavior mimicking popular libraries:

• accept a PRAGMA body string such as 'journal_mode = WAL' or 'table_info(users)'
• return all rows by default
• optionally support { simple: true } to return the first column of the first row for common scalar PRAGMAs

This would be an ergonomic wrapper over functionality that already exists via raw SQL, not a new SQLite capability.

What alternatives have you considered?

1. Continue using raw SQL with exec() / prepare().get() / prepare().all().
   This works today but it is more verbose and requires users to remember which call shape matches each PRAGMA use case.
2. Leave this entirely to userland helpers

[thisalihassan]

I would like to work on this.
cc: @Renegade334 @geeksilva97 @ronag

[Renegade334]

This suggestion essentially boils down to "node:sqlite should implement a fully validating object-oriented sqlite query string builder". (Pragmas are just plain old queries, there is no separate API that this suggestion would target.)

I'd be very against this personally. This isn't related to the sqlite3 API or the node:sqlite binding itself, and it would be an absolutely massive chunk of code to build and maintain (and bear in mind that Node.js can be built against an arbitrary shared sqlite3 version, so any such validation wouldn't necessarily match the featureset of the actual sqlite3 implementation in the node:sqlite binding). A partial implementation just for one query type makes just as little sense IMO.

Userland is the right place for this sort of thing.

[thisalihassan]

@Renegade334 To clarify, the proposal is not for validation or query building. It is a thin convenience wrapper over SQLite’s existing PRAGMA SQL, similar to:
db.prepare('PRAGMA ...').all() by default, with optional first-column/first-row convenience for common scalar pragmas.
It would not parse or validate pragma syntax, and behavior would still come entirely from the linked SQLite library.