Interface Sql

This can be used to add arbitrary identifiers to SQL snippets.

Does the same thing as ref and table but can also be used for any other identifiers like index names.

You should use ref and table instead of this whenever possible as they produce a more semantic operation node tree.

WARNING! Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not checked or escaped by Kysely in any way.

const indexName = 'person_first_name_index'

sql`create index ${indexName} on person`
Copy

The generated SQL (PostgreSQL):

create index "person_first_name_index" on person
Copy

Multiple identifiers get separated by dots:

const schema = 'public'
const columnName = 'first_name'
const table = 'person'

sql`select ${sql.id(schema, table, columnName)} from ${sql.id(schema, table)}`
Copy

The generated SQL (PostgreSQL):

select "public"."person"."first_name" from "public"."person"
Copy

This can be used to add lists of things to SQL snippets.

Examples

import type { Person } from 'type-editor' // imaginary module

function findByNicknames(nicknames: string[]): Promise<Person[]> {
  return db
    .selectFrom('person')
    .selectAll()
    .where('nicknames', '@>', sql<string[]>`ARRAY[${sql.join(nicknames)}]`)
    .execute()
}
Copy

The generated SQL (PostgreSQL):

select * from "person"
where "nicknames" @> ARRAY[$1, $2, $3, $4, $5, $6, $7, $8]
Copy

The second argument is the joining SQL expression that defaults to

sql`, `
Copy

In addition to values, items in the list can be also sql expressions, queries or anything else the normal substitutions support:

const things = [
  123,
  sql`(1 == 1)`,
  db.selectFrom('person').selectAll(),
  sql.lit(false),
  sql.id('first_name')
]

sql`BEFORE ${sql.join(things, sql`::varchar, `)} AFTER`
Copy

The generated SQL (PostgreSQL):

BEFORE $1::varchar, (1 == 1)::varchar, (select * from "person")::varchar, false::varchar, "first_name" AFTER
Copy

This can be used to add literal values to SQL snippets.

WARNING! Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not checked or escaped by Kysely in any way. You almost always want to use normal substitutions that get sent to the db as parameters.

const firstName = 'first_name'

sql`select * from person where first_name = ${sql.lit(firstName)}`
Copy

The generated SQL (PostgreSQL):

select * from person where first_name = 'first_name'
Copy

As you can see from the example above, the value was added directly to the SQL string instead of as a parameter. Only use this function when something can't be sent as a parameter.

This can be used to add arbitrary runtime SQL to SQL snippets.

WARNING! Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not checked or escaped by Kysely in any way.

const firstName = "'first_name'"

sql`select * from person where first_name = ${sql.raw(firstName)}`
Copy

The generated SQL (PostgreSQL):

select * from person where first_name = 'first_name'
Copy

Note that the difference to sql.lit is that this function doesn't assume the inputs are values. The input to this function can be any sql and it's simply glued to the parent string as-is.

This can be used to add runtime column references to SQL snippets.

By default ${} substitutions in sql template strings get transformed into parameters. You can use this function to tell Kysely to interpret them as column references instead.

WARNING! Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not checked or escaped by Kysely in any way.

const columnRef = 'first_name'

sql`select ${sql.ref(columnRef)} from person`
Copy

The generated SQL (PostgreSQL):

select "first_name" from person
Copy

The references can also include a table name:

const columnRef = 'person.first_name'

sql`select ${sql.ref(columnRef)}} from person`
Copy

The generated SQL (PostgreSQL):

select "person"."first_name" from person
Copy

The references can also include a schema on supported databases:

const columnRef = 'public.person.first_name'

sql`select ${sql.ref(columnRef)}} from person`
Copy

The generated SQL (PostgreSQL):

select "public"."person"."first_name" from person
Copy

This can be used to add runtime table references to SQL snippets.

By default ${} substitutions in sql template strings get transformed into parameters. You can use this function to tell Kysely to interpret them as table references instead.

WARNING! Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not checked or escaped by Kysely in any way.

const table = 'person'

sql`select first_name from ${sql.table(table)}`
Copy

The generated SQL (PostgreSQL):

select first_name from "person"
Copy

The references can also include a schema on supported databases:

const table = 'public.person'

sql`select first_name from ${sql.table(table)}`
Copy

The generated SQL (PostgreSQL):

select first_name from "public"."person"
Copy

sql.val(value) is a shortcut for:

import { sql } from 'kysely'

const value = 123
type ValueType = typeof value

sql<ValueType>`${value}`
Copy