SUPABASE_SERVICE_ROLE_KEY

• In the same API page
• Copy service_role key (⚠️ Secret, never expose to frontend)
• Paste into .env:

  SUPABASE_SERVICE_ROLE_KEY=eyJhbGc...
  

DATABASE_URL & DIRECT_URL

Step 1: Create dedicated Prisma user:

Go to SQL Editor in Supabase and run:

create user "prisma" with password 'YOUR_SECURE_PASSWORD' bypassrls createdb;
grant "prisma" to "postgres";
grant usage, create on schema public to prisma;
grant all on all tables in schema public to prisma;
grant all on all routines in schema public to prisma;
grant all on all sequences in schema public to prisma;
alter default privileges for role postgres in schema public grant all on tables to prisma;
alter default privileges for role postgres in schema public grant all on routines to prisma;
alter default privileges for role postgres in schema public grant all on sequences to prisma;

Step 2: Get connection strings:

• Go to Project Settings → Database → Connection string
• Select Session mode (NOT Transaction mode)
• Copy the URI shown (it uses the pooler host on port 5432)

Step 3: Format the connection strings:

The format should be:

postgresql://prisma.PROJECT_REF:YOUR_PASSWORD@aws-1-REGION.pooler.supabase.com:5432/postgres?sslmode=require

⚠️ Important: If your password contains special characters like @, !, or #, URL-encode them:

• @ → %40
• ! → %21
• # → %23

Example:

# If password is: my@pass!123
# Encoded becomes: my%40pass%2121

DATABASE_URL=postgresql://prisma.abcdefgh:my%40pass%2121@aws-1-ap-northeast-1.pooler.supabase.com:5432/postgres?sslmode=require
DIRECT_URL=postgresql://prisma.abcdefgh:my%40pass%2121@aws-1-ap-northeast-1.pooler.supabase.com:5432/postgres?sslmode=require

NODE_ENV (optional)

• Set to development or production
• Defaults to development

PORT (optional)

• Server port (default: 3000)

4. Initialize Database

Generate Prisma client and push schema to Supabase:

npm run prisma:generate
npm run prisma:push

This creates the Profile table in your database.

5. Start Development Server

npm run dev

Server runs at http://localhost:3000 🎉

📁 Project Structure

node-supabase-prisma/
├── prisma/
│   └── schema.prisma          # Database schema (tables, relations)
├── src/
│   ├── config/
│   │   ├── env.ts             # Environment validation with Zod
│   │   └── supabase.ts        # Supabase client singleton
│   ├── controllers/
│   │   └── auth.controller.ts # Request handlers (HTTP layer)
│   ├── middleware/
│   │   ├── auth.middleware.ts       # JWT authentication
│   │   ├── error.middleware.ts      # Centralized error handling
│   │   ├── rateLimiter.middleware.ts # Rate limiting config
│   │   └── validate.middleware.ts   # Zod schema validation
│   ├── routes/
│   │   ├── auth.routes.ts     # Auth endpoints
│   │   ├── health.routes.ts   # Health check endpoints
│   │   └── index.ts           # Route aggregator
│   ├── services/
│   │   └── auth.service.ts    # Business logic layer
│   ├── types/
│   │   └── index.ts           # Custom types & error classes
│   ├── validators/
│   │   └── auth.validator.ts  # Zod schemas for validation
│   ├── app.ts                 # Express app setup
│   ├── index.ts               # Server entry point
│   └── prisma.ts              # Prisma client instance
├── .env                       # Environment variables (git-ignored)
├── .gitignore                 # Files to ignore in git
├── example.env                # Environment template
├── package.json               # Dependencies & scripts
├── tsconfig.json              # TypeScript configuration
└── README.md                  # This file

Architecture Layers

1. Routes → Define endpoints and attach middleware
2. Middleware → Validation, authentication, rate limiting
3. Controllers → Handle HTTP requests/responses
4. Services → Business logic and orchestration
5. Prisma → Database operations
6. Config → Centralized configuration and clients

🔌 API Endpoints

Base URL: http://localhost:3000/api

Health Checks

GET /api/health

Check if the API is running.

Response:

{
  "success": true,
  "data": { "status": "ok" }
}

GET /api/health/db

Check database connectivity.

Response:

{
  "success": true,
  "data": { "status": "ok", "db": "up" }
}

Authentication

POST /api/auth/signup

Create a new user account.

Rate Limit: 5 requests per 15 minutes

Request:

{
  "email": "user@example.com",
  "password": "securepass123"
}

Validation:

• Email must be valid
• Password minimum 8 characters

Response (201):

{
  "success": true,
  "data": {
    "user": {
      "id": "uuid",
      "email": "user@example.com",
      ...
    }
  }
}

POST /api/auth/login

Login to get access token.

Rate Limit: 5 requests per 15 minutes

Request:

{
  "email": "user@example.com",
  "password": "securepass123"
}

Response (200):

{
  "success": true,
  "data": {
    "session": {
      "access_token": "eyJhbGc...",
      "refresh_token": "...",
      "expires_in": 3600
    },
    "user": { ... }
  }
}

Save the access_token for authenticated requests!

GET /api/auth/profile

Get user profile (protected route).

Authorization: Bearer token required

Request Header:

Authorization: Bearer YOUR_ACCESS_TOKEN

Response (200):

{
  "success": true,
  "data": {
    "id": "uuid",
    "email": "user@example.com",
    "createdAt": "2025-01-01T00:00:00.000Z",
    "updatedAt": "2025-01-01T00:00:00.000Z"
  }
}

🧪 Testing with cURL

1. Signup

curl -X POST http://localhost:3000/api/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email":"test@example.com","password":"password123"}'

2. Login

curl -X POST http://localhost:3000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"test@example.com","password":"password123"}'

Copy the access_token from the response.

3. Get Profile (Protected)

curl http://localhost:3000/api/auth/profile \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN_HERE"

4. Health Checks

curl http://localhost:3000/api/health
curl http://localhost:3000/api/health/db

📜 Available Scripts

🗄️ Database Management

Adding a New Table

1. Edit prisma/schema.prisma:

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String?
  authorId  String
  author    Profile  @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

2. Update Profile model to include relation:

model Profile {
  id        String   @id @default(uuid())
  email     String   @unique
  posts     Post[]   // Add this
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

3. Push to database:

npm run prisma:generate
npm run prisma:push

Using Prisma in Code

import { prisma } from './prisma.js';

// Create
const post = await prisma.post.create({
  data: {
    title: 'My Post',
    content: 'Hello world',
    authorId: 'user-id'
  }
});

// Read
const posts = await prisma.post.findMany({
  include: { author: true }
});

// Update
await prisma.post.update({
  where: { id: 'post-id' },
  data: { title: 'Updated Title' }
});

// Delete
await prisma.post.delete({ where: { id: 'post-id' } });

🔒 Security Features

• Helmet: Sets secure HTTP headers
• CORS: Configurable cross-origin requests
• Rate Limiting:
  • Auth endpoints: 5 requests/15min
  • API endpoints: 100 requests/15min
• JWT Authentication: Bearer token validation
• Zod Validation: Input sanitization and validation
• Service Role Key: Never exposed to frontend
• Environment Variables: Sensitive data in .env (git-ignored)

🚨 Error Handling

All errors return consistent format:

{
  "success": false,
  "error": "Error message",
  "details": { ... }  // Only in development
}

Error Types:

• 400 - Validation error (Zod)
• 401 - Authentication required/failed
• 404 - Resource not found
• 429 - Rate limit exceeded
• 500 - Internal server error

🔧 Troubleshooting

Database Connection Issues

Error: Can't reach database server

Solutions:

1. Verify DATABASE_URL is correct (check for typos)
2. URL-encode special characters in password (@ → %40)
3. Ensure Supabase project isn't paused
4. Check network allows outbound port 5432
5. Use the pooler host (*.pooler.supabase.com) not direct host if IPv4-only

Prisma Client Not Generated

Error: Cannot find module '@prisma/client'

Solution:

npm run prisma:generate

Port Already in Use

Error: EADDRINUSE: address already in use

Solution: Change PORT in .env or kill the process using port 3000.

📝 Environment Variables Reference

📚 Learn More

• Supabase Docs
• [P