Skip to Content
🔧 AdvancedRaw Sql

Last Updated: 3/20/2026

Raw SQL

The sql template tag (defined in src/raw-builder/sql.ts) lets you write raw SQL fragments when the query builder doesn’t cover your use case. It handles parameter binding automatically and can be used almost anywhere in a query.

Basic Usage

The sql tag creates a RawBuilder<T> where T is the result type you specify:

import { sql } from 'kysely'
 
const result = await db
  .selectFrom('person')
  .select(sql<string>`concat(first_name, ' ', last_name)`.as('full_name'))
  .execute()
 
console.log(result[0]?.full_name) // Type: string

The generated SQL (PostgreSQL):

select concat(first_name, ' ', last_name) as "full_name" from "person"

You must provide the result type manually (<string> in the example above). Kysely trusts you to get it right.

Parameter Binding

Substitutions (the things inside ${}) are automatically passed to the database as parameters, never interpolated into the SQL string. There’s no need to worry about SQL injection:

const firstName = 'Jennifer'
const lastName = 'Aniston'
 
const result = await db
  .selectFrom('person')
  .selectAll()
  .where(sql<boolean>`first_name = ${firstName} and last_name = ${lastName}`)
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where first_name = $1 and last_name = $2

Substitutions can be values, other sql expressions, queries, or anything that implements OperationNodeSource:

const petName = db.selectFrom('pet').select('name').limit(1)
const fullName = sql<string>`concat(first_name, ' ', last_name)`
 
const result = await sql<{ full_name: string; pet_name: string }[]>`
  select ${fullName} as full_name, ${petName} as pet_name
  from person
`.execute(db)

The generated SQL (PostgreSQL):

select concat(first_name, ' ', last_name) as full_name,
  (select "name" from "pet" limit $1) as pet_name
from person

Identifiers: sql.ref(), sql.table(), sql.id()

By default, substitutions are treated as values. Use these functions to tell Kysely to interpret them as identifiers instead.

sql.ref(columnReference)

Creates a column reference. The input can include a table name and schema:

const columnRef = 'person.first_name'
 
const result = await sql`select ${sql.ref(columnRef)} from person`.execute(db)

The generated SQL (PostgreSQL):

select "person"."first_name" from person

WARNING: Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. The input is not escaped by Kysely.

sql.table(tableReference)

Creates a table reference. The input can include a schema:

const table = 'public.person'
 
const result = await sql`select first_name from ${sql.table(table)}`.execute(db)

The generated SQL (PostgreSQL):

select first_name from "public"."person"

sql.id(...ids)

Creates an arbitrary identifier. Multiple arguments get separated by dots:

const schema = 'public'
const table = 'person'
const column = 'first_name'
 
const result = await sql`
  select ${sql.id(schema, table, column)}
  from ${sql.id(schema, table)}
`.execute(db)

The generated SQL (PostgreSQL):

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

Use sql.ref() and sql.table() instead of sql.id() whenever possible—they produce a more semantic operation node tree.

Literals: sql.lit() and sql.raw()

sql.lit(value)

Creates a literal value that gets merged into the SQL string (not sent as a parameter). Only accepts number, boolean, null, or string to reduce SQL injection risk:

const result = await db
  .selectFrom('person')
  .select(sql<number>`${sql.lit(1)} + ${sql.lit(2)}`.as('sum'))
  .execute()

The generated SQL (PostgreSQL):

select 1 + 2 as "sum" from "person"

WARNING: Using this with unchecked inputs WILL lead to SQL injection vulnerabilities. Use normal substitutions (which become parameters) whenever possible.

sql.raw(anySql)

Injects arbitrary SQL into the query without escaping:

const rawSql = "concat('Hello', ' ', 'World')"
 
const result = await db
  .selectFrom('person')
  .select(sql<string>`${sql.raw(rawSql)}`.as('greeting'))
  .execute()

The generated SQL (PostgreSQL):

select concat('Hello', ' ', 'World') as "greeting" from "person"

The difference between sql.lit() and sql.raw() is that sql.lit() assumes the input is a single value and quotes strings appropriately. sql.raw() assumes the input is arbitrary SQL and glues it in as-is.

WARNING: Using this with unchecked inputs WILL lead to SQL injection vulnerabilities.

Joining Lists: sql.join()

Use sql.join() to create comma-separated lists:

const nicknames = ['johnny', 'john', 'jon']
 
const result = await db
  .selectFrom('person')
  .where('nicknames', '@>', sql<string[]>`ARRAY[${sql.join(nicknames)}]`)
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):

select * from "person" where "nicknames" @> ARRAY[$1, $2, $3]

The second argument is the separator (defaults to sql, `):

const things = [123, sql`(1 == 1)`, sql.lit(false)]
 
const result = await sql`BEFORE ${sql.join(things, sql`::varchar, `)} AFTER`.execute(db)

The generated SQL (PostgreSQL):

BEFORE $1::varchar, (1 == 1)::varchar, false AFTER

Value Shortcut: sql.val()

sql.val(value) is a shortcut for sql&lt;ValueType>`${value}`:

const age = 25
 
// These are equivalent:
sql.val(age)
sql<number>`${age}`

Executing Raw Queries

Raw SQL snippets can be executed by calling .execute(db):

import { sql } from 'kysely'
 
const { rows } = await sql<Person[]>`select * from person`.execute(db)

The return value is a QueryResult with a rows property containing the result rows.

When to Use Raw SQL

Use raw SQL when:

  1. You need database-specific syntax not covered by the query builder (e.g., PostgreSQL’s ARRAY constructor, MySQL’s JSON_EXTRACT).
  2. You’re calling a database function that Kysely doesn’t have a helper for.
  3. You need to optimize a query in a way the builder doesn’t support.
  4. You’re migrating from raw SQL and want to incrementally adopt the query builder.

Prefer the query builder when possible. It provides better type safety, is easier to compose, and is more portable across databases.

Combining Raw SQL with the Query Builder

You can mix raw SQL and the query builder freely:

const result = await db
  .selectFrom('person')
  .select([
    'id',
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
  ])
  .where('age', '>=', 18)
  .where(sql<boolean>`birthdate between ${date1} and ${date2}`)
  .orderBy(sql<string>`concat(first_name, ' ', last_name)`)
  .execute()

The generated SQL (PostgreSQL):

select
  "id",
  concat(first_name, ' ', last_name) as "full_name"
from "person"
where "age" >= $1
  and birthdate between $2 and $3
order by concat(first_name, ' ', last_name)

What’s Next

  • Expressions & Expression Builder — Learn how to build type-safe expressions using eb() and related methods.
  • Reusable Helpers — Build reusable query helpers that combine raw SQL with type-safe references.
  • Plugins — Transform queries and results using the plugin system.
StreamsReusable Helpers