Custom data types for fields

[vchrm]

Hi, our company is moving from PHP to Node ecosystem and I am impressed by this project. Great work and great thinking especially! <3

Problem

However, there is one thing that feels natural to me when mapping raw rows from database to sophisticated and type-safe objects: custom data types. I spent the last three hours reading Prisma's manual and googling related issues but I don't see any open issue about this topic.

Use cases:

1. I found issues related to unsupported data types like Support a Date Scalar Type  #255 or Add support for 64-bit integers #5001.

2. In our application we use data types like currencies, IBANs, time zone offsets, IP addresses, EANs, locale territories, VAT numbers, and many others that are usually stored as simple chars/varchars in the database, but are represented with a more refined data type in the application (like some class instance).

3. You might want to use better alternatives for TIMESTAMP or DATETIME types than JavaScript's native Date class and map such database types to eg. a Luxon instance.

Suggested solution

All three of these cases can be handled by allowing to define a custom column data type that:

• is represented by some native type in the database (for migrations),
• is implemented as a custom deserialization function (for selects) and serialization function (for inserts/updates) by the application.

In PHP we use Doctrine and this is what I have in mind: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/cookbook/custom-mapping-types.html . (Yes, Doctrine is a full-blown ORM library but IMO this idea can be used nevertheless.)

Thanks to this feature people would be able to create own custom mappings for column types that have not been supported by Prisma yet (as a temporary workaround), as well as use some eg. "IP address" type without the need for explicit in-application mapping (from the object returned from Prisma to object with better data types) for better developer experience.

The closest ideas I found: #446 (comment) (if you add the (de)serialization mapping functions), #3618 (could be solved by custom data types), prisma/specs#119 (replaced by #446 which I don't think is the same).

What we would need:

1. A way how to define the custom data type in schema (something like this):

type VatNumber = String @custom("vatNumber") @db.Char(11)

model Company {
  id        Int       @default(autoincrement()) @id
  name      String    @unique
  vatNumber VatNumber
}

2. A way how to inject the custom vatNumber type serialization and deserialization functions to Prisma's attributes mapping functionality.

The expected behavior is that when reading some company row from the database, the vatNumber attribute in the object is going to be some eg. VatNumber class instance (as implemented by the deserialization function). Also, when inserting/updating I am going to be able to use the VatNumber instance directly without needing to serialize it to a string at first.

So what do you think? Is it something that is completely out of the scope of this project? Or would you consider this to be something useful? Or is there some other issue with the same idea that I simply missed?

Thanks! :)

PS: The first thing I naturally tried to do was to use the VatNumber type in the schema to see what happens and I got this error:

error: Type "VatNumber" is neither a built-in type, nor refers to another model, custom type, or enum.

That has got me thinking: what did you mean by this "custom type" in the error message? ;-)

[livthomas]

I would also very much appreciate if you implemented this feature. I have various use cases for this feature but the most pressing one at the moment is this:

• I use SQLite database in my application and I don't like the way it stores dates. They are being stored as numeric timestamps and It's hard to see what day it is when I open the DB directly in some DB tool. Especially, when the only thing I care about is date (without any time). Now, I have to define it as String type in Prisma schema. It would be nice to have an ability to define a mapper which would convert it to Date in JS but store it in a TEXT field in ISO date format.

[massivefermion]

+1 for custom types. Would really improve schema design!

[scriptcoded]

This isn't something I've felt the need for before, but as soon as I read your suggestion I saw a lot of solutions to some pretty annoying problems I have today.

It seems to me that this and #3394 has a little bit of common ground. Both modify the response after the data has been read from the database, but this is more of a read/write while the virtual fields would (probably) be read only. Perhaps it's insignificant.

Secondly, if you're daring enough you could probably already do this with middleware. They can be expensive on performance if used incorrectly, but sounds like just the right tool if anyone need to solve this urgently.

[jekh]

Custom serialization and deserialization into/out of javascript objects would be fantastic. TypeORM has an analogous feature called a "transformer".

I've used TypeORM in the past with the Postgres UUID type to remove dashes from the UUID in an application that didn't support them. Postgres treats all UUIDs as integers internally, so the representation on the wire and in app code is somewhat arbitrary, and having the ability to customize that by removing dashes, forcing uppercase/lowercase, using a BigInt instead of a string, or even something like Shakespearean UUIDs, would be handy.

Another use case might be mapping TC39's Temporal date and time values in application code to/from database types. For example, storing a civil date in a database as an ISO8601 string in a TEXT column (e.g. the string "2022-01-01"), but using a Temporal.PlainDate in the prisma-generated client code, with custom serialization/deserialization.

[felipe-gustavo]

+1 for custom types, this will helps on the transformers cases, working with pipes to control field transformation, currently I am accepting workarounds for it :)

[joseviniciusnunes]

+1

[madmoizo]

An other common use case: File field. A file is saved on a third party service on create/update and the pathname is persisted in the database.
Currently I see no other way than having to list all the "File" fields in my code (not suitable at all) and check in a middlewarre if data contains one of those. (if you know a better way, let me know!)

[reinaldorauch]

+1

[SimonCockx]

Similar use cases:

type UUID = String @id @default(uuid()) @db.Uuid

type Money = Decimal @default(0) @db.Decimal(10, 2)

[Uniphix]

This is one of the reasons why I haven't switched over from Sequelize because I can at least create custom types there such as I have a Snowflake custom type that transforms and I have a method where if it is a primary key with auto increment that it uses a third party library to generate the ids etc. So it would be nice to be able to not only be able to create the custom types but be able to create specific rules that would run through a transformer for example. "if I specify auto increment with a custom type, I can then hook in the third party that generates these "autofields".

[shtse8]

+1, bump this. we need this very much.
example use case

type Url = String @db.VarChar(2000)
type Md5 = String @db.Char(32)

so, one day, if we need to move from mysql to other engine.
we only need to modify this custom types without go through the whole long schema.

[toddhow]

+1 This would make dealing with Snowflakes(Datatype) a lot easier

[LinusU]

I would love this as well!

As an example, we currently use MongoDB and are migrating to Prisma/Postgres. In our current types we have phoneNumber typed as `+${number}` | 'redacted' in order to force every part of our application to deal with removed phone numbers. We want this in Prisma as well.

To be clear, we are not asking for a way to do any serialising/deserialising. We only want to use a subset of the string type, or a subset of the number type (e.g. Swedish VAT can only be 0 | 6 | 12 | 25).

I'm not sure if that's exactly what being asked here, so maybe I should open a separate issue for this...

[kbanman]

Another nudge on this one.

In my case I'd love to have typed identifiers for each of my entities:

type UserId = `usr-${string}`;

So when defining the prisma model, I'd like to not only alias a database type, but also customize the generated native type. I recognize that this becomes complicated as the generated types would either need to include the above type definition or import it (probably the former).

[Hetch3t]

Any news from maintainers on the matter?

[janpio]

No. The feature request is being tracked and the issue might be prioritized in the future. Right now it is not.

[pksunkara]

In my honest opinion, an ORM not prioritizing a vital issue like this is akin to shooting oneself in the foot. There are a lot of things an ORM needs to get right before considered production ready and this is one of them.

It's currently impossible to work with non-uuid IDs like nanoid, ksuid, xid, ulid etc... because of this issue.

[matheusvellone]

+1 on this issue too

In my case I would like to use Phantom Types to narrow ID types, so I get type errors when trying to assign a postId to a userId, when both models have a string type

I'll probably end up creating custom types on top of Prisma ones, but it would be nice to accomplish this natively with Prisma

// Emitted types from Prisma

type UserPrisma = {
  id: string
  name: string
}

type PostPrisma = {
  id: string
  title: string
}

type ID<Type extends string | number, Model> = Type & { __idFor: Model }

type User = Omit<UserPrisma,'id'> & {
  id: ID<UserPrisma['id'], User>
}

type Post = Omit<PostPrisma,'id'> & {
  id: ID<PostPrisma['id'], Post>
}

const userId = 'user_1' as User['id']

const findPost = (id: Post['id']): Post => {
  return { id, title: 'Phantom Types' }
}

findPost(userId)

But this doesn't work very well bc Prisma query outputs are the "basic" ones

[confuzeus]

I'm surprised that Prisma doesn't have this feature because most ORMs I've used have it.

Anyway, my solution for now is to use the Repository pattern so that I can de-serialize the data in a single place and consume it in my application, then also serialize it for storage in a single place as well.

It's a little more work than using the ORM directly but the only way I could think of that doesn't require complicated gymnastics.

[KATT]

Drizzle has this now and it's really nice.

Prisma client extensions work well for changing the result of the output value, but not really great for writes.

API proposal

It could be a part of Prisma client extensions

// create a custom type
const dateToTemporalInstant = Prisma.createCustomType<Date, Temporal.Instant>({
  encode: (value) => toInstant(value),
  decode: (value) => toJsDate(value),
});

// add it to `Post.createdAt` + `Post.updatedAt`
export const prisma = client.$extends({
  customTypes: {
    post: {
      createdAt: dateToTemporalInstant,
      updatedAt: dateToTemporalInstant,
    },
  },
});