db: Migrate from prisma to drizzle

8 files changed, 195 insertions, 162 deletions

diff --git a/packages/web/app/dashboard/tags/[tagName]/page.tsx b/packages/web/app/dashboard/tags/[tagName]/page.tsx
index 729a5cbc..493cb358 100644
--- a/packages/web/app/dashboard/tags/[tagName]/page.tsx
+++ b/packages/web/app/dashboard/tags/[tagName]/page.tsx
@@ -1,8 +1,10 @@
 import { getServerAuthSession } from "@/server/auth";
-import { prisma } from "@hoarder/db";
+import { db } from "@hoarder/db";
 import { notFound, redirect } from "next/navigation";
 import BookmarksGrid from "../../bookmarks/components/BookmarksGrid";
 import { api } from "@/server/api/client";
+import { bookmarkTags, tagsOnBookmarks } from "@hoarder/db/schema";
+import { and, eq } from "drizzle-orm";
 
 export default async function TagPage({
   params,
@@ -13,14 +15,12 @@ export default async function TagPage({
   if (!session) {
     redirect("/");
   }
-  const tag = await prisma.bookmarkTags.findUnique({
-    where: {
-      userId_name: {
-        userId: session.user.id,
-        name: params.tagName,
-      },
-    },
-    select: {
+  const tag = await db.query.bookmarkTags.findFirst({
+    where: and(
+      eq(bookmarkTags.userId, session.user.id),
+      eq(bookmarkTags.name, params.tagName),
+    ),
+    columns: {
       id: true,
     },
   });
@@ -30,11 +30,9 @@ export default async function TagPage({
     notFound();
   }
 
-  const bookmarkIds = await prisma.tagsOnBookmarks.findMany({
-    where: {
-      tagId: tag.id,
-    },
-    select: {
+  const bookmarkIds = await db.query.tagsOnBookmarks.findMany({
+    where: eq(tagsOnBookmarks.tagId, tag.id),
+    columns: {
       bookmarkId: true,
     },
   });
diff --git a/packages/web/app/dashboard/tags/page.tsx b/packages/web/app/dashboard/tags/page.tsx
index d76a7f91..4b12fe35 100644
--- a/packages/web/app/dashboard/tags/page.tsx
+++ b/packages/web/app/dashboard/tags/page.tsx
@@ -1,5 +1,7 @@
 import { getServerAuthSession } from "@/server/auth";
-import { prisma } from "@hoarder/db";
+import { db } from "@hoarder/db";
+import { bookmarkTags, tagsOnBookmarks } from "@hoarder/db/schema";
+import { count, eq } from "drizzle-orm";
 import Link from "next/link";
 import { redirect } from "next/navigation";
 
@@ -23,28 +25,24 @@ export default async function TagsPage() {
     redirect("/");
   }
 
-  let tags = await prisma.bookmarkTags.findMany({
-    where: {
-      userId: session.user.id,
-    },
-    include: {
-      _count: {
-        select: {
-          bookmarks: true,
-        },
-      },
-    },
-  });
+  let tags = await db
+    .select({
+      id: tagsOnBookmarks.tagId,
+      name: bookmarkTags.name,
+      count: count(),
+    })
+    .from(tagsOnBookmarks)
+    .where(eq(bookmarkTags.userId, session.user.id))
+    .groupBy(tagsOnBookmarks.tagId)
+    .innerJoin(bookmarkTags, eq(bookmarkTags.id, tagsOnBookmarks.tagId));
 
   // Sort tags by usage desc
-  tags = tags
-    .filter((t) => t._count.bookmarks > 0)
-    .sort((a, b) => b._count.bookmarks - a._count.bookmarks);
+  tags = tags.sort((a, b) => b.count - a.count);
 
   let tagPill;
   if (tags.length) {
     tagPill = tags.map((t) => (
-      <TagPill key={t.id} name={t.name} count={t._count.bookmarks} />
+      <TagPill key={t.id} name={t.name} count={t.count} />
     ));
   } else {
     tagPill = "No Tags";
diff --git a/packages/web/lib/types/api/bookmarks.ts b/packages/web/lib/types/api/bookmarks.ts
index 94f89e55..0970a7ed 100644
--- a/packages/web/lib/types/api/bookmarks.ts
+++ b/packages/web/lib/types/api/bookmarks.ts
@@ -17,14 +17,19 @@ export const zBookmarkContentSchema = z.discriminatedUnion("type", [
 ]);
 export type ZBookmarkContent = z.infer<typeof zBookmarkContentSchema>;
 
-export const zBookmarkSchema = z.object({
+export const zBareBookmarkSchema = z.object({
   id: z.string(),
   createdAt: z.date(),
   archived: z.boolean(),
   favourited: z.boolean(),
-  tags: z.array(zBookmarkTagSchema),
-  content: zBookmarkContentSchema,
 });
+
+export const zBookmarkSchema = zBareBookmarkSchema.merge(
+  z.object({
+    tags: z.array(zBookmarkTagSchema),
+    content: zBookmarkContentSchema,
+  }),
+);
 export type ZBookmark = z.infer<typeof zBookmarkSchema>;
 
 // POST /v1/bookmarks
diff --git a/packages/web/package.json b/packages/web/package.json
index 32016419..5b47ea6e 100644
--- a/packages/web/package.json
+++ b/packages/web/package.json
@@ -12,7 +12,6 @@
   "dependencies": {
     "@hoarder/db": "0.1.0",
     "@hookform/resolvers": "^3.3.4",
-    "@next-auth/prisma-adapter": "^1.0.7",
     "@next/eslint-plugin-next": "^14.1.0",
     "@radix-ui/react-dialog": "^1.0.5",
     "@radix-ui/react-dropdown-menu": "^2.0.6",
@@ -30,6 +29,7 @@
     "bcrypt": "^5.1.1",
     "class-variance-authority": "^0.7.0",
     "clsx": "^2.1.0",
+    "drizzle-orm": "^0.29.4",
     "install": "^0.13.0",
     "lucide-react": "^0.322.0",
     "next": "14.1.0",
diff --git a/packages/web/server/api/routers/apiKeys.ts b/packages/web/server/api/routers/apiKeys.ts
index 620ca223..eade5eec 100644
--- a/packages/web/server/api/routers/apiKeys.ts
+++ b/packages/web/server/api/routers/apiKeys.ts
@@ -1,7 +1,9 @@
 import { generateApiKey } from "@/server/auth";
 import { authedProcedure, router } from "../trpc";
-import { prisma } from "@hoarder/db";
+import { db } from "@hoarder/db";
 import { z } from "zod";
+import { apiKeys } from "@hoarder/db/schema";
+import { eq, and } from "drizzle-orm";
 
 export const apiKeysAppRouter = router({
   create: authedProcedure
@@ -29,13 +31,10 @@ export const apiKeysAppRouter = router({
     )
     .output(z.object({}))
     .mutation(async ({ input, ctx }) => {
-      const resp = await prisma.apiKey.delete({
-        where: {
-          id: input.id,
-          userId: ctx.user.id,
-        },
-      });
-      return resp;
+      await db
+        .delete(apiKeys)
+        .where(and(eq(apiKeys.id, input.id), eq(apiKeys.userId, ctx.user.id)))
+        .returning();
     }),
   list: authedProcedure
     .output(
@@ -51,11 +50,9 @@ export const apiKeysAppRouter = router({
       }),
     )
     .query(async ({ ctx }) => {
-      const resp = await prisma.apiKey.findMany({
-        where: {
-          userId: ctx.user.id,
-        },
-        select: {
+      const resp = await db.query.apiKeys.findMany({
+        where: eq(apiKeys.userId, ctx.user.id),
+        columns: {
           id: true,
           name: true,
           createdAt: true,
diff --git a/packages/web/server/api/routers/bookmarks.ts b/packages/web/server/api/routers/bookmarks.ts
index 65f20ef5..2af81d27 100644
--- a/packages/web/server/api/routers/bookmarks.ts
+++ b/packages/web/server/api/routers/bookmarks.ts
@@ -3,46 +3,28 @@ import { authedProcedure, router } from "../trpc";
 import {
   ZBookmark,
   ZBookmarkContent,
+  zBareBookmarkSchema,
   zBookmarkSchema,
   zGetBookmarksRequestSchema,
   zGetBookmarksResponseSchema,
   zNewBookmarkRequestSchema,
   zUpdateBookmarksRequestSchema,
 } from "@/lib/types/api/bookmarks";
-import { prisma } from "@hoarder/db";
+import { db } from "@hoarder/db";
+import { bookmarkLinks, bookmarks } from "@hoarder/db/schema";
 import { LinkCrawlerQueue } from "@hoarder/shared/queues";
 import { TRPCError, experimental_trpcMiddleware } from "@trpc/server";
 import { User } from "next-auth";
-
-const defaultBookmarkFields = {
-  id: true,
-  favourited: true,
-  archived: true,
-  createdAt: true,
-  link: {
-    select: {
-      url: true,
-      title: true,
-      description: true,
-      imageUrl: true,
-      favicon: true,
-      crawledAt: true,
-    },
-  },
-  tags: {
-    include: {
-      tag: true,
-    },
-  },
-};
+import { and, desc, eq, inArray } from "drizzle-orm";
+import { ZBookmarkTags } from "@/lib/types/api/tags";
 
 const ensureBookmarkOwnership = experimental_trpcMiddleware<{
   ctx: { user: User };
   input: { bookmarkId: string };
 }>().create(async (opts) => {
-  const bookmark = await prisma.bookmark.findUnique({
-    where: { id: opts.input.bookmarkId },
-    select: {
+  const bookmark = await db.query.bookmarks.findFirst({
+    where: eq(bookmarks.id, opts.input.bookmarkId),
+    columns: {
       userId: true,
     },
   });
@@ -62,17 +44,27 @@ const ensureBookmarkOwnership = experimental_trpcMiddleware<{
   return opts.next();
 });
 
-async function dummyPrismaReturnType() {
-  const x = await prisma.bookmark.findFirstOrThrow({
-    select: defaultBookmarkFields,
+async function dummyDrizzleReturnType() {
+  const x = await db.query.bookmarks.findFirst({
+    with: {
+      tagsOnBookmarks: {
+        with: {
+          tag: true,
+        },
+      },
+      link: true,
+    },
   });
+  if (!x) {
+    throw new Error();
+  }
   return x;
 }
 
 function toZodSchema(
-  bookmark: Awaited<ReturnType<typeof dummyPrismaReturnType>>,
+  bookmark: Awaited<ReturnType<typeof dummyDrizzleReturnType>>,
 ): ZBookmark {
-  const { tags, link, ...rest } = bookmark;
+  const { tagsOnBookmarks, link, ...rest } = bookmark;
 
   let content: ZBookmarkContent;
   if (link) {
@@ -82,7 +74,7 @@ function toZodSchema(
   }
 
   return {
-    tags: tags.map((t) => t.tag),
+    tags: tagsOnBookmarks.map((t) => t.tag),
     content,
     ...rest,
   };
@@ -94,18 +86,37 @@ export const bookmarksAppRouter = router({
     .output(zBookmarkSchema)
     .mutation(async ({ input, ctx }) => {
       const { url } = input;
-      const userId = ctx.user.id;
 
-      const bookmark = await prisma.bookmark.create({
-        data: {
-          link: {
-            create: {
+      const bookmark = await db.transaction(async (tx): Promise<ZBookmark> => {
+        const bookmark = (
+          await tx
+            .insert(bookmarks)
+            .values({
+              userId: ctx.user.id,
+            })
+            .returning()
+        )[0];
+
+        const link = (
+          await tx
+            .insert(bookmarkLinks)
+            .values({
+              id: bookmark.id,
               url,
-            },
-          },
-          userId,
-        },
-        select: defaultBookmarkFields,
+            })
+            .returning()
+        )[0];
+
+        const content: ZBookmarkContent = {
+          type: "link",
+          ...link,
+        };
+
+        return {
+          tags: [] as ZBookmarkTags[],
+          content,
+          ...bookmark,
+        };
       });
 
       // Enqueue crawling request
@@ -113,38 +124,48 @@ export const bookmarksAppRouter = router({
         bookmarkId: bookmark.id,
       });
 
-      return toZodSchema(bookmark);
+      return bookmark;
     }),
 
   updateBookmark: authedProcedure
     .input(zUpdateBookmarksRequestSchema)
-    .output(zBookmarkSchema)
+    .output(zBareBookmarkSchema)
     .use(ensureBookmarkOwnership)
     .mutation(async ({ input, ctx }) => {
-      const bookmark = await prisma.bookmark.update({
-        where: {
-          id: input.bookmarkId,
-          userId: ctx.user.id,
-        },
-        data: {
+      const res = await db
+        .update(bookmarks)
+        .set({
           archived: input.archived,
           favourited: input.favourited,
-        },
-        select: defaultBookmarkFields,
-      });
-      return toZodSchema(bookmark);
+        })
+        .where(
+          and(
+            eq(bookmarks.userId, ctx.user.id),
+            eq(bookmarks.id, input.bookmarkId),
+          ),
+        )
+        .returning();
+      if (res.length == 0) {
+        throw new TRPCError({
+          code: "NOT_FOUND",
+          message: "Bookmark not found",
+        });
+      }
+      return res[0];
     }),
 
   deleteBookmark: authedProcedure
     .input(z.object({ bookmarkId: z.string() }))
     .use(ensureBookmarkOwnership)
     .mutation(async ({ input, ctx }) => {
-      await prisma.bookmark.delete({
-        where: {
-          id: input.bookmarkId,
-          userId: ctx.user.id,
-        },
-      });
+      await db
+        .delete(bookmarks)
+        .where(
+          and(
+            eq(bookmarks.userId, ctx.user.id),
+            eq(bookmarks.id, input.bookmarkId),
+          ),
+        );
     }),
   recrawlBookmark: authedProcedure
     .input(z.object({ bookmarkId: z.string() }))
@@ -162,12 +183,19 @@ export const bookmarksAppRouter = router({
     )
     .output(zBookmarkSchema)
     .query(async ({ input, ctx }) => {
-      const bookmark = await prisma.bookmark.findUnique({
-        where: {
-          userId: ctx.user.id,
-          id: input.id,
+      const bookmark = await db.query.bookmarks.findFirst({
+        where: and(
+          eq(bookmarks.userId, ctx.user.id),
+          eq(bookmarks.id, input.id),
+        ),
+        with: {
+          tagsOnBookmarks: {
+            with: {
+              tag: true,
+            },
+          },
+          link: true,
         },
-        select: defaultBookmarkFields,
       });
       if (!bookmark) {
         throw new TRPCError({
@@ -182,25 +210,28 @@ export const bookmarksAppRouter = router({
     .input(zGetBookmarksRequestSchema)
     .output(zGetBookmarksResponseSchema)
     .query(async ({ input, ctx }) => {
-      const bookmarks = (
-        await prisma.bookmark.findMany({
-          where: {
-            userId: ctx.user.id,
-            archived: input.archived,
-            favourited: input.favourited,
-            id: input.ids
-              ? {
-                  in: input.ids,
-                }
-              : undefined,
-          },
-          orderBy: {
-            createdAt: "desc",
+      const results = await db.query.bookmarks.findMany({
+        where: and(
+          eq(bookmarks.userId, ctx.user.id),
+          input.archived !== undefined
+            ? eq(bookmarks.archived, input.archived)
+            : undefined,
+          input.favourited !== undefined
+            ? eq(bookmarks.favourited, input.favourited)
+            : undefined,
+          input.ids ? inArray(bookmarks.id, input.ids) : undefined,
+        ),
+        orderBy: [desc(bookmarks.createdAt)],
+        with: {
+          tagsOnBookmarks: {
+            with: {
+              tag: true,
+            },
           },
-          select: defaultBookmarkFields,
-        })
-      ).map(toZodSchema);
+          link: true,
+        },
+      });
 
-      return { bookmarks };
+      return { bookmarks: results.map(toZodSchema) };
     }),
 });
diff --git a/packages/web/server/api/routers/users.ts b/packages/web/server/api/routers/users.ts
index aecec1d4..032385ac 100644
--- a/packages/web/server/api/routers/users.ts
+++ b/packages/web/server/api/routers/users.ts
@@ -1,9 +1,10 @@
 import { zSignUpSchema } from "@/lib/types/api/users";
 import { publicProcedure, router } from "../trpc";
-import { Prisma, prisma } from "@hoarder/db";
+import { SqliteError, db } from "@hoarder/db";
 import { z } from "zod";
 import { hashPassword } from "@/server/auth";
 import { TRPCError } from "@trpc/server";
+import { users } from "@hoarder/db/schema";
 
 export const usersAppRouter = router({
   create: publicProcedure
@@ -16,20 +17,21 @@ export const usersAppRouter = router({
     )
     .mutation(async ({ input }) => {
       try {
-        return await prisma.user.create({
-          data: {
+        const result = await db
+          .insert(users)
+          .values({
             name: input.name,
             email: input.email,
             password: await hashPassword(input.password),
-          },
-          select: {
-            name: true,
-            email: true,
-          },
-        });
+          })
+          .returning({
+            name: users.name,
+            email: users.email,
+          });
+        return result[0];
       } catch (e) {
-        if (e instanceof Prisma.PrismaClientKnownRequestError) {
-          if (e.code === "P2002") {
+        if (e instanceof SqliteError) {
+          if (e.code == "SQLITE_CONSTRAINT_UNIQUE") {
             throw new TRPCError({
               code: "BAD_REQUEST",
               message: "Email is already taken",
diff --git a/packages/web/server/auth.ts b/packages/web/server/auth.ts
index a63bcac4..f2c78190 100644
--- a/packages/web/server/auth.ts
+++ b/packages/web/server/auth.ts
@@ -1,14 +1,16 @@
 import NextAuth, { NextAuthOptions, getServerSession } from "next-auth";
-import { PrismaAdapter } from "@next-auth/prisma-adapter";
+import type { Adapter } from "next-auth/adapters";
 import AuthentikProvider from "next-auth/providers/authentik";
 import serverConfig from "@hoarder/shared/config";
-import { prisma } from "@hoarder/db";
+import { db } from "@hoarder/db";
 import { DefaultSession } from "next-auth";
 import * as bcrypt from "bcrypt";
 import CredentialsProvider from "next-auth/providers/credentials";
+import { DrizzleAdapter } from "@auth/drizzle-adapter";
 
 import { randomBytes } from "crypto";
 import { Provider } from "next-auth/providers/index";
+import { apiKeys } from "@hoarder/db/schema";
 
 declare module "next-auth/jwt" {
   export interface JWT {
@@ -59,7 +61,8 @@ if (serverConfig.auth.authentik) {
 }
 
 export const authOptions: NextAuthOptions = {
-  adapter: PrismaAdapter(prisma),
+  // https://github.com/nextauthjs/next-auth/issues/9493
+  adapter: DrizzleAdapter(db) as Adapter,
   providers: providers,
   session: {
     strategy: "jwt",
@@ -99,14 +102,17 @@ export async function generateApiKey(name: string, userId: string) {
 
   const plain = `${API_KEY_PREFIX}_${id}_${secret}`;
 
-  const key = await prisma.apiKey.create({
-    data: {
-      name: name,
-      userId: userId,
-      keyId: id,
-      keyHash: secretHash,
-    },
-  });
+  const key = (
+    await db
+      .insert(apiKeys)
+      .values({
+        name: name,
+        userId: userId,
+        keyId: id,
+        keyHash: secretHash,
+      })
+      .returning()
+  )[0];
 
   return {
     id: key.id,
@@ -134,11 +140,9 @@ function parseApiKey(plain: string) {
 
 export async function authenticateApiKey(key: string) {
   const { keyId, keySecret } = parseApiKey(key);
-  const apiKey = await prisma.apiKey.findUnique({
-    where: {
-      keyId,
-    },
-    include: {
+  const apiKey = await db.query.apiKeys.findFirst({
+    where: (k, { eq }) => eq(k.keyId, keyId),
+    with: {
       user: true,
     },
   });
@@ -162,10 +166,8 @@ export async function hashPassword(password: string) {
 }
 
 export async function validatePassword(email: string, password: string) {
-  const user = await prisma.user.findUnique({
-    where: {
-      email,
-    },
+  const user = await db.query.users.findFirst({
+    where: (u, { eq }) => eq(u.email, email),
   });
 
   if (!user) {