Prisma vs Raw SQL: When to Use What

Dev
·Dante Chun

Introduction

In development, you often face the question "Should I use an ORM or write SQL directly?" Especially with Prisma gaining popularity in the Node.js ecosystem, this dilemma has deepened.

I use both depending on the situation. And the boundary between them is quite clearly defined for me.

Prisma's Strengths

1. Type Safety

Prisma's biggest advantage is TypeScript integration. Types are automatically generated from the schema.

// schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

// When using
const user = await prisma.user.findUnique({
  where: { email: "test@example.com" }
})
// user's type is automatically inferred
// user.name is string | null

Typos and wrong field names are caught at compile time. Impossible with SQL strings.

2. Relation Handling

Prisma is clean when fetching relational data.

// Query user with posts
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      orderBy: { createdAt: "desc" },
      take: 10
    }
  }
})

To do the same in SQL, you need to use JOINs or separate queries. And manually map the results.

3. Migration Management

One line of prisma migrate dev automatically generates migration files for schema changes. History is tracked, and production deployment is simple with prisma migrate deploy.

# Create migration in development
npx prisma migrate dev --name add_avatar_field

# Apply migration in production
npx prisma migrate deploy

4. Development Speed

Simple CRUD is implemented really fast.

// Create
await prisma.post.create({
  data: { title, body, authorId }
})

// Read
await prisma.post.findMany({
  where: { published: true }
})

// Update
await prisma.post.update({
  where: { id },
  data: { title: newTitle }
})

// Delete
await prisma.post.delete({
  where: { id }
})

When Raw SQL is Needed

1. Complex Aggregation Queries

Queries like this are difficult or impossible to express in Prisma.

SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as total_orders,
  SUM(amount) as revenue,
  AVG(amount) as avg_order_value
FROM orders
WHERE created_at >= NOW() - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Trying with Prisma requires combining groupBy and aggregate, which reduces readability and has many limitations.

// You can do this but...
const result = await prisma.order.groupBy({
  by: ['createdAt'],
  _count: { id: true },
  _sum: { amount: true },
  _avg: { amount: true },
  // How about DATE_TRUNC?
})

In these cases, use $queryRaw.

const stats = await prisma.$queryRaw`
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*)::int as total_orders,
    SUM(amount)::float as revenue
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '1 year'
  GROUP BY 1
  ORDER BY 1 DESC
`

2. Performance-Critical Queries

Queries generated by Prisma can sometimes be inefficient. N+1 problems are especially common.

// Prisma - may execute multiple queries internally
const posts = await prisma.post.findMany({
  include: { author: true, comments: true }
})

When dealing with large amounts of data, one optimized SQL query might be better.

SELECT 
  p.*,
  u.name as author_name,
  COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published = true
GROUP BY p.id, u.name
ORDER BY p.created_at DESC
LIMIT 20;

3. DB-Specific Features

PostgreSQL's advanced features have limited support in Prisma.

-- Full-text search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) 
  @@ plainto_tsquery('english', 'search term');

-- JSON queries
SELECT * FROM products
WHERE metadata->>'category' = 'electronics'
  AND (metadata->'specs'->'ram')::int >= 16;

-- Window functions
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
FROM products;

My Selection Criteria

When to Use Prisma

  • General CRUD operations
  • Fetching relational data (include)
  • When type safety is important
  • When fast development is the priority
  • When team members aren't familiar with SQL

When to Use Raw SQL

  • Complex aggregation/analytics queries
  • When performance optimization is needed
  • When using DB-specific features
  • Bulk data processing
  • Legacy DB integration

Real Project Example

Taking this blog as an example:

// Prisma - Article list query (simple CRUD)
export const listBlogPostsPaginated = async ({
  locale,
  cursor,
  take = 10
}) => {
  const articles = await prisma.blogPost.findMany({
    where: { locale },
    orderBy: { createdAt: "desc" },
    take: take + 1,
    ...(cursor && locale && {
      cursor: { id_locale: { id: cursor, locale } },
      skip: 1
    })
  })
  return {
    articles: articles.slice(0, take),
    nextCursor: articles.length > take ? articles[take - 1].id : null
  }
}

// Raw SQL - Stats dashboard (complex aggregation)
const viewStats = await prisma.$queryRaw`
  SELECT 
    slug,
    title,
    views,
    DATE(created_at) as published_date,
    views::float / GREATEST(
      EXTRACT(DAY FROM NOW() - created_at), 1
    ) as daily_avg_views
  FROM "BlogPost"
  WHERE locale = ${locale}
  ORDER BY views DESC
  LIMIT 10
`

Cautions When Mixing

1. Type Definitions

$queryRaw results are typed as unknown. You need to manually define types.

interface ViewStats {
  slug: string
  title: string
  views: number
  published_date: Date
  daily_avg_views: number
}

const stats = await prisma.$queryRaw<ViewStats[]>`...`

2. SQL Injection

$queryRaw automatically escapes when using template literals.

// Safe - template literal
const result = await prisma.$queryRaw`
  SELECT * FROM users WHERE id = ${userId}
`

// Dangerous - string concatenation
const result = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE id = '${userId}'`
)

3. Transactions

You can wrap Prisma operations and raw SQL together in a transaction.

await prisma.$transaction(async (tx) => {
  // Prisma operation
  const user = await tx.user.create({
    data: { email, name }
  })
  
  // Raw SQL operation
  await tx.$executeRaw`
    INSERT INTO audit_log (user_id, action)
    VALUES (${user.id}, 'created')
  `
})

Conclusion

"Prisma or SQL" is not a binary choice. Both are just tools, and you use them according to the situation.

Handle 80% of work quickly with Prisma, and write the 20% of complex queries in raw SQL. This is the balance I've found.

For those new to ORMs, I recommend Prisma. Start comfortably first, and when you encounter moments that need SQL, learn $queryRaw then. You can't completely avoid SQL anyway.

What matters is becoming proficient in both. Knowing only Prisma will hit limits, and insisting only on SQL reduces productivity.

다른 글