How to implement type safety in dynamic SQL queries with TypeScript and query builders

Backend
1

Building a desktop app with Electron and TypeScript here. I’m working on creating service functions that handle database operations and input validation.

My current setup:

  • Started with plain SQL queries
  • Using Joi for validation (works great for basic stuff)
  • Switched to Kysely query builder hoping for better type safety

The problem I’m facing:
Kysely gives me type safety when I write queries like this:

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

But I need dynamic queries where parameters come from user input:

// This function gets called from the frontend
const getData = (options: {relations: string[], columns: string[], filters: object, sorting: string}) => {
  return database.selectFrom('customers').leftJoin(options.relations).select(options.columns)
}

The issue: TypeScript can’t validate the dynamic field names and table relationships at compile time. I can handle validation with Joi at runtime, but I lose the compile-time safety benefits.

Is there a way to get proper TypeScript support for dynamic query building? Should I stick with raw SQL instead, or is there a better approach for this kind of flexible database layer?

2

same issue haha! i built query templates n mapped dynamic inputs to specific patterns. not as flexible as id like, but got type safety back n did the job. hope it helps!

3

Nice approach with the mapped types! How do you handle users adding completely new columns that weren’t in the original schema though? And did you hit any performance issues with type checking on bigger schemas?

4

I hit this exact problem building a reporting dashboard. Here’s what worked for me: I combined TypeScript’s literal types with mapped types to lock down the dynamic inputs without killing flexibility. First, I defined schema interfaces for my database structure. Then I used conditional types to check column selections against specific tables. For dynamic relations, I built a mapping object that explicitly shows which tables can join together. Instead of accepting random strings for columns and relations, I used union types pulled straight from the schema definitions. Yeah, it’s more work upfront to set up these type mappings, but it catches bogus field references at compile time while still letting you build queries on the fly. Performance didn’t take a hit, and debugging got way easier since TypeScript immediately calls out bad column names or invalid table relationships. I kept Joi for runtime validation as backup, but most issues got caught during development.