imoby Posts - Answer Overflow

imoby

•Created by imoby on 9/22/2023 in #help

Responses are coming back with Capitalized table names

When I run queries and joins, my table names are coming back with uppercase table names which breaks my models and UI that has existed with lowercase further down the chain. Any way to force everything to be lowercase? For example:

[
    {
        "Result": {
            "createdAt": "2023-09-05T11:18:46.140Z",
            "updatedAt": "2023-09-22T10:11:13.182Z"
        },
        "Note": {
            "createdAt": "2023-09-05T11:22:08.511Z"
        },
        "Alert": {
            "createdAt": "2023-09-05T11:18:50.264Z",
            "updatedAt": "2023-09-13T09:35:20.743Z"
        }
    }
]

[
    {
        "Result": {
            "createdAt": "2023-09-05T11:18:46.140Z",
            "updatedAt": "2023-09-22T10:11:13.182Z"
        },
        "Note": {
            "createdAt": "2023-09-05T11:22:08.511Z"
        },
        "Alert": {
            "createdAt": "2023-09-05T11:18:50.264Z",
            "updatedAt": "2023-09-13T09:35:20.743Z"
        }
    }
]

2 replies

DTDrizzle Team

•Created by imoby on 9/21/2023 in #help

Help migrating a nested Prisma update

Been trying to set this up without going through the raw sql route for some time but cant seem to wrap my head around it. Basically the structure here is I have a Result that can optionally have an alert or a note. If an alert or note already exist then update them. Any help here is appreciated it! thank you

 const updatedResult: Result = await prisma.result
    .update({
      where: {
        id: resultId,
      },
      data: {
        reviewedBy: resultData.reviewedBy,
        note: {
          upsert: {
            create: {
              reason: resultData.reason,
              patientId: resultData.patientId,
              detail: resultData.detail,
            },
            update: {
              reason: resultData.reason,
              detail: resultData.detail,
            },
          },
        },
        alert: alertId
          ? {
              update: {
                acknowledged: true,
              },
            }
          : undefined,
      },
      include: {
        note: true,
        alert: true,
      },
    })

 const updatedResult: Result = await prisma.result
    .update({
      where: {
        id: resultId,
      },
      data: {
        reviewedBy: resultData.reviewedBy,
        note: {
          upsert: {
            create: {
              reason: resultData.reason,
              patientId: resultData.patientId,
              detail: resultData.detail,
            },
            update: {
              reason: resultData.reason,
              detail: resultData.detail,
            },
          },
        },
        alert: alertId
          ? {
              update: {
                acknowledged: true,
              },
            }
          : undefined,
      },
      include: {
        note: true,
        alert: true,
      },
    })

14 replies

DTDrizzle Team

•Created by imoby on 9/19/2023 in #help

Best practice on running migrations

I have a serverless framework setup and during my testing I've been using the db push but now that I have something setup to actually test my migrations I'd like to run:

await migrate(db, { migrationsFolder: "drizzle" });

await migrate(db, { migrationsFolder: "drizzle" });

whats the best path to actually running this from an npm script or from a ci for example. Or even locally without using push. Basically, I've generated my sql migrations but I now need to run them.

16 replies

DTDrizzle Team

•Created by imoby on 9/18/2023 in #help

Help migrating raw query from prisma to drizzle

Trying to get the below query which has multiple joins into drizzle. It's one of the last ones I have and I am not sure if I should go the route of query or select. Query didnt have or show any docs on joins. Whats the optimal way to do this and is it possible to do also the count in the same query or will that still have to be an extra or separate?

  const results: any = await prisma.$queryRaw`
    SELECT COUNT(*) OVER()::integer AS count, 
             u.id as "userId",
       u."name", 
       u."plan",
       s.id AS "serviceId", 
       s."createdAt", 
       s."updatedAt", 
       s."name", 
       s."proposals" AS "serviceProposals",
       s."ack", 
       d."type",
             d."data"
    FROM "User" u
    LEFT JOIN "Directive" d ON u."userId" = d."id"
    LEFT JOIN "Service" s ON d."serviceId" = s."id"
    WHERE s."ack" = false
    AND u."plan" IN (${Prisma.join(plans)})
        ORDER BY s."createdAt" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

  const results: any = await prisma.$queryRaw`
    SELECT COUNT(*) OVER()::integer AS count, 
             u.id as "userId",
       u."name", 
       u."plan",
       s.id AS "serviceId", 
       s."createdAt", 
       s."updatedAt", 
       s."name", 
       s."proposals" AS "serviceProposals",
       s."ack", 
       d."type",
             d."data"
    FROM "User" u
    LEFT JOIN "Directive" d ON u."userId" = d."id"
    LEFT JOIN "Service" s ON d."serviceId" = s."id"
    WHERE s."ack" = false
    AND u."plan" IN (${Prisma.join(plans)})
        ORDER BY s."createdAt" ASC
    LIMIT ${take} OFFSET ${skip}
  `;

2 replies

DTDrizzle Team

•Created by imoby on 9/16/2023 in #help

Manually create migration file

Is it possible to manually create a migration file like the way prisma has it? basically just creating a sql file that is part of the migration but it does not get executed. I'm asking this because I want to create a migration file for a raw materialized view since views arent supported

13 replies

DTDrizzle Team

•Created by imoby on 9/12/2023 in #help

Unable to generate uuid as primary key

Hey everyone! I've searched through the help posts with similar titles but I could not get the answer I wanted. I have a prisma current setup, and trying to switch over, but my uuid is generated as my postgres primary key but I keep getting the following error:

"null value in column \"id\" of relation \"User\" violates not-null constraint"

"null value in column \"id\" of relation \"User\" violates not-null constraint"

I've tried all of the following:

export const patient = pgTable('User', {
  id: uuid('id')
    .default(sql`uuid_generate_v7()`)
    .primaryKey()
    .defaultRandom()

export const patient = pgTable('User', {
  id: uuid('id')
    .default(sql`uuid_generate_v7()`)
    .primaryKey()
    .defaultRandom()

  id: uuid('id').defaultRandom().primaryKey(),

  id: uuid('id').defaultRandom().primaryKey(),

and even the migration introspection tool set my id as this:

  id: text('id').primaryKey().notNull(),

  id: text('id').primaryKey().notNull(),

which definitely didnt work. Any ideas?

17 replies

DTDrizzle Team

•Created by imoby on 9/11/2023 in #help

Upsert create/update joined table similar to Prisma

Hey all, trying to get another one of my queries to migrate away from prisma. This one is a bit more complex. Trying to see if I am able to take a joined table and either update it or create it. Prisma has this as a nested option but I couldnt figure out how to solve this with drizzle. In the example below, I have a store that I want to update its joined table shelf or create it if its not there

await prisma.store
      .update({
        where: {
          id: store.id,
        },
        data: {
          shelf: {
            upsert: {
              create: {
                shelfName: data.shelfName,
              },
              update: {
                shelfName: data.shelfName,
              },
            },
          },
        },
      })

await prisma.store
      .update({
        where: {
          id: store.id,
        },
        data: {
          shelf: {
            upsert: {
              create: {
                shelfName: data.shelfName,
              },
              update: {
                shelfName: data.shelfName,
              },
            },
          },
        },
      })

4 replies

DTDrizzle Team

•Created by imoby on 9/10/2023 in #help

Similar building of where clause to Prisma

I'm trying to migrate my prisma queries to drizzle but one thing I'm trying to do is convert the following. I'm lost on how best to 'build' a where clause similar to what prisma has depending on what properties I have passed into my function:

 if (name) {
    whereClause.name = {
      contains: name,
      mode: 'insensitive',
    };
  }
  if (address) {
    whereClause.address = {
      contains: address,
      mode: 'insensitive',
    };
  }
  if (birthday) {
    whereClause.birthday = birthday;
  }
  if (phone) {
    whereClause = {
      ...whereClause,
      OR: [
        {
          phone,
        },
        { alternatePhone: phone },
      ],
    };
  }
  const person = await prisma.person
    .findMany({
      where: whereClause,
    })

 if (name) {
    whereClause.name = {
      contains: name,
      mode: 'insensitive',
    };
  }
  if (address) {
    whereClause.address = {
      contains: address,
      mode: 'insensitive',
    };
  }
  if (birthday) {
    whereClause.birthday = birthday;
  }
  if (phone) {
    whereClause = {
      ...whereClause,
      OR: [
        {
          phone,
        },
        { alternatePhone: phone },
      ],
    };
  }
  const person = await prisma.person
    .findMany({
      where: whereClause,
    })

3 replies

Gaming

Programming