Using Raw SQL with Prisma ORM

ORM (Object-Relational Mapping) technologies used in modern web projects offer developers the opportunity to perform database operations more easily and in a type-safe way. Prisma ORM, as one of the prominent ORM tools in Node.js-based projects, stands out with its flexible configuration and features that enhance developer experience. However, when some complex queries or advanced database operations are needed, using raw SQL with Prisma comes into play. In this article, we will discuss in detail the topic of "using raw SQL with Prisma ORM."

What is Raw SQL in Prisma ORM?

Prisma ORM usually works with its own provided methods, but when standard methods fall short, it may be necessary to run SQL codes directly. This is where the prisma.$queryRaw and prisma.$executeRaw methods come in. With these methods, you can send SQL queries directly to the database; dynamic and complex operations can be performed. Especially for performance optimizations and challenging queries, using raw SQL offers a significant advantage.

Examples of $queryRaw in Prisma

While using raw SQL with Prisma ORM, data fetching is generally done with the $queryRaw method. Its usage is quite simple:

// Example: Fetching all users
type User = {
  id: number,
  email: string,
  name: string
};

const users: User[] = await prisma.$queryRaw`SELECT * FROM "User"`;
console.log(users);

In the example above, all users are fetched from the "User" table. You can develop more flexible queries with raw SQL.

Parameterized Raw SQL and Security

When writing raw SQL queries, you should be careful about security vulnerabilities like SQL Injection. Prisma ORM securely supports parameterized queries inside $queryRaw:

const id = 10;
const user = await prisma.$queryRaw`SELECT * FROM "User" WHERE id = ${id}`;
console.log(user);

Since variables bound this way are automatically escaped by Prisma, secure queries are created.

Updating Data with $executeRaw

The $executeRaw method is used for operations like adding, deleting, or updating data. For example:

await prisma.$executeRaw`UPDATE "User" SET name = 'New Name' WHERE id = 1`;

Thus, with Prisma ORM, you have full control in both reading ($queryRaw) and writing/updating ($executeRaw) operations using raw SQL.

Conclusion: Caution When Using Raw SQL

Using raw SQL with Prisma ORM provides flexibility and power in projects, but it is very important that queries are written clearly and securely. Especially when working with dynamic values, you should always prefer parameterized queries (`... ${value}`). This way, you benefit from flexibility without compromising the security that Prisma provides.