Nested comment
How to create a nested comment model like prisma
model Post {
id String @id @default(uuid())
title String
body String
comments Comment[]
}
model User {
id String @id @default(uuid())
name String
comments Comment[]
likes Like[]
}
model Comment {
id String @id @default(uuid())
message String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
postId String
parent Comment? @relation("ParentChild", fields: [parentId], references: [id], onDelete: Cascade)
children Comment[] @relation("ParentChild")
parentId String?
likes Like[]
}
model Like {
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
comment Comment @relation(fields: [commentId], references: [id], onDelete: Cascade)
userId String
commentId String
@@id([userId, commentId])
}
model Post {
id String @id @default(uuid())
title String
body String
comments Comment[]
}
model User {
id String @id @default(uuid())
name String
comments Comment[]
likes Like[]
}
model Comment {
id String @id @default(uuid())
message String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userId String
post Post @relation(fields: [postId], references: [id], onDelete: Cascade)
postId String
parent Comment? @relation("ParentChild", fields: [parentId], references: [id], onDelete: Cascade)
children Comment[] @relation("ParentChild")
parentId String?
likes Like[]
}
model Like {
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
comment Comment @relation(fields: [commentId], references: [id], onDelete: Cascade)
userId String
commentId String
@@id([userId, commentId])
}
13 Replies
Take a look at the documentation for foreign keys - it includes how to self reference: https://orm.drizzle.team/docs/indexes-constraints#foreign-key
you may want to declare relations too: https://orm.drizzle.team/docs/rqb#declaring-relations
my planetscale db does't support foreign key
you can still define relations - they're seperate from the fks
What are i missing:
Schema and relations
It return don't have information time to infer relation
import {
mysqlTable,
primaryKey,
timestamp,
varchar,
} from "drizzle-orm/mysql-core"
import { nanoid } from "nanoid"
export const comments = mysqlTable("comments", {
id: varchar("id", { length: 191 })
.primaryKey()
.$defaultFn(() => nanoid()),
userId: varchar("user_id", { length: 191 }).notNull(),
postId: varchar("post_id", { length: 191 }).notNull(),
parentId: varchar("parent_id", { length: 191 }),
message: varchar("message", { length: 191 }).notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").onUpdateNow(),
})
export const commentsRelations = relations(comments, ({ one, many }) => ({
user: one(users, {
fields: [comments.userId],
references: [users.id],
}),
post: one(posts, {
fields: [comments.postId],
references: [posts.id],
}),
parent: one(comments, {
fields: [comments.parentId],
references: [comments.id],
}),
child: many(comments),
}))
import {
mysqlTable,
primaryKey,
timestamp,
varchar,
} from "drizzle-orm/mysql-core"
import { nanoid } from "nanoid"
export const comments = mysqlTable("comments", {
id: varchar("id", { length: 191 })
.primaryKey()
.$defaultFn(() => nanoid()),
userId: varchar("user_id", { length: 191 }).notNull(),
postId: varchar("post_id", { length: 191 }).notNull(),
parentId: varchar("parent_id", { length: 191 }),
message: varchar("message", { length: 191 }).notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").onUpdateNow(),
})
export const commentsRelations = relations(comments, ({ one, many }) => ({
user: one(users, {
fields: [comments.userId],
references: [users.id],
}),
post: one(posts, {
fields: [comments.postId],
references: [posts.id],
}),
parent: one(comments, {
fields: [comments.parentId],
references: [comments.id],
}),
child: many(comments),
}))
I translated the prisma schema to drizzle:
Adjust the connection to use planetscale (im testing locally with mysql)
Output:
@Kai Revona hope this helps
import { relations } from "drizzle-orm";
import {
char,
varchar,
mysqlTable,
timestamp,
AnyMySqlColumn,
primaryKey,
} from "drizzle-orm/mysql-core";
import { v4 as uuid } from "uuid";
export const posts = mysqlTable("posts", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
title: varchar("title", { length: 255 }).notNull(),
body: varchar("body", { length: 255 }).notNull(),
});
export const postsRelations = relations(posts, ({ many }) => ({
comments: many(comments),
}));
export const users = mysqlTable("users", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
name: varchar("name", { length: 255 }).notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
likes: many(likes),
}));
export const comments = mysqlTable("comments", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
message: varchar("message", { length: 255 }).notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().onUpdateNow().defaultNow(),
userId: char("user_id", { length: 36 })
.notNull()
.references(() => users.id),
postId: char("post_id", { length: 36 })
.notNull()
.references(() => posts.id),
parentId: char("parent_id", { length: 36 }).references(
(): AnyMySqlColumn => comments.id
),
});
export const commentsRelations = relations(comments, ({ one, many }) => ({
user: one(users, {
fields: [comments.userId],
references: [users.id],
}),
post: one(posts, {
fields: [comments.postId],
references: [posts.id],
}),
parent: one(comments, {
fields: [comments.parentId],
references: [comments.id],
relationName: "ParentComment",
}),
children: many(comments, { relationName: "ParentComment" }),
likes: many(likes),
}));
export const likes = mysqlTable(
"likes",
{
userId: char("user_id", { length: 36 })
.notNull()
.references(() => users.id),
commentId: char("comment_id", { length: 36 })
.notNull()
.references(() => posts.id),
},
(drizzle) => ({
userIdCommentIdPrimaryKey: primaryKey({
columns: [drizzle.userId, drizzle.commentId],
}),
})
);
export const likesRelations = relations(likes, ({ one }) => ({
user: one(users, {
fields: [likes.userId],
references: [users.id],
}),
comment: one(comments, {
fields: [likes.commentId],
references: [comments.id],
}),
}));
import { relations } from "drizzle-orm";
import {
char,
varchar,
mysqlTable,
timestamp,
AnyMySqlColumn,
primaryKey,
} from "drizzle-orm/mysql-core";
import { v4 as uuid } from "uuid";
export const posts = mysqlTable("posts", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
title: varchar("title", { length: 255 }).notNull(),
body: varchar("body", { length: 255 }).notNull(),
});
export const postsRelations = relations(posts, ({ many }) => ({
comments: many(comments),
}));
export const users = mysqlTable("users", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
name: varchar("name", { length: 255 }).notNull(),
});
export const usersRelations = relations(users, ({ many }) => ({
likes: many(likes),
}));
export const comments = mysqlTable("comments", {
id: char("id", { length: 36 })
.notNull()
.primaryKey()
.$defaultFn(() => uuid()),
message: varchar("message", { length: 255 }).notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().onUpdateNow().defaultNow(),
userId: char("user_id", { length: 36 })
.notNull()
.references(() => users.id),
postId: char("post_id", { length: 36 })
.notNull()
.references(() => posts.id),
parentId: char("parent_id", { length: 36 }).references(
(): AnyMySqlColumn => comments.id
),
});
export const commentsRelations = relations(comments, ({ one, many }) => ({
user: one(users, {
fields: [comments.userId],
references: [users.id],
}),
post: one(posts, {
fields: [comments.postId],
references: [posts.id],
}),
parent: one(comments, {
fields: [comments.parentId],
references: [comments.id],
relationName: "ParentComment",
}),
children: many(comments, { relationName: "ParentComment" }),
likes: many(likes),
}));
export const likes = mysqlTable(
"likes",
{
userId: char("user_id", { length: 36 })
.notNull()
.references(() => users.id),
commentId: char("comment_id", { length: 36 })
.notNull()
.references(() => posts.id),
},
(drizzle) => ({
userIdCommentIdPrimaryKey: primaryKey({
columns: [drizzle.userId, drizzle.commentId],
}),
})
);
export const likesRelations = relations(likes, ({ one }) => ({
user: one(users, {
fields: [likes.userId],
references: [users.id],
}),
comment: one(comments, {
fields: [likes.commentId],
references: [comments.id],
}),
}));
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { v4 as uuid } from "uuid";
import { comments, posts, users } from "./schema";
import * as schema from "./schema";
import { eq, isNull } from "drizzle-orm";
import { join } from "path";
async function main() {
const poolConnection = mysql.createPool({
host: "localhost",
port: 3306,
user: "root",
password: "root",
database: "drizzle_discord_kairevona",
});
const db = drizzle(poolConnection, { schema, mode: "default" });
await migrate(db, {
migrationsFolder: join(process.cwd(), "drizzle", "migrations"),
});
const post = await db.transaction(async (drizzle) => {
// insert user
const user1Id = uuid();
const user2Id = uuid();
await drizzle.insert(users).values([
{ id: user1Id, name: "user1" },
{ id: user2Id, name: "user2" },
]);
// insert post
const postId = uuid();
await drizzle
.insert(posts)
.values({ id: postId, title: "New post", body: "Hello world!" });
// insert comments
const comment1 = uuid();
const comment2 = uuid();
await drizzle.insert(comments).values([
{ id: comment1, postId, userId: user1Id, message: "comment1" },
{ id: comment2, postId, userId: user2Id, message: "comment2" },
]);
// insert nested comment
const nestedComment1 = uuid();
const nestedComment2 = uuid();
const nestedComment3 = uuid();
await drizzle.insert(comments).values([
{
id: nestedComment1,
postId,
parentId: comment2,
userId: user1Id,
message: "nestedComment1",
},
{
id: nestedComment2,
postId,
parentId: comment2,
userId: user2Id,
message: "nestedComment2",
},
{
id: nestedComment3,
postId,
userId: user2Id,
parentId: comment2,
message: "nestedComment3",
},
]);
// query
return drizzle.query.posts.findFirst({
with: {
comments: {
with: {
user: true,
children: {
with: {
user: true,
},
},
},
where: isNull(comments.parentId),
},
},
where: eq(posts.id, postId),
});
});
console.log(JSON.stringify(post, null, 2));
}
main();
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { v4 as uuid } from "uuid";
import { comments, posts, users } from "./schema";
import * as schema from "./schema";
import { eq, isNull } from "drizzle-orm";
import { join } from "path";
async function main() {
const poolConnection = mysql.createPool({
host: "localhost",
port: 3306,
user: "root",
password: "root",
database: "drizzle_discord_kairevona",
});
const db = drizzle(poolConnection, { schema, mode: "default" });
await migrate(db, {
migrationsFolder: join(process.cwd(), "drizzle", "migrations"),
});
const post = await db.transaction(async (drizzle) => {
// insert user
const user1Id = uuid();
const user2Id = uuid();
await drizzle.insert(users).values([
{ id: user1Id, name: "user1" },
{ id: user2Id, name: "user2" },
]);
// insert post
const postId = uuid();
await drizzle
.insert(posts)
.values({ id: postId, title: "New post", body: "Hello world!" });
// insert comments
const comment1 = uuid();
const comment2 = uuid();
await drizzle.insert(comments).values([
{ id: comment1, postId, userId: user1Id, message: "comment1" },
{ id: comment2, postId, userId: user2Id, message: "comment2" },
]);
// insert nested comment
const nestedComment1 = uuid();
const nestedComment2 = uuid();
const nestedComment3 = uuid();
await drizzle.insert(comments).values([
{
id: nestedComment1,
postId,
parentId: comment2,
userId: user1Id,
message: "nestedComment1",
},
{
id: nestedComment2,
postId,
parentId: comment2,
userId: user2Id,
message: "nestedComment2",
},
{
id: nestedComment3,
postId,
userId: user2Id,
parentId: comment2,
message: "nestedComment3",
},
]);
// query
return drizzle.query.posts.findFirst({
with: {
comments: {
with: {
user: true,
children: {
with: {
user: true,
},
},
},
where: isNull(comments.parentId),
},
},
where: eq(posts.id, postId),
});
});
console.log(JSON.stringify(post, null, 2));
}
main();
{
"id": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"title": "New post",
"body": "Hello world!",
"comments": [
{
"id": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"message": "comment2",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": null,
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
},
"children": [
{
"id": "11da841d-2dcb-42dc-99b3-275938925e05",
"message": "nestedComment1",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"name": "user1"
}
},
{
"id": "d097d3b1-f763-47d0-b067-a8357e24e53b",
"message": "nestedComment2",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
}
},
{
"id": "db6deb89-b82a-47a0-9675-c3e3c7956bdf",
"message": "nestedComment3",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
}
}
]
},
{
"id": "d4fff4ec-989a-4a5e-85c4-a3dfa19984c2",
"message": "comment1",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": null,
"user": {
"id": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"name": "user1"
},
"children": []
}
]
}
{
"id": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"title": "New post",
"body": "Hello world!",
"comments": [
{
"id": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"message": "comment2",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": null,
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
},
"children": [
{
"id": "11da841d-2dcb-42dc-99b3-275938925e05",
"message": "nestedComment1",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"name": "user1"
}
},
{
"id": "d097d3b1-f763-47d0-b067-a8357e24e53b",
"message": "nestedComment2",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
}
},
{
"id": "db6deb89-b82a-47a0-9675-c3e3c7956bdf",
"message": "nestedComment3",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": "45b10215-fce8-4a4c-a8a0-a1dabea08d51",
"user": {
"id": "a3d92891-eb53-4edc-b337-99547c3d62d7",
"name": "user2"
}
}
]
},
{
"id": "d4fff4ec-989a-4a5e-85c4-a3dfa19984c2",
"message": "comment1",
"createdAt": "2024-02-02T21:40:21.000Z",
"updatedAt": "2024-02-02T21:40:21.000Z",
"userId": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"postId": "fe126ec9-7eee-480e-9e83-9b20637e6e86",
"parentId": null,
"user": {
"id": "b1928b2c-ba33-4c19-8f80-f0c3b30ad916",
"name": "user1"
},
"children": []
}
]
}
yeah i will check that soon and reply you
but planetscale db doesn't support foreign key, so i can't use the ref relations
thats fine, you can remove the fks
It return don't have enough information to infer relation
Thank for help, it works
You're welcome :)
Hello Beautiful community,
How to define createdAt and updatedAt fields ? so that they get to have default insert date and update date ?
https://orm.drizzle.team/docs/column-types/pg#timestamp
https://orm.drizzle.team/docs/column-types/mysql#timestamp
Drizzle ORM - MySQL
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Drizzle ORM - PostgreSQL
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
@nagaraj7737 here's the docs reference, you should create a new thread since this is unrelated (
.defaultNow())Now I want to use likes table for post and commnet. But i don''t know how to do. Please help me