Will Posts - Answer Overflow

Will

Explore posts from servers

•Created by Will on 9/8/2023 in #help

Prepared Statement - Neon Syntax Error

Hi there, I am trying to use a prepared statement to run a query but am getting a syntax error. below you can see the .toSql() logged, as well as the error.

{
  sql: 'select "user_id", "advertiser_id", "partner_id", "display_name", "user_role", "email", "role_id", "created_at", "lastAudited", "lastAuditedBy", "status" from "dv360_user_profiles" where ("dv360_user_profiles"."partner_id" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) and "dv360_user_profiles"."status" = $12) limit $13',
  params: [
    '7', '3',
    '0', '1',
    '6', ',',
    '2', '4',
    '5', '8',
    '9', 'flagged',
    100
  ]
}
NeonDbError: db error: ERROR: syntax error at or near "$1"

Caused by:
    ERROR: syntax error at or near "$1"

{
  sql: 'select "user_id", "advertiser_id", "partner_id", "display_name", "user_role", "email", "role_id", "created_at", "lastAudited", "lastAuditedBy", "status" from "dv360_user_profiles" where ("dv360_user_profiles"."partner_id" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) and "dv360_user_profiles"."status" = $12) limit $13',
  params: [
    '7', '3',
    '0', '1',
    '6', ',',
    '2', '4',
    '5', '8',
    '9', 'flagged',
    100
  ]
}
NeonDbError: db error: ERROR: syntax error at or near "$1"

Caused by:
    ERROR: syntax error at or near "$1"

here is my prepared statement:

export const userRolesPreparedStatement = db
  .select()
  .from(dv360UserProfilesTable)
  .where(
    and(
      inArray(
        dv360UserProfilesTable.partnerId,
        sql.placeholder("uniquePartnerIds")
      ),

      eq(dv360UserProfilesTable.status, "flagged")
    )
  )
  .limit(100)
  .prepare("userRoles");

export const userRolesPreparedStatement = db
  .select()
  .from(dv360UserProfilesTable)
  .where(
    and(
      inArray(
        dv360UserProfilesTable.partnerId,
        sql.placeholder("uniquePartnerIds")
      ),

      eq(dv360UserProfilesTable.status, "flagged")
    )
  )
  .limit(100)
  .prepare("userRoles");

I don't see anything wrong with my prepared statement. Can anyone spot what I'm doing wrong or let me know if this is a bug? Much appreciated.

2 replies

DTDrizzle Team

•Created by Will on 9/4/2023 in #help

ERROR: prepared statement "s9656" does not exist

11 replies

DTDrizzle Team

•Created by Will on 8/29/2023 in #help

How to use a composite primary key in WHERE?

I have a table dv360UserProfilesTable which uses a composite key of userId and assignedUserProfileId. If I want to update this table, I'm struggling with how to use the composite key to batch update profiles. The below code throws the error: db error: ERROR: argument of WHERE must be type boolean, not type record The docs also do not show an example of this https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key

const updatedProfilesResponse = await db
    .update(dv360UserProfilesTable)
    .set({
      status: status,
      lastAuditedAt: new Date(),
      lastAuditedBy: session.user.id.toString(),
    })
    .where(
      or(
        profiles.map((profile: Profile) => {
          return and(
            eq(dv360UserProfilesTable.userId, profile.userId),
            eq(
              dv360UserProfilesTable.assignedUserRoleId,
              profile.assignedUserRoleId
            )
          );
        })
      )
    )
    .returning();

const updatedProfilesResponse = await db
    .update(dv360UserProfilesTable)
    .set({
      status: status,
      lastAuditedAt: new Date(),
      lastAuditedBy: session.user.id.toString(),
    })
    .where(
      or(
        profiles.map((profile: Profile) => {
          return and(
            eq(dv360UserProfilesTable.userId, profile.userId),
            eq(
              dv360UserProfilesTable.assignedUserRoleId,
              profile.assignedUserRoleId
            )
          );
        })
      )
    )
    .returning();

4 replies

DTDrizzle Team

•Created by Will on 8/18/2023 in #help

How would you write this REPLACE query?

UPDATE Examples
SET prompt = REPLACE(prompt, 'textToReplaceFrom', 'textToReplaceWith')
WHERE id IN [array of ids];

UPDATE Examples
SET prompt = REPLACE(prompt, 'textToReplaceFrom', 'textToReplaceWith')
WHERE id IN [array of ids];

I'm trying something like this:

 await db.execute(
sql`UPDATE ${examplesTable} SET prompt = REPLACE(${
  examplesTable.prompt
}, ${replaceTextFrom}, ${replaceTextTo}) WHERE ${
  examplesTable.id
} IN (${selected.included})`

 await db.execute(
sql`UPDATE ${examplesTable} SET prompt = REPLACE(${
  examplesTable.prompt
}, ${replaceTextFrom}, ${replaceTextTo}) WHERE ${
  examplesTable.id
} IN (${selected.included})`

but am getting this error: NeonDbError: db error: ERROR: argument of WHERE must be type boolean, not type record I don't really understand why the where is not type boolean here because I'm using an IN statement.

3 replies

DTDrizzle Team

•Created by Will on 8/17/2023 in #help

Connecting to Neon through Drizzle via Cloud Function

Hi there! I have a nextjs app that is hooked up to neon via drizzle. I want to use some cloud functions to do some additional data transfer into the DB. 1. Does anyone have any advice on how to set up a cloud function with Drizzle? 2. How would you go about sharing the schema between the cloud function and the next app? I'm thinking I'll need to set up some sort of monorepo like turborepo but I don't know what the deal is with putting a google cloud function in a turborepo. Thanks!

1 replies

DTDrizzle Team

•Created by Will on 8/3/2023 in #help

How do I import a type?

This might be something I'm just totally overlooking, but I'm used to prisma where you can just import any schema you've defined as a type. For example, if I have a table posts, I'd expecect to be able to import a Post type to be used in one of my child components. Is this possible with Drizzle? Or have you all found it to be unnecessary

14 replies

Gaming

Programming