Theo's Typesafe Cult•13mo ago

Clerk Auth with Drizzle relations

Hey, have just been watching Theo's live stream and has got me excited (for the 10th time) for restarting my side-project, I'm struggling a little on the best way to setup a relation between (and updates) between the user and my DB, so for example I want a recipe to belong to a user (which could easily be done with user Id) but I want username so it looks cleaner in the URL and in general, if a user however changes their username, how do I go about handling that?

3 Replies

max14•13mo ago

export const recipes = mysqlTable(
  "recipe",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    title: varchar("title", { length: 256 }).notNull(),
    description: varchar("description", { length: 512 }),
    ingredients: varchar("ingredients", { length: 1024 }).notNull(),
    steps: varchar("steps", { length: 1024 }).notNull(),
    image: varchar("image", { length: 256 }),
    userId: varchar("userId", { length: 256 }).notNull(),
    username: varchar("username", { length: 256 }).notNull(),
    published: boolean("published").default(false),
    public: boolean("public").default(true),
    slug: varchar("slug", { length: 256 }).notNull(),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt").onUpdateNow(),
  },
  (recipe) => ({
    titleIndex: index("title_idx").on(recipe.title),
  }),
);

export const recipes = mysqlTable(
  "recipe",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    title: varchar("title", { length: 256 }).notNull(),
    description: varchar("description", { length: 512 }),
    ingredients: varchar("ingredients", { length: 1024 }).notNull(),
    steps: varchar("steps", { length: 1024 }).notNull(),
    image: varchar("image", { length: 256 }),
    userId: varchar("userId", { length: 256 }).notNull(),
    username: varchar("username", { length: 256 }).notNull(),
    published: boolean("published").default(false),
    public: boolean("public").default(true),
    slug: varchar("slug", { length: 256 }).notNull(),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt").onUpdateNow(),
  },
  (recipe) => ({
    titleIndex: index("title_idx").on(recipe.title),
  }),
);

thats my drizzle schema so far (have literally just started again) please also criticise my schema if theres anything dumb

iammorganparry•13mo ago

Hey man, if you are using clerk for your auth youll need to sync events from clerk to your DB.. this can be achieved via a webhook: https://clerk.com/docs/integrations/webhooks I believe clerk usernames are unique so this should be trivial.. however if they are not.. it would obviously introduce some complexity as you would need to handle non unique changes. to create the relationship between a recipe and a user via username we can utilise foreign keys.. here is a great article that could help: https://planetscale.com/blog/working-with-related-data-using-drizzle-and-planetscale

Webhooks | Clerk

Clerk webhooks allow you to receive event notifications from Clerk. Clerk will send a POST request to a URL you specify when certain events happen in your Clerk account.

Emulating foreign key constraints with Drizzle relationships — Plan...

Learn how to build virtual relationships between tables in PlanetScale while using the Drizzle TypeScript ORM.

max14•13mo ago

hey, thanks for replying, with give this a read over after work!

Gaming

Programming

Clerk Auth with Drizzle relations