Drizzle Team•12mo ago

Less verbose generic types for helper functions

Trying to implement various helper functions to join or filter data, I keep facing the need to add quite verbose generic types whenever I want to handle subqueries and/or selections alongside tables. Are there type helpers I'm unaware of that could make things a bit more straightforward? Here's an example illustrating what I mean:

export function withTranslations<
  T extends AnyTable<TableConfig> | PgSelect | Subquery,
  TT extends (AnyTable<TableConfig> | PgSelect | Subquery) & {
    [K in keyof TranslationLangColumn]: AnyColumn;
  },
  PK extends T extends Table
    ? ValueOf<T['_']['columns']>
    : T extends PgSelect
      ? ValueOf<T['_']['selectedFields']>
      : T extends Subquery
        ? ValueOf<T['_']['selectedFields']>
        : never,
  FK extends TT extends Table
    ? ValueOf<TT['_']['columns']>
    : TT extends PgSelect
      ? ValueOf<TT['_']['selectedFields']>
      : TT extends Subquery
        ? ValueOf<TT['_']['selectedFields']>
        : never,
>(
  selection: T,
  translationsSelection: TT,
  relation: [PK, FK] | ((selection: T, translationsSelection: TT) => [PK, FK])
) {
  //...
}

export function withTranslations<
  T extends AnyTable<TableConfig> | PgSelect | Subquery,
  TT extends (AnyTable<TableConfig> | PgSelect | Subquery) & {
    [K in keyof TranslationLangColumn]: AnyColumn;
  },
  PK extends T extends Table
    ? ValueOf<T['_']['columns']>
    : T extends PgSelect
      ? ValueOf<T['_']['selectedFields']>
      : T extends Subquery
        ? ValueOf<T['_']['selectedFields']>
        : never,
  FK extends TT extends Table
    ? ValueOf<TT['_']['columns']>
    : TT extends PgSelect
      ? ValueOf<TT['_']['selectedFields']>
      : TT extends Subquery
        ? ValueOf<TT['_']['selectedFields']>
        : never,
>(
  selection: T,
  translationsSelection: TT,
  relation: [PK, FK] | ((selection: T, translationsSelection: TT) => [PK, FK])
) {
  //...
}

I could implement some type helpers of my own, but was wondering if anything was already included in drizzle-orm

7 Replies

Doron Torangy•12mo ago

Don't have an answer for you but this is a huge deal breaker for me when using drizzle to not have the ability to wrap queries with my own functions So If you find a solution I'll appreciate it if you can share it here 🙂

iolydOP•12mo ago

Do you have an example of wrapper function you'd plan on working with? It could maybe help me sketch out more robust type helpers Step one to help work with subqueries for me is to provide a config getter:

/**
 * Get a subquery's config in a manner similar to `getTableConfig`. Useful to retrieve a subquery's
 * alias or fields.
 */
export function getSubqueryConfig<S extends ColumnsSelection, A extends string>(
  subquery: WithSubqueryWithSelection<S, A> | SubqueryWithSelection<S, A>
) {
  return subquery[SubqueryConfig as unknown as string] as (typeof subquery)['_'];
}

/**
 * Get a subquery's config in a manner similar to `getTableConfig`. Useful to retrieve a subquery's
 * alias or fields.
 */
export function getSubqueryConfig<S extends ColumnsSelection, A extends string>(
  subquery: WithSubqueryWithSelection<S, A> | SubqueryWithSelection<S, A>
) {
  return subquery[SubqueryConfig as unknown as string] as (typeof subquery)['_'];
}

With simplified types:

export function getSubqueryConfig<S extends Subquery>(subquery: S) {
  return (subquery as Record<string, unknown>)[SubqueryConfig as unknown as string] as S['_'];
}

export function getSubqueryConfig<S extends Subquery>(subquery: S) {
  return (subquery as Record<string, unknown>)[SubqueryConfig as unknown as string] as S['_'];
}

Doron Torangy•12mo ago

I don't we are going for the same thing, I'm looking more for a domain specific wrapper functions, where the only generic thing is providing a projection while maintaining type safety, something like so:

async getUsersWithProjection<T extends SelectedFields>(filters: UsersFilters, fields: T) {
  const qb = this.db.select(fields).from(users).$dynamic()
  return this.withUsersFilter(qb, filters).execute()
}

withPagination<T extends PgSelect>(qb: T, page: number, pageSize: number = 10) {
  return qb.limit(pageSize).offset(page * pageSize)
}

private withUsersFilter<T extends PgSelect>(qb: T, filters: UsersFilters) {
  const conditions: SQL<unknown>[] = []
  if (filters.banned !== undefined) conditions.push(eq(users.banned, filters.banned))
  if (filters.deleted !== undefined) conditions.push(eq(users.deleted, filters.deleted))
  if (filters.emailVerified !== undefined) conditions.push(eq(users.emailVerified, filters.emailVerified))
  if (filters.roles) conditions.push(inArray(users.role, filters.roles))
  if (filters.accountSignInMethods)  conditions.push(arrayOverlaps(users.accountSignInMethods, filters.accountSignInMethods))
  if (filters.textSearch)
    conditions.push(
      or(
        ilike(users.firstName, `%${filters.textSearch}%`),
        ilike(users.lastName, `%${filters.textSearch}%`),
        ilike(users.email, `%${filters.textSearch}%`)
      )!
    )
  if (filters.orderBy) {
    switch (filters.orderBy) {
      case UsersOrderByFilter.CreatedAtAsc: {
        qb = qb.orderBy(asc(users.createdAt))
        break
      }
      case UsersOrderByFilter.CreatedAtDesc: {
        qb = qb.orderBy(desc(users.createdAt))
        break
        }
      }
    }

  if (conditions.length) qb = qb.where(and(...conditions))
  if (filters.page && filters.limit) qb = withPagination(qb, filters.page, filters.limit)
  return qb
}

async getUsersWithProjection<T extends SelectedFields>(filters: UsersFilters, fields: T) {
  const qb = this.db.select(fields).from(users).$dynamic()
  return this.withUsersFilter(qb, filters).execute()
}

withPagination<T extends PgSelect>(qb: T, page: number, pageSize: number = 10) {
  return qb.limit(pageSize).offset(page * pageSize)
}

private withUsersFilter<T extends PgSelect>(qb: T, filters: UsersFilters) {
  const conditions: SQL<unknown>[] = []
  if (filters.banned !== undefined) conditions.push(eq(users.banned, filters.banned))
  if (filters.deleted !== undefined) conditions.push(eq(users.deleted, filters.deleted))
  if (filters.emailVerified !== undefined) conditions.push(eq(users.emailVerified, filters.emailVerified))
  if (filters.roles) conditions.push(inArray(users.role, filters.roles))
  if (filters.accountSignInMethods)  conditions.push(arrayOverlaps(users.accountSignInMethods, filters.accountSignInMethods))
  if (filters.textSearch)
    conditions.push(
      or(
        ilike(users.firstName, `%${filters.textSearch}%`),
        ilike(users.lastName, `%${filters.textSearch}%`),
        ilike(users.email, `%${filters.textSearch}%`)
      )!
    )
  if (filters.orderBy) {
    switch (filters.orderBy) {
      case UsersOrderByFilter.CreatedAtAsc: {
        qb = qb.orderBy(asc(users.createdAt))
        break
      }
      case UsersOrderByFilter.CreatedAtDesc: {
        qb = qb.orderBy(desc(users.createdAt))
        break
        }
      }
    }

  if (conditions.length) qb = qb.where(and(...conditions))
  if (filters.page && filters.limit) qb = withPagination(qb, filters.page, filters.limit)
  return qb
}

shreddish•10mo ago

did either of you ever get a decent set of wrappers working?

iolydOP•10mo ago

Yeah, i made a package with various type / query helpers to help me reuse some wrappers across projects: https://github.com/iolyd/drizzle-orm-helpers

GitHub

GitHub - iolyd/drizzle-orm-helpers: Collection of unofficial helper...

Collection of unofficial helpers for Drizzle ORM. Contribute to iolyd/drizzle-orm-helpers development by creating an account on GitHub.

iolydOP•10mo ago

You can look at the source and copy what you want (or you can install the package but I'm still changing things quite a lot so i'd say its a bit unstable)

shreddish•10mo ago

wow this looks great! thank you for this!

Gaming

Programming

Less verbose generic types for helper functions