I previously posted a question here about creating a list of Strings in a database. @Tobbe suggested a many-to-many relationship. This way one could add other properties to this relationship. I also talked with @dthyresson during the office hour last Thursday and he made some good points about testing the many-to-many relational tables and validating the logic inside seed.js before making any database call. I finally was able to add, remove, and modify entries on the table. I am going to post the database and the way I am running my mutations and queries so that I can get some feedback. Please take it from someone who has almost zero experience in databases.
Hopefully others can also benefit in the future.
First off, the database schema is defined as
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
binaryTargets = "native"
}
model User {
id Int @id @default(autoincrement())
email String @unique
hashedPassword String @default("")
salt String @default("")
resetToken String?
resetTokenExpiresAt DateTime?
favoriteMetrics FavoriteMetricOnUser[]
}
model FavoriteMetric {
id Int @id @default(autoincrement())
name String @unique
users FavoriteMetricOnUser[]
}
model FavoriteMetricOnUser {
id Int @id @default(autoincrement())
favoriteMetricId Int
favoriteMetric FavoriteMetric @relation(fields: [favoriteMetricId], references: [id])
userId Int
user User @relation(fields: [userId], references: [id])
@@unique([userId, favoriteMetricId])
}
FavoriteMetricOnUser is the table that connects the User model to the FavoriteMetric model.
Next step: Migration with yarn rw prisma migrate dev
Next step: Creating sdl and service scripts with
-
yarn rw g sdl User -
yarn rw g sdl FavoriteMetric -
yarn rw g sdl FavoriteMetricOnUser
The generated service and sdl scripts will have 80% of the necessary utilities to interact with the database. The challenging part was creating/updating the relationship between User and FavoriteMetric. Here are three of the main difficulties I had in regards to querying and mutating in the database.
- Query a user’s id and get back all user properties along with their favoritemetrics.
User sdl needs to be modified to
type favorite {
id: Int!
name: String!
}
type User {
id: Int!
email: String!
hashedPassword: String!
salt: String!
resetToken: String
resetTokenExpiresAt: DateTime
// Adding favorites as a new property so that I can fetch it from user service
favorites: [favorite]
}
In the users’ service, the request happens as
export const user = async ({ id }) => {
const userInfo = await db.user.findUnique({
where: { id },
include: { favoriteMetrics: { include: { favoriteMetric: true } } },
})
// post processing the result
const result = {
id: userInfo.id,
email: userInfo.email,
favorites: userInfo.favoriteMetrics.map((item) => item.favoriteMetric),
}
return result
}
- Create a new relationship between User and FavoriteMetric.
The favoriteMetrics sdl gets updated as follows
input CreateFavoriteMetricInput {
name: String!
userId: Int!
}
type Mutation {
createFavoriteMetric(input: CreateFavoriteMetricInput!): FavoriteMetric!
@skipAuth}
Another update needs to happen inside favoritemetrics’ service section. This will create a new favoritemetric row if it doesn’t exit. If it already exists, it just connects that row to the user with id specified in the input.
export const createFavoriteMetric = ({ input }) => {
const connetCreateMetric = db.favoriteMetric.upsert({
where: { name: input.name },
create: {
name: input.name,
users: {
connectOrCreate: [
{
create: { userId: input.userId },
where: { id: input.userId },
},
],
},
},
update: {
users: {
connectOrCreate: [
{
create: { userId: input.userId },
where: { id: input.userId },
},
],
},
},
})
- Remove a relationship between User and FavoriteMetric.
This one took me a bit longer to figure out. The favoriteMetricOnUsers sdl script gets modified as:
input DeleteFavoriteMetricOnUser {
name: String!
userId: Int!
}
type Mutation {
deleteFavoriteMetricOnUser(
input: DeleteFavoriteMetricOnUser!
): FavoriteMetricOnUser! @skipAuth
}
On the service side, we first fetch the id of the favoritemetric (since we just know the name of the metric) and then delete the corresponding row in the relationship table.
export const deleteFavoriteMetricOnUser = async ({ input }) => {
const favMetric = await db.favoriteMetric.findUnique({
where: { name: input.name },
})
return db.favoriteMetricOnUser.delete({
where: {
userId_favoriteMetricId: {
userId: input.userId,
favoriteMetricId: favMetric.id,
},
},
})
}
Lastly, if someone wants to check the database before writing the service script, as @dthyresson pointed, try to run the queries and mutations inside seed.js via yarn rw prisma db seed. It’s much faster and keeps you moving.
