How to implement type-safe dynamic queries in TypeScript using Kysely query builder

Backend
1

I’m building an Electron application with TypeScript and struggling with dynamic query generation. My goal is to create backend functions that can handle different database operations while maintaining type safety.

Currently I’m using Kysely as my SQL query builder because it promises type safety. The issue is that Kysely works great for static queries like:

database.selectFrom('customers').select(['customerName', 'email'])

But I need to build dynamic queries where the parameters come from user input. Here’s what I’m trying to achieve:

const getUserData = (options: {relations: string[], columns: string[], filters: object, sorting: string}) => {
  return database.selectFrom('customers')
    .leftJoin(options.relations)
    .select(options.columns)
    .where(options.filters)
}

The problem is making the options parameter type-safe so TypeScript knows which columns and relations are valid based on the table schema. I’ve also tried using Yup for validation but that doesn’t solve the TypeScript typing issue.

Is there a way to make dynamic query parameters type-safe in TypeScript, either with Kysely or raw SQL approaches?

2

For type-safe dynamic queries with Kysely, conditional types are your best friend. Create a generic function that enforces constraints based on your database schema - this way columns and relations get proper type-checking:

type TableColumns<T extends keyof Database> = keyof Database[T]
type TableRelations<T extends keyof Database> = /* define based on your FK relationships */

const buildQuery = <T extends keyof Database>(
  table: T,
  options: {
    columns: TableColumns<T>[],
    relations?: TableRelations<T>[],
    filters?: Partial<Database[T]>
  }
) => {
  return database.selectFrom(table)
    .select(options.columns)
    // dynamically apply joins and filters
}

This gives you smooth IntelliSense and catches invalid column references at compile time instead of blowing up at runtime.

3

try using kysely’s DynamicModule interface. it helps create a type-safe wrapper using Selectable & Insertable from your schema. u could do type ValidColumns<T> = keyof Selectable<T> and then apply that on your options. this worked for me on a similar project, but it can get kinda verbose.

4

nice use of dynamicmodule! have you tried kysely’s expression type for filters? i’m wondering how you handle nested relations - does type safety work with multiple joins? and how’s performance with complex queries?