Insert One to Many

How can I insert relations as well at create the parent?

import { Customer } from "@/types/customer";
import { NextResponse } from "next/server";
import { cookies } from "next/headers";
import { db } from "@/lib/db";
import { journeys } from "@/lib/db/schema";
import { v4 as uuidv4 } from "uuid";

export async function POST(req: Request) {
  const body = (await req.json()) as Customer;
  // Body has product refs...

  const journeyRef = uuidv4();

  await db.transaction(async (tx) => {
    await tx.insert(journeys).values({ ...body, journeyRef });
    // Then need to link products to that journey...
  });

  cookies().set({
    name: "journeyRef",
    value: journeyRef,
    httpOnly: true,
    path: "/",
  });

  return NextResponse.json({});
}

import { Customer } from "@/types/customer";
import { NextResponse } from "next/server";
import { cookies } from "next/headers";
import { db } from "@/lib/db";
import { journeys } from "@/lib/db/schema";
import { v4 as uuidv4 } from "uuid";

export async function POST(req: Request) {
  const body = (await req.json()) as Customer;
  // Body has product refs...

  const journeyRef = uuidv4();

  await db.transaction(async (tx) => {
    await tx.insert(journeys).values({ ...body, journeyRef });
    // Then need to link products to that journey...
  });

  cookies().set({
    name: "journeyRef",
    value: journeyRef,
    httpOnly: true,
    path: "/",
  });

  return NextResponse.json({});
}

My schema:

import { mysqlTable, serial, text } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const journeys = mysqlTable("journey", {
  id: serial("id").primaryKey(),
  journeyRef: text("journeyRef"),
  customer_firstName: text("customer_firstName"),
  customer_lastName: text("customer_lastName"),
  customer_emailAddress: text("customer_emailAddress"),
  type: text("type"),
});

export const products = mysqlTable("product", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const journeysRelations = relations(journeys, ({ many }) => ({
  products: many(products),
}));

import { mysqlTable, serial, text } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const journeys = mysqlTable("journey", {
  id: serial("id").primaryKey(),
  journeyRef: text("journeyRef"),
  customer_firstName: text("customer_firstName"),
  customer_lastName: text("customer_lastName"),
  customer_emailAddress: text("customer_emailAddress"),
  type: text("type"),
});

export const products = mysqlTable("product", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const journeysRelations = relations(journeys, ({ many }) => ({
  products: many(products),
}));

I want to add products to the insert for creating the new journey. Currently using a transaction but not sure what the code needs to be for linking products to the journey?

31 Replies

rphlmr ⚡•16mo ago

hum I don't use relation api (I was there before this api lol) let see what we can do you want to create products and then link them to a journey?

NickolakiOP•16mo ago

Yes thats right. Or even better so, simply query these products and then link them to the journey. (As they may have already been created)

rphlmr ⚡•16mo ago

just checking what kind of tape relations is creating to you have the generated sql ?

NickolakiOP•16mo ago

I've only got those two tables. Lemme link it to ya

rphlmr ⚡•16mo ago

many to many relation so a product can be on 0 or more journey, and journey can have 0 or many products, right?

NickolakiOP•16mo ago

NickolakiOP•16mo ago

One to many i think. Journeys will have products but products wont have reference to journeys

rphlmr ⚡•16mo ago

ah that's true that there is no foreign key concept in mysql/planetscale (postgres user here hehe)

NickolakiOP•16mo ago

The query part is simple just the insert. For example: this is what the query could look like:

  const journeyRef = params.ref;

  const journey = await db.query.journeys.findMany({
    with: {
      products: true,
    },
    where: (journey, { eq }) => eq(journey.journeyRef, journeyRef),
  });

  const journeyRef = params.ref;

  const journey = await db.query.journeys.findMany({
    with: {
      products: true,
    },
    where: (journey, { eq }) => eq(journey.journeyRef, journeyRef),
  });

But yeah all the docs just bloody reference query queries (hehe) and not any insert/updates with relations

rphlmr ⚡•16mo ago

I think there is a missing pieces\

rphlmr ⚡•16mo ago

look at https://orm.drizzle.team/docs/rqb#many-to-many

Relational queries – DrizzleORM

Drizzle ORM | %s

rphlmr ⚡•16mo ago

usersRelations points to a 3rd table joining user and group this is what troubled me, I didn't see where this relation lived on the db or I miss something related to planetscale?

NickolakiOP•16mo ago

Yeah that isn't there atm. Only those 2 tables I'd always expect a 3rd like journeyProduct table for many to many I wonder how does the one to many magic work then without foreign keys

rphlmr ⚡•16mo ago

in prisma world, it will create this, but with drizzle it's on your own col association I guess like a weak foreign key

NickolakiOP•16mo ago

So if i go with something like from that example:

export const usersToGroups = pgTable('users_to_groups', {
        userId: integer('user_id').notNull().references(() => users.id),
        groupId: integer('group_id').notNull().references(() => groups.id),
    }, (t) => ({
        pk: primaryKey(t.userId, t.groupId),
    }),
);

export const usersToGroups = pgTable('users_to_groups', {
        userId: integer('user_id').notNull().references(() => users.id),
        groupId: integer('group_id').notNull().references(() => groups.id),
    }, (t) => ({
        pk: primaryKey(t.userId, t.groupId),
    }),
);

What will my insert query look like ? For my Journey/Product scenario I'm guessing something like

rphlmr ⚡•16mo ago

export const productsToJourneys = mysqlTable('products_to_journeys', {
        journeyId: serial('journey_id').notNull(),
        productId: serial('product_id').notNull(),
    }, (t) => ({
        pk: primaryKey(t.journeyId, t.productId),
    }),
);

export const productsToJourneys = mysqlTable('products_to_journeys', {
        journeyId: serial('journey_id').notNull(),
        productId: serial('product_id').notNull(),
    }, (t) => ({
        pk: primaryKey(t.journeyId, t.productId),
    }),
);

NickolakiOP•16mo ago

export const journeysToProducts = mysqlTable(
  "journeys_to_products",
  {
    journeyId: serial("journey_id")
      .notNull()
      .references(() => journeys.id),
    productId: serial("product_id")
      .notNull()
      .references(() => products.id),
  },
  (t) => ({
    pk: primaryKey(t.journeyId, t.productId),
  })
);

export const journeysToProducts = mysqlTable(
  "journeys_to_products",
  {
    journeyId: serial("journey_id")
      .notNull()
      .references(() => journeys.id),
    productId: serial("product_id")
      .notNull()
      .references(() => products.id),
  },
  (t) => ({
    pk: primaryKey(t.journeyId, t.productId),
  })
);

ye So my query would look likeeee this maybe?

rphlmr ⚡•16mo ago

Depending on what you want in your POST. Inserting new products on new journey. Inserting products in new journey Updating journey with new products, or existing products

NickolakiOP•16mo ago

  const journeyRef = uuidv4();
  const exampleJourneyId = 1;

  await db.transaction(async (tx) => {
    await tx.insert(journeys).values({ ...body, journeyRef });
    await tx.insert(journeysToProducts).values({
      journeyId: exampleJourneyId,
      productId: 1,
    });
    // Then need to link products to that journey...
  });

  const journeyRef = uuidv4();
  const exampleJourneyId = 1;

  await db.transaction(async (tx) => {
    await tx.insert(journeys).values({ ...body, journeyRef });
    await tx.insert(journeysToProducts).values({
      journeyId: exampleJourneyId,
      productId: 1,
    });
    // Then need to link products to that journey...
  });

rphlmr ⚡•16mo ago

Since relation query seems only for reading, you'll insert the classic way

NickolakiOP•16mo ago

Like that?

rphlmr ⚡•16mo ago

you can or you could just let the db giving you created id like:

await db.transaction(async (tx) => {
    const {id: journeyId} = await tx.insert(journeys).values({ ...body.journey }).returning({ insertedId: journeys.id });
 const {id: productId} = await tx.insert(products).values({ ...body.product }).returning({ insertedId: products.id });
    await tx.insert(journeysToProducts).values({
      journeyId,
      productId,
    });
    // Then need to link products to that journey...
  });

await db.transaction(async (tx) => {
    const {id: journeyId} = await tx.insert(journeys).values({ ...body.journey }).returning({ insertedId: journeys.id });
 const {id: productId} = await tx.insert(products).values({ ...body.product }).returning({ insertedId: products.id });
    await tx.insert(journeysToProducts).values({
      journeyId,
      productId,
    });
    // Then need to link products to that journey...
  });

NickolakiOP•16mo ago

Yeah I was thinking of that but mysql apparently does let you use returning Or am i wrong

rphlmr ⚡•16mo ago

oh sorry

NickolakiOP•16mo ago

If im not then postgres might be the way xD

rphlmr ⚡•16mo ago

you are right, I misread >_>

NickolakiOP•16mo ago

So to round this all up i guess, will drizzle know to pull in the correct products if I use that query from earlier?

  const journeyRef = params.ref;

  const journey = await db.query.journeys.findMany({
    with: {
      products: true,
    },
    where: (journey, { eq }) => eq(journey.journeyRef, journeyRef),
  });

  const journeyRef = params.ref;

  const journey = await db.query.journeys.findMany({
    with: {
      products: true,
    },
    where: (journey, { eq }) => eq(journey.journeyRef, journeyRef),
  });

Or is there more config to define: it's now looking like this all together:

import { mysqlTable, primaryKey, serial, text } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const journeys = mysqlTable("journey", {
  id: serial("id").primaryKey(),
  journeyRef: text("journeyRef"),
  customer_firstName: text("customer_firstName"),
  customer_lastName: text("customer_lastName"),
  customer_emailAddress: text("customer_emailAddress"),
  type: text("type"),
});

export const journeysToProducts = mysqlTable(
  "journeys_to_products",
  {
    journeyId: serial("journey_id")
      .notNull()
      .references(() => journeys.id),
    productId: serial("product_id")
      .notNull()
      .references(() => products.id),
  },
  (t) => ({
    pk: primaryKey(t.journeyId, t.productId),
  })
);

export const products = mysqlTable("product", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const journeysRelations = relations(journeys, ({ many }) => ({
  products: many(products),
}));

import { mysqlTable, primaryKey, serial, text } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const journeys = mysqlTable("journey", {
  id: serial("id").primaryKey(),
  journeyRef: text("journeyRef"),
  customer_firstName: text("customer_firstName"),
  customer_lastName: text("customer_lastName"),
  customer_emailAddress: text("customer_emailAddress"),
  type: text("type"),
});

export const journeysToProducts = mysqlTable(
  "journeys_to_products",
  {
    journeyId: serial("journey_id")
      .notNull()
      .references(() => journeys.id),
    productId: serial("product_id")
      .notNull()
      .references(() => products.id),
  },
  (t) => ({
    pk: primaryKey(t.journeyId, t.productId),
  })
);

export const products = mysqlTable("product", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const journeysRelations = relations(journeys, ({ many }) => ({
  products: many(products),
}));

rphlmr ⚡•16mo ago

I understand it like you wrote

NickolakiOP•16mo ago

We shall see I guess, if that doesn't work its for another ticket i guess 😛 Thank you for your help bro

rphlmr ⚡•16mo ago

You are welcome. Again sorry, I'm more a Postgres guy 😄 no returning trouble me so much lol It makes things harder for me :/

NickolakiOP•16mo ago

You and me both, god knows why Im using mysql

Gaming

Programming

Insert One to Many