feature: Add support for sql-template-strings

[pke]

Would this lib profit from adding support for https://github.com/felixfbecker/node-sql-template-strings?

I might create a PR, but only if you @JoshuaWise see any benefit from adding it here.

[JoshuaWise]

@pke It doesn't make sense to use that templating library with better-sqlite3. One of the biggest advantages to using a synchronous SQLite library is that you can reuse prepared statements, even in a random-access program (such as a web server). For this reason better-sqlite3 separates binding parameters into a second operation, after the statement was already prepared.

With the library you linked above, the parameter binding happens at the same time as preparing the statement. You'd have to create a new prepared statement each time you want to bind new parameters. That's how it works in mysql, mysql2, postgres, but not in better-sqlite3. Many people do write code that way (not reusing prepared statements) when using better-sqlite3, but it's not something that I'd want to sanction within this repo.

[pke]

This makes sense yes. I've used prepared statements before.

[leafac]

I too wanted to use tagged template literals this way (their simplicity is really nice). But I didn’t want to give up on the performance benefits of prepared statements. So I came up with this: @leafac/sqlite (and I also created a lightweight migration system for it at @leafac/sqlite-migration).

[benbucksch]

@JoshuaWise I understand that preparing an SQL statement has a high performance penalty, as you explained in #389. Prepared statements are kind-of a manual cache of this work that the DB engine has to do.

OTOH, tagged expressions have an enormous benefit in making the query easier to read, by showing the variables used directly in the SQL. With prepared statements, I have to match the ? with the variables that are listed elsewhere - even if just in the line below, it's still a hassle to match. This makes writing the SQL a pain.

Would it be possible to combine both advantages? To keep the tagged expression syntax, but cache the prepared statement behind the scene? Given that tags are just functions, they can do whatever they want. Instead of directly replacing the variables in the SQL string, it would create a prepared statement, put that into an internal cache (keyed on the string "select ... ?", the prepared statement input string), and check that cache before creating another prepared statement. Then, as a second step, use the cached prepared statement with the variable values supplied by the tagged expression.

Do you think that could work? Do you see problems (which cannot be fixed) in this idea? Would it be a good idea? I could see that this won't work when somebody inserts arbitrary SQL into a variable value, but that could simply be forbidden.

Would be really nice to be able to use tagged expressions, for the ease of writing code they provide, while still preserving the benefits of cached statements.

[leafac]

@benbucksch: I did what you described in @leafac/sqlite. It’s been serving me quite well over two years 😃

[benbucksch]

@leafac (sorry for the late response)
That's fantastic! I'm very happy to hear that. Unfortunately, @leafac/sqlite is now deprecated, and the replacement is completely undocumented. What are your plans for this sqlite library?

Demo, direct link to the relevant time (13:10): https://youtu.be/3PCpXOPcVlM?t=793

@JoshuaWise Would it be an option to add something like this to better-sqlite3 directly? sql template strings that create cached prepared statements? This gives the dev convenience and code readability, security, and the performance.

[benbucksch]

Followup in #1157

[leafac]

@benbucksch: You may find the documentation for @radically-straightforward/sqlite at https://github.com/radically-straightforward/radically-straightforward/tree/main/sqlite (see the README). Also, the source code is documented, so you’ll also see documentation in your editor as you hover over function names. If there’s something in the documentation that’s unclear, please reach out and we’ll make it better.