DT
Drizzle Team•16mo ago
Marcel Overdijk

Typesafe where? argument possible?

I have a service class containing: 
  async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }
  async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }
now I want to add a where? argument to this findAll function. Is there a type I can use from Drizzle to make this where? arg it typesafe?
5 Replies
rphlmr ⚡
rphlmr ⚡•16mo ago
I don't think there is something built-in :/ We can recreate the expected type: 
type Selection<T extends PgTable | Subquery | PgViewBase | SQL> = CreatePgSelectFromBuilderMode<"db", GetSelectTableName<T>, GetSelectTableSelection<T>, 'single'>['_']['selection'];

export type Where<T extends PgTable | Subquery | PgViewBase | SQL> = ((aliases: Selection<T>) => SQL | undefined) | SQL | undefined;
type Selection<T extends PgTable | Subquery | PgViewBase | SQL> = CreatePgSelectFromBuilderMode<"db", GetSelectTableName<T>, GetSelectTableSelection<T>, 'single'>['_']['selection'];

export type Where<T extends PgTable | Subquery | PgViewBase | SQL> = ((aliases: Selection<T>) => SQL | undefined) | SQL | undefined;
demo: https://drizzle.run/k0swqddotuays16lyweqzbj9
Drizzle Run
Typed where - Drizzle Run
Marcel Overdijk
Marcel OverdijkOP•16mo ago
Thx for your help! I’m going to look into this, however it looks quite overwhelming. Would there be a chance that drizzle could provide something like this out of the box?
rphlmr ⚡
rphlmr ⚡•16mo ago
Yeah I think it could. Maybe @Andrew Sherman have plans about that
Andrii Sherman
Andrii Sherman•16mo ago
How would you like those types to look? @Raphaël M (@rphlmr) ⚡ sent a pretty good example of how to structure types for 'where'. If you have any better options, I'd love to implement those
Marcel Overdijk
Marcel OverdijkOP•16mo ago
I came up with this for now: 
  async findAll2(...args: Parameters<DrizzleF1DBD1Database['query']['circuits']['findMany']>): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with args=%o', args);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany(...args);

    return circuits;
  }
  async findAll2(...args: Parameters<DrizzleF1DBD1Database['query']['circuits']['findMany']>): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with args=%o', args);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany(...args);

    return circuits;
  }
this now basically delegates the findAll to drizzle's findMany. Maybe this looks akward, but this findAll is part of service class with other methods like findById whichi e.g. first looks in a cache before doing the query. So this service class encapsulates my database access implementation. But back to the original question, my original findAll looked like: 
async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }
async findAll(limit: number, offset: number): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset });

    return circuits;
  }
and ideally I would like it to look like: 
async findAll({ limit: number, offset: number, where?: ???, orderBy?: ??? }): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset, where, orderBy });

    return circuits;
  }
async findAll({ limit: number, offset: number, where?: ???, orderBy?: ??? }): Promise<Circuit[]> {
    logger.debug('CircuitService.findAll() called with limit=%s, offset=%s', limit, offset);

    // Retrieve the circuits from the database.
    const circuits = await this.f1db.query.circuits.findMany({ limit, offset, where, orderBy });

    return circuits;
  }
and then call it like: 
circuitService.findAll({
  limit: 20,
  offset: 0,
  where: eq(circuits.type, 'ROAD').
  orderBy: [asc(circuits.name)],
);
circuitService.findAll({
  limit: 20,
  offset: 0,
  where: eq(circuits.type, 'ROAD').
  orderBy: [asc(circuits.name)],
);
in the method signature I put in <???> for the where and orderBy arguments as I don't know how to type them; and that's exactly the thing I would like to do, that these args are typed. Hope it is clear what I mean... Note I'm using drizzle with the Cloudflare D1 adapter. Maybe that is what is confusing me, as @Raphaël M (@rphlmr) ⚡ example (and many thx for that!) is using PostgreSQL it seems to define that Where type: 
import { SQL, Subquery } from "drizzle-orm";
import { integer, serial, text, pgTable, PgTable, CreatePgSelectFromBuilderMode } from "drizzle-orm/pg-core";
import { PgViewBase } from "drizzle-orm/pg-core/view-base";
import { GetSelectTableName, GetSelectTableSelection } from "drizzle-orm/query-builders/select.types";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull(),
});

type Selection<T extends PgTable | Subquery | PgViewBase | SQL> = CreatePgSelectFromBuilderMode<"db", GetSelectTableName<T>, GetSelectTableSelection<T>, 'single'>['_']['selection'];

export type Where<T extends PgTable | Subquery | PgViewBase | SQL> = ((aliases: Selection<T>) => SQL | undefined) | SQL | undefined;
import { SQL, Subquery } from "drizzle-orm";
import { integer, serial, text, pgTable, PgTable, CreatePgSelectFromBuilderMode } from "drizzle-orm/pg-core";
import { PgViewBase } from "drizzle-orm/pg-core/view-base";
import { GetSelectTableName, GetSelectTableSelection } from "drizzle-orm/query-builders/select.types";

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull(),
});

type Selection<T extends PgTable | Subquery | PgViewBase | SQL> = CreatePgSelectFromBuilderMode<"db", GetSelectTableName<T>, GetSelectTableSelection<T>, 'single'>['_']['selection'];

export type Where<T extends PgTable | Subquery | PgViewBase | SQL> = ((aliases: Selection<T>) => SQL | undefined) | SQL | undefined;

Did you find this page helpful?