Drizzle Team•2mo ago

Nested select in an insert

Hi, is there a better way to use nested select inside an insert than to just use the sql function? For example I have this function:

  static async upsert(idPerson: number, studyType: StudyType) {
    const idStudyTypeSql = sql`(select id_study_type from study_type where study_type = ${studyType})`;
    await db
      .insert(student)
      .values({
        idPerson,
        idStudyType: idStudyTypeSql,
      })
      .onConflictDoUpdate({
        target: student.idPerson,
        set: {
          idStudyType: idStudyTypeSql,
        },
      });
  }

  static async upsert(idPerson: number, studyType: StudyType) {
    const idStudyTypeSql = sql`(select id_study_type from study_type where study_type = ${studyType})`;
    await db
      .insert(student)
      .values({
        idPerson,
        idStudyType: idStudyTypeSql,
      })
      .onConflictDoUpdate({
        target: student.idPerson,
        set: {
          idStudyType: idStudyTypeSql,
        },
      });
  }

It works well, but by using the sql I lose the benefit of typescript and I have to execute the query to verify that it works as expected. If I, for example, use non-existing column in the sql, I won't get any warnings. I could create a separate query to get the id_study_type, but I would prefer to have just one database call if possible.

    const idStudyType = (
      await db
        .select({ id: studyType.idStudyType })
        .from(studyType)
        .where(eq(studyType.studyType, studyTypeEnum))
    )[0]?.id;

    const idStudyType = (
      await db
        .select({ id: studyType.idStudyType })
        .from(studyType)
        .where(eq(studyType.studyType, studyTypeEnum))
    )[0]?.id;

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Nested select in an insert