Raw SQL

import { sql } from 'kysely'
 
// Basic usage with parameters
const id = 123
const query = sql<Person[]>`select * from person where id = ${id}`
 
// Execute the query
const result = await query.execute(db)

const persons = await db
  .selectFrom('person')
  .select([
    // Use .as() to alias SQL expressions
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name'),
  ])
  .execute()

const result = await db
  .selectFrom('person')
  .selectAll()
  .where(sql`birthdate between ${startDate} and ${endDate}`)
  .execute()

const petQuery = db.selectFrom('pet').select('name').limit(1)
const nameExpr = sql`concat(first_name, ' ', last_name)`
 
const combined = sql`
  select ${nameExpr} as full_name, ${petQuery} as pet_name
  from person
`

const columnName = 'first_name'
sql`select ${sql.ref(columnName)} from person`
// Result: select "first_name" from person

const tableName = 'person'
sql`select * from ${sql.table(tableName)}`
// Result: select * from "person"

const nicknames = ['nick1', 'nick2', 'nick3']
sql`select * from person where nickname in (${sql.join(nicknames)})`
// Result: select * from person where nickname in ($1, $2, $3)
 
// Custom separator
sql.join(items, sql`::varchar, `)

sql`select * from person where status = ${sql.lit('active')}`
// Result: select * from person where status = 'active'

sql`select * from ${sql.raw('person')} where active = true`
// Result: select * from person where active = true

// Type-safe column references
db.selectFrom('person')
  .select((eb) => {
    const firstName = eb.ref('first_name')
    const lastName = eb.ref('last_name')
    return sql<string>`concat(${firstName}, ' ', ${lastName})`.as('full_name')
  })
  .execute()