Using one function to query many tables of same structure

Hello i have many tables (5-6) that have same structure like this

export const speciality = pgTable('speciality', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 255 }).notNull()
});

export const speciality = pgTable('speciality', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 255 }).notNull()
});

this is what i think is called lookup table, i want a simple query that fetches from one of the tables (based on function arg) and gives out a a simple result, query is simple, no joins or anything, just a limit, is there a way to create a function that does this in a typesafe manner

5 Replies

ricinOP•15mo ago

example query

async function searchInReference<T extends ?>(query : string) {
    return await db.select()
    .from(?)
    .where(eq(T.name, query))
    .limit(25)
}

async function searchInReference<T extends ?>(query : string) {
    return await db.select()
    .from(?)
    .where(eq(T.name, query))
    .limit(25)
}

Angelelz•15mo ago

T extends from PgTable should do the trick. PgTable should be imported from "drizzle-orm/pg-core" I believe T.name might give you problems You might want to pass in the table as a parameter. Not sure what your requirements are

ricinOP•15mo ago

i have 4-5 tables that share the same columns 'id serial' and 'name varchar', i want to use one function to for example : -search one of the tables for a string - maybe insert to one of the tables a string (id is auto incremented) without repeating the same function 4-5 times, while maintaining type safety

Angelelz•15mo ago

async function searchInReference<T extends PgTable & { name: any }>(table: T, query : string) {
    return await db.select()
    .from(table)
    .where(eq(table.name, query))
    .limit(25)
}

async function searchInReference<T extends PgTable & { name: any }>(table: T, query : string) {
    return await db.select()
    .from(table)
    .where(eq(table.name, query))
    .limit(25)
}

ricinOP•15mo ago

that didn't work but i did this instead

type ReferenceTable = typeof speciality | typeof domain | /* all other tables */;

export async function searchInReference
(t: ReferenceTable, query: string)

type ReferenceTable = typeof speciality | typeof domain | /* all other tables */;

export async function searchInReference
(t: ReferenceTable, query: string)

this works for my use-case for now

Gaming

Programming

Using one function to query many tables of same structure