From 328710be69d218007477fc46f6642a71ac0e385f Mon Sep 17 00:00:00 2001 From: nsfisis Date: Wed, 3 Dec 2025 05:06:00 +0900 Subject: feat(db): add Drizzle ORM setup MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add Drizzle ORM with PostgreSQL for database access. Includes: - Schema definitions for users, decks, cards, and review_logs tables - Database connection with node-postgres driver - Drizzle Kit configuration for migrations - npm scripts for db:generate, db:migrate, db:push, db:studio 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude --- pkgs/server/drizzle.config.ts | 15 ++++++ pkgs/server/package.json | 12 ++++- pkgs/server/src/db/index.ts | 12 +++++ pkgs/server/src/db/schema.ts | 104 ++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 141 insertions(+), 2 deletions(-) create mode 100644 pkgs/server/drizzle.config.ts create mode 100644 pkgs/server/src/db/index.ts create mode 100644 pkgs/server/src/db/schema.ts (limited to 'pkgs') diff --git a/pkgs/server/drizzle.config.ts b/pkgs/server/drizzle.config.ts new file mode 100644 index 0000000..0e4596b --- /dev/null +++ b/pkgs/server/drizzle.config.ts @@ -0,0 +1,15 @@ +import { defineConfig } from "drizzle-kit"; + +const databaseUrl = process.env.DATABASE_URL; +if (!databaseUrl) { + throw new Error("DATABASE_URL environment variable is not set"); +} + +export default defineConfig({ + out: "./drizzle", + schema: "./src/db/schema.ts", + dialect: "postgresql", + dbCredentials: { + url: databaseUrl, + }, +}); diff --git a/pkgs/server/package.json b/pkgs/server/package.json index 7a3169b..8416a55 100644 --- a/pkgs/server/package.json +++ b/pkgs/server/package.json @@ -8,7 +8,11 @@ "build": "tsc", "start": "node dist/index.js", "test": "vitest run", - "test:watch": "vitest" + "test:watch": "vitest", + "db:generate": "drizzle-kit generate", + "db:migrate": "drizzle-kit migrate", + "db:push": "drizzle-kit push", + "db:studio": "drizzle-kit studio" }, "author": "nsfisis", "license": "MIT", @@ -16,10 +20,14 @@ "type": "module", "dependencies": { "@hono/node-server": "^1.19.6", - "hono": "^4.10.7" + "drizzle-orm": "^0.44.7", + "hono": "^4.10.7", + "pg": "^8.16.3" }, "devDependencies": { "@types/node": "^24.10.1", + "@types/pg": "^8.15.6", + "drizzle-kit": "^0.31.7", "vitest": "^4.0.14" } } diff --git a/pkgs/server/src/db/index.ts b/pkgs/server/src/db/index.ts new file mode 100644 index 0000000..6730947 --- /dev/null +++ b/pkgs/server/src/db/index.ts @@ -0,0 +1,12 @@ +import { drizzle } from "drizzle-orm/node-postgres"; +import * as schema from "./schema"; + +const databaseUrl = process.env.DATABASE_URL; + +if (!databaseUrl) { + throw new Error("DATABASE_URL environment variable is not set"); +} + +export const db = drizzle(databaseUrl, { schema }); + +export * from "./schema"; diff --git a/pkgs/server/src/db/schema.ts b/pkgs/server/src/db/schema.ts new file mode 100644 index 0000000..23f19d1 --- /dev/null +++ b/pkgs/server/src/db/schema.ts @@ -0,0 +1,104 @@ +import { + integer, + pgTable, + real, + smallint, + text, + timestamp, + uuid, + varchar, +} from "drizzle-orm/pg-core"; + +// Card states for FSRS algorithm +export const CardState = { + New: 0, + Learning: 1, + Review: 2, + Relearning: 3, +} as const; + +// Rating values for reviews +export const Rating = { + Again: 1, + Hard: 2, + Good: 3, + Easy: 4, +} as const; + +export const users = pgTable("users", { + id: uuid("id").primaryKey().defaultRandom(), + username: varchar("username", { length: 255 }).notNull().unique(), + passwordHash: varchar("password_hash", { length: 255 }).notNull(), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), +}); + +export const decks = pgTable("decks", { + id: uuid("id").primaryKey().defaultRandom(), + userId: uuid("user_id") + .notNull() + .references(() => users.id), + name: varchar("name", { length: 255 }).notNull(), + description: text("description"), + newCardsPerDay: integer("new_cards_per_day").notNull().default(20), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + deletedAt: timestamp("deleted_at", { withTimezone: true }), + syncVersion: integer("sync_version").notNull().default(0), +}); + +export const cards = pgTable("cards", { + id: uuid("id").primaryKey().defaultRandom(), + deckId: uuid("deck_id") + .notNull() + .references(() => decks.id), + front: text("front").notNull(), + back: text("back").notNull(), + + // FSRS fields + state: smallint("state").notNull().default(CardState.New), + due: timestamp("due", { withTimezone: true }).notNull().defaultNow(), + stability: real("stability").notNull().default(0), + difficulty: real("difficulty").notNull().default(0), + elapsedDays: integer("elapsed_days").notNull().default(0), + scheduledDays: integer("scheduled_days").notNull().default(0), + reps: integer("reps").notNull().default(0), + lapses: integer("lapses").notNull().default(0), + lastReview: timestamp("last_review", { withTimezone: true }), + + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + deletedAt: timestamp("deleted_at", { withTimezone: true }), + syncVersion: integer("sync_version").notNull().default(0), +}); + +export const reviewLogs = pgTable("review_logs", { + id: uuid("id").primaryKey().defaultRandom(), + cardId: uuid("card_id") + .notNull() + .references(() => cards.id), + userId: uuid("user_id") + .notNull() + .references(() => users.id), + rating: smallint("rating").notNull(), + state: smallint("state").notNull(), + scheduledDays: integer("scheduled_days").notNull(), + elapsedDays: integer("elapsed_days").notNull(), + reviewedAt: timestamp("reviewed_at", { withTimezone: true }) + .notNull() + .defaultNow(), + durationMs: integer("duration_ms"), + syncVersion: integer("sync_version").notNull().default(0), +}); -- cgit v1.2.3-70-g09d2