Prisma get data with foreign key in sqlite DB

Question

I have an SqlLite database that contains these models

model Product {
  id                    Int      @id @unique(map: "sqlite_autoindex_products_1") @default(autoincrement())
  label                 String
  description           String?
  price                 Int
  category_id           Int
  thumbnail_url         String?
  categories            Category @relation("categoriesToproducts", fields: [category_id], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@map("products")
}

model Category {
  id          Int       @id @unique(map: "sqlite_autoindex_categories_1") @default(autoincrement())
  index       Int?
  label       String
  description String?
  products    Product[] @relation("categoriesToproducts")

  @@map("categories")
}

I would like to get products list using :

async allProducts(): Promise<Product[] | null> {
  return this.prisma.product.findMany();
}

It returns Products raw data with category_Id field.

I would like to know if there is a way to get category mapped data inside Category table instead of only the id field ?

I have a solution is to get Category table data than loop on products list then replace category_id with the category object.

But I would like to know if there is a better way to map directly data when making request via Prisma client.

Answer 1

In order to query a related model, you have to either use select or include.

prisma.product.findMany({
  include: {
    categories: true
  }
})

// or

prisma.product.findMany({
  select: {
    categories: true
  }
});

Useful documentation links

• Relations
• Relation queries