hankandre
hankandre
PostsComments
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
I'm not the OP, so I can't close this, but I'm golden now. Thanks for all the help everyone! Sorry I didn't look at the highlights in the docs hard enough.
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
Thank you so much, @Angelelz !
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
Oh my gosh. I knew it was something idiotic and I just overlooked it in the docs. 🤦‍♂️
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
Gotcha! I see where I went wrong. The relation on each is to the join table.
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
When I remove that, like it says you can do in the docs, I'm uncertain how I can write my query. Doing this results in an error (using tRPC) when attempting to launch Drizzle Studio: 
export const bookRouter = createTRPCRouter({
  one: protectedProcedure.query(({ ctx: { db } }) => {
    return db.query.books.findFirst({
      where: eq(books.id, 1),
      with: {
        authors: true,
      },
    });
  }),
});
export const bookRouter = createTRPCRouter({
  one: protectedProcedure.query(({ ctx: { db } }) => {
    return db.query.books.findFirst({
      where: eq(books.id, 1),
      with: {
        authors: true,
      },
    });
  }),
});
Error: There is not enough information to infer relation "authors.books"
Error: There is not enough information to infer relation "authors.books"
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
Presumably that's because of the references(() => books.id) and references(() => authors.id) usage.
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
When I attempt to push that schema I get this message from PlanetScale: 
Error: VT10001: foreign key constraints are not allowed
Error: VT10001: foreign key constraints are not allowed
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
Here's an example from a schema I wrote out that has a many-to-many relationship of books and authors: 
import {
  text,
  bigint,
  varchar,
  serial,
  primaryKey,
} from 'drizzle-orm/mysql-core';
import { mysqlTable } from '../config';
import { createId } from '~/utils/create-nanoid';
import { relations } from 'drizzle-orm';

export const books = mysqlTable('books', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  title: text('title').notNull(),
});

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

export const authors = mysqlTable('authors', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  name: text('name').notNull(),
  biography: text('biography'),
});

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

export const authorsToBooks = mysqlTable(
  'authors_to_books',
  {
    bookId: bigint('book_id', { mode: 'number' })
      .notNull()
      .references(() => books.id),
    authorId: bigint('author_id', { mode: 'number' })
      .notNull()
      .references(() => authors.id),
  },
  (t) => ({
    pk: primaryKey(t.bookId, t.authorId),
  }),
);

export const authorsToBooksRelations = relations(authorsToBooks, ({ one }) => ({
  book: one(books, {
    fields: [authorsToBooks.bookId],
    references: [books.id],
  }),
  author: one(authors, {
    fields: [authorsToBooks.authorId],
    references: [authors.id],
  }),
}));
import {
  text,
  bigint,
  varchar,
  serial,
  primaryKey,
} from 'drizzle-orm/mysql-core';
import { mysqlTable } from '../config';
import { createId } from '~/utils/create-nanoid';
import { relations } from 'drizzle-orm';

export const books = mysqlTable('books', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  title: text('title').notNull(),
});

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

export const authors = mysqlTable('authors', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  name: text('name').notNull(),
  biography: text('biography'),
});

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

export const authorsToBooks = mysqlTable(
  'authors_to_books',
  {
    bookId: bigint('book_id', { mode: 'number' })
      .notNull()
      .references(() => books.id),
    authorId: bigint('author_id', { mode: 'number' })
      .notNull()
      .references(() => authors.id),
  },
  (t) => ({
    pk: primaryKey(t.bookId, t.authorId),
  }),
);

export const authorsToBooksRelations = relations(authorsToBooks, ({ one }) => ({
  book: one(books, {
    fields: [authorsToBooks.bookId],
    references: [books.id],
  }),
  author: one(authors, {
    fields: [authorsToBooks.authorId],
    references: [authors.id],
  }),
}));
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
I have a junction table and the relations setup, like the example, but in the junction table, usersToGroups, is referencing foreign keys. I can give my specific example in a couple hours. I’m AFK right now.
42 replies
DTDrizzle Team
Created by miigaarino on 7/4/2023 in #help
Many to many - Planetscale
I think I could actually use help here. I'd rather avoid doing things manually via a bunch of table joins, if I can avoid it.
42 replies