15.4 Prisma Advanced Queries — Raw SQL, Transactions, Extensions

Raw SQL

import { prisma } from './lib/prisma'
import { Prisma } from '@prisma/client'

// Tagged query (prevents SQL injection — use $queryRaw)
const email = 'alice@example.com'
const users = await prisma.$queryRaw<Array<{ id: number; name: string }>>`
  SELECT id, name FROM "User" WHERE email = ${email}
`

// Dynamic parameters — use Prisma.sql tag
const minAge = 18
const maxAge = 65
const result = await prisma.$queryRaw<Array<{ count: bigint }>>`
  SELECT COUNT(*) as count
  FROM "User"
  WHERE age BETWEEN ${minAge} AND ${maxAge}
`
// Convert BigInt to Number
const count = Number(result[0].count)

// Mutation query — $executeRaw
const affected = await prisma.$executeRaw`
  UPDATE "Post" SET "viewCount" = "viewCount" + 1
  WHERE id = ${1}
`
console.log(affected)  // Number of affected rows

---

Transactions

Sequential Transaction

// Process multiple operations in one transaction
const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: 'alice@example.com', name: 'Alice' },
  }),
  prisma.post.create({
    data: { title: 'First Post', authorId: 1 },
  }),
])

Interactive Transaction

// Handle conditional logic within a transaction
const result = await prisma.$transaction(async (tx) => {
  // Debit
  const sender = await tx.account.update({
    where: { id: 'sender-id' },
    data: { balance: { decrement: 100 } },
  })

  // Check balance
  if (sender.balance < 0) {
    throw new Error('Insufficient balance')  // Auto-rollback
  }

  // Credit
  const receiver = await tx.account.update({
    where: { id: 'receiver-id' },
    data: { balance: { increment: 100 } },
  })

  // Record transfer
  await tx.transfer.create({
    data: {
      senderId: 'sender-id',
      receiverId: 'receiver-id',
      amount: 100,
    },
  })

  return { sender, receiver }
})

// Transaction options
const result2 = await prisma.$transaction(
  async (tx) => {
    // ...
  },
  {
    maxWait: 5000,   // Max wait time (ms)
    timeout: 10000, // Transaction timeout (ms)
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  }
)

---

Prisma Extensions

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient().$extends({
  // Query extension — automatic soft delete
  query: {
    user: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null }
        return query(args)
      },
      async delete({ args, query }) {
        return prisma.user.update({
          where: args.where,
          data: { deletedAt: new Date() },
        })
      },
    },
  },

  // Model extension — add custom methods
  model: {
    user: {
      async findByEmail(email: string) {
        return prisma.user.findUnique({ where: { email } })
      },

      async softDelete(id: number) {
        return prisma.user.update({
          where: { id },
          data: { deletedAt: new Date() },
        })
      },
    },
  },

  // Result extension — add virtual fields
  result: {
    user: {
      fullName: {
        needs: { firstName: true, lastName: true },
        compute(user) {
          return `${user.firstName} ${user.lastName}`
        },
      },
    },
  },
})

// Use extended methods
const user = await prisma.user.findByEmail('alice@example.com')
const users = await prisma.user.findMany()  // Auto-applies deletedAt: null
console.log(user?.fullName)  // Virtual field

---

Pagination

// Offset-based pagination
async function getPaginatedUsers(page: number, limit: number) {
  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    prisma.user.count(),
  ])

  return {
    data: users,
    total,
    page,
    limit,
    totalPages: Math.ceil(total / limit),
    hasNext: page * limit < total,
    hasPrev: page > 1,
  }
}

// Cursor-based pagination (infinite scroll)
async function getPostsAfterCursor(cursor?: number, take = 10) {
  const posts = await prisma.post.findMany({
    take: take + 1,  // Fetch +1 to check if next page exists
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1,  // Exclude the cursor itself
    }),
    orderBy: { id: 'asc' },
  })

  const hasNextPage = posts.length > take
  const data = hasNextPage ? posts.slice(0, -1) : posts
  const nextCursor = hasNextPage ? data[data.length - 1].id : undefined

  return { data, nextCursor, hasNextPage }
}

---

Full-Text Search

// PostgreSQL full-text search
model Post {
  id      Int    @id @default(autoincrement())
  title   String
  content String

  @@fulltext([title, content])  // MySQL/PlanetScale only
}

// PostgreSQL — contains + mode: insensitive
const results = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'TypeScript', mode: 'insensitive' } },
      { content: { contains: 'TypeScript', mode: 'insensitive' } },
    ],
  },
})

// MySQL — fulltext search
const results2 = await prisma.post.findMany({
  where: {
    OR: [
      { title: { search: 'TypeScript' } },
      { content: { search: 'TypeScript' } },
    ],
  },
})

---

Pro Tips

Type-Safe Dynamic Sorting

import { Prisma } from '@prisma/client'

type UserSortField = keyof Pick<Prisma.UserOrderByWithRelationInput, 'name' | 'email' | 'createdAt'>

async function getSortedUsers(sortBy: UserSortField, order: 'asc' | 'desc') {
  return prisma.user.findMany({
    orderBy: { [sortBy]: order },
  })
}

// Usage
const users = await getSortedUsers('createdAt', 'desc')