Back to DocumentationDatabase
Database Management

Database System

Complete guide to Prisma ORM, PostgreSQL with Neon, migrations, and database management in NextReady.

Type-safe queries
Auto-migrations
Connection pooling

Database Stack

Neon PostgreSQL
Serverless PostgreSQL with branching and auto-scaling
  • Serverless architecture
  • Database branching for development
  • Connection pooling built-in
  • Automatic backups and scaling
Prisma ORM
Type-safe database client with powerful query capabilities
  • Full TypeScript integration
  • Schema-first development
  • Auto-generated client
  • Advanced query optimization
Migration System
Version-controlled schema changes with rollback support
  • Automated migrations
  • Development & production modes
  • Schema version control
  • Safe rollback procedures
Development Tools
Comprehensive tooling for database management
  • Prisma Studio GUI
  • Database seeding
  • Backup & restore utilities
  • Performance monitoring

Schema & Setup

Complete Database Schema
Full schema definition with all NextReady models and relationships
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // Used for migrations
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      UserRole @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime? // Soft delete support
  
  // Relations
  accounts Account[]
  sessions Session[]
  files    File[]
  
  @@map("users")
}

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])
  @@map("accounts")
}

model File {
  id        String   @id @default(cuid())
  name      String
  size      Int
  type      String
  url       String
  key       String   @unique
  userId    String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("files")
}

model EmailQueue {
  id          String      @id @default(cuid())
  to          String[]
  subject     String
  template    String
  data        Json?
  status      EmailStatus @default(PENDING)
  attempts    Int         @default(0)
  maxAttempts Int         @default(3)
  scheduledAt DateTime?
  sentAt      DateTime?
  failedAt    DateTime?
  error       String?
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  @@map("email_queue")
}

enum UserRole {
  USER
  ADMIN
  SUPER_ADMIN
}

enum EmailStatus {
  PENDING
  SENT
  FAILED
  CANCELLED
}

Development Workflow

Development Mode
Rapid prototyping with schema pushing
# Quick schema changes
npm run db:push        # Push changes instantly
npm run db:studio      # Browse data visually
npm run db:seed        # Add sample data
Production Mode
Version-controlled migrations for production
# Production-safe migrations
npm run db:migrate     # Generate migration files
npm run db:migrate:prod # Deploy to production
Available Commands
Comprehensive database management commands
npm run db:setup

Complete database setup with seeding

First-time setup or reset

npm run db:push

Push schema changes to development database

Development workflow

npm run db:migrate

Generate and apply migrations

Production deployments

npm run db:studio

Open Prisma Studio GUI

Database browsing and editing

npm run db:seed

Run database seeding scripts

Populate with sample data

npm run db:reset

Reset database (destructive)

Start fresh (development only)

Database Seeding

Seeding Script
Populate your database with sample data for development
// scripts/seed.ts
import { PrismaClient, UserRole } from "@prisma/client";
import bcrypt from "bcryptjs";

const prisma = new PrismaClient();

async function main() {
  console.log("🌱 Starting database seeding...");

  // Create admin user
  const adminPassword = await bcrypt.hash("admin123", 12);
  const admin = await prisma.user.upsert({
    where: { email: "admin@nextready.com" },
    update: {},
    create: {
      email: "admin@nextready.com",
      name: "Admin User",
      role: UserRole.ADMIN,
      password: adminPassword,
    },
  });

  // Create sample users
  const sampleUsers = await Promise.all([
    prisma.user.create({
      data: {
        email: "user1@example.com",
        name: "John Doe",
        role: UserRole.USER,
      },
    }),
    prisma.user.create({
      data: {
        email: "user2@example.com", 
        name: "Jane Smith",
        role: UserRole.USER,
      },
    }),
  ]);

  // Create sample files
  await prisma.file.createMany({
    data: [
      {
        name: "sample-document.pdf",
        size: 1024000,
        type: "application/pdf",
        url: "https://example.com/sample.pdf",
        key: "uploads/sample-document.pdf",
        userId: sampleUsers[0].id,
      },
      {
        name: "profile-picture.jpg",
        size: 512000,
        type: "image/jpeg", 
        url: "https://example.com/profile.jpg",
        key: "uploads/profile-picture.jpg",
        userId: sampleUsers[1].id,
      },
    ],
  });

  console.log("✅ Database seeded successfully!");
  console.log(`Created admin user: ${admin.email}`);
  console.log(`Created ${sampleUsers.length} sample users`);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Best Practices

Performance & Monitoring

Query Optimization
Tips for efficient database queries
  • Use select to fetch only needed fields
  • Implement proper database indexes
  • Use pagination for large datasets
  • Batch database operations when possible
Monitoring Tools
Track database performance
  • Neon dashboard for connection monitoring
  • Prisma query logging in development
  • Built-in Vercel analytics integration
  • Custom metrics via API routes

🗄️ Database Ready!

Your database system is configured and ready. Next, learn about file management and storage systems.

File ManagementEmail System