Drizzle Team•12mo ago

MySQL Relational CRUD

I am the own of issue #1395, it was closed but i don't know way to follow this suggestion: MySql doesn't support returning() you have to do it in a migration and send 2 different queries. Please create a help post in discord or a question in a discussion if you need further help.

8 Replies

Angelelz•12mo ago

My apologies, when I wrote migration I meant transaction I'll edit the reply there Can you show what the queries you're attempting to do look like?

Kai RevonaOP•12mo ago

I want to create insert relation queries like prisma const userAndPosts = await prisma.user.create({ data: { posts: { create: [ { title: 'Prisma Day 2020' }, // Populates authorId with user's id { title: 'How to write a Prisma schema' }, // Populates authorId with user's id ], }, }, } But MySQL does''t support to returning the id of the first record. So, I do not know what to do pplease give me example code

Angelelz•12mo ago

const response = await db.transaction(async (tx) => {
  const insertedUser = db.insert(user).values(myNewUserValues);
  const insertedId = insertedUser[0].insertId // You'll have to double check this, I replying from memory

  const insertedPosts = db.insert(posts).values([
    { title: 'My Title 1', userId: insertedId }, 
    { title: 'My Title 2', userId: insertedId }, 
  ])
  
  return [insertedUser, insertedPosts]
})

const response = await db.transaction(async (tx) => {
  const insertedUser = db.insert(user).values(myNewUserValues);
  const insertedId = insertedUser[0].insertId // You'll have to double check this, I replying from memory

  const insertedPosts = db.insert(posts).values([
    { title: 'My Title 1', userId: insertedId }, 
    { title: 'My Title 2', userId: insertedId }, 
  ])
  
  return [insertedUser, insertedPosts]
})

This is just an example. But this is the canonical way of inserting related data in Mysql

Kai RevonaOP•12mo ago

Thank you so much This is my drizzle model

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

export const users = mysqlTable("users", {
  id: varchar("id", { length: 256 })
    .primaryKey()
    .$defaultFn(() => nanoid()),
  username: varchar("username", { length: 256 }).unique().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
export const posts = mysqlTable("posts", {
  id: varchar("id", { length: 256 })
    .primaryKey()
    .$defaultFn(() => nanoid()),
  title: varchar("title", { length: 256 }).notNull(),
  authorId: varchar("author_id", { length: 256 }).notNull(),
  content: text("content").notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

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

export const users = mysqlTable("users", {
  id: varchar("id", { length: 256 })
    .primaryKey()
    .$defaultFn(() => nanoid()),
  username: varchar("username", { length: 256 }).unique().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
export const posts = mysqlTable("posts", {
  id: varchar("id", { length: 256 })
    .primaryKey()
    .$defaultFn(() => nanoid()),
  title: varchar("title", { length: 256 }).notNull(),
  authorId: varchar("author_id", { length: 256 }).notNull(),
  content: text("content").notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

And a component

import { db } from "@/lib/db";
import { posts, users } from "@/lib/db/schema";
const Home = async () => {
  const response = await db.transaction(async (tx) => {
    const insertedUser = await db.insert(users).values({ username: "My Name" });
    const insertedId = insertedUser.insertId; // You'll have to double check this, I replying from memory
    console.log(insertedId);
    const insertedPosts = db.insert(posts).values([
      { title: "My Title 1", content: "My content 1", authorId: insertedId },
      { title: "My Title 2", content: "My content 2", authorId: insertedId },
    ]);

    return [insertedId, insertedPosts];
  });
  const [insertedId, insertedPosts] = response;
  console.log(insertedId);
  return <div>Home</div>;
};

export default Home;

import { db } from "@/lib/db";
import { posts, users } from "@/lib/db/schema";
const Home = async () => {
  const response = await db.transaction(async (tx) => {
    const insertedUser = await db.insert(users).values({ username: "My Name" });
    const insertedId = insertedUser.insertId; // You'll have to double check this, I replying from memory
    console.log(insertedId);
    const insertedPosts = db.insert(posts).values([
      { title: "My Title 1", content: "My content 1", authorId: insertedId },
      { title: "My Title 2", content: "My content 2", authorId: insertedId },
    ]);

    return [insertedId, insertedPosts];
  });
  const [insertedId, insertedPosts] = response;
  console.log(insertedId);
  return <div>Home</div>;
};

export default Home;

I double check your solution but the insertedId always return 0

Angelelz•12mo ago

Unfortunately, MySQL2 only gives you back the insertedId if it is an autoincrement. You have 2 options, either get the id with an extra select after the inversion, or create the id manually, storing it in a variable and use it on both insertions

Kai RevonaOP•12mo ago

please give me example code for each option

Angelelz•12mo ago

I wrote inversion, I meant insertion

Kai RevonaOP•12mo ago

Thank you I changed my code, the insert relation has work but now I have another issue:

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

export const users = mysqlTable("users", {
  id: serial("id").autoincrement().primaryKey(),
  username: varchar("username", { length: 256 }).unique().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  followers: many(users),
}));
export const posts = mysqlTable("posts", {
  id: serial("id").autoincrement().primaryKey(),

  title: varchar("title", { length: 256 }).notNull(),
  authorId: varchar("author_id", { length: 256 }).notNull(),
  content: text("content").notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

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

export const users = mysqlTable("users", {
  id: serial("id").autoincrement().primaryKey(),
  username: varchar("username", { length: 256 }).unique().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
  followers: many(users),
}));
export const posts = mysqlTable("posts", {
  id: serial("id").autoincrement().primaryKey(),

  title: varchar("title", { length: 256 }).notNull(),
  authorId: varchar("author_id", { length: 256 }).notNull(),
  content: text("content").notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

I want to add relation followers and following to users table ( followers and following is users ). It means i want to add self relation to users table And update my updatedAt element, I am not certain it works

Gaming

Programming

MySQL Relational CRUD