Best practices for list of Strings in Prisma

Get Help and Help Others
1

Hello RW community,

What’s the recommended for a list of strings inside a prisma Model?

model User {
  id                  Int       @id @default(autoincrement())
  email               String    @unique
  hashedPassword      String    @default("")
  salt                String    @default("")
  resetToken          String?
  resetTokenExpiresAt DateTime?
  favoriteMovies     String[]

It seems like we can’t have something like String[] as above. I am using sqlite.

1 Like
2

From the Prisma docs

Scalar lists (arrays) are only supported in the data model if your database natively supports them. Currently, scalar lists are therefore only supported when using PostgreSQL or CockroachDB (since MySQL and SQLite don’t natively support scalar lists).

So I’m afraid your options are to either switch your underlaying DB, or come up with another way to represent your data.

For your datamodel I’d probably create another model

model FavoriteMovie {
  userId    Int
  user      User
  movieName String
}

Depending on your future needs I’d even consider making a Movie model, so you can keep track of more data on every movie than just their name. And in that case your FavoriteMovie table would link to the Movie table with a movieId and movie just like it does for the user

2 Likes
3

If you don’t need to search against that column or anything, and don’t care about aggregating data or joining with other tables (if you do, then @Tobbe 's solution is best) just JSON.stringify() before storing and JSON.parse() before you use it! Even if you did need to search you still could, you’d just have to do a LIKE query against that column, which won’t be very efficient.

1 Like
4

Thanks @Tobbe and @rob for shedding light and pointers.
Two follow up questions:

  1. Was there a practical reason for picking sqlite as the default? Was it ease of use?
  2. If a FavoriteMovie can be picked by multiple Users, would it make sense to have a ManyToMany relationship between Users and FavoriteMovies Assuming I am using postgress. Something like:
model User {
  id    Int    @id @default(autoincrement())
  name String
  favoriteMovies  FavoriteMovies[]
}

model FavoriteMovies {
  id    Int    @id @default(autoincrement())
  name  String @unique
  users User[]
}

Let me get to working on these ideas now.

5

Yup, you got it. We didn’t want people to have to setup Postgres just to get started with the tutorial.

Yeah, I’d say so. Personally I like to make the join table explicit, but you can totally let Prisma create it for you. I just find it a bit difficult sometimes to remember what names it uses for everything if I ever need to reach in there. Plus, if I need to add some metadata about the connection in the future it’s very easy if the table is explicitly defined in my schema :slight_smile:

1 Like
6

Thanks for the clarification @Tobbe. I need more help :see_no_evil:.
I have almost 0 experience in prisma and backend stuff and hence being slow.

I was able to create the schema in prisma and do the migration.
My Schema looks like

model User {
  id                  Int                    @id @default(autoincrement())
  email               String                 @unique
  hashedPassword      String                 @default("")
  salt                String                 @default("")
  resetToken          String?
  resetTokenExpiresAt DateTime?
  favoriteMovies      FavoriteMovieOnUser[]
}

model FavoriteMovie {
  id    Int                    @id @default(autoincrement())
  name  String                 @unique
  users FavoriteMovieOnUser[]
}

model FavoriteMovieOnUser {
  id               Int            @id @default(autoincrement())
  favoriteMovieId  Int
  favoriteMovie    FavoriteMovie @relation(fields: [favoriteMovieId], references: [id])
  userId           Int
  user             User           @relation(fields: [userId], references: [id])

  @@unique([userId, favoriteMovieId])
}

After creating the schema, I performed the migration via yarn rw prisma migrate dev.

Next, I created three SDLs with.

yarn rw g sdl FavoriteMovie
yarn rw g sdl User
yarn rw g sdl FavoriteMovieOnUser

This in turn created the sdl files as well as the service scripts.

Since the service scripts are already generated, I then utilized the graphql playground in order to play with the database and familiarize myself with it. I am able to create and query stuff from individual tables.

However, one thing I am totally unsure of is creating new entry relationship between tables by utilizing the combination of sdl and service scripts. The default generated ones are very useful but only when you want to make changes to individual tables. Here are the things I am not sure of:

  • I am not clear on how to create relationship between table entries. Let’s say I have a User row and a FavoriteMovie row, how can I created a relationship between these two.
  • Or I want to create a relationship between a User and FavoriteMovie that doesn’t exist yet. What’s the right way of doing this.

Lastly, I am aware that there are ways like pointed here but I wanted to do it the RW WayTM. The same way RW does it in the service and sdl scripts. For example for updating a favorite movie entry we have:
Inside sdl files:

  input UpdateFavoriteMovieInput {
    name: String
  }

  type Mutation {
    updateFavoriteMovie(
      id: Int!
      input: UpdateFavoriteMovieInput!
    ): FavoriteMovie! @skipAuth}
  }

And then inside service scripts:

export const updateFavoriteMovie = ({ id, input }) => {
  return db.favoriteMovie.update({
    data: input,
    where: { id },
  })
}

Thanks and sorry for going verbose here.

7

What RW generates is great to get you started. Like you say, it works great for like changing the name of a movie. But as soon as you start working with relations things get trickier. You’re going to have to start writing your own code that works well with how you design and intend to use your backend. And for this those prisma docs you linked are great.

1 Like
8

Gotcha - then I better familiarize myself with the prisma stuff.
Thanks @Tobbe