Drizzle Team•15mo 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•15mo 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)
});

UmbranoxOP•15mo ago

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

Angelelz•15mo ago

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

Angelelz•15mo ago

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

Usage - DrizzleORM

Drizzle ORM | %s

Angelelz•15mo 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

UmbranoxOP•15mo 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•15mo 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

UmbranoxOP•15mo 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•15mo 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

UmbranoxOP•15mo 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•15mo ago

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

Goodies - DrizzleORM

Drizzle ORM | %s

UmbranoxOP•15mo ago

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

shreddish•10mo 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•10mo ago

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

shreddish•10mo ago

yep that worked

shreddish•10mo ago

sorry to keep pinging you @Angelelz following your example above for the where condition I'm getting this error curious if I'm doing something different

shreddish•10mo ago

Angelelz•10mo ago

Does it work if you spread locationWhere?

shreddish•10mo ago

it did not but reading one of the other threads on dyanmic where i found that i needed to type the locationsWhere to SQL[] and then it stopped complaining

shreddish•10mo ago

okay hahah weird - i could've sworn i tried spreading and it still didn't work until i added the SQL[] type but just got rid of it and now it works so for anyone in the future i think @Angelelz is correct just need to spread the where conditions array

Angelelz•10mo ago

👍

Gaming

Programming

Possible to reuse columns while selecting?