Prisma #orm-help

stuart Lynn

09/12/2022, 3:41 PM

Hey all. Looking for some help here. I am trying to implement a polymorphic relation in prisma with postgresql. I have the following models • User : a user • App : an App • Dateset: a Datasets • Collaborator: A user with permissions to edit, view etc either an App or a Dataset I have tried to set this up two different ways. First, the simpler looks like

Copy code

enum ResourceType {
  App
  Dataset
}

model App {
  id            String         @id @default(cuid())
  spec          Json
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @default(now())
  name          String
  description   String
  public        Boolean
  ownerId       String
  noViews       Int            @default(0)
  noForks       Int            @default(0)
  owner         User           @relation(fields: [ownerId], references: [id])
  collaborators Collaborator[] @relation("AppColaborator")

  @@map("apps")
}

model Dataset {
  id            String         @id @default(cuid())
  createdAt     DateTime       @default(now())
  name          String
  description   String
  public        Boolean
  path          String
  ownerId       String
  owner         User           @relation(fields: [ownerId], references: [id])
  collaborators Collaborator[] @relation("DatasetColaborator")

  @@map("datasets")
}


model User {
  id             String         @id @default(cuid())
  name           String?
  apps           App[]
  collaborations Collaborator[]
  Datasets       Dataset[]
  @@map("users")
}


model Collaborator {
  id           String       @id @default(cuid())
  user         User         @relation(fields: [userId], references: [id])
  userId       String
  appId        String?
  datasetId    String?
  resourceType ResourceType
  view         Boolean
  edit         Boolean
  manage       Boolean
  app          App?         @relation("AppColaborator", fields: [appId], references: [id], map: "app_colaboration_id")
  dataset      Dataset?     @relation("DatasetColaborator", fields: [datasetId], references: [id], map: "dataset_colaboration_id")

  @@unique([userId, appId, datasetId])
  @@map("collaborators")
}

The issue with this is if I try and create or upsert a Collaborator object, I cant because one of the appId or datasetId needs to be undefined which prisma rejects. The other approach I have tried is to have a single resourceId with a resourceType that defines what kind of resource this is and define two relationships like so

Copy code

model Collaborator {
  id           String       @id @default(cuid())
  user         User         @relation(fields: [userId], references: [id])
  userId       String
  resourceId   String
  resourceType ResourceType
  view         Boolean
  edit         Boolean
  manage       Boolean
  app          App?         @relation("AppColaborator", fields: [resourceId], references: [id], map: "app_colaboration_id")
  dataset      Dataset?     @relation("DatasetColaborator", fields: [resourceId], references: [id], map: "dataset_colaboration_id")
  @@unique([userId, resourceId])
  @@map("collaborators")
}

However that fails when I try to create a new collaborator because both foreign key constraints for datasetId and appId cant both be satisfied. Is there a good model or example for doing this kind of thing? A little stumped here

✅ 1

Sim Kai Chien

09/12/2022, 3:47 PM

Hi guys, Is it possible to have multiple schemas for a collection via prisma? Like something done via mongoose below: var schemaOne = new Schema({ "stype": { type: String, default: "one" }, "myid": String, "f1": String }); var schemaTwo = new Schema({ "stype": { type: String, default: "two" }, "myid": String, "f2": String }); var One = mongoose.schema('one', schemaOne, 'coll'); var Two = mongoose.schema('two', schemaTwo, 'coll');

✅ 1

Kay Khan

09/12/2022, 3:54 PM

When using mongodb is it possible to set the keepAlive, keepAliveInitialDelay on the underlining mongodb driver? https://stackoverflow.com/questions/62718415/what-is-the-use-of-nodejs-mongodb-driver-keepalive-option#:~:text=When%20keep%2[…]flagged%20as%20failed

✅ 1

Samrith Shankar

09/12/2022, 3:55 PM

Hello, any update on this issue? https://github.com/prisma/prisma/issues/15029 I am stuck with deployment due to this

👀 1

Yaniv Efraim

09/12/2022, 3:56 PM

Hello guys, I’m trying to use a 1 to 1 relation in my Prisma model. I have a limitation in which I cannot use a foreign key in my database. I created the relation as in the docs:

Copy code

model tagged_artifact_assets {
  artifact_id String @db.VarChar(128)
  tag         String @db.VarChar(32)
  app_def_id  String @db.VarChar(32)
  config_id   String @db.VarChar(32)
  config configs? //This is a relation to configs table
  @@id([artifact_id, tag])
  @@unique([artifact_id, tag])
}

model configs {
  config_id String @id @db.VarChar(32)
  value     Json
  artifacts tagged_artifact_assets @relation(fields: [artifact_id, tag], references: [artifact_id, tag])
  artifact_id String @db.VarChar(32)
  tag String @db.VarChar(32)
  @@unique([artifact_id, tag])
}

The relation is

tagged_artifact_assets

configs

. When trying to

create

, I get:

Copy code

The column `artifact_id` does not exist in the current database.GrpcStatusError: UNKNOWN:
    Invalid `prisma.configs.create()` invocation:

I guess that this is because I do not have a foreign key named

artifact_id

in `configs`… So, • Is it possible to have 1to1 relation with no foreign key? • If not, how can I use

join

for

select

statment? Thanks, and sorry for the long post 🙏

👀 1

Robert Lee

09/12/2022, 5:55 PM

my guess without reading any code is that

User

probably has null built into its type.

Samrith Shankar

09/12/2022, 6:12 PM

Anyone here figured out how to make Prisma library available as a lambda layer when deploying to Serverless locally?

✅ 1

Isaac Curtiss

09/12/2022, 7:53 PM

I have a few tables and I’m getting an error that I don’t quite follow

Copy code

model Item {
  id       String @id @default(cuid()) 
  age    String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  publishers  Publisher[]

}

Model Publisher {
  id            String @id @default(cuid()) 
  name          String @unique

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  media Item[]
}

When I try to create/upsert an Item, I get the error that publisher

name

is not unique (

Unique constraint failed on the fields: ("name")

. However, I’m using

connectOrCreate

within the upsert which means (in my understanding) if the current transaction has found that

name

already exists it should

connect

it (and not try to create it, which might be causing the error). Regardless, I’m trying to figure out where I’m wrong or what I can do to investigate more but so far I don’t understand how to solve this. I’d appreciate any help/thoughts from anyone 👍 (I have tried regenerating and that has not made a difference)

✅ 1

Tommy Jeon

09/12/2022, 8:11 PM

Is there a way to set up referential actions to work in both directions? have two models

Copy code

model Foo {
   id      String     @id @default(auto()) @map("_id") @db.ObjectId
   title   String
   bar     Bar?
}

model Bar {
   id      String     @id @default(auto()) @map("_id") @db.ObjectId
   fooId   String     @db.ObjectId
   foo     Foo        @relation(fields: [fooId], references: [id], onDelete: Cascade)
}

But when I delete an instance of Bar, the related Foo still remains in the database. Does this referential rule only work in one direction? I.e. only when you delete Foo, the related Bar will delete? How can I make it so deleting a Bar instance cascades to Foo? I tried adding a

@relation(onDelete: Cascade

to the Foo schema, but that triggered a prisma schema error.

✅ 1

Hubert Kowalski

09/12/2022, 10:01 PM

I have two interconnected models, I'll oversimplify them:

Copy code

model Wallet {
  user User
  balance Int
  transactions Transaction[] 
}

model Transaction {
  id Int 
  change Int
  wallet Wallet 
}

and now, I want to make a query that: creates a Transaction and increments the connected

wallet.balance

by the

change

, but I have no idea if it's even possible.

✅ 1

Dave Edelhart

09/12/2022, 10:49 PM

if you have an enum

Copy code

enum EDU_TYPE {
  COLLEGE
  SKILL
}

model edu {
  abbr       String       @id @db.Char(3)
  type       EDU_TYPE     @default(SKILL)
  name       String
  notes      String
  aspect     String       @db.Char(1)
  edu        edu?         @relation(name: "parent_edu", fields: [parent], references: [abbr])
  children   edu[]        @relation("parent_edu")
  general    Boolean      @default(false)
  parent     String?      @db.Char(3)
  person_edu person_edu[]
}

I’m using nest and it has a pattern of a typescript def for the “data” value; what “type” of data should I use for the data I save to edu? ex:

Copy code

export class CreateEduDto {
  abbr: string;
  name: string;
  notes: string;
  aspect: string;
  type: string; // <-- doesn't work
  general: boolean;

}

Dave Edelhart

09/13/2022, 1:49 AM

getting an error - had some elements with id’s set to varchar(3) and I changed them to varchar(4) and now I’m getting this:

Copy code

Error: P3006

Migration `20220913013923_expanded_abbr_size` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: cannot drop constraint edu_pkey on table edu because other objects depend on it
DETAIL: constraint person_edu_abbr_fkey on table person_edu depends on index edu_pkey
HINT: Use DROP ... CASCADE to drop the dependent objects too.
   0: sql_migration_connector::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:272
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:250

👀 1

David Hancu

09/13/2022, 6:35 AM

👋 Hi everyone! Got a new feature idea for Prisma Util and I was wondering if anyone would be interested in it: Database Triggers. You can setup triggers at a database level that run you queries configured in your Prisma Util config. Let me know if you'd be interested in this feature. Other News Cross-file relations are exiting the Experimental program and becoming fully fledged features (v1.3.0). If interest is shown in Database Triggers, they'll be added in v1.3.0. v1.3.0 is also going to transition to a JS config rather than JSON, to make code-generated schemas implementations easier. Code-generated schemas and alternative methods for configuring Prisma are also confirmed for v1.4.0.

💯 1

prisma cool 1

✅ 1

prisma rainbow 2

Slackbot

09/13/2022, 8:15 AM

This message was deleted.

Oleksii Rumiantsev

09/13/2022, 10:44 AM

Hello everyone! When I do this:

Copy code

this.prisma.message.updateMany({
  where: {
    chat_id: chatId,
    sender: {
      not: sender,
    },
    created_at: {
      lte: lastReadMessageCreatedAt,
    },
  },
  data: {
    read_at: new Date(),
  },
});

Prisma firstly selects all the message ids for messages which match the query, and then updates messages with

IN (...message ids)

approach

Copy code

UPDATE `message` SET `read_at` = '2022-09-13 07:47:00.288000' WHERE `message`.`id` IN ('ecdf8353-ce11-44ff-891c-8491bf1f736b','ecdfac6a-325a-4b6d-9425-e16d4064c467','ecdfb9a1-ba26-4e81-8e56-a5daf0dfab5b','ece0b1d7-388d-4cbd-8b5a-06628d39a6b9','ece220d8-f8b9-4fd6-ac05-2844d60685aa','ece32bf9-f867-46f9-b901-7692e41ba67f','ece84045-0fb1-478b-8b82-82db4273ae49','ece898dd-a299-4df8-88c3-61e20fd7087a','ecead321-3ec3-4901-861c-d39b2decee6a','ecebee42-255c-4210-9934-db2b2ac857e6','ecec2422-b48b-4825-a8b8-1ca30de678cc','ecee5f77-63ba-45d9-829d-0e293d1780fe', ... and so on)

Is it possible to make such an update using one query without using queryRaw? Also, would be nice, if you share the reason for this behavior) Thanks

✅ 1

Kay Khan

09/13/2022, 12:59 PM

I wonder if anyone has used the prisma middleware to create a retry requests to the database? Is this a valid usecase?

Copy code

PrismaService.$use(async (params, next) => {
    console.log(params);
    let c = 0;
    let response;
    while (c < 5) {
        console.log("count", c);
        try {
            const result = await next(params);
            response = result;
            break;
        } catch (err) {
            c++;
            console.log(err);
            response = err;
        }
    }
    return response;
});

✅ 1

Gaurish

09/13/2022, 5:55 PM

Hello people, I'm having this issue (using prisma for the first time) My model is as such:

Copy code

model Reminder {
  userId String 
  id String @id @default(uuid()) @unique
  content String
  endsAt DateTime
}

Error: ⚠️ We found changes that cannot be executed: • Step 0 The required column

id

was added to the

Reminder

table with a prisma-level default value. There are 1 rows in this table, it is not possible to execute this step. Please add this column as optional, then populate it before making it required. You can use prisma migrate dev --create-only to create the migration file, and manually modify it to address the underlying issue(s). Then run prisma migrate dev to apply it and verify it works.

✅ 1

Raiyan Sarker

09/13/2022, 5:56 PM

Hi everyone, this is the first time, I am using Prisma in a production environment. I am having an issue. The build process inside docker is not working, it is giving this error,

👀 1

Raiyan Sarker

09/13/2022, 5:57 PM

> [builder 8/8] RUN yarn build:

Raiyan Sarker

09/13/2022, 5:57 PM

#14 0.326 yarn run v1.22.19 #14 0.354 $ tsc -p . #14 9.478 src/Post/post.constroller.ts(120,13): error TS7006: Parameter 'error' implicitly has an 'any' type. #14 9.478 src/auth/createOrUpdateUser.ts(4,10): error TS2305: Module '"@prisma/client"' has no exported member 'Provider'. #14 9.479 src/auth/createOrUpdateUser.ts(4,20): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.479 src/auth/index.ts(6,10): error TS2305: Module '"@prisma/client"' has no exported member 'Provider'. #14 9.479 src/auth/sign.ts(4,10): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.479 src/helper/auth/anyUser.ts(1,10): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.479 src/helper/auth/authenticatedUser.ts(1,10): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.479 src/helper/auth/authorOrAdminUser.ts(1,10): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.479 src/helper/auth/authorUser.ts(1,10): error TS2305: Module '"@prisma/client"' has no exported member 'Role'. #14 9.666 info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command. #14 9.666 error Command failed with exit code 2.

Raiyan Sarker

09/13/2022, 5:58 PM

I think it is due to prisma generate not working properly, but the build works outside of docker

Raiyan Sarker

09/13/2022, 6:00 PM

Here are some details

Raiyan Sarker

09/13/2022, 6:00 PM

https://gist.github.com/raiyansarker/54cc1529207369acd716ce59fda82dda

Ted Joe

09/13/2022, 8:38 PM

I'm trying to change a column type from an enum to an array of enums. When I run

prisma migragte dev

, or even with the flag

--create-only

, I get an error:

Error: P3006

Migration
20220913190558_updated_user_types_type
failed to apply cleanly to the shadow database.

Error:

db error: ERROR: column "userType" cannot be cast automatically to type "USER_TYPE"[]

HINT: You might need to specify "USING "userType"::"USER_TYPE"[]".

0: sql_migration_connector::validate_migrations

at migration-engine/connectors/sql-migration-connector/src/lib.rs:270

1: migration_core:state:DevDiagnostic

at migration-engine/core/src/state.rs:250

✅ 1

David Hancu

09/14/2022, 6:16 AM

Hi everyone! I'm currently testing the code generated schemas for Prisma Util and I need some opinions on this format:

Copy code

SchemaCreator
    .model("User")
        .column("id", "Int")
        .column("name", "String")
        .column("posts", "Post[]")
    .model("Post")
        .column("id", "Int", Constraints.Column.ID(), Constraints.Column.DEFAULT(Functions.AUTOINCREMENT()))
        .column("title", "String", Constraints.Column.UNIQUE())
        .column("userId", "Int")
        .column("user", "User", Constraints.Column.RELATION({fields: ["userId"], references: ["id"]}))
    .build();

Mattèo Gauthier

09/14/2022, 7:39 AM

Did you add the env variable DATABASE_URL in your vercel project ?

Kyle Gammon

09/14/2022, 8:08 AM

Hi all, what graphql query pattern should be used for when I want to return all records where either the

where

filter against a particular field matches or that field is empty/null. I've been using this pattern (this is a pseudo schema, but take it that the

User

model being queried would have a field named

tags

which is an Array of a

Tag

model):

query SampleQuery($tagIds: [ID!]!) { users(where: { OR: [ { tags_every: { id_in: $tagIds } }, { tags_every: { id: null } } ] }) { id }

Which seemed to do the job in Prisma 1 but when migrating over to more recent versions the query can end up crashing the client. I was wondering if there was a more efficient way of doing this that may be able to avoid using this more complex pattern?

👀 1

David Hancu

09/14/2022, 9:24 AM

👋 Hello everyone! I'm yet again asking for some opinions on another feature idea for Prisma Util: Support for

pg_trgm

in the Prisma client! For those who don't know,

pg_trgm

allows fuzzy matching in PostgreSQL databases. As for implementation ideas, Prisma Util will generate an SQL query to support this feature, then run your migrations. Then, it will add

@Unsupported("TSVECTOR")

to your fields and create GIN or GiST (configured per field) indexes, as well as install

pg_trgm

. As for usage, I'd need some suggestions for it.

✅ 1

Lewis

09/14/2022, 11:32 AM

Copy code

import prisma from './prisma';
import { getOrCreateUser } from "utils";
import type { Prisma } from '@prisma/client';

export class Stats {
    static async update(id: string, stat: Prisma.StatsScalarFieldEnum, amount: number) {
        await getOrCreateUser(id);
        return await prisma.stats.update({
            where: {
                user_id: id,
            },
            data: {
                [stat]: {
                    increment: amount,
                }
            }
        });
    }
}

Hey, need a little bit of assistance here! I'm just wondering if this is the correct way of getting my column field names (Prisma.StatsScalarFieldEnum)? I saw this is how some other people do this, is there a better solution or am I being really dumb?

✅ 1

Ted Joe

09/14/2022, 1:41 PM

I deleted a migration folder locally, and reset my local database. Now when I run

yarn prisma migrate deploy && yarn data-migrate up

on production, I get the following error:

migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: <https://pris.ly/d/migrate-resolve>

`The

folderName

migration started at 2022-09-12 112150.636936 UTC failed`

👀 1