uralsmh Posts - Answer Overflow

uralsmh

•Created by uralsmh on 1/11/2025 in #help-and-questions

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);

3 replies

PPrisma

•Created by uralsmh on 8/4/2024 in #help-and-questions

Issues with Prisma 5.3.1: OpenSSL Version Mismatch during Build and Runtime in Docker

I am encountering issues with my setup involving Prisma 5.3.1 and Docker. During the build process, I receive the following error: in this GitHub issue, https://github.com/prisma/prisma/issues/16232 which suggests that there is no need to install OpenSSL manually. However, this did not resolve the issue for me.Is there a specific configuration I am missing in my Dockerfile or schema.prisma file that can ensure compatibility? Is there a recommended way to ensure that the Prisma Client is correctly generated for the required OpenSSL version within a Docker environment? Project Details: Prisma Version: 5.3.1 Node Version: 20 Docker Image: node:20-bookworm and node:20-bookworm-slim

FROM node:20-bookworm AS build
WORKDIR /app
RUN --mount=type=cache,target=/var/cache/apt \
    apt-get update && \
    apt-get --no-install-recommends install -y openssl && \
    rm -rf /var/lib/apt/lists/*
COPY package.json package-lock.json tsconfig.json ./
COPY prisma/schema.prisma ./
RUN --mount=type=cache,target=/app/.npm \
    npm set cache /app/.npm && \
    npm ci
COPY . .
RUN npx prisma generate && npm run build

FROM node:20-bookworm-slim AS runtime
WORKDIR /app
COPY --from=build /app/node_modules ./node_modules
COPY --from=build /app/prisma/schema.prisma ./prisma/schema.prisma
COPY --from=build /app/package.json ./package.json
COPY --from=build /app/package-lock.json ./package-lock.json
COPY --from=build /app/dist ./dist

FROM runtime AS collector
CMD ["node","./dist/collector/server.js"]

FROM runtime AS workers
CMD ["node","./dist/workers/master.js"]

FROM runtime AS api
CMD ["node","./dist/api/server.js"]

FROM runtime AS webhook
CMD ["node", "./dist/webhook/server.js"]

FROM node:20-bookworm AS build
WORKDIR /app
RUN --mount=type=cache,target=/var/cache/apt \
    apt-get update && \
    apt-get --no-install-recommends install -y openssl && \
    rm -rf /var/lib/apt/lists/*
COPY package.json package-lock.json tsconfig.json ./
COPY prisma/schema.prisma ./
RUN --mount=type=cache,target=/app/.npm \
    npm set cache /app/.npm && \
    npm ci
COPY . .
RUN npx prisma generate && npm run build

FROM node:20-bookworm-slim AS runtime
WORKDIR /app
COPY --from=build /app/node_modules ./node_modules
COPY --from=build /app/prisma/schema.prisma ./prisma/schema.prisma
COPY --from=build /app/package.json ./package.json
COPY --from=build /app/package-lock.json ./package-lock.json
COPY --from=build /app/dist ./dist

FROM runtime AS collector
CMD ["node","./dist/collector/server.js"]

FROM runtime AS workers
CMD ["node","./dist/workers/master.js"]

FROM runtime AS api
CMD ["node","./dist/api/server.js"]

FROM runtime AS webhook
CMD ["node", "./dist/webhook/server.js"]

6 replies

PPrisma

•Created by uralsmh on 6/18/2024 in #help-and-questions

prisma is generating some crappy migrations for no reason

Hey folks, Can someone guide me why prims is generating some crappy migrations for no reason? I mean why it deletes the constraint ActionDependencyList_pkey and add it again after?

-- AlterTable
ALTER TABLE "ActionDependencyList" DROP CONSTRAINT "ActionDependencyList_pkey", --->here
ADD COLUMN     "filename" TEXT NOT NULL,
DROP COLUMN "actions",
ADD COLUMN     "actions" TEXT[],
ADD CONSTRAINT "ActionDependencyList_pkey" PRIMARY KEY ("repository_id", "filename");

-- AlterTable
ALTER TABLE "DailyRunnerCost" RENAME CONSTRAINT "CostTable_pkey" TO "DailyRunnerCost_pkey";

-- AddForeignKey
ALTER TABLE "ActionDependencyList" ADD CONSTRAINT "ActionDependencyList_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "Repository"("repository_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AlterTable
ALTER TABLE "ActionDependencyList" DROP CONSTRAINT "ActionDependencyList_pkey", --->here
ADD COLUMN     "filename" TEXT NOT NULL,
DROP COLUMN "actions",
ADD COLUMN     "actions" TEXT[],
ADD CONSTRAINT "ActionDependencyList_pkey" PRIMARY KEY ("repository_id", "filename");

-- AlterTable
ALTER TABLE "DailyRunnerCost" RENAME CONSTRAINT "CostTable_pkey" TO "DailyRunnerCost_pkey";

-- AddForeignKey
ALTER TABLE "ActionDependencyList" ADD CONSTRAINT "ActionDependencyList_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "Repository"("repository_id") ON DELETE RESTRICT ON UPDATE CASCADE;

2 replies

PPrisma

•Created by uralsmh on 5/24/2024 in #help-and-questions

ERROR: column "filename" of relation "ActionDependencyList" contains null values

Sorry for the noob question but I am a bit confused what should I do after this error. Can someone help me please?

npx prisma migrate dev                                                                                                                                                                                                                                                                                                                      ✔  16s   22:16:18  
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "watchtower_db", schema "public" at "localhost:5432"

Applying migration `20240524201618_`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20240524201618_

Database error code: 23502

Database error:
ERROR: column "filename" of relation "ActionDependencyList" contains null values

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E23502), message: "column \"filename\" of relation \"ActionDependencyList\" contains null values", detail: None, hint: None, position: None, where_: None, schema: Some("public"), table: Some("ActionDependencyList"), column: Some("filename"), datatype: None, constraint: None, file: Some("tablecmds.c"), line: Some(6046), routine: Some("ATRewriteTable") }

npx prisma migrate dev                                                                                                                                                                                                                                                                                                                      ✔  16s   22:16:18  
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "watchtower_db", schema "public" at "localhost:5432"

Applying migration `20240524201618_`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20240524201618_

Database error code: 23502

Database error:
ERROR: column "filename" of relation "ActionDependencyList" contains null values

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E23502), message: "column \"filename\" of relation \"ActionDependencyList\" contains null values", detail: None, hint: None, position: None, where_: None, schema: Some("public"), table: Some("ActionDependencyList"), column: Some("filename"), datatype: None, constraint: None, file: Some("tablecmds.c"), line: Some(6046), routine: Some("ATRewriteTable") }

12 replies

Gaming

Programming