diff options
Diffstat (limited to 'packages')
33 files changed, 1159 insertions, 629 deletions
diff --git a/packages/db/drizzle.config.ts b/packages/db/drizzle.config.ts new file mode 100644 index 00000000..ef31abc1 --- /dev/null +++ b/packages/db/drizzle.config.ts @@ -0,0 +1,10 @@ +import "dotenv/config"; +import type { Config } from "drizzle-kit"; +export default { + schema: "./schema.ts", + out: "./drizzle", + driver: "better-sqlite", + dbCredentials: { + url: process.env.DATABASE_URL || "", + }, +} satisfies Config; diff --git a/packages/db/drizzle.ts b/packages/db/drizzle.ts new file mode 100644 index 00000000..def1fc0a --- /dev/null +++ b/packages/db/drizzle.ts @@ -0,0 +1,7 @@ +import "dotenv/config"; +import { drizzle } from "drizzle-orm/better-sqlite3"; +import Database from "better-sqlite3"; +import * as schema from "./schema"; + +const sqlite = new Database(process.env.DATABASE_URL); +export const db = drizzle(sqlite, { schema, logger: true }); diff --git a/packages/db/drizzle/0000_luxuriant_johnny_blaze.sql b/packages/db/drizzle/0000_luxuriant_johnny_blaze.sql new file mode 100644 index 00000000..44350e3b --- /dev/null +++ b/packages/db/drizzle/0000_luxuriant_johnny_blaze.sql @@ -0,0 +1,92 @@ +CREATE TABLE `account` ( + `userId` text NOT NULL, + `type` text NOT NULL, + `provider` text NOT NULL, + `providerAccountId` text NOT NULL, + `refresh_token` text, + `access_token` text, + `expires_at` integer, + `token_type` text, + `scope` text, + `id_token` text, + `session_state` text, + PRIMARY KEY(`provider`, `providerAccountId`), + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `apiKey` ( + `id` text PRIMARY KEY NOT NULL, + `name` text NOT NULL, + `createdAt` integer NOT NULL, + `keyId` text NOT NULL, + `keyHash` text NOT NULL, + `userId` text NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `bookmarkLinks` ( + `id` text PRIMARY KEY NOT NULL, + `url` text NOT NULL, + `title` text, + `description` text, + `imageUrl` text, + `favicon` text, + `crawledAt` integer, + FOREIGN KEY (`id`) REFERENCES `bookmarks`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `bookmarkTags` ( + `id` text PRIMARY KEY NOT NULL, + `name` text NOT NULL, + `createdAt` integer NOT NULL, + `userId` text NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `bookmarks` ( + `id` text PRIMARY KEY NOT NULL, + `createdAt` integer NOT NULL, + `archived` integer DEFAULT false NOT NULL, + `favourited` integer DEFAULT false NOT NULL, + `userId` text NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `session` ( + `sessionToken` text PRIMARY KEY NOT NULL, + `userId` text NOT NULL, + `expires` integer NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `tagsOnBookmarks` ( + `bookmarkId` text NOT NULL, + `tagId` text NOT NULL, + `attachedAt` text, + `attachedBy` text, + PRIMARY KEY(`bookmarkId`, `tagId`), + FOREIGN KEY (`bookmarkId`) REFERENCES `bookmarks`(`id`) ON UPDATE no action ON DELETE cascade, + FOREIGN KEY (`tagId`) REFERENCES `bookmarkTags`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `user` ( + `id` text PRIMARY KEY NOT NULL, + `name` text NOT NULL, + `email` text NOT NULL, + `emailVerified` integer, + `image` text, + `password` text +); +--> statement-breakpoint +CREATE TABLE `verificationToken` ( + `identifier` text NOT NULL, + `token` text NOT NULL, + `expires` integer NOT NULL, + PRIMARY KEY(`identifier`, `token`) +); +--> statement-breakpoint +CREATE UNIQUE INDEX `apiKey_name_unique` ON `apiKey` (`name`);--> statement-breakpoint +CREATE UNIQUE INDEX `apiKey_keyId_unique` ON `apiKey` (`keyId`);--> statement-breakpoint +CREATE UNIQUE INDEX `apiKey_name_userId_unique` ON `apiKey` (`name`,`userId`);--> statement-breakpoint +CREATE UNIQUE INDEX `bookmarkTags_userId_name_unique` ON `bookmarkTags` (`userId`,`name`);--> statement-breakpoint +CREATE UNIQUE INDEX `user_email_unique` ON `user` (`email`);
\ No newline at end of file diff --git a/packages/db/drizzle/meta/0000_snapshot.json b/packages/db/drizzle/meta/0000_snapshot.json new file mode 100644 index 00000000..a61b516d --- /dev/null +++ b/packages/db/drizzle/meta/0000_snapshot.json @@ -0,0 +1,562 @@ +{ + "version": "5", + "dialect": "sqlite", + "id": "926e135f-db63-4273-b193-4eb2b5c1784d", + "prevId": "00000000-0000-0000-0000-000000000000", + "tables": { + "account": { + "name": "account", + "columns": { + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "type": { + "name": "type", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "provider": { + "name": "provider", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "providerAccountId": { + "name": "providerAccountId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "refresh_token": { + "name": "refresh_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "access_token": { + "name": "access_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "expires_at": { + "name": "expires_at", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "token_type": { + "name": "token_type", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "scope": { + "name": "scope", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "id_token": { + "name": "id_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "session_state": { + "name": "session_state", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "account_userId_user_id_fk": { + "name": "account_userId_user_id_fk", + "tableFrom": "account", + "tableTo": "user", + "columnsFrom": ["userId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "account_provider_providerAccountId_pk": { + "columns": ["provider", "providerAccountId"], + "name": "account_provider_providerAccountId_pk" + } + }, + "uniqueConstraints": {} + }, + "apiKey": { + "name": "apiKey", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "keyId": { + "name": "keyId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "keyHash": { + "name": "keyHash", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "apiKey_name_unique": { + "name": "apiKey_name_unique", + "columns": ["name"], + "isUnique": true + }, + "apiKey_keyId_unique": { + "name": "apiKey_keyId_unique", + "columns": ["keyId"], + "isUnique": true + }, + "apiKey_name_userId_unique": { + "name": "apiKey_name_userId_unique", + "columns": ["name", "userId"], + "isUnique": true + } + }, + "foreignKeys": { + "apiKey_userId_user_id_fk": { + "name": "apiKey_userId_user_id_fk", + "tableFrom": "apiKey", + "tableTo": "user", + "columnsFrom": ["userId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkLinks": { + "name": "bookmarkLinks", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "url": { + "name": "url", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "title": { + "name": "title", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "imageUrl": { + "name": "imageUrl", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "favicon": { + "name": "favicon", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "crawledAt": { + "name": "crawledAt", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "bookmarkLinks_id_bookmarks_id_fk": { + "name": "bookmarkLinks_id_bookmarks_id_fk", + "tableFrom": "bookmarkLinks", + "tableTo": "bookmarks", + "columnsFrom": ["id"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkTags": { + "name": "bookmarkTags", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "bookmarkTags_userId_name_unique": { + "name": "bookmarkTags_userId_name_unique", + "columns": ["userId", "name"], + "isUnique": true + } + }, + "foreignKeys": { + "bookmarkTags_userId_user_id_fk": { + "name": "bookmarkTags_userId_user_id_fk", + "tableFrom": "bookmarkTags", + "tableTo": "user", + "columnsFrom": ["userId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarks": { + "name": "bookmarks", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "archived": { + "name": "archived", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "favourited": { + "name": "favourited", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "bookmarks_userId_user_id_fk": { + "name": "bookmarks_userId_user_id_fk", + "tableFrom": "bookmarks", + "tableTo": "user", + "columnsFrom": ["userId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "session": { + "name": "session", + "columns": { + "sessionToken": { + "name": "sessionToken", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "expires": { + "name": "expires", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "session_userId_user_id_fk": { + "name": "session_userId_user_id_fk", + "tableFrom": "session", + "tableTo": "user", + "columnsFrom": ["userId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "tagsOnBookmarks": { + "name": "tagsOnBookmarks", + "columns": { + "bookmarkId": { + "name": "bookmarkId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "tagId": { + "name": "tagId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "attachedAt": { + "name": "attachedAt", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "attachedBy": { + "name": "attachedBy", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "tagsOnBookmarks_bookmarkId_bookmarks_id_fk": { + "name": "tagsOnBookmarks_bookmarkId_bookmarks_id_fk", + "tableFrom": "tagsOnBookmarks", + "tableTo": "bookmarks", + "columnsFrom": ["bookmarkId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "tagsOnBookmarks_tagId_bookmarkTags_id_fk": { + "name": "tagsOnBookmarks_tagId_bookmarkTags_id_fk", + "tableFrom": "tagsOnBookmarks", + "tableTo": "bookmarkTags", + "columnsFrom": ["tagId"], + "columnsTo": ["id"], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "tagsOnBookmarks_bookmarkId_tagId_pk": { + "columns": ["bookmarkId", "tagId"], + "name": "tagsOnBookmarks_bookmarkId_tagId_pk" + } + }, + "uniqueConstraints": {} + }, + "user": { + "name": "user", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "email": { + "name": "email", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "emailVerified": { + "name": "emailVerified", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "image": { + "name": "image", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "password": { + "name": "password", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": { + "user_email_unique": { + "name": "user_email_unique", + "columns": ["email"], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "verificationToken": { + "name": "verificationToken", + "columns": { + "identifier": { + "name": "identifier", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "token": { + "name": "token", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "expires": { + "name": "expires", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": {}, + "compositePrimaryKeys": { + "verificationToken_identifier_token_pk": { + "columns": ["identifier", "token"], + "name": "verificationToken_identifier_token_pk" + } + }, + "uniqueConstraints": {} + } + }, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + } +} diff --git a/packages/db/drizzle/meta/_journal.json b/packages/db/drizzle/meta/_journal.json new file mode 100644 index 00000000..2dbd6ca1 --- /dev/null +++ b/packages/db/drizzle/meta/_journal.json @@ -0,0 +1,13 @@ +{ + "version": "5", + "dialect": "sqlite", + "entries": [ + { + "idx": 0, + "version": "5", + "when": 1708710681721, + "tag": "0000_luxuriant_johnny_blaze", + "breakpoints": true + } + ] +} diff --git a/packages/db/index.ts b/packages/db/index.ts index 31ebeec2..433d8db2 100644 --- a/packages/db/index.ts +++ b/packages/db/index.ts @@ -1,16 +1,3 @@ -import { PrismaClient } from "@prisma/client"; - -const globalForPrisma = globalThis as unknown as { - prisma: PrismaClient | undefined; -}; - -export const prisma = - globalForPrisma.prisma ?? - new PrismaClient({ - log: - process.env.NODE_ENV === "development" - ? ["query", "error", "warn"] - : ["error"], - }); - -export * from "@prisma/client"; +export { db } from "./drizzle"; +export * as schema from "./schema"; +export { SqliteError } from "better-sqlite3"; diff --git a/packages/db/migrate.ts b/packages/db/migrate.ts new file mode 100644 index 00000000..62cb4128 --- /dev/null +++ b/packages/db/migrate.ts @@ -0,0 +1,4 @@ +import { db } from "./drizzle"; +import { migrate } from "drizzle-orm/better-sqlite3/migrator"; + +migrate(db, { migrationsFolder: "./drizzle" }); diff --git a/packages/db/package.json b/packages/db/package.json index 59d569b3..6d3d7c06 100644 --- a/packages/db/package.json +++ b/packages/db/package.json @@ -4,10 +4,18 @@ "version": "0.1.0", "private": true, "main": "index.ts", + "scripts": { + "migrate": "ts-node migrate.ts", + "studio": "drizzle-kit studio" + }, "dependencies": { - "@prisma/client": "^5.9.1" + "@auth/drizzle-adapter": "^0.7.0", + "@paralleldrive/cuid2": "^2.2.2", + "better-sqlite3": "^9.4.3", + "drizzle-orm": "^0.29.4" }, "devDependencies": { - "prisma": "^5.9.1" + "@types/better-sqlite3": "^7.6.9", + "drizzle-kit": "^0.20.14" } } diff --git a/packages/db/prisma/migrations/20240205153748_add_users/migration.sql b/packages/db/prisma/migrations/20240205153748_add_users/migration.sql deleted file mode 100644 index cbf47073..00000000 --- a/packages/db/prisma/migrations/20240205153748_add_users/migration.sql +++ /dev/null @@ -1,56 +0,0 @@ --- CreateTable -CREATE TABLE "Account" ( - "id" TEXT NOT NULL PRIMARY KEY, - "userId" TEXT NOT NULL, - "type" TEXT NOT NULL, - "provider" TEXT NOT NULL, - "providerAccountId" TEXT NOT NULL, - "refresh_token" TEXT, - "access_token" TEXT, - "expires_at" INTEGER, - "token_type" TEXT, - "scope" TEXT, - "id_token" TEXT, - "session_state" TEXT, - CONSTRAINT "Account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "Session" ( - "id" TEXT NOT NULL PRIMARY KEY, - "sessionToken" TEXT NOT NULL, - "userId" TEXT NOT NULL, - "expires" DATETIME NOT NULL, - CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "User" ( - "id" TEXT NOT NULL PRIMARY KEY, - "name" TEXT, - "email" TEXT, - "emailVerified" DATETIME, - "image" TEXT -); - --- CreateTable -CREATE TABLE "VerificationToken" ( - "identifier" TEXT NOT NULL, - "token" TEXT NOT NULL, - "expires" DATETIME NOT NULL -); - --- CreateIndex -CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId"); - --- CreateIndex -CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken"); - --- CreateIndex -CREATE UNIQUE INDEX "User_email_key" ON "User"("email"); - --- CreateIndex -CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token"); - --- CreateIndex -CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token"); diff --git a/packages/db/prisma/migrations/20240206000813_add_links/migration.sql b/packages/db/prisma/migrations/20240206000813_add_links/migration.sql deleted file mode 100644 index 38c8d938..00000000 --- a/packages/db/prisma/migrations/20240206000813_add_links/migration.sql +++ /dev/null @@ -1,43 +0,0 @@ --- CreateTable -CREATE TABLE "BookmarkedLink" ( - "id" TEXT NOT NULL PRIMARY KEY, - "url" TEXT NOT NULL, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "userId" TEXT NOT NULL, - CONSTRAINT "BookmarkedLink_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "BookmarkedLinkDetails" ( - "id" TEXT NOT NULL PRIMARY KEY, - "title" TEXT NOT NULL, - "description" TEXT NOT NULL, - "imageUrl" TEXT NOT NULL, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - CONSTRAINT "BookmarkedLinkDetails_id_fkey" FOREIGN KEY ("id") REFERENCES "BookmarkedLink" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "BookmarkTags" ( - "id" TEXT NOT NULL PRIMARY KEY, - "name" TEXT NOT NULL, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "userId" TEXT NOT NULL, - CONSTRAINT "BookmarkTags_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "TagsOnLinks" ( - "linkId" TEXT NOT NULL, - "tagId" TEXT NOT NULL, - "attachedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "bookmarkTagsId" TEXT NOT NULL, - CONSTRAINT "TagsOnLinks_linkId_fkey" FOREIGN KEY ("linkId") REFERENCES "BookmarkedLink" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT "TagsOnLinks_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "BookmarkTags" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateIndex -CREATE UNIQUE INDEX "BookmarkTags_name_key" ON "BookmarkTags"("name"); - --- CreateIndex -CREATE UNIQUE INDEX "TagsOnLinks_linkId_tagId_key" ON "TagsOnLinks"("linkId", "tagId"); diff --git a/packages/db/prisma/migrations/20240206192241_add_favicon/migration.sql b/packages/db/prisma/migrations/20240206192241_add_favicon/migration.sql deleted file mode 100644 index 330575e9..00000000 --- a/packages/db/prisma/migrations/20240206192241_add_favicon/migration.sql +++ /dev/null @@ -1,16 +0,0 @@ --- RedefineTables -PRAGMA foreign_keys=OFF; -CREATE TABLE "new_BookmarkedLinkDetails" ( - "id" TEXT NOT NULL PRIMARY KEY, - "title" TEXT, - "description" TEXT, - "imageUrl" TEXT, - "favicon" TEXT, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - CONSTRAINT "BookmarkedLinkDetails_id_fkey" FOREIGN KEY ("id") REFERENCES "BookmarkedLink" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); -INSERT INTO "new_BookmarkedLinkDetails" ("createdAt", "description", "id", "imageUrl", "title") SELECT "createdAt", "description", "id", "imageUrl", "title" FROM "BookmarkedLinkDetails"; -DROP TABLE "BookmarkedLinkDetails"; -ALTER TABLE "new_BookmarkedLinkDetails" RENAME TO "BookmarkedLinkDetails"; -PRAGMA foreign_key_check; -PRAGMA foreign_keys=ON; diff --git a/packages/db/prisma/migrations/20240207204211_drop_extra_field_in_tags_links/migration.sql b/packages/db/prisma/migrations/20240207204211_drop_extra_field_in_tags_links/migration.sql deleted file mode 100644 index 78184041..00000000 --- a/packages/db/prisma/migrations/20240207204211_drop_extra_field_in_tags_links/migration.sql +++ /dev/null @@ -1,21 +0,0 @@ -/* - Warnings: - - - You are about to drop the column `bookmarkTagsId` on the `TagsOnLinks` table. All the data in the column will be lost. - -*/ --- RedefineTables -PRAGMA foreign_keys=OFF; -CREATE TABLE "new_TagsOnLinks" ( - "linkId" TEXT NOT NULL, - "tagId" TEXT NOT NULL, - "attachedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - CONSTRAINT "TagsOnLinks_linkId_fkey" FOREIGN KEY ("linkId") REFERENCES "BookmarkedLink" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT "TagsOnLinks_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "BookmarkTags" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); -INSERT INTO "new_TagsOnLinks" ("attachedAt", "linkId", "tagId") SELECT "attachedAt", "linkId", "tagId" FROM "TagsOnLinks"; -DROP TABLE "TagsOnLinks"; -ALTER TABLE "new_TagsOnLinks" RENAME TO "TagsOnLinks"; -CREATE UNIQUE INDEX "TagsOnLinks_linkId_tagId_key" ON "TagsOnLinks"("linkId", "tagId"); -PRAGMA foreign_key_check; -PRAGMA foreign_keys=ON; diff --git a/packages/db/prisma/migrations/20240209013653_toplevel_bookmark/migration.sql b/packages/db/prisma/migrations/20240209013653_toplevel_bookmark/migration.sql deleted file mode 100644 index 2b5aa370..00000000 --- a/packages/db/prisma/migrations/20240209013653_toplevel_bookmark/migration.sql +++ /dev/null @@ -1,62 +0,0 @@ -/* - Warnings: - - - You are about to drop the `BookmarkedLinkDetails` table. If the table is not empty, all the data it contains will be lost. - - You are about to drop the `TagsOnLinks` table. If the table is not empty, all the data it contains will be lost. - - You are about to drop the column `createdAt` on the `BookmarkedLink` table. All the data in the column will be lost. - - You are about to drop the column `userId` on the `BookmarkedLink` table. All the data in the column will be lost. - -*/ --- DropIndex -DROP INDEX "TagsOnLinks_linkId_tagId_key"; - --- DropTable -PRAGMA foreign_keys=off; -DROP TABLE "BookmarkedLinkDetails"; -PRAGMA foreign_keys=on; - --- DropTable -PRAGMA foreign_keys=off; -DROP TABLE "TagsOnLinks"; -PRAGMA foreign_keys=on; - --- CreateTable -CREATE TABLE "Bookmark" ( - "id" TEXT NOT NULL PRIMARY KEY, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "archived" BOOLEAN NOT NULL DEFAULT false, - "favourited" BOOLEAN NOT NULL DEFAULT false, - "userId" TEXT NOT NULL, - CONSTRAINT "Bookmark_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateTable -CREATE TABLE "TagsOnBookmarks" ( - "bookmarkId" TEXT NOT NULL, - "tagId" TEXT NOT NULL, - "attachedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "attachedBy" TEXT NOT NULL, - CONSTRAINT "TagsOnBookmarks_bookmarkId_fkey" FOREIGN KEY ("bookmarkId") REFERENCES "Bookmark" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT "TagsOnBookmarks_tagId_fkey" FOREIGN KEY ("tagId") REFERENCES "BookmarkTags" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- RedefineTables -PRAGMA foreign_keys=OFF; -CREATE TABLE "new_BookmarkedLink" ( - "id" TEXT NOT NULL PRIMARY KEY, - "url" TEXT NOT NULL, - "title" TEXT, - "description" TEXT, - "imageUrl" TEXT, - "favicon" TEXT, - "crawledAt" DATETIME, - CONSTRAINT "BookmarkedLink_id_fkey" FOREIGN KEY ("id") REFERENCES "Bookmark" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); -INSERT INTO "new_BookmarkedLink" ("id", "url") SELECT "id", "url" FROM "BookmarkedLink"; -DROP TABLE "BookmarkedLink"; -ALTER TABLE "new_BookmarkedLink" RENAME TO "BookmarkedLink"; -PRAGMA foreign_key_check; -PRAGMA foreign_keys=ON; - --- CreateIndex -CREATE UNIQUE INDEX "TagsOnBookmarks_bookmarkId_tagId_key" ON "TagsOnBookmarks"("bookmarkId", "tagId"); diff --git a/packages/db/prisma/migrations/20240211184744_add_api_key/migration.sql b/packages/db/prisma/migrations/20240211184744_add_api_key/migration.sql deleted file mode 100644 index c39bf511..00000000 --- a/packages/db/prisma/migrations/20240211184744_add_api_key/migration.sql +++ /dev/null @@ -1,16 +0,0 @@ --- CreateTable -CREATE TABLE "ApiKey" ( - "id" TEXT NOT NULL PRIMARY KEY, - "name" TEXT NOT NULL, - "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, - "keyId" TEXT NOT NULL, - "keyHash" TEXT NOT NULL, - "userId" TEXT NOT NULL, - CONSTRAINT "ApiKey_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User" ("id") ON DELETE CASCADE ON UPDATE CASCADE -); - --- CreateIndex -CREATE UNIQUE INDEX "ApiKey_keyId_key" ON "ApiKey"("keyId"); - --- CreateIndex -CREATE UNIQUE INDEX "ApiKey_name_userId_key" ON "ApiKey"("name", "userId"); diff --git a/packages/db/prisma/migrations/20240214011350_fix_tag_name_index/migration.sql b/packages/db/prisma/migrations/20240214011350_fix_tag_name_index/migration.sql deleted file mode 100644 index cbcd8821..00000000 --- a/packages/db/prisma/migrations/20240214011350_fix_tag_name_index/migration.sql +++ /dev/null @@ -1,11 +0,0 @@ -/* - Warnings: - - - A unique constraint covering the columns `[userId,name]` on the table `BookmarkTags` will be added. If there are existing duplicate values, this will fail. - -*/ --- DropIndex -DROP INDEX "BookmarkTags_name_key"; - --- CreateIndex -CREATE UNIQUE INDEX "BookmarkTags_userId_name_key" ON "BookmarkTags"("userId", "name"); diff --git a/packages/db/prisma/migrations/20240221104430_add_password_support/migration.sql b/packages/db/prisma/migrations/20240221104430_add_password_support/migration.sql deleted file mode 100644 index 4c9b7b00..00000000 --- a/packages/db/prisma/migrations/20240221104430_add_password_support/migration.sql +++ /dev/null @@ -1,2 +0,0 @@ --- AlterTable -ALTER TABLE "User" ADD COLUMN "password" TEXT; diff --git a/packages/db/prisma/migrations/20240222152033_name_and_email_required/migration.sql b/packages/db/prisma/migrations/20240222152033_name_and_email_required/migration.sql deleted file mode 100644 index fa73b56e..00000000 --- a/packages/db/prisma/migrations/20240222152033_name_and_email_required/migration.sql +++ /dev/null @@ -1,23 +0,0 @@ -/* - Warnings: - - - Made the column `email` on table `User` required. This step will fail if there are existing NULL values in that column. - - Made the column `name` on table `User` required. This step will fail if there are existing NULL values in that column. - -*/ --- RedefineTables -PRAGMA foreign_keys=OFF; -CREATE TABLE "new_User" ( - "id" TEXT NOT NULL PRIMARY KEY, - "name" TEXT NOT NULL, - "email" TEXT NOT NULL, - "emailVerified" DATETIME, - "password" TEXT, - "image" TEXT -); -INSERT INTO "new_User" ("email", "emailVerified", "id", "image", "name", "password") SELECT "email", "emailVerified", "id", "image", "name", "password" FROM "User"; -DROP TABLE "User"; -ALTER TABLE "new_User" RENAME TO "User"; -CREATE UNIQUE INDEX "User_email_key" ON "User"("email"); -PRAGMA foreign_key_check; -PRAGMA foreign_keys=ON; diff --git a/packages/db/prisma/migrations/migration_lock.toml b/packages/db/prisma/migrations/migration_lock.toml deleted file mode 100644 index e5e5c470..00000000 --- a/packages/db/prisma/migrations/migration_lock.toml +++ /dev/null @@ -1,3 +0,0 @@ -# Please do not edit this file manually -# It should be added in your version-control system (i.e. Git) -provider = "sqlite"
\ No newline at end of file diff --git a/packages/db/prisma/schema.prisma b/packages/db/prisma/schema.prisma deleted file mode 100644 index 3b6063a3..00000000 --- a/packages/db/prisma/schema.prisma +++ /dev/null @@ -1,129 +0,0 @@ -// This is your Prisma schema file, -// learn more about it in the docs: https://pris.ly/d/prisma-schema - -generator client { - provider = "prisma-client-js" -} - -datasource db { - provider = "sqlite" - url = env("DATABASE_URL") -} - -model Account { - id String @id @default(cuid()) - userId String - type String - provider String - providerAccountId String - refresh_token String? - access_token String? - expires_at Int? - token_type String? - scope String? - id_token String? - session_state String? - - user User @relation(fields: [userId], references: [id], onDelete: Cascade) - - @@unique([provider, providerAccountId]) -} - -model Session { - id String @id @default(cuid()) - sessionToken String @unique - userId String - expires DateTime - user User @relation(fields: [userId], references: [id], onDelete: Cascade) -} - -model User { - id String @id @default(cuid()) - name String - email String @unique - emailVerified DateTime? - password String? - image String? - accounts Account[] - sessions Session[] - tags BookmarkTags[] - bookmarks Bookmark[] - apiKeys ApiKey[] -} - -model VerificationToken { - identifier String - token String @unique - expires DateTime - - @@unique([identifier, token]) -} - -model ApiKey { - id String @id @default(cuid()) - name String - createdAt DateTime @default(now()) - keyId String @unique - keyHash String - userId String - user User @relation(fields: [userId], references: [id], onDelete: Cascade) - - @@unique([name, userId]) -} - -model Bookmark { - id String @id @default(cuid()) - createdAt DateTime @default(now()) - archived Boolean @default(false) - favourited Boolean @default(false) - userId String - - // Content relation - link BookmarkedLink? - - // Other relations - user User @relation(fields: [userId], references: [id], onDelete: Cascade) - tags TagsOnBookmarks[] -} - -model BookmarkedLink { - id String @id - url String - - // Crawled info - title String? - description String? - imageUrl String? - favicon String? - crawledAt DateTime? - - // Relations - parentBookmark Bookmark @relation(fields: [id], references: [id], onDelete: Cascade) -} - -model BookmarkTags { - id String @id @default(cuid()) - name String - createdAt DateTime @default(now()) - - userId String - - // Relations - user User @relation(fields: [userId], references: [id], onDelete: Cascade) - bookmarks TagsOnBookmarks[] - - @@unique([userId, name]) -} - -model TagsOnBookmarks { - bookmark Bookmark @relation(fields: [bookmarkId], references: [id], onDelete: Cascade) - bookmarkId String - - tag BookmarkTags @relation(fields: [tagId], references: [id], onDelete: Cascade) - tagId String - - attachedAt DateTime @default(now()) - attachedBy String // "human" or "ai" (if only prisma sqlite supported enums) - - @@unique([bookmarkId, tagId]) -} diff --git a/packages/db/schema.ts b/packages/db/schema.ts new file mode 100644 index 00000000..0a30cf59 --- /dev/null +++ b/packages/db/schema.ts @@ -0,0 +1,208 @@ +import { + integer, + sqliteTable, + text, + primaryKey, + unique, +} from "drizzle-orm/sqlite-core"; +import type { AdapterAccount } from "@auth/core/adapters"; +import { createId } from "@paralleldrive/cuid2"; +import { relations } from "drizzle-orm"; + +function createdAtField() { + return integer("createdAt", { mode: "timestamp" }) + .notNull() + .$defaultFn(() => new Date()); +} + +export const users = sqliteTable("user", { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + name: text("name").notNull(), + email: text("email").notNull().unique(), + emailVerified: integer("emailVerified", { mode: "timestamp_ms" }), + image: text("image"), + password: text("password"), +}); + +export const accounts = sqliteTable( + "account", + { + userId: text("userId") + .notNull() + .references(() => users.id, { onDelete: "cascade" }), + type: text("type").$type<AdapterAccount["type"]>().notNull(), + provider: text("provider").notNull(), + providerAccountId: text("providerAccountId").notNull(), + refresh_token: text("refresh_token"), + access_token: text("access_token"), + expires_at: integer("expires_at"), + token_type: text("token_type"), + scope: text("scope"), + id_token: text("id_token"), + session_state: text("session_state"), + }, + (account) => ({ + compoundKey: primaryKey({ + columns: [account.provider, account.providerAccountId], + }), + }), +); + +export const sessions = sqliteTable("session", { + sessionToken: text("sessionToken") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + userId: text("userId") + .notNull() + .references(() => users.id, { onDelete: "cascade" }), + expires: integer("expires", { mode: "timestamp_ms" }).notNull(), +}); + +export const verificationTokens = sqliteTable( + "verificationToken", + { + identifier: text("identifier").notNull(), + token: text("token").notNull(), + expires: integer("expires", { mode: "timestamp_ms" }).notNull(), + }, + (vt) => ({ + compoundKey: primaryKey({ columns: [vt.identifier, vt.token] }), + }), +); + +export const apiKeys = sqliteTable( + "apiKey", + { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + name: text("name").notNull().unique(), + createdAt: createdAtField(), + keyId: text("keyId").notNull().unique(), + keyHash: text("keyHash").notNull(), + userId: text("userId") + .notNull() + .references(() => users.id, { onDelete: "cascade" }), + }, + (ak) => ({ + unq: unique().on(ak.name, ak.userId), + }), +); + +export const bookmarks = sqliteTable("bookmarks", { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + createdAt: createdAtField(), + archived: integer("archived", { mode: "boolean" }).notNull().default(false), + favourited: integer("favourited", { mode: "boolean" }) + .notNull() + .default(false), + userId: text("userId") + .notNull() + .references(() => users.id, { onDelete: "cascade" }), +}); + +export const bookmarkLinks = sqliteTable("bookmarkLinks", { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()) + .references(() => bookmarks.id, { onDelete: "cascade" }), + url: text("url").notNull(), + + // Crawled info + title: text("title"), + description: text("description"), + imageUrl: text("imageUrl"), + favicon: text("favicon"), + crawledAt: integer("crawledAt", { mode: "timestamp" }), +}); + +export const bookmarkTags = sqliteTable( + "bookmarkTags", + { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + name: text("name").notNull(), + createdAt: createdAtField(), + userId: text("userId") + .notNull() + .references(() => users.id, { onDelete: "cascade" }), + }, + (bt) => ({ + uniq: unique().on(bt.userId, bt.name), + }), +); + +export const tagsOnBookmarks = sqliteTable( + "tagsOnBookmarks", + { + bookmarkId: text("bookmarkId") + .notNull() + .references(() => bookmarks.id, { onDelete: "cascade" }), + tagId: text("tagId") + .notNull() + .references(() => bookmarkTags.id, { onDelete: "cascade" }), + + attachedAt: integer("attachedAt", { mode: "timestamp" }).$defaultFn( + () => new Date(), + ), + attachedBy: text("attachedBy", { enum: ["ai", "human"] }), + }, + (tb) => ({ + pk: primaryKey({ columns: [tb.bookmarkId, tb.tagId] }), + }), +); + +// Relations + +export const userRelations = relations(users, ({ many }) => ({ + tags: many(bookmarkTags), + bookmarks: many(bookmarks), +})); + +export const bookmarkRelations = relations(bookmarks, ({ many, one }) => ({ + user: one(users, { + fields: [bookmarks.userId], + references: [users.id], + }), + link: one(bookmarkLinks, { + fields: [bookmarks.id], + references: [bookmarkLinks.id], + }), + tagsOnBookmarks: many(tagsOnBookmarks), +})); + +export const bookmarkTagsRelations = relations( + bookmarkTags, + ({ many, one }) => ({ + user: one(users, { + fields: [bookmarkTags.userId], + references: [users.id], + }), + tagsOnBookmarks: many(tagsOnBookmarks), + }), +); + +export const tagsOnBookmarksRelations = relations( + tagsOnBookmarks, + ({ one }) => ({ + tag: one(bookmarkTags, { + fields: [tagsOnBookmarks.tagId], + references: [bookmarkTags.id], + }), + bookmark: one(bookmarks, { + fields: [tagsOnBookmarks.bookmarkId], + references: [bookmarks.id], + }), + }), +); 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) { diff --git a/packages/workers/crawler.ts b/packages/workers/crawler.ts index a4d8d05c..bfb46218 100644 --- a/packages/workers/crawler.ts +++ b/packages/workers/crawler.ts @@ -10,7 +10,7 @@ import { import { Worker } from "bullmq"; import { Job } from "bullmq"; -import { prisma } from "@hoarder/db"; +import { db } from "@hoarder/db"; import { Browser } from "puppeteer"; import puppeteer from "puppeteer-extra"; @@ -28,6 +28,8 @@ import metascraperReadability from "metascraper-readability"; import { Mutex } from "async-mutex"; import assert from "assert"; import serverConfig from "@hoarder/shared/config"; +import { bookmarkLinks } from "@hoarder/db/schema"; +import { eq } from "drizzle-orm"; const metascraperParser = metascraper([ metascraperReadability(), @@ -91,8 +93,8 @@ export class CrawlerWorker { } async function getBookmarkUrl(bookmarkId: string) { - const bookmark = await prisma.bookmarkedLink.findUnique({ - where: { id: bookmarkId }, + const bookmark = await db.query.bookmarkLinks.findFirst({ + where: eq(bookmarkLinks.id, bookmarkId), }); if (!bookmark) { @@ -155,18 +157,16 @@ async function runCrawler(job: Job<ZCrawlLinkRequest, void>) { html: htmlContent, }); - await prisma.bookmarkedLink.update({ - where: { - id: bookmarkId, - }, - data: { + await db + .update(bookmarkLinks) + .set({ title: meta.title, description: meta.description, imageUrl: meta.image, favicon: meta.logo, crawledAt: new Date(), - }, - }); + }) + .where(eq(bookmarkLinks.id, bookmarkId)); // Enqueue openai job OpenAIQueue.add("openai", { diff --git a/packages/workers/index.ts b/packages/workers/index.ts index c021ec67..67be7af2 100644 --- a/packages/workers/index.ts +++ b/packages/workers/index.ts @@ -1,5 +1,4 @@ -import dotenv from "dotenv"; -dotenv.config(); +import "dotenv/config"; import { CrawlerWorker } from "./crawler"; import { OpenAiWorker } from "./openai"; diff --git a/packages/workers/openai.ts b/packages/workers/openai.ts index 8972eb66..ed4c72e8 100644 --- a/packages/workers/openai.ts +++ b/packages/workers/openai.ts @@ -1,4 +1,4 @@ -import { prisma, BookmarkedLink } from "@hoarder/db"; +import { db } from "@hoarder/db"; import logger from "@hoarder/shared/logger"; import serverConfig from "@hoarder/shared/config"; import { @@ -11,6 +11,13 @@ import { Job } from "bullmq"; import OpenAI from "openai"; import { z } from "zod"; import { Worker } from "bullmq"; +import { + bookmarkLinks, + bookmarkTags, + bookmarks, + tagsOnBookmarks, +} from "@hoarder/db/schema"; +import { eq } from "drizzle-orm"; const openAIResponseSchema = z.object({ tags: z.array(z.string()), @@ -57,17 +64,19 @@ Description: ${description} } async function fetchBookmark(linkId: string) { - return await prisma.bookmark.findUnique({ - where: { - id: linkId, - }, - include: { + return await db.query.bookmarks.findFirst({ + where: eq(bookmarks.id, linkId), + with: { link: true, }, }); } -async function inferTags(jobId: string, link: BookmarkedLink, openai: OpenAI) { +async function inferTags( + jobId: string, + link: typeof bookmarkLinks.$inferSelect, + openai: OpenAI, +) { const linkDescription = link?.description; if (!linkDescription) { throw new Error( @@ -111,51 +120,26 @@ async function inferTags(jobId: string, link: BookmarkedLink, openai: OpenAI) { } async function createTags(tags: string[], userId: string) { - const existingTags = await prisma.bookmarkTags.findMany({ - select: { - id: true, - name: true, - }, - where: { - userId, - name: { - in: tags, - }, - }, - }); - - const existingTagSet = new Set<string>(existingTags.map((t) => t.name)); - - const newTags = tags.filter((t) => !existingTagSet.has(t)); - - // TODO: Prisma doesn't support createMany in Sqlite - const newTagObjects = await Promise.all( - newTags.map((t) => { - return prisma.bookmarkTags.create({ - data: { - name: t, - userId: userId, - }, - }); - }), - ); - - return existingTags.map((t) => t.id).concat(newTagObjects.map((t) => t.id)); + const res = await db + .insert(bookmarkTags) + .values( + tags.map((t) => ({ + name: t, + userId, + })), + ) + .onConflictDoNothing() + .returning({ id: bookmarkTags.id }); + return res.map((r) => r.id); } async function connectTags(bookmarkId: string, tagIds: string[]) { - // TODO: Prisma doesn't support createMany in Sqlite - // TODO: This could fail on refetch if the tags are already there - await Promise.all( - tagIds.map((tagId) => { - return prisma.tagsOnBookmarks.create({ - data: { - tagId, - bookmarkId, - attachedBy: "ai", - }, - }); - }), + await db.insert(tagsOnBookmarks).values( + tagIds.map((tagId) => ({ + tagId, + bookmarkId, + attachedBy: "ai" as const, + })), ); } diff --git a/packages/workers/package.json b/packages/workers/package.json index 48510531..b2170441 100644 --- a/packages/workers/package.json +++ b/packages/workers/package.json @@ -10,6 +10,7 @@ "async-mutex": "^0.4.1", "bullmq": "^5.1.9", "dotenv": "^16.4.1", + "drizzle-orm": "^0.29.4", "metascraper": "^5.43.4", "metascraper-description": "^5.43.4", "metascraper-image": "^5.43.4", @@ -23,17 +24,16 @@ "puppeteer": "^22.0.0", "puppeteer-extra": "^3.3.6", "puppeteer-extra-plugin-stealth": "^2.11.2", - "ts-node": "^10.9.2", + "tsx": "^4.7.1", "typescript": "^5", "zod": "^3.22.4" }, "devDependencies": { - "@types/metascraper": "^5.14.3", - "nodemon": "^3.0.3" + "@types/metascraper": "^5.14.3" }, "scripts": { - "start": "nodemon index.ts", - "start:prod": "ts-node -T index.ts", + "start": "tsx watch index.ts", + "start:prod": "tsx index.ts", "typecheck": "tsc --noEmit" } } diff --git a/packages/workers/tsconfig.json b/packages/workers/tsconfig.json index 5ab467a9..cf49c407 100644 --- a/packages/workers/tsconfig.json +++ b/packages/workers/tsconfig.json @@ -2,5 +2,10 @@ "$schema": "https://json.schemastore.org/tsconfig", "extends": "@tsconfig/node21/tsconfig.json", "include": ["**/*.ts"], - "exclude": ["node_modules"] + "exclude": ["node_modules"], + "compilerOptions": { + "module": "ESNext", + "moduleResolution": "node", + "esModuleInterop": true + } } |