Setting a more specific database type in Prisma

Prisma is a nice ORM, you can define your schema and it works backwards to write the migration needed to make your database reflective of your system.

The problem is, often our business logic has a looser definition of a data type than our database can have. For example, most applications only care about strings in as far as they are a collection of characters, maybe with a maximum or minimum length. It is perfectly reasonable in Prisma to have a schema that says something like;

model User {
  ...
  name String
  ...
}

If we were adding this attribute to our model, the resulting migration would look like this;

-- AlterTable
ALTER TABLE "users" ADD COLUMN "name" TEXT;

At a quick glance this seems fine, we run the migration and get on with our day.

The problem is, TEXT is a bit wishy-washy when it comes to databases, it is a big blob of characters that can be pretty massive, or just a couple. Because of this how they are represented and queried is a little different to say VARCHAR(30) where the database knows the string can only be a maximum of 30 characters.

In the Prisma docs for String, you can see the defaults for String, and under it, the native database types you could set instead.

If in our schema we wrote the following, we would get a more specific migration file created;

model User {
  ...
  name String @db.VarChar(60)
  ...
}

This creates something more like;

-- AlterTable
ALTER TABLE "users" ADD COLUMN "name" VARCHAR(60);

Why not to do this

I’m going to say 90% of the time it is worth understanding the specific type you want your database to represent, it makes things so much easier, but there are times when you maybe don’t want this.

The first that springs to mind is if you need to support an unknown set of databases. Looking back at the docs you will notice a difference between MySQL and Postgres, writing something specific for Postgres won’t work for MySQL. If you aren’t sure, it is better to go with a default.