PostgreSQL

While StarterKitPro defaults to MongoDB, you can easily switch to using a PostgreSQL database with Prisma.

1. Update Prisma Schema (prisma/schema.prisma)

Modify your prisma/schema.prisma file entirely. Change the datasource provider to postgresql and update your models to use PostgreSQL-compatible types and conventions (like auto-incrementing integer IDs).

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
generator client {
  provider = "prisma-client-js"
}
 
model User {
  id             Int       @id @default(autoincrement())
  name           String?
  hashedPassword String?
  email          String    @unique
  emailVerified  DateTime? @map("email_verified")
  image          String?
 
  customerId     String?   @map("customer_id")
  priceId        String?   @map("price_id")
  hasAccess      Boolean   @default(false) @map("has_access")
 
  createdAt      DateTime  @default(now()) @map("created_at")
  updatedAt      DateTime  @updatedAt @map("updated_at")
 
  accounts Account[]
 
  @@map("users")
}
 
model Account {
  id                Int     @id @default(autoincrement())
  type              String
  provider          String
  providerAccountId String
  token_type        String?
  refresh_token     String? @db.Text // Use Text for potentially long tokens
  access_token      String? @db.Text
  id_token          String? @db.Text
  scope             String?
  session_state     String?
  expires_at        Int?
 
  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId Int  @map("user_id")
 
  @@unique([provider, providerAccountId], map: "accounts_provider_provider_account_id_key") // Custom index name
  @@map("accounts")
}
 
model VerificationToken {
  id         Int      @id @default(autoincrement())
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @updatedAt @map("updated_at")
 
  @@unique([identifier, token], map: "verification_tokens_identifier_token_key")
  @@map("verification_tokens")
}

2. Configure Database URL (.env.local)

Update the DATABASE_URL in your .env.local file with your PostgreSQL connection string.

# Example PostgreSQL connection string
DATABASE_URL="postgresql://<user>:<password>@<host>:<port>/<database>?schema=<schema>"
 
# Example using Supabase
# DATABASE_URL="postgresql://postgres:<password>@db.<ref-id>.supabase.co:5432/postgres"

• Replace placeholders with your actual PostgreSQL connection details.

3. Install PostgreSQL Driver

Ensure the pg driver is installed:

npm install pg
# or
yarn add pg

4. Update package.json Scripts

When switching from MongoDB to PostgreSQL, you need to adjust your database management scripts in package.json. The core change is shifting from db:push to db:migrate for schema changes.

Recommended Scripts for PostgreSQL:

{
  "scripts": {
    // ... other scripts
 
    "postinstall": "fumadocs-mdx && prisma generate", // Ensure Prisma Client is generated after install
    "db:migrate": "prisma migrate dev", // Create and apply migrations
    "db:reset": "prisma migrate reset", // Reset DB using migrations
    "db:seed": "prisma db seed", // Run seed script
    "db:generate": "prisma generate",
    "db:studio": "prisma studio", // Open Prisma Studio GUI
    "db:deploy": "prisma migrate deploy" // Deploy migrations to production/staging
 
    // Consider removing or commenting out specific scripts becuse with these you lost track of migration and do rollback may be not possible:
    // "db:push": "prisma db push",
    // "db:pull:schema": "prisma db pull"
 
    // ... other scripts
  }
}

5. Database Management & Migrations

Managing PostgreSQL schemas with Prisma revolves around migrations.

Script Explanations:

• npm run postinstall: Runs prisma generate automatically after npm install. This ensures your Prisma Client is always up-to-date with your schema.prisma.

• npm run db:migrate (mapped to prisma migrate dev):

  • The primary command for development.
  • Compares your schema.prisma to the database state recorded by previous migrations.
  • Generates a new SQL migration file in prisma/migrations reflecting the changes.
  • Applies the new migration to your development database.
  • Prompts for a migration name.

• npm run db:push (mapped to prisma db push):

  • Use Sparingly/Avoid with PostgreSQL.
  • Directly syncs your schema.prisma with the database, bypassing the migration history.
  • Can be useful for rapid prototyping before establishing a migration history, but generally discouraged for PostgreSQL.

• npm run db:reset (mapped to prisma migrate reset):

  • Use with caution!
  • Drops the database.
  • Re-runs all existing migration files from the prisma/migrations folder in order.
  • Optionally runs the seed script if configured.
  • Essential for resetting the development database to a clean state based on the migration history.

• npm run db:pull (mapped to prisma db pull):

  • Introspects the database and updates your schema.prisma to match.
  • Generally not recommended when using migrations, as schema.prisma should be the source of truth driving migrations.

• npm run db:generate (mapped to prisma generate):

  • Generates the Prisma Client based on your schema. This needs to be run after any changes to your schema.prisma file to update the TypeScript types and database access methods available in your application.

6. Typical PostgreSQL Workflow

1. Define/Modify Models: Edit prisma/schema.prisma.
2. Create Migration: Run npm run db:migrate to generate and apply a migration file.
3. (Optional) Seed Data: Run npm run db:seed if needed.
4. Develop: Start your application (npm run dev).
5. (Optional) Inspect Data: Use npm run db:studio.
6. Deployment: Use prisma migrate deploy (potentially via an npm run db:deploy script) in your deployment process to apply migrations to production/staging databases.