willy_24 Posts - Answer Overflow

willy_24

•Created by willy_24 on 4/15/2025 in #help

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!

8 replies

Gaming

Programming