Retrieve compiled SQL query

[ThomasAribart]

Hello and thanks for this wonderful lib !

On my project, I use AWS S3 Select feature on CSVs stored in S3. It allows executing a SQL-like query directly on those CSVs:

import { S3Client, SelectObjectContentCommand } from '@aws-sdk/client-s3';

const s3Client = new s3Client()

const Response = await s3Client.send(
  new SelectObjectContentCommand({
    ... // some serialization options
    ExpressionType: 'SQL',
    Expression: `
      SELECT ...
      FROM S3Object
      WHERE ...
    `
  })
);

I would really like using kysely to build such queries but for the moment I cannot:

• I haven't found a way to simply retrieve the compiled SQL query string to provide it to the S3Client
• The Kysely class constructor requires a dialect whereas I don't really need one (other solution is to build an adapter for S3Select but that's a whole lotta work)

Would it be possible to make dialect optional in the constructor (and throw an error if none is found when needed) and expose a compileQuery method on the db to retrieve the compiled query ?

[igalklebanov]

Hey 👋

You can brew a dialect with existing built-in components and replace the driver with built-in DummyDriver.

This is documented in the browser example.

Now just use .compile() with any of the query builders to get CompiledQuery instances that contain sql and parameters fields.

[ThomasAribart]

Hi @igalklebanov !

Thanks for the response, I missed the DummyDriver class :)

I had seen the compile method but it still requires to hydrate the parameters in the sql query. Is there a way to not have to do that ? Nitpick but it's always nice to reduce verbosity and custom code.

Still, I'll give it a try ! Thanks again 👍

[koskimas]

Normally you should never "hydrate" parameters to the SQL string. That's a sure way to get your system pwned. For that reason, there's no built in way to do it.

[ThomasAribart]

I see, high risk of SQL injection there.

Sadly there is no Parameters attribute in the S3Select interface, so I'll have to create a dangerouslyHydrateParameters helper to do that 🤷‍♂️ Many thanks !

[wirekang]

I see, high risk of SQL injection there.

Sadly there is no Parameters attribute in the S3Select interface, so I'll have to create a dangerouslyHydrateParameters helper to do that man_shrugging Many thanks !

Can you share your helper?

[koskimas]

Sadly there is no Parameters attribute in the S3Select interface

Yeah, I understand that in this case it's necessary. I was just explaining why a helper like that won't be added to Kysely.

Can you share your helper?

const parameters = ['foo', 'bar']
const sql = 'select $1 from $2'

const hydrated = sql.replace(/(\$\d+)/g, (arg) => parameters[parseInt(arg.slice(1)) - 1])

In case of mysql placeholders:

const parameters = ['foo', 'bar']
const sql = 'select ? from ?'
let i = 0;

const hydrated = sql.replace(/(\?)/g, () => parameters[i++])

Depending on your query, you also need to add some ' characters around strings

[ThomasAribart]

@wirekang I went for this:

const dangerouslyHydrateSQLParameters = (
  sql: string,
  parameters: readonly unknown[],
): string => {
  for (const parameter of parameters) {
    sql = sql.replace('?', `'${String(parameter)}'`);
  }

  return sql;
};

[igalklebanov]

@ThomasAribart Got inspired and implemented this based on your blog post. 🤗

[ThomasAribart]

@igalklebanov Awesome ! 🤩