diff options
Diffstat (limited to 'packages/db')
20 files changed, 909 insertions, 400 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], + }), + }), +); |