Implement Prisma ORM with Next.js, Next-Auth & Neon Postgres

[jonulak]

Summary

Set up Prisma ORM as the database layer for our Next.js application, integrating with Next-Auth for authentication and Neon Postgres as the database provider. This will provide type-safe database operations, automated migrations, and seamless authentication.

Problem Statement

Currently, our Next.js application lacks:

• Type-safe database operations: Manual SQL queries prone to runtime errors
• Database schema management: No structured approach to migrations
• Authentication persistence: Need secure user session management
• Developer experience: No auto-completion or compile-time validation for database queries

Proposed Solution

Implement a complete Prisma stack with:

• Prisma Client: Type-safe database queries
• Prisma Schema: Single source of truth for database structure
• Prisma Migrate: Automated database migrations
• Next-Auth: Authentication with Prisma adapter
• Neon Postgres: Serverless Postgres database

Architecture Overview

Next.js App
├── Prisma Client (Type-safe queries)
├── Next-Auth (Authentication)
│   └── Prisma Adapter (Session storage)
└── Neon Postgres (Database)
    └── Connection Pooling

Implementation Steps

Phase 1: Prisma Setup & Configuration

1.1 Install Dependencies

• Install core Prisma packages:

  npm install prisma @prisma/client
  npm install -D prisma

• Install Next-Auth and Prisma adapter:

  npm install next-auth @next-auth/prisma-adapter

• Install additional auth providers (if needed):

  npm install @auth/google-provider @auth/github-provider

1.2 Initialize Prisma

• Initialize Prisma in the project:

  npx prisma init

• Configure environment variables for Neon connection
• Set up Prisma schema file structure

Phase 2: Database Schema Design

2.1 Core Next-Auth Schema

• Implement required Next-Auth tables:

  model Account {
    id                String  @id @default(cuid())
    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?
    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?
    image         String?
    accounts      Account[]
    sessions      Session[]
    // Add custom user fields here
    createdAt     DateTime  @default(now())
    updatedAt     DateTime  @updatedAt
  }
  
  model VerificationToken {
    identifier String
    token      String   @unique
    expires    DateTime
    @@unique([identifier, token])
  }

Phase 3: Environment & Configuration

3.1 Environment Variables Setup

• Configure .env.local:

  # Database
  DATABASE_URL="postgresql://username:password@host:port/database?sslmode=require"
  DATABASE_URL_UNPOOLED="postgresql://username:password@host:port/database?sslmode=require"
  
  # Next-Auth
  NEXTAUTH_URL="http://localhost:3000"
  NEXTAUTH_SECRET="your-secret-key"
  
  # OAuth Providers (if using)
  GOOGLE_CLIENT_ID="your-google-client-id"
  GOOGLE_CLIENT_SECRET="your-google-client-secret"
  GITHUB_ID="your-github-id"
  GITHUB_SECRET="your-github-secret"

3.2 Prisma Configuration

• Configure prisma/schema.prisma:

  generator client {
    provider = "prisma-client-js"
  }
  
  datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
    directUrl = env("DATABASE_URL_UNPOOLED")
  }

Phase 4: Next-Auth Integration

4.1 Next-Auth Configuration

• Create pages/api/auth/[...nextauth].js or app/api/auth/[...nextauth]/route.js:

  import NextAuth from 'next-auth'
  import { PrismaAdapter } from '@next-auth/prisma-adapter'
  import { prisma } from '@/lib/prisma'
  import GoogleProvider from 'next-auth/providers/google'
  import GitHubProvider from 'next-auth/providers/github'
  
  export default NextAuth({
    adapter: PrismaAdapter(prisma),
    providers: [
      GoogleProvider({
        clientId: process.env.GOOGLE_CLIENT_ID!,
        clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
      }),
      GitHubProvider({
        clientId: process.env.GITHUB_ID!,
        clientSecret: process.env.GITHUB_SECRET!,
      }),
    ],
    session: {
      strategy: 'database',
    },
    callbacks: {
      session: async ({ session, user }) => {
        if (session?.user) {
          session.user.id = user.id;
        }
        return session;
      },
    },
  })

4.2 Prisma Client Setup

• Create lib/prisma.js:

  import { PrismaClient } from '@prisma/client'
  
  const globalForPrisma = globalThis as unknown as {
    prisma: PrismaClient | undefined
  }
  
  export const prisma = globalForPrisma.prisma ?? new PrismaClient()
  
  if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Phase 5: Database Migration & Deployment

5.1 Initial Migration

• Generate and apply initial migration:

  npx prisma migrate dev --name init

• Generate Prisma Client:

  npx prisma generate

Phase 7: Development Tools & Scripts

7.1 TypeScript Configuration

• Ensure TypeScript is configured for Prisma types:

  // tsconfig.json
  {
    "compilerOptions": {
      "types": ["@types/node"]
    }
  }

Testing Strategy

Unit Tests

• Test Prisma queries in isolation
• Mock Prisma Client for component tests
• Test authentication flows

Integration Tests

• Test API routes with database operations
• Test Next-Auth authentication flow
• Test database migrations

Example Test Setup

// __tests__/setup.js
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.TEST_DATABASE_URL,
    },
  },
})

beforeEach(async () => {
  await prisma.$executeRaw`TRUNCATE TABLE "User" RESTART IDENTITY CASCADE`
})

afterAll(async () => {
  await prisma.$disconnect()
})

Performance Considerations

Connection Pooling

• Configure connection pooling for Neon:

  datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")        // Pooled connection
    directUrl = env("DATABASE_URL_UNPOOLED") // Direct connection for migrations
  }

Query Optimization

• Implement proper indexing in schema
• Use select to limit returned fields
• Implement pagination for large datasets
• Use include vs select appropriately

Security Considerations

• Validate all input data before database operations
• Use Prisma's built-in SQL injection protection
• Implement proper authorization checks
• Sanitize user inputs in API routes
• Use environment variables for sensitive data

Documentation & Training

• Document database schema and relationships
• Create development guidelines for Prisma usage
• Document authentication flow
• Create troubleshooting guide

Deployment Considerations

Production Setup

• Configure production environment variables
• Set up automated migrations in CI/CD
• Configure connection pooling for production
• Set up database monitoring

Vercel Integration

• Configure build commands:

  {
    "build": "prisma generate && next build",
    "vercel-build": "prisma generate && prisma migrate deploy && next build"
  }

Monitoring & Maintenance

• Set up Prisma query logging
• Monitor database performance
• Regular database backups
• Monitor authentication metrics

Resources

• [Prisma Documentation](https://www.prisma.io/docs)
• [Next.js with Prisma Guide](https://www.prisma.io/nextjs)
• [Next-Auth Prisma Adapter](https://next-auth.js.org/adapters/prisma)
• [Neon with Prisma](https://neon.tech/docs/guides/prisma)
• [Prisma Best Practices](https://www.prisma.io/docs/guides/performance-and-optimization)

Acceptance Criteria

• Prisma Client successfully connects to Neon Postgres
• Database schema includes all Next-Auth required tables
• Authentication flow working with Prisma adapter
• Type-safe database operations throughout the application
• Migrations working in development and production
• API routes using Prisma for data operations
• Proper error handling and validation
• Tests passing for authentication and database operations

Breaking Changes

⚠️ Note: This implementation will require:

• Database migration from existing setup (if any)
• Updates to existing API routes
• Changes to authentication flow
• Environment variable updates

[jeromehardaway]

Hey @jonulak , why use Prism and Neon?

[jonulak]

@jeromehardaway Prisma will allow defining the database model within the codebase and allow for automated database schema migrations when setup with vercel. This would make it easier to add features which require data persistence in the future. For example: requiring login for application submission to allow users to link a github repo to an application/make changes to an application/track application status, converting blog post data to be stored in neon so new posts can be added without having to merge a PR, etc.

[jeromehardaway]

@jonulak Just to confirm—we’re only planning to use Neon for functionality after users are logged in. For anything client-side, like the blog, we’re sticking with static generation using MDX for now.