Storing really big strings with Prisma

Say you’re like me and you’ve written a CRUD app using Next.js, with Prisma as your ORM. One of the goals you have is to store big chunks of text. Like, we want someone to be able to shove an entire novel into our database. Not an unreasonable desire!

You may be tempted to reach for the Prisma String scalar type in your model. You are storing strings, after all! And this will work perfectly fine for you when you’re doing your development with SQLite, because Prisma maps this to SQLite’s TEXT type, the length of which is unlimited.

However, if you move to a different database, you’ll likely run into trouble. Prisma’s String maps by default to MySQL’s varchar(191), which is 191 characters. That’s not going to work! Likewise, Postgres’ text caps out at 65,535 bytes, which might be plenty for some, but it’s not enough for what we need it for.

Instead, the straight-forward answer is to use Prisma’s Bytes type. in PostgreSQL, bytea can hold a whole GB of data, and MySQL’s LONGBLOB can hold 4GB. Perfect!

So, in our schema.prisma, if we have:

model Blob {
    id      Int  @id @default(autoincrement())
    data    Bytes
}

All we have to do is create a Buffer from our string when inserting, and then cast our Buffer to a string when getting the data back out. All together:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const run = async () => {
  const item = await prisma.blob.create({
    data: {
      data: Buffer.from("Hello, world", "utf8"),
    },
  });

  const retrievedItem = await prisma.blob.findUnique({
    where: { id: item.id },
  });

  console.log(retrievedItem.data.toString("utf8"));
};

run();

Happy hacking!