looking forward to your answers cc @nodejs/sqlite

cc: @geeksilva97 @miguelmarcondesf

Since the API requires passing in the database instance, I'd prefer something like...

const db = new DatabaseSync(":memory:");
const template = db.createSqlTag();

As opposed to a standalone top-level function.

Hello @0hmX . I wonder how this will fit the current API interface. I like @jasnell's suggestion. Do you think we could make it like the following?

const db = new DatabaseSync(":memory:");
const template = db.createSqlTag();

template.run`...`;
template.get`...`
template.all`...`

EDIT: I'm not sure if my question makes sense. I asked about it in the issue to get more information.

From a technical point of view, @0hmX . This PR needs tests and documentation.

The cache is important in the implementation since statements are meant to be reused. With template tags, we miss this. In such a situation, the cache is important, as in Matteo's implementation.

I also wonder if we could have this implementation on C++ side, as all the implementations of node:sqlite module.

@geeksilva97 and @jasnell, thank you for the feedback.

One of the key features that tag templates could bring is SQL syntax highlighting. However, this will only work if we have a pattern like this:

const willSyntaxHighlight = sql`SELECT * FROM users WHERE age = ${age}` // will get syntax highlighted with extensions
const willNotSyntaxHighlight = `SELECT * FROM users WHERE age = ${age}`

This is the pattern I see in all the extensions that allow you to highlight a string template inside ts or js for SQL in VS Code.

We need to have a template function called sql in front. Therefore, we should export a top-level function called SQL that returns an object with the raw SQL string and parameters. I have implemented this in C++ and added it! This means we will need to add support for this custom object in database.exec (we will convert the custom object into a raw string) and database.prepare.

const db = new DatabaseSync(":memory:");
const template = db.createSqlTag();

I think the name should be changed to something like db.createCache(). as this is creating a cache, storing the SQL string, and checking if a prepared statement is already made for it and calling the appropriate method on it and returning the outputs.

the final Api should look something like this

const { sql, DatabaseSync } = require("node:sql")

const db = new DatabaseSync(":memory:")

// adding sql template function in front for syntax highlight, else it's the same as using a multiline string
db.exec(sql`CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE
)`)

// same as using a multiline string
db.exec(sql`INSERT INTO contacts (name, phone, email) VALUES (${"Alice"}, ${"111-222-3333"}, ${"alice@example.com"})`)
db.exec(sql`INSERT INTO contacts (name, phone, email) VALUES (${"Bob"}, ${"444-555-6666"}, ${"bob@example.com"})`)
db.exec(sql`INSERT INTO contacts (name, phone, email) VALUES (${"Charlie"}, ${"777-888-9999"}, ${"charlie@example.com"})`)

const cache = db.createCache()

const emailDomain = "%@example.com"
cache.all(sql`SELECT * FROM contacts WHERE email LIKE ${emailDomain}`)

const contactId = 2
cache.get(sql`SELECT * FROM contacts WHERE id = ${contactId}`)

const namePrefix = "C%"
cache.iterate(sql`SELECT * FROM contacts WHERE name LIKE ${namePrefix} ORDER BY name`)

One of the key features that tag templates could bring is SQL syntax highlighting. However, this will only work if we have a pattern like this: [...] We need to have a template function called sql in front.

VSCode supports more complex patterns than just literally an identifier, I'm almost certain. It can be made to handle db.prepare or anything.sql just as well as a bare sql. See this repo for an example.

// same as using a multiline string

It should very much not be the same as using a multiline string. The whole point of tagged templates - literally the only reason they are in the language - is that unlike strings they allow using a representation which is immune to problems like sql injection. From your implementation it looks like you're correctly avoiding sql injections, but this is something which should be emphasized in the docs and tested extensively. Users need to be aware that omitting the tag is not safe; it's not just a convenience for getting syntax highlighting.

On that note, you should also be doing parsing of the string up front: users should get an error as soon as they write sql`invalid`; rather than needing to actually pass it somewhere to get an error. Note that the first argument to the template tag is always the same value on subsequent calls so that it can be used as a key in a cache instead of needing to check every time the function is called. Probably the easiest thing is to prepare the statement right away, and store the prepared statement in a cache.

Codecov Report

Attention: Patch coverage is 66.76301% with 115 lines in your changes missing coverage. Please review.

Project coverage is 90.02%. Comparing base (5f7dbf4) to head (f3571b7).
Report is 185 commits behind head on main.

@@            Coverage Diff             @@
##             main   #58748      +/-   ##
==========================================
- Coverage   90.16%   90.02%   -0.14%     
==========================================
  Files         636      646      +10     
  Lines      188060   189488    +1428     
  Branches    36899    37173     +274     
==========================================
+ Hits       169568   170596    +1028     
- Misses      11235    11533     +298     
- Partials     7257     7359     +102     

The PR is as stable as it gets now with all the tests passing. I need reviews on the PR. Look at the test file test-sqlite-template-tag.js to see how the api is designed.

cc: @jasnell @geeksilva97 @bakkot also @mcollina (as creator of the main issues)

You've been doing a great job here, @0hmX . Thanks for that.

I left a few more comments.

Thanks, just wanted to ask, can we make this capital naming conversion some kind of lint rule? Do we have lint rules for C++? Then I will make an issue and a Pr for it! It will be much better for new C++ writers like me!