Connecting Prisma application to Supabase Postgres

Creste a custom user fro Prisma

create a Prisma DB user with full privileges on the public schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Create custom user
create user "prisma" with password 'custom_password' bypassrls createdb;

-- extend prisma's privileges to postgres (necessary to view changes in Dashboard)
grant "prisma" to "postgres";

-- Grant it necessary permissions over the relevant schemas (public)
grant usage on schema public to prisma;
grant 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;

check privileges
1
SELECT usename, usecreatedb, usebypassrls FROM pg_user;

check privileges

alter prisma password if needed

1
alter user "prisma" with password 'new_password';

Create a Prisma Project

1
2
mkdir hello-prisma
cd hello-prisma

Initiate a new Prisma project

1
2
3
4
5
6
7
8
mkdir hello-prisma
cd hello-prisma
npm init -y
npm install prisma typescript ts-node @types/node --save-dev

npx tsc --init

npx prisma init

Add connection information to .env file
add connection information

1
2
DIRECT_URL is used for migrations
DATABASE_URL is used for connection pooling

Create migration

create new tables in prisma.shcema file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

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

commit migration
1
npx prisma migrate dev --name first_prisma_migration

commit migration

Install the prisma client

1
2
npm install @prisma/client
npx prisma generate

install the prisma client

set schema.prisma

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

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

set prisma.config.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
import { defineConfig, env } from "prisma/config";
import "dotenv/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  engine: "classic",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

set index.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
    const val = await prisma.user.findMany({
        take: 10,
    });
    console.log(val);
}

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

Insert test data

insert data

Test index.ts

test file

Reference

repo
supabase doc