Home Supabase How to setu ...

How to setup nextjs project with supabase backend

At higher level, we have below steps

Create a projet in Supabase. Postgress database is provisioned for project automatically
Define database schema and models. You can use chatgpt for this.
Create nextjs app using supabase template - npx create-next-app --example with-supabase hr
Once project is created in above step, update .env file with Supabase config.
Then create tables in database using prisma
Check db connection
Add auth
Add front end routes
Add api routes (optional as we can access db from server components)

Create a projet in Supabase.

When project is created, you will get project url, Anon key, Database connection string

Defind database schema and models.

Create models like below

model User {
  id         String     @id @default(uuid())
  email      String     @unique
  password   String
  role       UserRole   @default(CompanyAdmin)
  companyId  String?
  company    Company?   @relation(fields: [companyId], references: [id])
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
}

Create nextjs app using supabase template.

npx create-next-app --example with-supabase hr

Once project is created in above step, update .env file with Supabase config.

NEXT_PUBLIC_SUPABASE_URL=https://yourprojref.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=youranonkey
DATABASE_URL_POOLING="postgresql://postgres.yourprojref:[email protected]:6543/postgres?pgbouncer=true"
# Direct connection to the database. Used for migrations
DATABASE_URL="postgresql://postgres.yourprojref:[email protected]:5432/postgres"

Then create tables in database using prisma

- npm install prisma --save-dev
- npm install @prisma/client
- npm i -D ts-node
- npx prisma init 
- Update schema.prisma - Keep models in prisma/schema.prisma 
- npx prisma db push or Run prisma migrations - npx prisma migrate dev --name init (Create your DB tables )
- npx prisma generate  (Generate Prisma Client) - ensure prisma client is generated in node_modules dir

Here is a sample schema.prisma file


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

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


enum UserRole {
  SuperAdmin
  CompanyAdmin
  Employee
}


model User {
  id        String   @id @default(uuid())
  email     String   @unique
  password  String
  role      UserRole @default(CompanyAdmin)
  companyId String?
  company   Company? @relation(fields: [companyId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Inverse relation fields
  companiesCreated   Company[]       @relation("CompanyCreatedBy")
  companiesModified  Company[]       @relation("CompanyModifiedBy")
  employeesCreated   Employee[]      @relation("EmployeeCreatedBy")
  employeesModified  Employee[]      @relation("EmployeeModifiedBy")
  payrunsCreated     Payrun[]        @relation("PayrunCreatedBy")
  payrunsModified    Payrun[]        @relation("PayrunModifiedBy")
  payrunEntriesCreated  PayrunEntry[] @relation("PayrunEntryCreatedBy")
  payrunEntriesModified PayrunEntry[] @relation("PayrunEntryModifiedBy")
}



model Company {
  id            String    @id @default(uuid())
  name          String
  abn           String    @unique
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  createdById   String?
  modifiedById  String?

  createdBy     User?     @relation("CompanyCreatedBy", fields: [createdById], references: [id])
  modifiedBy    User?     @relation("CompanyModifiedBy", fields: [modifiedById], references: [id])

  users         User[]
  employees     Employee[]
  payruns       Payrun[]
}



model Employee {
  id           String     @id @default(uuid())
  firstName    String
  lastName     String
  tfn          String
  salary       Decimal    @db.Decimal(10, 2)
  superRate    Decimal    @db.Decimal(5, 2)
  companyId    String
  company      Company    @relation(fields: [companyId], references: [id])
  entries      PayrunEntry[]
  createdAt    DateTime   @default(now())
  updatedAt    DateTime   @updatedAt

  createdById  String?
  modifiedById String?

  createdBy    User?      @relation("EmployeeCreatedBy", fields: [createdById], references: [id])
  modifiedBy   User?      @relation("EmployeeModifiedBy", fields: [modifiedById], references: [id])
}

When running "npx prisma db push", Error may come if client is not on ip6 network. In that case, you will need to use session or transaction pooler. Also ensure you use correct database connection string per ORM, mobile, app etc

Next you need to seed db for testing purpose.

Create a prisma/seed.ts file:

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

async function main() {
  // Create a company
  const company = await prisma.company.create({
    data: {
      name: "Test Co Pty Ltd",
      abn: "12345678901",
    },
  });

  // Create a user
  const user = await prisma.user.create({
    data: {
      email: "[email protected]",
      password: "hashed-password",
      role: "CompanyAdmin",
      companyId: company.id,
    },
  });

  // Create an employee
  const employee = await prisma.employee.create({
    data: {
      firstName: "Alice",
      lastName: "Smith",
      tfn: "123456789",
      salary: 85000,
      superRate: 10.5,
      companyId: company.id,
      createdById: user.id,
    },
  });

  console.log("Seeded data successfully!");
}

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

Then in package.json file,

"prisma": {

    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"

if you run "seed": "ts-node prisma/seed.ts" command, you will see error saying "unknown file extension" thats because ts-node doesnot load esm module correctly

then run

npx prisma db seed

Check db connection

Once database is seeded, you can check connection from nextjs server component.

create /lib/prisma.ts

import { PrismaClient } from '@prisma/client'
const globalForPrisma = global as unknown as { prisma?: PrismaClient }

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
  })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

create app/employees/page.tsx (a server component by default)

import { prisma } from '@/lib/prisma'

export default async function EmployeesPage() {
  // Directly call Prisma from the server component
  const employees = await prisma.employee.findMany()

  return (
    <div>
      <h1>Employees</h1>
      <ul>
        {employees.map((emp) => (
          <li key={emp.id}>
            {emp.firstName} {emp.lastName}
          </li>
        ))}
      </ul>
    </div>
  )
}

App should show table data.

Add auth

Supabase template comes up with all routes to be able to login. e.g. /auth/login - this page allows user to login

I am not using magic link as it is for temporary users
We need to enable rls for tables to prevent direct db access from browser

We need to install below packages

npm install @supabase/supabase-js @supabase/ssr

Then we need to update .env file

NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

Then create lib/supabase/client.ts, lib/supabase/server.ts, lib/supabase/client.ts and lib/supabase/middleware.ts

Then you can check if user is authenticated in server component like below.

import { createClient } from '@/lib/supabase/server';
import { redirect } from 'next/navigation';

export default async function DashboardPage() {

 const supabase = await createClient();

 const {

   data: { session },

 } = await supabase.auth.getSession();

 if (!session) redirect('/login');

 return <div>Welcome, {session.user.email}</div>;
}

Add front end routes

Then you can add routes like - app/admin/companies, app/admin/create-company, app/company/employees, app/company/payruns, app/employee/login