Type safe SQL in TypeScript, from inferred rows to raw queries

#What type safe SQL in TypeScript actually means

"Type safe" gets used loosely. For database code it is useful to split it into three questions:

• Rows: does the compiler know the exact shape of what a query returns, including which fields can be null?
• Inputs: are the values you filter or insert checked against the column types, and are they sent as bound parameters rather than pasted into the SQL text?
• Names: does a typo in a column or relation name fail at compile time, before a user hits it?

An ORM with an inferred schema answers all three for everyday queries. Raw SQL can only answer the second one completely, but with the right API it gets close on the first. This guide covers both, using kastor's API, so you know exactly where the compiler protects you and where it cannot.

#Inferred row types and select narrowing

Everything starts with the schema. Each column builder carries a TypeScript type: t.datetime() is a Date, t.decimal(10, 2) is a string (so no precision is lost), t.bigint() is a bigint, t.enum([...]) is a union of literals, and .nullable() adds | null.

import { table, t } from 'kastor-orm'
import type { InferSelect, InferInsert } from 'kastor-orm'

export const customers = table('customers', {
  id: t.id(),
  email: t.string(255).unique(),
  name: t.string().nullable(),
})

export const orders = table('orders', {
  id: t.id(),
  customerId: t.int().references(() => customers.id).map('customer_id'),
  status: t.enum(['pending', 'paid', 'refunded']).default('pending'),
  total: t.decimal(10, 2),
  note: t.text().nullable(),
  createdAt: t.datetime().defaultNow().map('created_at'),
})

export type Order = InferSelect<typeof orders>
// { id: number; customerId: number; status: 'pending' | 'paid' | 'refunded';
//   total: string; note: string | null; createdAt: Date }

export type NewOrder = InferInsert<typeof orders>
// customerId and total are required; id, status, note and createdAt are optional

Queries derive their result type from the arguments. Without select you get the full row. With select the type narrows to the keys you asked for, and with include each relation appears under its name with the related table's row type.

import { db } from '../db/client'
// relations: customers.orders = many(orders), orders.customer = one(customers, ...)

export async function dashboard(since: Date) {
  // Order[]
  const paid = await db.orders.findMany({
    where: { status: 'paid', createdAt: { gte: since } },
    orderBy: { createdAt: 'desc' },
    take: 20,
  })

  // { id: number; total: string }[]
  const totals = await db.orders.findMany({ select: { id: true, total: true } })

  // each customer row gains orders: Order[] and _count: { orders: number }
  const top = await db.customers.findMany({
    include: { orders: { where: { status: 'paid' }, take: 3 }, _count: { select: { orders: true } } },
  })

  return { paid, totals, top }
}

Use InferSelect and InferInsert at the edges of your app: function signatures, API handlers and test factories. Inside a function, let inference do the work and avoid writing row types by hand, because hand written types drift from the schema.

#Mistakes the compiler catches

Because every argument is typed against the schema, a whole class of bugs never reaches runtime. Each line below is a compile error, shown with @ts-expect-error so the snippet itself type checks:

// @ts-expect-error: 'stauts' is not a column of orders
await db.orders.findMany({ where: { stauts: 'paid' } })

// @ts-expect-error: 'shipped' is not one of the enum values
await db.orders.findMany({ where: { status: 'shipped' } })

// @ts-expect-error: decimal columns take a string, not a number
await db.orders.create({ data: { customerId: 1, total: 19.9 } })

// @ts-expect-error: customerId is required on insert
await db.orders.create({ data: { total: '19.90' } })

// @ts-expect-error: there is no relation named 'customr'
await db.orders.findMany({ include: { customr: true } })

Rename a column in the schema and every query that uses the old name turns red at once. There is no generate step in between, so the feedback arrives while you type.

#JSON, money and big numbers

A few column types deserve attention because their TypeScript type is chosen to avoid silent data loss, and that choice shows up in your code.

• Decimals are strings. A JavaScript number cannot hold every decimal(10, 2) value exactly, so kastor returns the string the database sent. Do money math with integer cents or a decimal library, and send strings back on insert.
• Big integers are bigint. Columns declared with t.bigint() or t.bigId() come back as bigint. Note that JSON.stringify throws on bigint, so convert them to strings in your API layer before returning a response.
• JSON columns use the type you declare. t.json<Settings>() types the column as Settings in queries and inserts. The database does not check that shape, so validate data from outside your app before it is stored.
• Dates and times. t.datetime() is a Date, while t.date() and t.time() are strings such as '2026-10-13' and '14:30:00', which avoids time zone shifts for calendar dates.

These rules are the same on every adapter, so code that handles them correctly on PostgreSQL also works on MySQL, SQLite or SQL Server.

#Typed raw SQL with db.$sql

Some queries read better as SQL: reports with grouping, window functions, recursive CTEs. db.$sql is a tagged template. Every ${value} you interpolate becomes a bound parameter, sent to the database separately from the SQL text, so the database never parses it as SQL. That is what makes it safe from SQL injection, and it is why you should never build a query string by concatenation.

The type argument describes the rows. Remember that raw SQL uses database names, so createdAt is created_at here because of .map().

import { db } from '../db/client'

type DailyRevenue = { day: string; orders: number; revenue: string }

export function dailyRevenue(since: Date) {
  return db.$sql<DailyRevenue>`
    select to_char(created_at, 'YYYY-MM-DD') as day,
           count(*)::int as orders,
           sum(total)::text as revenue
    from orders
    where status = ${'paid'} and created_at >= ${since}
    group by 1
    order by 1`
}

export async function refund(orderId: number) {
  // $execute returns the number of affected rows
  const affected = await db.$execute`
    update orders set status = 'refunded'
    where id = ${orderId} and status = 'paid'`
  return affected === 1
}

Be honest with yourself about what the type argument is: a declaration, not a check. kastor cannot know the shape of arbitrary SQL, so values arrive as your driver returns them. The pg driver, for example, returns count(*) as a string because it is a 64 bit integer, which is why the query casts it with ::int. Keep each raw query covered by a test that runs against a real database.

#sql fragments inside normal queries

Often you need one piece of SQL, not a whole query. The sql tag builds a fragment you can put in a where clause next to typed filters. The rest of the query, including its result type, stays the same.

import { sql } from 'kastor-orm'
import { db } from '../db/client'

export function longNotes(minLength: number) {
  return db.orders.findMany({
    where: { status: 'paid', AND: [sql`char_length(note) > ${minLength}`] },
    select: { id: true, note: true },
  })
}

const sortable = ['created_at', 'total'] as const

export function newestFirst(column: (typeof sortable)[number], limit: number) {
  // sql.ident quotes an identifier; only pass names from an allowlist
  return db.$sql<{ id: number }>`
    select id from orders order by ${sql.ident(column)} desc limit ${limit}`
}

Three helpers cover the rest. sql.ident quotes a table or column name, for dynamic sorting with an allowlist as above. sql.join combines a list of fragments. sql.raw inserts text without parameters: use it only for constant strings you wrote yourself, never for anything that came from a request. If you can see user input flowing into sql.raw, treat it as a security bug.

#A short checklist

• Declare the schema once in TypeScript and derive types with InferSelect and InferInsert instead of writing interfaces by hand.
• Prefer the object API for CRUD and relations: it gives you checked names, checked values and exact result types.
• Use select to return only what a screen or endpoint needs; the type follows.
• For raw SQL, use db.$sql with a type argument, interpolate values, cast aggregates in SQL and test against a real database.
• Never concatenate strings into SQL and never pass request data to sql.raw.
• Handle failures with isKastorError and the error code, which works the same on every database. See the API reference for the full list.

To see how these types work alongside relation loading, read the TypeScript ORM guide and the guide on avoiding N+1 queries.

#Questions people ask

#Related guides