I am trying to create an Electron.js app with TypeScript. I need to make some services to handle validations and db queries. In the beginning, I tried to use raw SQL and indeed I created validations using Yup.
However, I ran into trouble creating a type-safe function that takes into account the fields that can be used in the query, depending on the joined tables and similar. I tried again to search for an easier solution and I found an SQL query builder called Kysely described as a type-safe API. Then I inferred the problem was not about raw SQL or query builder, but because of a Typescript limitation or my Typescript skills, because I can do these validations programmatically using Yup with no problems.
The type-safety in Kysely is only for hard coded params like:
db.selectFrom('users').select(['userID']) ...
While I need to receive params and pass it to Kysely or use it in raw SQL like this:
// This function will be exposed to the preload process and then to the renderer
const find({include, fields, where, orderBy}: {here where I need type safety}) {
return db.selectFrom('users').innerJoin(include).select(fields)
}
How can I overcome this problem, whether I will use raw SQL or a query builder?
If I understand your requirements then let's say this is your Kysely database type configuration:
export interface Database {
users: UsersTable;
other: OtherTable;
anotherTable: AnotherTable;
}
export interface UsersTable {
id: string;
}
export interface OtherTable {
column: string;
}
export interface AnotherTable {
anotherColumn: string;
}
You can get something like what you need by using the types:
type FieldWithTable<T extends keyof Database> = `${T}.${Extract<keyof Database[T], string>}`; // Extract because otherwise keyof could result in symbol or number which we don't want
interface FindParams<T extends keyof Database> {
include: T;
fields: FieldWithTable<T>|FieldWithTable<'users'>;
where: FieldWithTable<T>|FieldWithTable<'users'>;
orderBy: FieldWithTable<T>|FieldWithTable<'users'>;
}
function find<T extends keyof Database>({include, fields, where, orderBy}: FindParams<T>) {
return db.selectFrom('users').innerJoin(include).select(fields)
}
This should provide some type safety for calls like:
find<'other'>({
fields: 'users.id',
include: 'other',
orderBy: 'other.column',
where: 'users.id'
}); // OK
find<'other'>({
fields: "anotherTable.anotherColumn",
include: 'other',
orderBy: 'other.column',
where: 'users.id'
}); // Wrong
Admittedly this does mean that you need to know at least the join table when calling find. If these parameters come from a client request then then TypeScript (or any static type checker) is not appropriate for these checks because TypeScript itself only does compile-time checks.
For a more hybrid approach to type checking I have heard good things about https://zod.dev/ (though have not personally used it).