Function sql

Template tag for creating raw SQL snippets and queries.

import { sql } from 'kysely'

const id = 123
const snippet = sql<Person[]>`select * from person where id = ${id}`
Copy

Substitutions (the things inside ${}) are automatically passed to the database as parameters and are never interpolated to the SQL string. There's no need to worry about SQL injection vulnerabilities. Substitutions can be values, other sql expressions, queries and almost anything else Kysely can produce and they get handled correctly. See the examples below.

If you need your substitutions to be interpreted as identifiers, value literals or lists of things, see the ref, table, id, lit, raw and join functions.

You can pass sql snippets returned by the sql tag pretty much anywhere. Whenever something can't be done using the Kysely API, you should be able to drop down to raw SQL using the sql tag. Here's an example query that uses raw sql in a bunch of methods:

const persons = await db
  .selectFrom('person')
  .select(
    // If you use `sql` in a select statement, remember to call the `as`
    // method to give it an alias.
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
  )
  .where(sql`birthdate between ${date1} and ${date2}`)
  // Here we assume we have list of nicknames for the person
  // (a list of strings) and we use the PostgreSQL `@>` operator
  // to test if all of them are valid nicknames for the user.
  .where('nicknames', '@>', sql`ARRAY[${sql.join(nicknames)}]`)
  .orderBy(sql`concat(first_name, ' ', last_name)`)
  .execute()
Copy

The generated SQL (PostgreSQL):

select concat(first_name, ' ', last_name) as "full_name"
from "person"
where birthdate between $1 and $2
and "nicknames" @> ARRAY[$3, $4, $5, $6, $7, $8, $9, $10]
order by concat(first_name, ' ', last_name)
Copy

SQL snippets can be executed by calling the execute method and passing a Kysely instance as the only argument:

const result = await sql<Person[]>`select * from person`.execute(db)
Copy

You can merge other sql expressions and queries using substitutions:

const petName = db.selectFrom('pet').select('name').limit(1)
const fullName = sql`concat(first_name, ' ', last_name)`

sql`
  select ${fullName} as full_name, ${petName} as pet_name
  from person
`
Copy

Substitutions also handle ref, ref and pretty much anything else you throw at it. Here's an example of calling a function in a type-safe way:

db.selectFrom('person')
  .select([
    'first_name',
    'last_name',
    (eb) => {
      // The `eb.ref` method is type-safe and only accepts
      // column references that are possible.
      const firstName = eb.ref('first_name')
      const lastName = eb.ref('last_name')

      const fullName = sql<string>`concat(${firstName}, ' ', ${lastName})`
      return fullName.as('full_name')
    }
  ])
Copy

don't know if that amount of ceremony is worth the small increase in type-safety though... But it's possible.