Help with queryRawUnsafe

Hey all,

I have a query that previously worked that started to fail. Based off documentation, this is expected since the column names are dynamically generated when using query raw. Can I get help on converting it to a queryRawUnsafe?

Here is a sampe query of what I am trying to convert:

import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;

import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;

I have tried following this implementation, but ran into unexpected trailing spaces error:

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);

Should I add a validation steps the trims each value or do we have a better approach?

Prisma•11mo ago•

6 replies

Bluestopher

Help with queryRawUnsafe

import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;

import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;

I have tried following this implementation, but ran into unexpected trailing spaces error:

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);

Should I add a validation steps the trims each value or do we have a better approach?

Help with queryRawUnsafe

Similar Threads

Help with queryRawUnsafe

Similar Threads

Similar Threads

Similar Threads