Raw SQL

The sql template tag allows you to write raw SQL snippets and queries when Kysely’s query builder API doesn’t cover your use case. It provides a safe way to embed SQL while preventing SQL injection through parameterization.

Basic Usage

Simple Raw SQL

Create a raw SQL snippet:

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 into the SQL string. There’s no need to worry about SQL injection vulnerabilities.

Executing Raw SQL

SQL snippets can be executed by calling the execute method:

import { sql } from 'kysely'

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

Using Raw SQL in Queries

You can use raw SQL expressions in select, where, and other clauses:

const nicknames = ['johnny', 'john', 'jon']
const date1 = new Date('2000-01-01')
const date2 = new Date('2001-01-01')

const persons = await db
  .selectFrom('person')
  .select(
    // Raw SQL in select - remember to call `as` to give it an alias
    sql<string>`concat(first_name, ' ', last_name)`.as('full_name')
  )
  .where(sql<boolean>`birthdate between ${date1} and ${date2}`)
  // PostgreSQL array operator
  .where('nicknames', '@>', sql<string[]>`ARRAY[${sql.join(nicknames)}]`)
  .orderBy(sql<string>`concat(first_name, ' ', last_name)`)
  .execute()

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)

Merging SQL Expressions

You can merge other sql expressions and queries using substitutions:

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

sql<{ full_name: string; pet_name: string }[]>`
  select ${fullName} as full_name, ${petName} as pet_name
  from person
`.execute(db)

Helper Methods

The sql tag provides several helper methods for common use cases:

sql.ref - Column References

Interpret a substitution as a column reference:

const columnRef = 'first_name'

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

The generated SQL (PostgreSQL):

select "first_name" from person

References can include table names:

const columnRef = 'person.first_name'

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

The generated SQL (PostgreSQL):

select "person"."first_name" from person

And schemas on supported databases:

const columnRef = 'public.person.first_name'

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

The generated SQL (PostgreSQL):

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

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

sql.table - Table References

Interpret a substitution as a table reference:

const table = 'person'

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

The generated SQL (PostgreSQL):

select first_name from "person"

With schema:

const table = 'public.person'

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

The generated SQL (PostgreSQL):

select first_name from "public"."person"

Using sql.table with unchecked inputs WILL lead to SQL injection vulnerabilities.

sql.id - Arbitrary Identifiers

Add arbitrary identifiers (like index names):

const indexName = 'person_first_name_index'

sql`create index ${sql.id(indexName)} on person`

The generated SQL (PostgreSQL):

create index "person_first_name_index" on person

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

The generated SQL (PostgreSQL):

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

Using sql.id with unchecked inputs WILL lead to SQL injection vulnerabilities.

sql.val - Value Parameters

sql.val(value) is a shortcut for:

const value = 123
type ValueType = typeof value

sql<ValueType>`${value}`

Example:

await db
  .selectFrom('person')
  .select((eb) => [
    'first_name',
    eb.fn.coalesce('last_name', sql.val('N/A')).as('last_name')
  ])
  .execute()

sql.lit - Literal Values

Add literal values directly to the SQL string:

const firstName = 'first_name'

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

The generated SQL (PostgreSQL):

select * from person where first_name = 'first_name'

The value is added directly to the SQL string instead of as a parameter. Using sql.lit with unchecked inputs WILL lead to SQL injection vulnerabilities. Only use this when something can’t be sent as a parameter.

sql.raw - Raw SQL Strings

Add arbitrary runtime SQL:

const firstName = "'first_name'"

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

The generated SQL (PostgreSQL):

select * from person where first_name = 'first_name'

The difference between sql.lit and sql.raw is that sql.lit assumes the input is a value and will quote it appropriately, while sql.raw assumes the input is already valid SQL and glues it in as-is.

Using sql.raw with unchecked inputs WILL lead to SQL injection vulnerabilities.

sql.join - Lists

Join arrays of values or expressions:

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

The generated SQL (PostgreSQL):

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

The second argument is the joining expression (defaults to , ):

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`

The generated SQL (PostgreSQL):

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

Type Safety

When using the sql tag, you need to specify the type of the expression:

// Specify that this expression returns a string
const fullName = sql<string>`concat(first_name, ' ', last_name)`

// Specify that this expression returns a boolean
const isAdult = sql<boolean>`age >= 18`

// Specify the return type of a query
const results = await sql<Person[]>`select * from person`.execute(db)

Working with Expression Builder

You can combine raw SQL with the expression builder for type-safe column references:

db.selectFrom('person')
  .select([
    'first_name',
    'last_name',
    (eb) => {
      // The `eb.ref` method is type-safe
      const firstName = eb.ref('first_name')
      const lastName = eb.ref('last_name')

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

Common Use Cases

Database Functions

Use raw SQL for database-specific functions:

// PostgreSQL JSON operations
const result = await db
  .selectFrom('person')
  .select([
    'id',
    sql<string>`data->>'name'`.as('json_name')
  ])
  .execute()

// Window functions
const result = await db
  .selectFrom('person')
  .select([
    'id',
    'age',
    sql<number>`row_number() over (order by age)`.as('row_num')
  ])
  .execute()

CTEs with Raw SQL

const result = await db
  .with('top_persons', sql`
    select * from person
    order by age desc
    limit 10
  `)
  .selectFrom('top_persons')
  .selectAll()
  .execute()

Complex WHERE Conditions

const result = await db
  .selectFrom('person')
  .selectAll()
  .where(sql<boolean>`
    (age > 18 AND country = 'USA')
    OR (age > 21 AND country = 'Canada')
  `)
  .execute()

Database-Specific Features

// PostgreSQL full-text search
const result = await db
  .selectFrom('articles')
  .selectAll()
  .where(sql<boolean>`to_tsvector('english', content) @@ to_tsquery('english', ${searchTerm})`)
  .execute()

// MySQL MATCH AGAINST
const result = await db
  .selectFrom('articles')
  .selectAll()
  .where(sql<boolean>`MATCH(title, content) AGAINST(${searchTerm} IN BOOLEAN MODE)`)
  .execute()

Best Practices

Always use parameters: Never concatenate user input directly into SQL strings
Specify types: Always provide type parameters to the sql tag
Use helpers cautiously: Only use sql.ref, sql.table, sql.id, sql.lit, and sql.raw with trusted inputs
Prefer query builder: Use raw SQL only when the query builder doesn’t support your use case
Keep it simple: Break complex raw SQL into smaller, reusable pieces

API Reference

Main Tag

sql<T> - Create a raw SQL expression

Helper Methods

sql.ref<R>(columnReference) - Column reference
sql.table<T>(tableReference) - Table reference
sql.id<T>(...ids) - Arbitrary identifier
sql.val<V>(value) - Value parameter
sql.lit<V>(value) - Literal value
sql.raw<R>(sql) - Raw SQL string
sql.join<T>(array, separator?) - Join array items

Deprecated

sql.value<V>(value) - Use sql.val instead
sql.literal<V>(value) - Use sql.lit instead

Get Started

Core Concepts

Query Operations

Schema & Migrations

Recipes & Guides

Basic Usage

Simple Raw SQL

Executing Raw SQL

Using Raw SQL in Queries

Merging SQL Expressions

Helper Methods

sql.ref - Column References

sql.table - Table References

sql.id - Arbitrary Identifiers

sql.val - Value Parameters

sql.lit - Literal Values

sql.raw - Raw SQL Strings

sql.join - Lists

Type Safety

Working with Expression Builder

Common Use Cases

Database Functions

CTEs with Raw SQL

Complex WHERE Conditions

Database-Specific Features

Best Practices

API Reference

Main Tag

Helper Methods

Deprecated

Build docs developers (and LLMs) love

Get Started

Core Concepts

Query Operations

Schema & Migrations

Recipes & Guides

​Basic Usage

​Simple Raw SQL

​Executing Raw SQL

​Using Raw SQL in Queries

​Merging SQL Expressions

​Helper Methods

​sql.ref - Column References

​sql.table - Table References

​sql.id - Arbitrary Identifiers

​sql.val - Value Parameters

​sql.lit - Literal Values

​sql.raw - Raw SQL Strings

​sql.join - Lists

​Type Safety

​Working with Expression Builder

​Common Use Cases

​Database Functions

​CTEs with Raw SQL

​Complex WHERE Conditions

​Database-Specific Features

​Best Practices

​API Reference

​Main Tag

​Helper Methods

​Deprecated

Build docs developers (and LLMs) love

Basic Usage

Simple Raw SQL

Executing Raw SQL

Using Raw SQL in Queries

Merging SQL Expressions

Helper Methods

sql.ref - Column References

sql.table - Table References

sql.id - Arbitrary Identifiers

sql.val - Value Parameters

sql.lit - Literal Values

sql.raw - Raw SQL Strings

sql.join - Lists

Type Safety

Working with Expression Builder

Common Use Cases

Database Functions

CTEs with Raw SQL

Complex WHERE Conditions

Database-Specific Features

Best Practices

API Reference

Main Tag

Helper Methods

Deprecated