Prisma + PostgreSQL with Supabase

This guide will help you set up a database for your web application using three simple tools. Prisma makes working with databases very easy by writing simple code instead of complex database commands. PostgreSQL is a strong and reliable database that many big companies use for their applications. Supabase hosts your database online in the cloud, so you don't need to worry about managing servers or technical setup details.

Prerequisites

Before starting this tutorial, you need to install Node.js version 14 or newer from nodejs.org website, which will automatically include npm for managing packages. You also need to create a free Supabase account at supabase.com where your database will be hosted online.

Step 1: Create your supabase database

Creating your database on Supabase is simple and quick. Go to supabase.com and create your free account, then follow these steps:

1. Click "New Project"

2. Enter project name like "my-first-app"

3. Set a strong database password

4. Choose region close to you

5. Wait 2-3 minutes for setup

6. Go to Settings → Database for connection details

Step 2: Set up your project folder

Open your terminal and create a new project folder:

mkdir my-prisma-app
cd my-prisma-app
npm init -y

Step 3: Install required packages

Install Prisma and necessary dependencies:

npm install prisma --save-dev
npm install @prisma/client
npm install dotenv

Step 4: Initialize prisma

Set up Prisma in your project:

npx prisma init

This creates:

• A prisma folder with config files

• A .env file for database secrets

Step 5: Connect to database

Open .env file and add your Supabase connection strings:

DATABASE_URL="postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"
DIRECT_URL="postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"

Get these values from:

1. Your Supabase dashboard

2. Settings → Database

3. "Connection String" section

4. Replace [ref], [password], and [region] with actual values

Step 6: Design database schema

Edit prisma/schema.prisma file:

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

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

Step 7: Create database tables

Run migration to build your database:

npx prisma migrate dev --name init

Step 8: Test your setup

Create test.js file:

const { PrismaClient } = require('@prisma/client');

// Initialize Prisma Client
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'], // Enable logging for debugging
});

async function main() {
  try {
    // Create a user with validation
    const user = await prisma.user.create({
      data: {
        email: 'john.doe@example.com',
        name: 'John Doe',
      },
    });

    console.log('User created:', user);

    // Create a post linked to the user
    const post = await prisma.post.create({
      data: {
        title: 'Getting Started with Prisma',
        content: 'This is my first post using Prisma ORM with Supabase!',
        authorId: user.id,
      },
    });

    console.log('Post created:', post);

    // Fetch all posts with their authors (relational query)
    const postsWithAuthors = await prisma.post.findMany({
      include: {
        author: {
          select: {
            id: true,
            name: true,
            email: true,
          },
        },
      },
      orderBy: {
        createdAt: 'desc',
      },
    });

    console.log('Posts with authors:', JSON.stringify(postsWithAuthors, null, 2));

  } catch (error) {
    console.error('Error occurred:', error);
    throw error;
  }
}

// Execute main function with proper error handling
main()
  .catch((error) => {
    console.error('Application error:', error);
    process.exit(1);
  })
  .finally(async () => {
    // Always disconnect from database
    await prisma.$disconnect();
    console.log('Database connection closed');
  });

Step 9: Run your test

Execute the test script:

node test.js

You should see your test data printed in the console.

What's next

Now you have a solid foundation for building modern web applications! With this Prisma and Supabase setup, you can expand your project by adding more complex database models, building REST APIs using Express.js, or creating full-stack web applications with Next.js. The Prisma documentation offers comprehensive guides on advanced features like query optimization, migrations, and data seeding, while Supabase documentation covers real-time subscriptions, authentication, and serverless functions.

Conclusion

This tutorial has walked you through setting up a complete database solution using Prisma as your ORM, PostgreSQL as your database engine, and Supabase as your cloud hosting platform. This modern stack provides type-safe database operations, automatic migrations, real-time capabilities, and enterprise-grade security out of the box. You now have all the tools necessary to build scalable, production-ready applications with confidence in your database architecture. Whether you're creating a simple blog, an e-commerce platform, or a complex SaaS application, this foundation will serve you well as your project grows.