Database (Prisma)

This project uses Prisma as the Object-Relational Mapper (ORM) to interact with the PostgreSQL database.

Prerequisites & Setup

1. Install PostgreSQL: You need a running PostgreSQL server on your local machine for development. If you don't have one:

   • Recommendation (macOS/Windows): A very convenient way to install and manage PostgreSQL (and other databases like Redis) is using DBngin. It's a free, native application that requires no dependencies like Docker.
   • Alternatives: You can also use Docker, Homebrew (macOS), or the official PostgreSQL installers.

2. Configure Database URL: Once PostgreSQL is running, set the DATABASE_URL environment variable in your .env file to point to your local database instance. Make sure the user and database name exist. (See Configuration section below for format).

3. Run Initial Migration: After setting the DATABASE_URL, run the initial migration script to create the necessary tables in your database:

   npm run db:migrate
   # or yarn db:migrate / pnpm db:migrate

   This command will create the database if it doesn't exist (based on the URL) and apply all migrations found in prisma/migrations.

---

Configuration

• Database URL: The connection string for your PostgreSQL database must be set in the DATABASE_URL environment variable.
  • Example: DATABASE_URL="postgresql://postgres@localhost:5432/your-db-name"
  • ( Ensure the user, host, port, and database name match your local setup. The user typically needs permission to create databases for the first migration run.)
• Direct URL (for Supabase): If you are using Supabase, you might also need to set the DIRECT_URL environment variable. This is often required for tools like Prisma Migrate to bypass connection poolers (like PgBouncer) and connect directly to the database. Uncomment the directUrl line in prisma/schema.prisma and set the corresponding environment variable in your .env file if needed.
  • Example: DIRECT_URL="postgresql://postgres:[YOUR-PASSWORD]@[YOUR-HOST]:5432/[YOUR-DB-NAME]"

Schema

• The database schema, defining models, relations, and fields, is located at prisma/schema.prisma.
• Whenever you modify this file (e.g., add a model, change a field), you need to apply these changes to your database using migrations

Migrations

Prisma uses a migration system to manage database schema changes safely.

• Making Changes: Edit the prisma/schema.prisma file to reflect your desired database structure.

• Applying Changes: Run the migration script defined in package.json:

  npm run db:migrate

  • This script typically executes prisma migrate dev, which does the following:
    1. Compares your schema.prisma to the database state.
    2. Generates SQL migration files in prisma/migrations/.
    3. Applies the generated SQL to your development database.
    4. Ensures the Prisma Client is updated (runs prisma generate).

• IMPORTANT: Do not use prisma db push for schema changes in development or production. Always use the migration workflow (npm run db:migrate) to ensure proper versioning and safety.

Prisma Client

• Generation: Prisma Client is a type-safe database client automatically generated based on your schema.prisma. It's generated whenever you run npm run db:migrate or manually via:

  npm run db:generate

  (This script usually runs prisma generate). The generated client is outputted to src/generated/prisma as configured in schema.prisma.

• Initialization: The client is initialized in src/server/db/index.ts. This file handles creating the client instance and uses a global singleton pattern in development to prevent issues with Hot Module Replacement (HMR) creating too many connections.

• Extensions: This file (src/server/db/index.ts) also applies Prisma Client Extensions (like userExtension from ./extensions/user). Extensions allow adding custom computed fields or methods to your models.

Usage in tRPC

• The initialized Prisma Client instance (db) is added to the tRPC context in src/server/api/trpc.ts during createTRPCContext.

• This makes the client accessible within any tRPC procedure (queries, mutations) via ctx.db.

  // Example usage in a tRPC router (e.g., src/server/api/routers/user.ts)
  import { createTRPCRouter, protectedProcedure } from "../trpc";
   
  export const userRouter = createTRPCRouter({
    getUserProfile: protectedProcedure.query(async ({ ctx }) => {
      const user = await ctx.db.user.findUnique({
        where: { id: ctx.session.user.id },
      });
      return user;
    }),
  });

Prisma Studio

Prisma provides a GUI tool, Prisma Studio, to view and interact with your database data.

• Run the following script from package.json:

  npm run db:studio
  # or yarn db:studio / pnpm db:studio

• This typically runs prisma studio and opens a web interface in your browser.