TypeScript & Prisma Workflow

Initialize a TypeScript project and install required dev dependencies:

mkdir my-prisma-project
cd my-prisma-project
npm init -y
npm install typescript tsx @types/node --save-dev
npx tsc --init

Nodemon Configuration (nodemon.json)

Create a nodemon.json file to automatically restart your server on changes using tsx:

{
  "watch": ["src"],
  "ext": "ts",
  "exec": "tsx src/server.ts"
}

Package Scripts (package.json)

Add these scripts to your package.json. You can use either tsx watch directly, or use the nodemon script configured above:

"type": "module",
"scripts": {
  "dev": "nodemon",
  "dev:tsx": "tsx watch src/index.ts",
  "build": "tsc",
  "start": "node dist/server.js"
}

Install the Prisma CLI and PostgreSQL dependencies:

# Install Prisma CLI
npm install prisma @types/pg --save-dev

# Install Client & Driver
npm install @prisma/client @prisma/adapter-pg pg dotenv

Initialize Prisma with PostgreSQL as the data provider:

npx prisma init --datasource-provider postgresql --output ./generated/prisma

Environment Variables (.env)

Replace the connection string with your PostgreSQL credentials:

DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"

Prisma Schema (prisma/schema.prisma)

Define your database models and their relationships:

generator client {
  provider = "prisma-client-js"
  output   = "../generated/prisma"
}

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

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

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

Migrate and Generate

Push your schema to the database and generate the TypeScript client:

npx prisma migrate dev --name init
npx prisma generate

Instantiate Prisma Client (src/lib/prisma.ts)

Create a singleton Prisma instance to avoid opening too many connections. This acts as your prisma.config.ts equivalent.

import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../../generated/prisma/client.js";

const connectionString = process.env.DATABASE_URL!;
const adapter = new PrismaPg({ connectionString });
const prisma = new PrismaClient({ adapter });

export { prisma };

Now, you can import this anywhere in your project:

import { prisma } from "./lib/prisma.js";

Create

const user = await prisma.user.create({
  data: { name: "Bob", email: "[email protected]" }
});

Find Many

const users = await prisma.user.findMany();

Find Unique / First

const user = await prisma.user.findUnique({
  where: { email: "[email protected]" }
});

Update

const updatedUser = await prisma.user.update({
  where: { email: "[email protected]" },
  data: { name: "Bobby" }
});

Delete

const deletedUser = await prisma.user.delete({
  where: { email: "[email protected]" }
});

Prisma supports creating, updating, and querying related records in a single database query.

Nested Create

// Create a user with multiple posts at once
const user = await prisma.user.create({
  data: {
    name: "Alice",
    email: "[email protected]",
    posts: {
      create: [
        { title: "Post 1", content: "Learning Prisma", published: true },
        { title: "Post 2", content: "Advanced Queries" }
      ],
    },
  },
  include: { posts: true }, // Returns the posts along with the user
});

Filtering on Relations

// Get all published posts with author's name containing "Alice"
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      name: { contains: "Alice" }
    }
  },
  include: { author: true },
});

Pagination and Sorting

Useful for large datasets to avoid loading everything into memory at once.

const posts = await prisma.post.findMany({
  take: 5,          // limit (how many records to fetch)
  skip: 10,         // offset (how many records to skip)
  orderBy: {        // sort direction
    id: "desc",
  },
});

Batch Operations

Update or delete multiple records in one go.

// Update multiple posts
await prisma.post.updateMany({
  where: { published: false },
  data: { published: true },
});

// Delete multiple posts
await prisma.post.deleteMany({
  where: { published: false },
});

What is Aggregation? Aggregation allows you to perform mathematical operations on your data directly inside the database. Instead of fetching records, you ask the database to calculate metrics like counting rows, calculating averages, summing values, or finding the minimum/maximum values. It is highly optimized and essential for dashboards.

Count Records

// Count total posts for a specific user
const postCount = await prisma.post.count({
  where: { authorId: 1 },
});
console.log(`User has ${postCount} posts.`);

Aggregate Values

// Get the average, minimum, and maximum of post IDs
const postStats = await prisma.post.aggregate({
  _avg: { id: true },
  _min: { id: true },
  _max: { id: true },
});
console.log("Stats:", postStats);

Group By

// Group users by a specific field and count them
const groupUsers = await prisma.user.groupBy({
  by: ['name'],
  _count: {
    email: true,
  },
  having: {
    email: {
      _count: { gt: 1 } // Only return groups where count > 1
    }
  }
});

What is a Transaction? A transaction is a sequence of database operations executed as a single, "all-or-nothing" unit of work. If any operation within the transaction fails (due to an error, logic check, or constraint), all previous operations in that transaction are rolled back (undone). This ensures your database never ends up in a partially updated, corrupted state.

Sequential Array Transaction

The simplest way to run a transaction: pass an array of Prisma queries.

const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { name: "Alice", email: "[email protected]" } }),
  prisma.post.create({ data: { title: "Transactional Post", authorId: 1 } }),
]);
console.log("Both operations succeeded together!");

Interactive Transactions

Use interactive transactions when you need to run queries, perform logic based on the result in Node.js, and then run more queries—all within the same transaction.

await prisma.$transaction(async (tx) => {
  // 1. Fetch sender balance
  const sender = await tx.user.findUnique({ where: { id: 1 } });
  
  // 2. Perform logic check
  if (!sender || sender.balance < 100) {
    throw new Error("Insufficient funds"); // Rolls back the entire transaction!
  }

  // 3. Update records safely
  await tx.user.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  });

  await tx.user.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  });
});

Upsert (Update or Insert)

Prisma will attempt to find a record. If it exists, it updates it. If it doesn't exist, it creates a new one.

const user = await prisma.user.upsert({
  where: { email: "[email protected]" },
  update: { name: "Alice Updated Name" },
  create: { email: "[email protected]", name: "Alice New Name" },
});

Raw SQL Queries

If Prisma's query API cannot handle a complex operation, you can safely write raw SQL.

// Example using prisma.$queryRaw for safe variable interpolation
const emailToFind = "[email protected]";
const result = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${emailToFind}`;
console.log(result);

// Example using prisma.$executeRaw (for INSERT/UPDATE/DELETE)
await prisma.$executeRaw`UPDATE "User" SET name = ${"Alice Updated"} WHERE id = ${1}`;