Theo's Typesafe Cult•11mo ago

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
});

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,
        },
      });
    }),

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>

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() }))...

Jump to solution

5 Replies

Solution

Trader Launchpad•11mo ago

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;
    }),

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;
    }),

Trader LaunchpadOP•11mo ago

I am still looking for an answer on how to use findfirst with drizzle so it returns a single array rather than an object. I cannot figure out how to use drizzle, findfirst, and a where clause...

Martoxdlol•11mo ago

https://orm.drizzle.team/docs/rqb#find-first

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Martoxdlol•11mo ago

Here is how to use where with drizzle https://orm.drizzle.team/docs/select#filtering

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Martoxdlol•11mo ago

To get a row you can use select or use query Query is easier and allows you to get a single document directly Check the first link The where option works different than prisma. Check de second link

Gaming

Programming

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