PostgreSQL

By default, NextSaasPilot uses MongoDB as its database. However, if you prefer PostgreSQL, you can easily switch to it and manage your database with Prisma. This guide explains how to use PostgreSQL with NextSaasPilot.

Quick Start with PostgreSQL Branch

NextSaasPilot provides a dedicated PostgreSQL branch for quick setup. You can checkout this branch to get started with PostgreSQL immediately

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

Edit your prisma/schema.prisma file so that the datasource provider is set to postgresql. Make sure your models are using types and settings that work well with PostgreSQL.

prisma/schema.prisma

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

generator client {
    provider = "prisma-client-js"
}

model User {
    id            String    @id @default(uuid())
    name          String?
    email         String?   @unique
    emailVerified DateTime?
    password      String?
    image         String?
    accounts      Account[]
    customerId    String?
    priceId       String?
    isAdmin       Boolean   @default(false)
    hasAccess     Boolean   @default(false)
    subscribedAt  DateTime?
    createdAt     DateTime  @default(now())
    updatedAt     DateTime  @updatedAt

    @@map("users")
}

model Account {
    id                String   @id @default(uuid())
    userId            String
    type              String
    provider          String
    providerAccountId String
    refresh_token     String?  @db.Text
    access_token      String?  @db.Text
    expires_at        Int?
    token_type        String?
    scope             String?
    id_token          String?  @db.Text
    session_state     String?
    createdAt         DateTime @default(now())
    updatedAt         DateTime @updatedAt
    user              User     @relation(fields: [userId], references: [id], onDelete: Cascade)

    @@unique([provider, providerAccountId])
    @@map("accounts")
}

model VerificationToken {
    id         String   @id @default(uuid())
    identifier String
    token      String
    expires    DateTime

    @@unique([identifier, token])
    @@map("verification_tokens")
}

model PasswordResetToken {
    id        String   @id @default(uuid())
    email     String
    token     String   @unique
    expires   DateTime
    createdAt DateTime @default(now())

    @@map("password_reset_tokens")
}

2. Configure Your MongoDB Connection (.env)

• Update the DATABASE_URL in your .env file to use your PostgreSQL connection string.

• Replace placeholders with your actual PostgreSQL connection details.

# Example PostgreSQL connection string
DATABASE_URL="postgresql://[USER]:[PASSWORD]@[HOST]:[PORT]/[DATABASE]"

# Example using Supabase
# DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

3. Update package.json file

When moving from MongoDB to PostgreSQL, update your package.json database scripts. The main difference is that you'll use db:migrate instead of db:push to manage schema changes.

package.json

{
  "scripts": {
    // ... other scripts

    "postinstall": "prisma generate",
    "db:migrate": "prisma migrate dev",
    "db:reset": "prisma migrate reset",
    "db:generate": "prisma generate",
    "db:studio": "prisma studio",
    "db:deploy": "prisma migrate deploy"

    // ... other scripts
  }
}

Here’s what each command does:

• postinstall: Runs prisma generate automatically after dependencies are installed. This keeps your Prisma Client up to date with changes in your Prisma schema.

• db:migrate: Runs prisma migrate dev. This is the main command for creating and applying migrations during development; it keeps your database in sync with your defined schema by generating migration files and applying them.

• db:reset: Runs prisma migrate reset. Completely resets your development database: drops it, runs all migrations from scratch, and optionally runs seeds if configured. Use with caution, as this operation deletes all existing data.

• db:generate: Runs prisma generate. (You can use this independently to regenerate the Prisma Client code after changes to your schema.)

• db:studio: Opens Prisma Studio, a web UI for safely viewing and editing your database content.

• db:deploy: Runs prisma migrate deploy. This command applies all migrations to a production or staging database environment, ensuring that the deployed schema is up to date.

4. Typical PostgreSQL Workflow

1. Define Models: Modify your prisma/schema.prisma file.
2. Create & Apply Migration: Run npm run db:migrate to generate and apply a migration.
3. Develop: Start your application (npm run dev).
4. Inspect Data: Use npm run db:studio for a GUI view of your database.
5. Deploy: Run npm run db:deploy (maps to prisma migrate deploy) during deployment to apply all migrations to your production or staging database.