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}`

    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
    // 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)`)

    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)

    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)

    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)`

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

    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:

    (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})`

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

    Type Parameters

    • T = unknown


    • sqlFragments: TemplateStringsArray
    • Rest ...parameters: unknown[]

    Returns RawBuilder<T>

Generated using TypeDoc