Drizzle Team•12mo ago

Possible to reuse columns while selecting?

Apologies if this is documented somewhere and I've missed it I am currently in the process of transitioning from Prisma and follow this pattern quite often:

// This resides in a separate file for utilization across various queries
export const usersSelect = Prisma.validator<Prisma.usersSelect>()({
   id: true,
   name: true,
   ...
   someRelation: {
      select: {
         someData: true,
      },
   },
});

// This resides in a separate file for utilization across various queries
export const usersSelect = Prisma.validator<Prisma.usersSelect>()({
   id: true,
   name: true,
   ...
   someRelation: {
      select: {
         someData: true,
      },
   },
});

// We construct our 'where' condition based on certain options...
let usersWhere: Prisma.usersWhereInput = {
  banned: false
};
if (includeInactiveUsers) {
  usersWhere = {
    ...usersWhere,
    inactive: true
}
// Execute the query
const users = await prisma.users.findMany({
  select: usersSelect
  where: usersWhere
  ...
});

// We construct our 'where' condition based on certain options...
let usersWhere: Prisma.usersWhereInput = {
  banned: false
};
if (includeInactiveUsers) {
  usersWhere = {
    ...usersWhere,
    inactive: true
}
// Execute the query
const users = await prisma.users.findMany({
  select: usersSelect
  where: usersWhere
  ...
});

I am curious to know if there exists a method to adhere to this pattern using Drizzle, or if it would be advisable for me to reconsider my approach. Thanks!

22 Replies

Angelelz•12mo ago

Yeah, you can do pretty much the same with drizzle

const usersWhere = [eq(users.banned, true)]

if (includeInactiveUsers) {
  usersWhere.push(eq(users.inactive, true)]
}

const dbUsers = await db.query.findMany({
  where: and(usersWhere)
});

const usersWhere = [eq(users.banned, true)]

if (includeInactiveUsers) {
  usersWhere.push(eq(users.inactive, true)]
}

const dbUsers = await db.query.findMany({
  where: and(usersWhere)
});

Umbranox•12mo ago

That looks great for the where condition, but how about the usersSelect typesafe with relations?

Angelelz•12mo ago

Drizzle has drizzle-zod, drizzle-typebox and drizzle-valibot, to help with schema validation

Angelelz•12mo ago

For example with drizzle-zod: https://orm.drizzle.team/docs/zod

Usage - DrizzleORM

Drizzle ORM | %s

Angelelz•12mo ago

You can do

// Schema for inserting a user - can be used to validate API requests
const insertUserSchema = createInsertSchema(users);

// Schema for inserting a user - can be used to validate API requests
const insertUserSchema = createInsertSchema(users);

To create schema validation object

Umbranox•12mo ago

I did attempt using drizzle-zod but wasn't entirely sure how to use it's result in a select query, especially when relations are involved, not sure if I'm thinking of this in the correct way 😅

Angelelz•12mo ago

I don't think it works with the relations yet I think prima spoiled us too much. Prima hid a lot of the underlying SQL and by doing so, created a lot of inneficient queries You can't - in SQL - insert in more than 1 table at the same time

Umbranox•12mo ago

I agree, lots of problems were caused in my current codebase due to prisma being rather inefficient, forcing me to use raw queries.

Angelelz•12mo ago

You need to use a transaction to do so That's way, currently, drizzle doesn't let you insert with a relation, because in SQL you can't

Umbranox•12mo ago

That's not what I'm trying to do, I'm essentially just trying to make the "columns" I am selecting constant, so in two seperate queries I'm not having to type them again, while also having consistency So instead of having to type

  columns: {
            name: true,
            banned: true
  },

  columns: {
            name: true,
            banned: true
  },

every query I can type

  columns: usersSelect

  columns: usersSelect

Angelelz•12mo ago

Is this what you're looking for? https://orm.drizzle.team/docs/goodies#get-typed-table-columns

Goodies - DrizzleORM

Drizzle ORM | %s

Umbranox•12mo ago

This looks exactly what I was looking for, thank you so much

shreddish•7mo ago

@Angelelz is it possible to do something like this using the query object? or do we need to build the queries and joins ourselves

Angelelz•7mo ago

Make the with object as const and see if that solves it

shreddish•7mo ago

yep that worked

Gaming

Programming

Possible to reuse columns while selecting?