DEV Community: Tushar Patil

Handle Many to Many Relations in PostgreSQL using Prisma ORM

Tushar Patil — Tue, 17 Jun 2025 15:34:09 +0000

Handling many-to-many relationships in PostgreSQL using Prisma is straightforward and efficient. Prisma can manage both implicit and explicit many-to-many relations.

1. Implicit Many-to-Many Relation

model User {
  id       Int      @id @default(autoincrement())
  name     String
  projects Project[] // Many-to-many relation
}

model Project {
  id    Int    @id @default(autoincrement())
  title String
  users User[] // Many-to-many relation
}

By declaring each side as a list of the other, Prisma knows to spin up a hidden join table for you.

Generated SQL Migration

When you run prisma migrate dev, Prisma will produce SQL akin to:

-- 1. Create the two main tables
CREATE TABLE "User" (
  "id"   SERIAL PRIMARY KEY,
  "name" TEXT   NOT NULL
);

CREATE TABLE "Project" (
  "id"    SERIAL PRIMARY KEY,
  "title" TEXT    NOT NULL
);

-- 2. Create the hidden join table
CREATE TABLE "_UserToProject" (
  "A" INTEGER NOT NULL,
  "B" INTEGER NOT NULL,

  PRIMARY KEY ("A","B"),

  FOREIGN KEY ("A") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY ("B") REFERENCES "Project"("id") ON DELETE CASCADE ON UPDATE CASCADE
);

Key points:

The join table is named by alphabetically sorted model names with an underscore prefix: _To.

Columns are auto‑named "A"/"B"; you never interact with them directly in Prisma Client.

ON DELETE CASCADE ensures that if a User (or Project) is deleted, all linking rows vanish automatically.

Example 2
Here you can see many to many relation in between User and Team,
Team and Project. the joining table for this schema will be created by prisma automatically.

model User {
    id       String    @id @default(cuid())
    email    String    @unique
    username String    @unique
    password String
    projects Project[]
    teams    Team[]
}

model Project {
    id        String @id @default(cuid())
    name      String
    creatorId String @map("creator_id")
    creator   User   @relation(fields: [creatorId], references: [id])

    teams Team[]
}

model Team {
    id        String    @id @default(cuid())
    name      String?
    creatorId String    @map("creator_id")
    users     User[]
    projects  Project[]
}

implicit handling of many to many relations in prisma is more easier than manually creating a joining table.
but it lacks in flexibility, like in above example user and team have many to many relation, we can not add more field describing additional information about this relation like role of the user in related team.

2.Explicit Many to Many Relations

In Explicit Many to Many Relations you have to manually create a join table for tow models.
in this example i created two join tables UserTeam and ProjectTeam.

model User {
    id       String     @id @default(cuid())
    email    String     @unique
    username String     @unique
    password String
    projects Project[]
    teams    UserTeam[]
}

model Project {
    id        String @id @default(cuid())
    name      String
    creatorId String @map("creator_id")
    creator   User   @relation(fields: [creatorId], references: [id])

    teams ProjectTeam[]
}

model Team {
    id        String        @id @default(cuid())
    name      String?
    creatorId String        @map("creator_id")
    users     UserTeam[]
    projects  ProjectTeam[]
}

model UserTeam {
    id     String @id @default(cuid())
    teamId String
    userId String
    team   Team   @relation(fields: [teamId], references: id)
    user   User   @relation(fields: [userId], references: id)
}

model ProjectTeam {
    id      String  @id @default(cuid())
    teamId  String
    userId  String
    team    Team    @relation(fields: [teamId], references: id)
    Project Project @relation(fields: [userId], references: id)
}

this manual implementation for joining tables add more flexibility in defining schema, since we can add more fields in join table that does not possible with implicit relations.

example we can add role for the user in team

model UserTeam {
    id     String @id @default(cuid())
    teamId String
    userId String
    role   String

    team   Team   @relation(fields: [teamId], references: id)
    user   User   @relation(fields: [userId], references: id)
}

💡Conclusion:

choosing between implicit and explicit relational handling is depend upon you project requirement. if you require simpler many to many link between two models or don't need extra data on relation itself then you should go with implicit handling.
if your project require flexibility in schema , to add more data on relation itself, you should try creating a joining table explicitly to handle many to many relation.

Reference:

Prisma Documentation: https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations

🚀 Set Up PostgreSQL with pgAdmin Using Docker (Step-by-Step Guide)

Tushar Patil — Mon, 19 May 2025 18:17:40 +0000

🚀 Introduction

Managing PostgreSQL databases becomes much easier with pgAdmin, a powerful browser-based GUI. In this guide, you'll learn how to spin up both PostgreSQL and pgAdmin using Docker — no manual installation required!

📝 Note
Setting up PostgreSQL and pgAdmin using individual docker run commands is a valid approach, but it requires running multiple commands manually.

If you prefer using individual Docker commands, follow the steps below.
If you want a cleaner setup, skip directly to the Docker Compose section and run everything with a single command.

🛠️ Prerequisites

Make sure the following are installed:

✅ Docker
✅ Docker Compose (optional)

Step 1: Pull PostgreSQL Docker Image

docker pull postgres

Step 2: Run PostgreSQL Container

docker run --name my-postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb -p 5432:5432 -d postgres

🔍 This sets up a PostgreSQL container with:

Username: admin
Password: secret
Database name: mydb
Port: Exposes DB on localhost:5432

Step 3: Pull pgAdmin Docker Image

docker pull dpage/pgadmin4

Step 4: Run pgAdmin container

docker run --name pgadmin-container \
  -e PGADMIN_DEFAULT_EMAIL=admin@local.com \
  -e PGADMIN_DEFAULT_PASSWORD=admin123 \
  -p 5050:80 \
  -d dpage/pgadmin4

-Email: admin@local.com
-Password: admin123
-port : -p 5050
-image name(you also can use image id) : -d dpage/pgadmin4

This Starts pgAdmin on http://localhost:5050

Step 5: Access pgAdmin in Browser

Open your browser and go to:

http://localhost:5050

step 6: Add a New Server in pgAdmin

After logging into pgAdmin:

Click "Query Tool Workspace" (tool-> query tool) in the dashboard.
Under the General tab, fill the following information

server name: postgres
Port: 5432 (port you mentioned while running postgres)
Host Name: my-postgres (your postgres container name or service name)
Username: admin (mentioned while running postgres container)
Password: secret (your postgres password here)
Database: mydb (database name)

Step 7: run sample query

to ensure all is working properly run the following queries in pgAdmin's query space

1.Create Table Users

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL, 
    password_hash VARCHAR(255) NOT NULL
);

2.Insert a user in users Table

INSERT INTO users (username, password_hash) VALUES ('your_username', 'your_hashed_password');

3.query all users from database

SELECT * FROM users;

🚀 Using Docker-compose

1.Create docker-compose.yaml file

docker-compose.yaml

version: '3.8'

services:
  postgres:
    image: postgres:15
    container_name: postgres
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: admin123
    ports:
      - "5050:80"
    depends_on:
      - postgres
    volumes:
      - pgadmin_data:/var/lib/pgadmin

volumes:
  pgdata:
  pgadmin_data:

2.Run it

docker-compose up -d

3. Access pgAdmin on Browser

open browser and enter url http://localhost:5050/browser
now follow the process from Step 5 mentioned above

🚀 Setting Up PostgreSQL with Prisma ORM in a Node.js Project (Using Docker)

Tushar Patil — Sun, 18 May 2025 06:11:20 +0000

In my recent Node.js project, I set up a PostgreSQL database using Prisma ORM—one of the most efficient and type-safe ORMs available. I used docker to run postgres on my linux machine

Here’s a detailed walkthrough of the setup—from pulling the Docker image to generating the Prisma client. If you're getting started with Prisma and PostgreSQL in Node.js, this guide is for you.

🛠️ Prerequisites

Make sure the following are installed:

✅ Node.js (v14+ recommended)
✅ Docker
✅ npm

Step 1: Pull PostgreSQL Docker Image

docker pull postgres

Step 2: Run PostgreSQL Container

docker run --name my-postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=mydb -p 5432:5432 -d postgres

🔍 This sets up a PostgreSQL container with:

Username: admin
Password: secret
Database name: mydb
Port: Exposes DB on localhost:5432

Step 3: Init NodeJs project

npm init -y

Step 4: Install Prisma

npm install prisma --save-dev
npm install @prisma/client
npx prisma init

🔐 Step 5: Configure .env

add DATABASE_URL in .env file
DATABASE_URL="postgresql://admin:secret@localhost:5432/mydb?schema=public"

admin: PostgreSQL username
secret: PostgreSQL password
localhost: Host of the DB server
5432: PostgreSQL's default port
mydb: Database name
schema=public: Default schema in PostgreSQL

📝 Step 6: Define Your Prisma Schema

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

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

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

⚙️ Step 7: Migrate and Generate Prisma Client

npx prisma migrate dev --name init
npx prisma generate

--
migrate dev creates migration files and updates the DB
generate creates the Prisma client for querying

Step 8: Test the Database Connection

Let’s write a quick script to ensure everything’s working as expected.

Create a script.js file:

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

async function main() {
  const user = await prisma.user.create({
    data: {
      email: 'abc@example.com',
      name: 'Tushar',
    },
  });

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

  const allUsers = await prisma.user.findMany();
  console.log('All users:', allUsers);
}

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

Run the Script

node script.js

You should see something like:

User created: { id: 1, email: 'abc@example.com', name: 'Tushar', createdAt: 2025-05-18T... }
All users: [ { id: 1, email: 'abc@example.com', name: 'Tushar', createdAt: 2025-05-18T... } ]

🎉 Congratulations! You've successfully connected Node.js to a PostgreSQL database running inside a Docker container using Prisma.