How can I save an array of image Urls in MySQL database using Prisma ORM?

Question

I am developing an e-commerce application using Next.js, planetscale(MySQL database) & Prisma ORM.

I have a form using which I can upload images. I am saving these images in Firebase Storage. In return, I receive an array of image Urls. For example, the array I receive from firebase storage looks like this:

['https://firebasestorage.googleapis.com/v0/b/ishop-…=media&token=2633c821-38f3-437b-b905-2995168e3b42', 'https://firebasestorage.googleapis.com/v0/b/ishop-…=media&token=163aaf93-accc-4d64-8e27-92609c14aaaa']

Now, I want to save these images in my database. I am using planetscale for my database and Prisma to interact with my database.

Here is my question:

(1) What should be the field type of the urls field inside the Product model? Here is my schema.json file:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

model Product {
  id          Int      @id @default(autoincrement())
  urls        
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
}

I know that it can't be a scalar list (for example String[]) as MySQL does not support scalar lists.

I also want to know after I decide on the field type of the urls field, should I pay any attention to how I use Prisma to create a record in the database?

Here is my product-upload API route, where I am using the Prisma client to create a record in the database?

import prisma from "../../lib/prisma";

export default async function handler(req, res) {
  if (req.method === "POST") {
    try {
      const { urls } = req.body;

      const product = await prisma.product.create({
        data: { urls },
      });
      res.status(200).json(product);
    } catch (error) {
      res.status(500).json({ message: error.message });
    }
  } else {
    res.setHeader("Allow", ["POST"]);
    res.status(405).json({
      message: `HTTP method ${req.method} is not allowed.`,
    });
  }
}

Please note that the Product model will have other fields, but first, I want to sort this issue first.

Answer 1

You could create a new model to manage the one-to-many relationship between products and urls:

model Product {
  id        Int          @id @default(autoincrement())
  urls      ProductUrl[]
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
}

model ProductUrl {
  url       String
  productId Int
  product   Product @relation(fields: [productId], references: [id], onDelete: Cascade)

  @@id([url, productId])
  @@index([productId])
}

Then to add the URLs you would update your API route to be something like:

import prisma from "../../lib/prisma";

export default async function handler(req, res) {
  if (req.method === "POST") {
    try {
      // assuming urls is of type string[]
      const { urls } = req.body;

      const product = await prisma.product.create({
        data: {
          urls: {
            create: urls.map((url) => ({ url })),
          },
        },
        include: {
          urls: true,
        },
      });
      res.status(200).json(product);
    } catch (error) {
      res.status(500).json({ message: error.message });
    }
  } else {
    res.setHeader("Allow", ["POST"]);
    res.status(405).json({
      message: `HTTP method ${req.method} is not allowed.`,
    });
  }
}