Theo's Typesafe CultTTC
Theo's Typesafe Cult•2y ago
LaunchThat.App

How to find single record in database using trpc and drizzle?

I am trying to do a simple database lookup finding a user by a custom field discordId.

I have added that id to my drizzle schema and pushed to the database:

export const users = mysqlTable("user", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).default(sql`CURRENT_TIMESTAMP(3)`),
  discordId: varchar("discordId", { length: 255 }), // Add Discord ID field
  googleId: varchar("googleId", { length: 255 }), // Add Google ID field
});


and now I am trying to create a trpc route for getting the info:

findUserByDiscordId: publicProcedure
    .input(z.object({ text: z.string() }))
    .query(({ ctx, input }) => {
      return ctx.db.query.users.findFirst({
        where: {
          discordId: input.discordId,
        },
      });
    }),


but I am getting an error:

Type '{ discordId: any; }' is not assignable to type 'SQL<unknown>


looking at a similar process with prisma, the database structures are models and not simple 'consts' should the databasse structure type be automatically inferred or so I need to custom define this?
Solution
I think i figured it out:

findUserByDiscordId: publicProcedure
    .input(z.object({ discordId: z.string() }))
    .query(({ ctx, input }) => {
      const data = ctx.db
      .select()
      .from(users)
      .where(eq(users.discordId, input.discordId))
    return data;
    }),
Jump to solution
Was this page helpful?