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 ⚡•3mo 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•3mo 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 ⚡•2mo ago

Yeah I think it could. Maybe @Andrew Sherman have plans about that

Andrii Sherman•2mo 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•2mo 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;

Gaming

Programming

Typesafe where? argument possible?