Using nested literals to construct queries passed to SQLocal.sql

[MigFerreira]

Not sure if appropriate question to post here but here goes.

Does anyone know if its possible to nest query template literals? I cant seem to get it to work. Example:

DB.sql<Transaction[]> `SELECT id, account_id, date, time, description, notes, amount, total, source, active, p_id, created_at, updated_at
        ${filter ? `WHERE description=${filter.description}` : ``}
        FROM Transactions`;

[DallasHoff]

Do feel free to ask questions here. It's not just helpful to you; it could help others, and it's also a good form of feedback for me since I can use it to identify points of confusion and get ideas for how to improve the library to better address your use-case.

The reason this does not work is the way the sql tagged template works is it takes the expressions that you pass into the template and passes them to SQLite as parameters for a parameterized query. For example, this...

const requiredScore = 100;
const players = await sql`SELECT * FROM players WHERE score >= ${requiredScore}`;

...gets sent to SQLite as the parameterized query SELECT * FROM players WHERE score >= ? with the literal 100 as the parameter. This is a mechanism to prevent SQL injection since the parameter is treated strictly as a literal by the SQLite engine and never executed.

Now you could argue that SQL injection is not really a concern for local-first databases, but I don't think it's a good idea to open the door to that even in this library's case since allowing risky queries can still lead to some broken or insecure behavior in people's apps, depending on how they use the library.

If you need to conditionally add parts to your queries, you have a few options.

A) You could rewrite your query to something like this:

const description = filter?.description ?? null;
const transactions = await sql`SELECT * FROM transactions WHERE ${description} IS NULL OR description = ${description}`;

B) You could move the condition outside of the query, using an if statement to choose between 2 different queries, one with the additional part and one without.

C) If you need to do anything beyond a basic level of complexity with your queries, I'd really recommend using SQLocal with Kysely or Drizzle ORM. They do a great job of helping you write safe SQL queries in TypeScript.