Safal Bhandari

Posted on Sep 6

Connecting PostgreSQL with Prisma and TypeScript

Prisma works seamlessly with TypeScript, providing type-safety for your database queries. Here’s the full setup:

1. Prerequisites

You need:

Node.js (>=16.x)
npm or yarn
PostgreSQL installed (local/cloud like Neon, Supabase, RDS)

2. Initialize Project with TypeScript

Create a new project:

mkdir prisma-postgres-ts
cd prisma-postgres-ts
npm init -y

Install TypeScript and required types:

npm install typescript ts-node @types/node --save-dev

Initialize TypeScript config:

npx tsc --init

3. Install Prisma

Install Prisma and the client:

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

Initialize Prisma:

npx prisma init

This creates:

prisma/schema.prisma
.env

4. Configure Database URL

Edit .env and add PostgreSQL connection:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"

Example (local setup):

DATABASE_URL="postgresql://postgres:mysecretpassword@localhost:5432/mydb?schema=public"

5. Define Schema

Open prisma/schema.prisma and update it:

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

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

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

6. Run Migration

Apply schema to your PostgreSQL database:

npx prisma migrate dev --name init

This:

Creates the User table
Generates Prisma Client with TypeScript typings

7. Use Prisma with TypeScript

Create a file src/index.ts:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  // Create a new user
  const newUser = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@example.com",
    },
  });
  console.log("Created user:", newUser);

  // Fetch all users
  const users = await prisma.user.findMany();
  console.log("All users:", users);
}

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

8. Run TypeScript Code

Run directly with ts-node:

npx ts-node src/index.ts

Or build & run:

npx tsc
node dist/index.js

9. Prisma Studio (Optional)

Use Prisma’s built-in DB explorer:

npx prisma studio

✅ Summary

Installed TypeScript + Prisma
Configured PostgreSQL in .env
Defined schema and ran migration
Wrote type-safe queries with Prisma Client in TS

DEV Community