Parameter Reuse Optimization for SQL Template Literals

[programmarchy]

Parameter Reuse Optimization for SQL Template Literals

Current Behavior

When using SQL template literals with Kysely, identical parameters are assigned new parameter indices each time they appear in the query, even when the values are the same. This leads to unnecessary parameter duplication in the compiled query.

For example:

const terms = ['test', 'example'];
const query = sql<any>`
  select *
  from some_table
  where
    column1 = any(${terms}::text[])  -- Becomes $1
    or column2 = any(${terms}::text[]) -- Becomes $2
`;

Compiles to:

select * from some_table where column1 = any($1::text[]) or column2 = any($2::text[])
Parameters: [['test', 'example'], ['test', 'example']]

Expected Behavior

When the same parameter value is used multiple times in a query, it would be more efficient to reuse the same parameter index:

select * from some_table where column1 = any($1::text[]) or column2 = any($1::text[])
Parameters: [['test', 'example']]

This optimization would:

1. Reduce the number of parameters passed to the database
2. Potentially improve query planning as the database can recognize the parameters are identical
3. Make the generated SQL more concise

[koskimas]

How would this work on MySQL or sqlite?

It wouldn't. And if it's impossible on two of the three most uses dialects in the world, it's probably not horribly important?

[programmarchy]

SQLite supports named params, which provide similar functionality.

But is the kysely approach to only support the lowest common denominator of dialects? That does not seem to be the case because there are plenty of unit tests where MySQL and SQLite have “not supported” conditions.

[koskimas]

But is the kysely approach to only support the lowest common denominator of dialects

No, but it is our approach to only optimize when there's an actual measured real-world problem. Micro-optimization based on guesswork is almost always useless or harmful.

I can see there could be an issue if you're passing a very large array multiple times. But optimizing this without breaking existing code can be surprisingly hard.

[koskimas]

It's pretty easy to make this work in most cases though and you can do this for your own project: https://kyse.link/MkpJ1

~You don't see the executed queries in the playground right panel because I created a new instance of Kysely. You need to open dev tools and look at the console.~ 

EDIT: updated the link to use db.executeQuery

[igalklebanov]

@koskimas

You don't see the executed queries in the playground left panel because I created a new instance of Kysely. You need to open dev tools and look at the console.

You can:

const compiledQuery = query.compile();

await db.executeQuery(compiledQuery);