P
Prisma•3w ago
uralsmh

Prisma.sql vs Prisma $queryRaw diff

Can someone guide me what is the main benefit of Prisma.sql and main differences with $queryRaw? I could not find the Prisma.sql in official docs. Is below approach corrct? 
export async function aggregateOpenPullRequestsInPeriod(
  req: AnalyticsRequest,
  dbClient: PrismaClient,
): Promise<
  DistributionPeriodStats<{ opened: number; closed: number; merged: number }>[]
> {
  const { start_date, end_date, aggregate_by, repository_names } = req;
  const rawStartingTimeColumn = Prisma.raw('created_at');
  const rawClosingTimeColumn = Prisma.raw('closed_at');
  const rawMergedTimeColumn = Prisma.raw('merged_at');

  const dateSeriesQuery = generateDateSeriesQuery(
    start_date,
    end_date,
    aggregate_by,
  );

  const query = Prisma.sql` --> here
    WITH date_series AS (
      ${dateSeriesQuery}
    )
    SELECT
      ds.bucket_start AS start,
      ds.bucket_end AS end,
      JSON_BUILD_OBJECT(
        'opened', COALESCE(SUM(CASE WHEN pr.${rawStartingTimeColumn} >= ds.bucket_start AND pr.${rawStartingTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0),
        'closed', COALESCE(SUM(CASE WHEN pr.${rawClosingTimeColumn} >= ds.bucket_start AND pr.${rawClosingTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0),
        'merged', COALESCE(SUM(CASE WHEN pr.${rawMergedTimeColumn} >= ds.bucket_start AND pr.${rawMergedTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0)
      ) AS value
    FROM
      date_series ds
      LEFT JOIN "PullRequest" pr ON pr.repository_id IS NOT NULL
      JOIN "Repository" r ON pr.repository_id = r.repository_id
    WHERE
      r.repository_name IN (${Prisma.join(repository_names)})
    GROUP BY
      ds.bucket_start, ds.bucket_end
    ORDER BY
      ds.bucket_start;
  `;

  const result = await dbClient.$queryRaw<
    DistributionPeriodStats<{
      opened: number;
      closed: number;
      merged: number;
    }>[]
  >(query);
export async function aggregateOpenPullRequestsInPeriod(
  req: AnalyticsRequest,
  dbClient: PrismaClient,
): Promise<
  DistributionPeriodStats<{ opened: number; closed: number; merged: number }>[]
> {
  const { start_date, end_date, aggregate_by, repository_names } = req;
  const rawStartingTimeColumn = Prisma.raw('created_at');
  const rawClosingTimeColumn = Prisma.raw('closed_at');
  const rawMergedTimeColumn = Prisma.raw('merged_at');

  const dateSeriesQuery = generateDateSeriesQuery(
    start_date,
    end_date,
    aggregate_by,
  );

  const query = Prisma.sql` --> here
    WITH date_series AS (
      ${dateSeriesQuery}
    )
    SELECT
      ds.bucket_start AS start,
      ds.bucket_end AS end,
      JSON_BUILD_OBJECT(
        'opened', COALESCE(SUM(CASE WHEN pr.${rawStartingTimeColumn} >= ds.bucket_start AND pr.${rawStartingTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0),
        'closed', COALESCE(SUM(CASE WHEN pr.${rawClosingTimeColumn} >= ds.bucket_start AND pr.${rawClosingTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0),
        'merged', COALESCE(SUM(CASE WHEN pr.${rawMergedTimeColumn} >= ds.bucket_start AND pr.${rawMergedTimeColumn} < ds.bucket_end + INTERVAL '1 day' THEN 1 ELSE 0 END), 0)
      ) AS value
    FROM
      date_series ds
      LEFT JOIN "PullRequest" pr ON pr.repository_id IS NOT NULL
      JOIN "Repository" r ON pr.repository_id = r.repository_id
    WHERE
      r.repository_name IN (${Prisma.join(repository_names)})
    GROUP BY
      ds.bucket_start, ds.bucket_end
    ORDER BY
      ds.bucket_start;
  `;

  const result = await dbClient.$queryRaw<
    DistributionPeriodStats<{
      opened: number;
      closed: number;
      merged: number;
    }>[]
  >(query);
2 Replies
Prisma AI Help
Prisma AI Help•3w ago
You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!
RaphaelEtim
RaphaelEtim•3w ago
Hi @uralsmh Prisma.sql is a helper function provided by Prisma to create SQL query fragments that can be safely interpolated into larger queries. See this section of the documentation. You can also take a look at sql-template-tag repo that Prisma.sql utilizes under the hood. $queryRaw is a method to execute raw SQL queries. The main differences are: 1. When used as a tagged template literal prisma.$queryRawSELECT * FROM User WHERE email = ${email}, it's safe from SQL injections. When used as a function call (like prisma.$queryRaw("SELECT * FROM User WHERE email = " + email)), it's susceptible to SQL injections. Your approach using Prisma.sql in your code is correct. You're using Prisma.sql to create a safe SQL fragment that includes other SQL fragments and interpolated values. You're using Prisma.raw for column names, which is correct as column names can't be parameterized and you are using Prisma.join to safely join the repository_names array into the query.
GitHub
GitHub - blakeembrey/sql-template-tag: ES2015 tagged template strin...
ES2015 tagged template string for preparing SQL statements, works with pg, mysql, sqlite and oracledb - blakeembrey/sql-template-tag
Raw queries | Prisma Documentation
Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API.

Did you find this page helpful?