Epic: Support Native Database Types

[matthewmueller]

Problem

Today we support the following core data types across databases:

• String
• Boolean
• Int
• Float
• DateTime
• Json

While this is a useful set of data types for getting started, many databases offer a richer set of native database types. For example,

• Postgres offers data types like numeric, date, varchar, smallint, and money.
• MySQL offers data types like blob, varbinary, and set.

These datatypes influence how the data is stored and the operations you can perform on that data. Prisma needs to understand these native database types better across it's tooling.

• Introspect: We understand these native database types, and map them to core data types. There's information loss here.
• Schema: We do not persist these native database types in the schema. For example, a Prisma Int could map to a integer, smallint or bigint.
• Client: We do not take advantage of the additional information native types provide. For example, our Typescript client could use the BigInt Javascript type for the database data type bigint.
• Migrate: We're only able to migrate a narrow set of native database types. For example, we can map Int to integer, but we cannot map Int to bigint. We also cannot currently generate text fields from the String type without more granular data types.
• Studio: Database native types could influence formatting. For example, the money type could be prefixed with $.

Solution

In the last days we looked into this and have reached internal consensus on a proposal.

The proposal is based on the mechanism that a user is decorating a field with an additional attribute that specifies the native data type in the underlying datasource. The attribute is scoped to the underlying datasource by prefixing it with {datasource_name}.{data_type_name}. The scoping is intended for future proofing the schema for support for multiple datasources within one schema.

datasource mydb {
  provider = "postgres"
  url      = "..."
}

model Blog {
  id       Int    @id
  title    String @mydb.VarChar(99)
  someJson Json   @mydb.JsonB
}

Notes

• If the user does not specify a custom type this way the existing default type mapping kicks in.
• A user can easily see which data types are used in the client and
  which one in the datasource because the combination is spelled out explicitly.
• The name of the native type is the Pascal cased version of the original name in the docs of the database. E.g. varchar(x) on Postgres becomes @myb.VarChar(x).
• The user needs to know the compatibility of prisma and datasource types. E.g. a String is compatible with @mydb.VarChar(x) and @mydb.Text. But String is not compatible with @mydb.XML. The parser will validate this and error for wrong combinations. The VS Code extension will be to assist with this through auto completion.
• A native type always maps to exactly one Prisma data type.

Status

1. Done/In Preview Add support for native types in the Schema.
2. Done/In Preview Update Introspect to understand and generate Prisma Schemas with native types.
3. Done/In Preview Add support for native types in Prisma Migrate
4. Done/In Preview Unsupported type: Ensure Prisma Migrate does not drop columns or tables with columns of unsupported types - these columns are also not exposed in Client [Native Types] Ensure that fields of unsupported types aren't dropped #3673
5. Done/In Preview dbgenerated(STRING): Make it possible to encode raw database defaults in the @default attribute of fields
6. Done/In Preview @@ignore attribute for models and @ignore attribute for fields to be managed via Prisma Migrate and introspection but not surfaced in Prisma Client Schema notation to not surface specific models in the Prisma Client API #5217
   Not planned yet
7. Done/In Preview Upgrading Client to take advantage of native types is still up for discussion.
8. Done/In Preview Studio has basic support for native types

Related issues

• Native Types for SQL Server: Supporting native types on SQL Server #4077

Additional context

• There's a lot more discussion about native types in this issue.
• Request for binary: Support for binary type  #1747
• Request for PostGIS: PostGIS/GIS support #2789

[Hebilicious]

The solution looks really clean !
Since the introspection will work before the migration, will it work with geometry columns (ie mapping geojson-like columns to json) ? While I understand the entire spatial database support is outside of the scope of this ticket, this would be nice to have.

Adding support for native types in Migrate will come later. => Any chance you could move this to the next steps ? Unplanned sounds a little bit scary. Imo this is one of the most valuable features to have when trying to push for prisma adoption in a project.

[janpio]

I updated the description from Unplanned to Not planned yet as that better reflects what this should express.

[albertoperdomo]

@Hebilicious We definitely have plans to support these additional native types in migrate. It's just not our current top priority (that's releasing a production-ready version of migrate), but we definitely want to add this capability sooner rather than later as we consider this really important (could potentially be in scope for the GA release).

[chrissisura]

Hey, I'm just wondering if there is a reason, why there is blob support for MySQL but not bytea for Postgres. Is it still on the current roadmap? Or is there any other recommended way, that is better for storing images? Keep up the good work! Cheers

[pantharshit00]

@chrissisura ByteA will be there in the first version. MayI know where did you came to know that we won't support that initially.

[chrissisura]

@pantharshit00 Thanks for clearing that up! I was just confused because ByteA hasn't been mentioned on the Type List mentioned in the initial post nor anywhere on this thread and the subPage on ByteType Support page switched over to this one, so I was afraid the focus was heavier on other types.

[jeliasson]

Extended native database type support is a preview feature in 2.11.0
https://www.prisma.io/docs/concepts/components/preview-features/native-types
https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings

[tsiege]

Would this feature include Postgres's native ltree type? I'm not seeing it here https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings#postgresql-connector and I don't see it listed in this table either https://www.prisma.io/docs/concepts/database-connectors/postgresql#introspection

[chrissisura]

{ mode: "insensitive" } query option not working with PostgresQL UUID native type because "function lower(uuid) does not exist", which is really annoying because it means prisma studio relation browsing (opening in new Tab) doesn't work as they always query with mode: "insensitive" on

[pantharshit00]

@chrissisura Thanks for reporting. I have opened: #5311

We will address that soon.

[albertoperdomo]

In addition to the native types originally in scope and documented here, we are also adding the following types on PostgreSQL:

• Inet, mapped to String, e.g. field String @db.Inet
• Money, mapped to Decimal, e.g. field Decimal @db.Money
• Oid, mapped to Int, e.g. field Int @db.Oid
• Citext, mapped to String, e.g. field String @db.Citext (caveat: requires the Citext extension to be enabled)

[albertoperdomo]

Native types was released in General Availability in 2.17.0. Release notes: https://github.com/prisma/prisma/releases/tag/2.17.0