Theo's Typesafe Cult•8mo ago

Relations using Drizzle and Planetscale

I'm trying to start using Drizzle with Planetscale for my new project, but I have some doubts regarding relations... I know that Planetscale doesn't allow Foreign key constraints on the DB, meaning you need an application level constraint. For example, from the starter template for T3

export const posts = createTable(
  "post",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    createdById: varchar("createdById", { length: 255 }).notNull(),
    ...
  },
  (example) => ({
    createdByIdIdx: index("createdById_idx").on(example.createdById),
  }),
);

export const users = createTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  ...
});

export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts),
  sessions: many(sessions),
}));

export const posts = createTable(
  "post",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    createdById: varchar("createdById", { length: 255 }).notNull(),
    ...
  },
  (example) => ({
    createdByIdIdx: index("createdById_idx").on(example.createdById),
  }),
);

export const users = createTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  ...
});

export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts),
  sessions: many(sessions),
}));

As we can see, the Posts has the createdById attribute, but it's not connected to the User table in any way. My question here are: 1. Why is there no many() relation to posts like accounts and sessions? 2. Since we do not have DB level restrictions, we trust that we will not make mistakes in the app and add wrong user ids to posts? 3. Is it even possible to add wrong author ids to posts? 4. What to do in case or to prevent 3? All help is appreciated 😄

3 Replies

MadMax•8mo ago

firstly planetscale does support foreign key constraints for relations you have to define them both ways user -> many posts post -> one user that way its kind of guranteed that you are getting and inserting right data depends upon if you did it correctly all your other points relate to data access patterns which is upon you to implement correctly read drizzle docs they are great

JulieCezar•8mo ago

I was confused because of this from the Planetscale website, https://planetscale.com/docs/learn/operating-without-foreign-key-constraints, At PlanetScale, we don't recommend using foreign key constraints. However, if you still want to use them, we currently have beta support for foreign key constraints with minor limitations. But I found the solution at https://orm.drizzle.team/docs/rqb#foreign-keys You might’ve noticed that relations look similar to foreign keys — they even have a references property. So what’s the difference? While foreign keys serve a similar purpose, defining relations between tables, they work on a different level compared to relations.

Drizzle ORM - next gen TypeScript ORM

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

JulieCezar•8mo ago

Will post updates as I find more info 😁

Gaming

Programming

Relations using Drizzle and Planetscale