T3NED Comments - Answer Overflow

{
  "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": []
    }
  ]
}

20 replies

DTDrizzle Team

•Created by Kai Revona on 2/2/2024 in #help

Nested comment

Adjust the connection to use planetscale (im testing locally with mysql)

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();

20 replies

DTDrizzle Team

•Created by Kai Revona on 2/2/2024 in #help

Nested comment

I translated the prisma schema to drizzle:

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],
  }),
}));

20 replies

DTDrizzle Team

•Created by Kai Revona on 2/2/2024 in #help

Nested comment

you can still define relations - they're seperate from the fks

20 replies

DTDrizzle Team

•Created by Kai Revona on 2/2/2024 in #help

Nested comment

20 replies

DTDrizzle Team

•Created by babakfp on 1/22/2024 in #help

What should I put inside `dbCredentials`? I'm using a local `.db` file with `better-sqlite3`

8 replies

DTDrizzle Team

•Created by jakeleventhal on 1/20/2024 in #help

Anyone have any idea about this bug?

great :D

14 replies

DTDrizzle Team

•Created by jakeleventhal on 1/20/2024 in #help

Anyone have any idea about this bug?

although in your prisma schema the relations are for the Branding table?

14 replies

DTDrizzle Team

•Created by jakeleventhal on 1/20/2024 in #help

Anyone have any idea about this bug?

export const settingsRelations = relations(settings, ({ one }) => ({
  packageInsert: one(designs, {
    fields: [settings.id],
    references: [designs.packageInsertId],
    relationName: "packageInsert",
  }),
  packageSticker: one(designs, {
    fields: [settings.id],
    references: [designs.packageStickerId],
    relationName: "packageSticker",
  }),
}));

export const designRelations = relations(designs, ({ one }) => ({
  packageInsertSettings: one(settings, {
    fields: [designs.packageInsertId],
    references: [settings.id],
    relationName: "packageInsert",
  }),
  packageStickerSettings: one(settings, {
    fields: [designs.packageStickerId],
    references: [settings.id],
    relationName: "packageSticker",
  }),
}));

export const settingsRelations = relations(settings, ({ one }) => ({
  packageInsert: one(designs, {
    fields: [settings.id],
    references: [designs.packageInsertId],
    relationName: "packageInsert",
  }),
  packageSticker: one(designs, {
    fields: [settings.id],
    references: [designs.packageStickerId],
    relationName: "packageSticker",
  }),
}));

export const designRelations = relations(designs, ({ one }) => ({
  packageInsertSettings: one(settings, {
    fields: [designs.packageInsertId],
    references: [settings.id],
    relationName: "packageInsert",
  }),
  packageStickerSettings: one(settings, {
    fields: [designs.packageStickerId],
    references: [settings.id],
    relationName: "packageSticker",
  }),
}));

perhaps?

14 replies

DTDrizzle Team

•Created by jakeleventhal on 1/20/2024 in #help

Anyone have any idea about this bug?

would you mind providing the tables/relations with the actual names you're using? feel free to strip down irrelevant columns

14 replies

DTDrizzle Team

•Created by jakeleventhal on 1/20/2024 in #help

Anyone have any idea about this bug?

@jakeleventhal what are you trying to do? 1) table1Relations is missing a column in fields and references 2) table2Relations does not have a table1ItemOne so typescript is correct in saying it doesn't exist

14 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

~~.nullsNotDistinct() is available, so i presume nulls are distinct by default?~~ nvm you're using mysql

40 replies

DTDrizzle Team

•Created by Grid on 1/17/2024 in #help

Unique constraint on multiple fields/columns

something like that would be possible. I believe drizzle supports all sql if you use the helpers... or you can modify the migration file

40 replies

Gaming

Programming