kinsyu Posts - Answer Overflow

kinsyu

Explore posts from servers

TtRPC TTCTheo's Typesafe Cult DTDrizzle Team

•Created by kinsyu on 5/25/2024 in #help

onConflictDoUpdate excluded with a dynamic column

Hey, I'm trying to perform an insert where it's possible that the record already exists, therefore I'm using onConflictDoUpdate, the problem I'm running into is that I can't dynamically set the column to update, here's my current approach:

export async function editGroupSettings(
  db: Db,
  option: string,
  groupId: number,
  userId: string,
  value: number,
) {
  await db
    .insert(groupAlertsConfigTable)
    .values({
      groupId: groupId,
      userId: userId,
      [option]: value,
    })
    .onConflictDoUpdate({
      target: [groupAlertsConfigTable.mafiaId, groupAlertsConfigTable.userId],
      set: {
        [option]: sql`EXCLUDED."${option}"`,
      },
      where: and(
        eq(groupAlertsConfigTable.groupId, groupId),
        eq(groupAlertsConfigTable.userId, userId),
      ),
    });
}

editGroupSettings(db, "minMCap", 1, "ry73wkah5ojhl76k", 2);

export async function editGroupSettings(
  db: Db,
  option: string,
  groupId: number,
  userId: string,
  value: number,
) {
  await db
    .insert(groupAlertsConfigTable)
    .values({
      groupId: groupId,
      userId: userId,
      [option]: value,
    })
    .onConflictDoUpdate({
      target: [groupAlertsConfigTable.mafiaId, groupAlertsConfigTable.userId],
      set: {
        [option]: sql`EXCLUDED."${option}"`,
      },
      where: and(
        eq(groupAlertsConfigTable.groupId, groupId),
        eq(groupAlertsConfigTable.userId, userId),
      ),
    });
}

editGroupSettings(db, "minMCap", 1, "ry73wkah5ojhl76k", 2);

I'm getting the following error when executing this

PostgresError: column excluded.$4 does not exist

PostgresError: column excluded.$4 does not exist

4 replies

DTDrizzle Team

•Created by kinsyu on 12/1/2023 in #help

There's not enough information to infer relation

I am trying to use drizzle studio but I'm running into the following issue, There is not enough information to infer relation "__public__.collectionsTable.tokens". Here's a simplified version of the schema.

export const tokensTable = mysqlTable(
    'tokens',
    {
        tokenId: varchar('token_id', { length: 255           }).notNull(),

        metadataName: varchar('metadata_name', {               length: 255 }),
        metadataDescription:         text('metadata_description'),
        collectionId: varbinary('collection_id', {
            length: 42,
        }).notNull(),

        createdAt: timestamp('created_at', {
            mode: 'string',
        }).defaultNow(),
        updatedAt: timestamp('updated_at', {
            mode: 'string',
        }).defaultNow(),
    }
)

export type Token = InferSelectModel<typeof tokensTable>
export type TokenInsert = InferInsertModel<typeof tokensTable>

export const tokensRelations = relations(tokensTable, ({ one }) => ({
    collection: one(collectionsTable, {
        fields: [tokensTable.collectionId],
        references: [collectionsTable.id],
    }),
}))

export const collectionsTable = mysqlTable(
    'collections',
    {
        id: varbinary('id', {
            length: 42,
        }).primaryKey(),

        name: varchar('name', {
            length: 255,
        }).notNull(),
        description: text('description'),
        createdAt: timestamp('created_at', {
            mode: 'string',
        }).defaultNow(),
        updatedAt: timestamp('updated_at', {
            mode: 'string',
        }).defaultNow(),
    }
)

export type Collection = InferSelectModel<typeof collectionsTable>
export type CollectionInsert = InferInsertModel<typeof collectionsTable>

export const collectionsRelations = relations(collectionsTable, ({ one, many }) => ({
    tokens: many(tokensTable, {
        relationName: 'collectionTokens',
    }),
}))

export const tokensTable = mysqlTable(
    'tokens',
    {
        tokenId: varchar('token_id', { length: 255           }).notNull(),

        metadataName: varchar('metadata_name', {               length: 255 }),
        metadataDescription:         text('metadata_description'),
        collectionId: varbinary('collection_id', {
            length: 42,
        }).notNull(),

        createdAt: timestamp('created_at', {
            mode: 'string',
        }).defaultNow(),
        updatedAt: timestamp('updated_at', {
            mode: 'string',
        }).defaultNow(),
    }
)

export type Token = InferSelectModel<typeof tokensTable>
export type TokenInsert = InferInsertModel<typeof tokensTable>

export const tokensRelations = relations(tokensTable, ({ one }) => ({
    collection: one(collectionsTable, {
        fields: [tokensTable.collectionId],
        references: [collectionsTable.id],
    }),
}))

export const collectionsTable = mysqlTable(
    'collections',
    {
        id: varbinary('id', {
            length: 42,
        }).primaryKey(),

        name: varchar('name', {
            length: 255,
        }).notNull(),
        description: text('description'),
        createdAt: timestamp('created_at', {
            mode: 'string',
        }).defaultNow(),
        updatedAt: timestamp('updated_at', {
            mode: 'string',
        }).defaultNow(),
    }
)

export type Collection = InferSelectModel<typeof collectionsTable>
export type CollectionInsert = InferInsertModel<typeof collectionsTable>

export const collectionsRelations = relations(collectionsTable, ({ one, many }) => ({
    tokens: many(tokensTable, {
        relationName: 'collectionTokens',
    }),
}))

In short, each collection can have multiple tokens, but each token can belong to only one collection. I haven't had any issues with this schema for around the 6 months that we've been using it, but we wanted to try out drizzle studio and ran into that issue. The database is running on Planetscale, not sure if that's relevant.

3 replies

Gaming

Programming