K
Kysely•3w ago
willy_24

How to make general purpose queries?

Hello everyone. I am currently developing a medium-scale server using express and Kysely as a query builder (which I have adored so far). However, with the growing codebase, I've noticed that many select, update and insert queries are similar between them. That being said, it would be very useful to have a wrapper to perform those similar queries, which I've tried to build like so: 
import * as Kysely from "kysely";
import PT_db, { sqlQueryErrors, type SqlQueryResult } from "./db.ts";
import type { DB } from "./kysely-interfaces";


type Table = keyof DB;
type Column<TB extends Table> = keyof DB[TB];
type ColumnType<TB extends Table, CL extends Column<TB>> = DB[TB][CL];

type AllowedOperators = Kysely.Operator;

type WhereExpression<TB extends Table, CL extends Column<TB>, OP extends AllowedOperators> = {
    column: CL;
    operator: OP;
    filterBy: OP extends "in"
        ? ColumnType<TB, CL>[]
        : ColumnType<TB, CL>;
}

async function selectAll<TB extends Table>(
    table: TB,
    where?: WhereExpression<TB, Column<TB>, AllowedOperators>
): Promise<SqlQueryResult<Kysely.Selectable<DB[TB]>[]>> {

    const db: Kysely.Kysely<DB> | null = await PT_db.getInstance();
    if (!db) return sqlQueryErrors.nullConnection();
    
    try {
        let q = db.selectFrom(table)
            .selectAll();

        if (where) {
            q = q.where(where.column, where.operator, where.filterBy);
        }
        return { sqlError: false, data: [] };
    }
    catch (e) {
        return sqlQueryErrors.handleQueryException(e);
    }
}
import * as Kysely from "kysely";
import PT_db, { sqlQueryErrors, type SqlQueryResult } from "./db.ts";
import type { DB } from "./kysely-interfaces";


type Table = keyof DB;
type Column<TB extends Table> = keyof DB[TB];
type ColumnType<TB extends Table, CL extends Column<TB>> = DB[TB][CL];

type AllowedOperators = Kysely.Operator;

type WhereExpression<TB extends Table, CL extends Column<TB>, OP extends AllowedOperators> = {
    column: CL;
    operator: OP;
    filterBy: OP extends "in"
        ? ColumnType<TB, CL>[]
        : ColumnType<TB, CL>;
}

async function selectAll<TB extends Table>(
    table: TB,
    where?: WhereExpression<TB, Column<TB>, AllowedOperators>
): Promise<SqlQueryResult<Kysely.Selectable<DB[TB]>[]>> {

    const db: Kysely.Kysely<DB> | null = await PT_db.getInstance();
    if (!db) return sqlQueryErrors.nullConnection();
    
    try {
        let q = db.selectFrom(table)
            .selectAll();

        if (where) {
            q = q.where(where.column, where.operator, where.filterBy);
        }
        return { sqlError: false, data: [] };
    }
    catch (e) {
        return sqlQueryErrors.handleQueryException(e);
    }
}
The problem here is that Kysely doesn't directly expose ReferenceExpression<DB, ExtractTableAlias<DB, TB>>, which is the required type from the where() method. Then I also have a problem with the filterBy param, which at the moment always accepts Generated<number> | number | null | Generated<Date> | Date | string | [Generated<number> | number | null | Generated<Date> | Date | string]. Any help would be very helpful!
Solution:
Hey 👋 Don't. You're building your own ORM, might as well use an ORM - at least for, as you say, plenty of your access patterns, and use Kysely only when you get value from it being specific or from its composition capabilities. Some ORMs already work with Kysely (e.g. Prisma) - so you get a type-safe low-level escape hatch, some will in the future (e.g. Mikro, Zenstack, etc.). ...
Jump to solution
3 Replies
Unknown User
Unknown User•3w ago
Message Not Public
Sign In & Join Server To View
willy_24
willy_24OP•3w ago
Thanks for reaching out, and yeah maybe I’ll just stick to your solution to keep the code as concise as possible. However having a query wrapper which can handle also the where clause would be really really helpful.
Solution
Igal
Igal•2w ago
Hey 👋 Don't. You're building your own ORM, might as well use an ORM - at least for, as you say, plenty of your access patterns, and use Kysely only when you get value from it being specific or from its composition capabilities. Some ORMs already work with Kysely (e.g. Prisma) - so you get a type-safe low-level escape hatch, some will in the future (e.g. Mikro, Zenstack, etc.). Or, do something else. e.g. write common helpers and use them with $call to narrow down and reduce complexity.

Did you find this page helpful?