RickDom Posts - Answer Overflow

RickDom

•Created by RickDom on 7/23/2024 in #help

Is there a way to create a unique constrain on a jsonb column in a table?

I have a table with a "label" column that is a jsonb column storing objects that look like this: { "en": "Girl", "ja": "女性" }. I would like to set up the unique constraint to ensure that each row is unique based on label->>en at least. Is this possible when defining the schema? Or do I need to restort to brute forcing it in the SQL? I am using Drizzle ORM with node postrgesql.

12 replies

DTDrizzle Team

•Created by RickDom on 5/8/2024 in #help

Inserting into tables with foreign key relationships

I have two tables: "part_of_speech" and "type". part_of_speech.type_id is a foreign key column referencing type.id. I want to insert a row into "part_of_speech" and set the "typeId" based on the "id" corresponding to a value from another column on the "type" table based on some filter. So far I have managed to do this in typescript land:

const verbType = (
    await dbClient
      .select({ typeId: types.id })
      .from(types)
      .where(sql`label->>'en-US' = 'Verb'`)
  )[0];

  const insertedPos = await dbClient
    .insert(partsOfSpeech)
    .values([
      {
        typeId: verbType!.typeId,
        value: '食べる',
        translation: jsonbSql({ 'en-US': 'Eat' }),
      },
      {
        typeId: verbType!.typeId,
        value: '読む',
        translation: jsonbSql({ 'en-US': 'Read' }),
      },
    ])
    .onConflictDoNothing()
    .returning();

const verbType = (
    await dbClient
      .select({ typeId: types.id })
      .from(types)
      .where(sql`label->>'en-US' = 'Verb'`)
  )[0];

  const insertedPos = await dbClient
    .insert(partsOfSpeech)
    .values([
      {
        typeId: verbType!.typeId,
        value: '食べる',
        translation: jsonbSql({ 'en-US': 'Eat' }),
      },
      {
        typeId: verbType!.typeId,
        value: '読む',
        translation: jsonbSql({ 'en-US': 'Read' }),
      },
    ])
    .onConflictDoNothing()
    .returning();

However, is this not possible somehow using db.$with(...).as(...) and db.with(...).insert(...).values(...) structure where I could reference the result column from the "$with" query in the values for each row I am inserting? I was trying to do this and seems logical, but was getting type errors (and while I know that there should be a single result returned in the with, drizzle doesn't know so an array will always be returned). is there a proper way with drizzle to do this? Or do you have to fall back to doing query(...) and writing SQL?

1 replies

Gaming

Programming