10 replies

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

Jump to solution

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

Jump to solution

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

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

Similar Threads

Similar Threads

Similar Threads