Prisma 2.16.0 — Release Notes & Engineering Impact

Today, we are excited to share the `2.16.0` stable release 🎉 🌟 **Help us spread the word about Prisma by starring the repo or [tweeting](https://twitter.com/intent/tweet?text=Check%20out%20the%20latest%20Prisma%20release%202.16.0%20%F0%9F%9A%80%0D%0A%0D%0Ahttps://github.com/prisma/prisma/releases/tag/2.16.0) about the release.** 🌟  ## Major improvements ### The Prisma CLI moves from the `@prisma/cli` to the `prisma` npm package Going forward, you can install the Prisma CLI via the `prisma` npm package: ``` npm install prisma --save-dev ``` or ``` yarn add prisma --dev ``` The reason for this is that a number of users were experiencing issues when running the `npx prisma` command. Without a local installation of the `@prisma/cli` package, this would invoke the Prisma 1 CLI leading to an error message. From now on, `npx prisma` is _always_ going to work. We will also deprecate the `@prisma/cli` package. Please do a _find and replace_ across your codebase to transition over from `@prisma/cli` to `prisma`. To make this transition easier, we'll keep publishing updates to both packages for another month and plan to stop updating the `@prisma/cli` package with release `2.18.0`. There are no changes to the `@prisma/client` npm package name. ### Efficient bulk creates with `createMany` Insert data a whole lot faster with `createMany`. Here's an example: ```ts const result = await prisma.user.createMany({ data: [ { email: "[email protected]" }, { email: "[email protected]" }, { email: "[email protected]" }, { email: "[email protected]" }, ], }); console.log(`Created ${result.count} users!`); ``` This feature is in preview right now. Enable it with the `createMany` preview flag: ```prisma generator client { provider = "prisma-client-js" previewFeatures = ["createMany"] } ``` 📚 **Documentation**: [Create multiple records](https://www.prisma.io/docs/concepts/components/prisma-client/crud#create-multiple-records) Learn more in this [issue](https://github.com/prisma/prisma/issues/4998#issuecomment-766742955). ### Order by relation fields with `orderBy` Ever wish you could order posts by an author's name? Or sort transactions by account? Now you can! We've expanded `orderBy` to support ordering by relations: ```ts cons posts = await prisma.post.findMany({ orderBy: [ { author: { name: "asc", }, }, ], }); ``` This feature is in preview right now. Enable it with the `orderByRelation` preview flag: ```prisma generator client { provider = "prisma-client-js" previewFeatures = ["orderByRelation"] } ``` 📚 **Documentation**: [Sort by relation](https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#sorting) Learn more in this [issue](https://github.com/prisma/prisma/issues/5008#issuecomment-768444693). ### Improvements to the `nativeTypes` Preview feature We are working hard on making the Prisma experience smoother for folks that are using the `nativeTypes` Preview feature and are getting close to releasing it for General Availability. If you have any feedback for this feature, please share it on [GitHub](https://github.com/prisma/prisma/issues/4045). #### New `Unsupported` type allows to surface _any_ database type in the Prisma schema This release introduces a new `Unsupported` type in the Prisma schema. It acts as an _escape hatch_ and allows to surface fields in Prisma schema for database types that are not yet supported by Prisma. For example, MySQL's `POLYGON` type is not yet natively supported by Prisma, but can now be added to the Prisma schema using the `Unsupported("polygon")` type. The new type is especially relevant during introspection where previously fields of unsupported types would have been commented out, they're now included in the Prisma schema and of type `Unsupported`. Fields of the `Unsupported` type are added to the Prisma schema in either of two ways: - via _introspection_ in cases when Prisma detects a database column with a type that is not yet natively supported by Prisma - _manually_ in combination with Prisma Migrate; in this case Prisma Migrate generates a SQL migration with the type that's provided as an argument to `Unsupported` in the Prisma schema Note that fields of type `Unsupported` will **not** be surfaced in the Prisma Client API. <!-- However, they can be accessed using plain SQL queries submitted with `$queryRaw`. --> ##### Example with Prisma Migrate Here's an example of the new `Unsupported` type that uses MySQL's [`multilinestring`](https://dev.mysql.com/doc/refman/8.0/en/gis-linestring-property-functions.html): ```prisma generator client { provider = "prisma-client-js" previewFeatures = ["nativeTypes"] } datasource db { provider = "mysql" url = env("DATABASE_URL") } model User { id Int @id @default(autoincrement()) email String @unique name String? bio Unsupported("multilinestring") @unique } ``` Prisma Migrate generates the following SQL for the `User` model: ```sql CREATE TABLE `User` ( `id` INTEGER NOT NULL AUTO_INCREMENT, `email` VARCHAR(191) NOT NULL, `name` VARCHAR(191), `bio` multilinestring NOT NULL, UNIQUE INDEX `User.email_unique`(`email`), UNIQUE INDEX `User.bio_unique`(`bio`), PRIMARY KEY (`id`) ); ``` ##### How `Unsupported` fields affect the Prisma Client API Since Prisma Client doesn't yet "understand" the `Unsupported` type and therefore cannot read or write to its fields, we've also disabled creates and upserts on models that have a required `Unsupported` field without a default value. This prevents constraint violations at runtime. To make this explicit, a new `@@ignore` attribute is added to the models that contain fields of type `Unsupported` during introspection: ```prisma model User { id Int @id @default(autoincrement()) location Unsupported("point") @@ignore } ``` ```ts // Not permitted because the database requires a `location`, but it can't be // provided because the `point` type is unsupported by Prisma Client. const user = await prisma.user.create({}); ``` If the `@@ignore` attribute is added to a model, the relation fields of that model on other models will be annotated with the `@ignore` attribute, for example: ```prisma model User { id Int @id @default(autoincrement()) location Unsupported("point") @@ignore } model Post { id Int @id @default(autoincrement()) title String author User @ignore } ``` #### New notation for default values via `dbgenerated()` `dbgenerated()` in the `@default` field directive can now take a `String` argument that enables developers to reflect database-level default values that are not yet natively supported by Prisma. This changes the signature of the attribute from `dbgenerated()` to `dbgenerated(default: String)`. As an example, you can use `@default(dbgenerated("''"))` to define an empty string as the default value for a column in MySQL. These default values will be surfaced when introspecting the database or created/changed when defined manually in combination with Prisma Migrate. This feature can be used alongside the new `Unsupported` type as well as with regular Prisma types. ##### Examples with Prisma Migrate _Setting a default value on a `multilinestring` column_: ```prisma generator client { provider = "prisma-client-js" previewFeatures = ["nativeTypes"] } datasource db { provider = "mysql" url = env("DATABASE_URL") } model User { id Int @id @default(autoincrement()) email String @unique name String? bio Unsupported("multilinestring") @unique @default(dbgenerated("''")) } ``` Prisma Migrate generates the following SQL for the `User` model: ```sql CREATE TABLE `User` ( `id` INTEGER NOT NULL AUTO_INCREMENT, `email` VARCHAR(191) NOT NULL, `name` VARCHAR(191), `multilinestringField` multilinestring NOT NULL DEFAULT '', UNIQUE INDEX `User.email_unique`(`email`), UNIQUE INDEX `User.bio_unique`(`bio`), PRIMARY KEY (`id`) ); ``` _Setting a default value for UUIDs via the `pgcrypto` PostgreSQL extension_: ```prisma model User { id String @id @db.Uuid @default(dbgenerated("gen_random_uuid()")) name String? } ``` Prisma Migrate generates the following SQL for the `User` model: ```sql CREATE TABLE "User" ( "id" UUID NOT NULL DEFAULT gen_random_uuid(), "name" TEXT, PRIMARY KEY ("id") ); ``` Note that the example above requires the corresponding extension to be enabled (in this case: `CREATE EXTENSION "pgcrypto";`). #### New native type attributes available on PostgreSQL We've added new native types for the `postgresql` provider. Here's an overview of the new type mappings: | PostgreSQL | Prisma | Note | | ---------- | --------- | ------------------------------- | | `Inet` | `String` | | | `Money` | `Decimal` | | | `Oid` | `Int` | | | `Citext` | `String` | Requires the `Citext` extension | Here's an example that uses the four new types in a Prisma schema: ```prisma generator client { provider = "prisma-client-js" previewFeatures = ["nativeTypes"] } datasource db { provider = "postgresql" url = env("DATABASE_URL") } model User { id Int @id @default(autoincrement()) ipAddress String? @db.Inet balance Decimal @db.Money oid Int @db.Oid citextName String @db.Citext } ``` Prisma Migrate generates the following SQL for the `User` model: ```sql CREATE TABLE "User" ( "id" SERIAL NOT NULL, "ipAddress" INET, "balance" MONEY NOT NULL, "oid" OID NOT NULL, "citextName" CITEXT NOT NULL, PRIMARY KEY ("id") ); ``` #### More changes to the `nativeTypes` Preview feature - When using the `nativeTypes` Preview feature, introspection will only render a native attribute if the column type does _not_ map to Prisma's corresponding default type. This might remove any existing type annotations upon (re-)introspection where the default mapping is currently in place. - **MySQL** - Columns of type `BIT(1)` can now be mapped to Prisma's `Boolean` type as well using the `Bit` native type attribute, e.g. `myField Boolean @db.Bit(1)`. - Default mapping for Prisma's `Float` type has been changed from `DECIMAL(65,30)` to `Double`. - **PostgreSQL** - Default mapping for Prisma's `Float` type has been changed from `DECIMAL(65,30)` to `Double`. - **SQLite** - Default mapping for Prisma's `Decimal` type has been changed from `REAL` to `DECIMAL`. <details><summary>Expand for some more info about how this affects current users</summary> ##### PostgreSQL and MySQL users - If no action is taken, the next time a new migration is created with `prisma migrate dev`, Prisma Migrate will generate DDL statements to change columns of Prisma's type `Float` to use `Double` instead of `Decimal`. These changes may lead to rounding errors due to differences in precision. - If changing the underlying column is not desired, users can specify `Decimal` and continue using that as as the column type. - Steps involved: 1. make sure the preview feature flag for native types is enabled 2. Change the field to be of scalar type `Decimal` 3. use the native type notation to pin the underlying type to `Decimal` or re-introspect your database with prisma introspect after enabling native types - Example before: `myField Float` - Example after: `myField Decimal @db.Decimal(65,30)` ##### SQLite - If you were using `Decimal` before on SQLite, with the native type feature flag enabled, Prisma Migrate will change the type in the next migration from `REAL` to `DECIMAL`. - This can be avoided by changing the scalar type in the Prisma schema from `Decimal` to `Float`, or running re-introspection which will do the same. </details> <!-- ### Skip native type syntax for default types during (re-)introspection When re-introspecting a database and the native type preview feature flag is enabled, Prisma will not render the native type notation for fields that are using Prisma's default column types. It will: - omit (or remove) the native type notation for any field which is using the default mapping, e.g. `name String?` for a field of type `varchar(191)` on MySQL (new behavior) - render (or ad the native type notation for all other fields, e.g. `name String? @db.VarChar(50)` for a field of type `varchar(50)` on MySQL (previous behavior) --> ### `queryRaw` Breaking Changes On MySQL _only_, fields of type `Boolean` are stored as `TINYINT(1)` in the database. We no longer coerce the value to a Boolean. That means `queryRaw` now returns integers of value `0` or `1` instead of `true` or `false` for `Boolean` fields. ### Host Prisma Studio on Vercel You can host your own instance of Prisma Studio on Vercel by following the instructions in the README of this [example repo](https://github.com/prisma/studio-vercel-guide). ### More improvements - You can now skip seeding when calling `prisma migrate dev` or `prisma migrate reset` with the `—-skip-seed` flag. - You can now use the `--schema` option on the `prisma db seed` command to execute the command with a custom Prisma schema location. ### Prisma Client Go gets dynamic filters Give your users more control over how they filter and order their data. Build up filters over time instead of all at once. Here's an example of setting specific attributes dynamically: ```go func CreateUser(w http.ResponseWriter, r *http.Request) { var params []db.UserSetParam email := r.PostFormValue("email") kind := r.PostFormValue("kind") if kind == "customer" { // Set the referrer for users of type customer only params = append(params, db.User.Referer.Set(r.Header.Get("Referer")) } user, err := client.User.CreateOne( db.User.Kind.Set(kind), db.User.Email.Set(email), params..., ).Exec(r.Context()) // ... Handle the response } ``` Learn more in the [Go documentation](https://github.com/prisma/prisma-client-go/blob/master/docs/reference/16-dynamic-queries.md). ## Fixes and improvements ### Prisma Client - [Improve Prisma Client error handling](https://github.com/prisma/prisma/issues/3756) - [TypeScript deploy "Cannot find module `.prisma/client`"](https://github.com/prisma/prisma/issues/5266) - [Environmental variables conflict](https://github.com/prisma/prisma/issues/4789) - [Inconsistent DMMF for relation lists](https://github.com/prisma/prisma/issues/4808) - [querying an integer from a mysql tinyint(1) column fails](https://github.com/prisma/prisma/issues/4981) - [Efficiently create many records](https://github.com/prisma/prisma/issues/4998) - [Support Order by Relations](https://github.com/prisma/prisma/issues/5008) - [PANIC: 1](https://github.com/prisma/prisma/issues/5073) - [PANIC: 1, select same model twice in different levels](https://github.com/prisma/prisma/issues/5092) - [PANIC: called `Result::unwrap()` on an `Err` value: Could not parse stored DateTime string: 2020-11-27 01:17:30 (input contains invalid characters)](https://github.com/prisma/prisma/issues/5096) - [Narrow type for `rejectOnNotFound`](https://github.com/prisma/prisma/issues/5190) - [Remove misleading "in use" numbers from Connection Pool timeout error message](https://github.com/prisma/prisma/issues/5228) - [`count` query not working in react-prisma](https://github.com/prisma/prisma/issues/5271) ### Prisma Migrate - [Error: Error in migration engine. Reason: [C:\Users\runneradmin\.cargo\git\checkouts\quaint-9f01e008b9a89c14\6efc6f6\src\connector\postgres\error.rs:138:29] index out of bounds: the len is 1 but the index is 1 ](https://github.com/prisma/prisma/issues/4606) - [Better error message when seeding if dependencies are missing](https://github.com/prisma/prisma/issues/5182) - [DB seed does not work with custom schema location](https://github.com/prisma/prisma/issues/5213) - [Do not render specific native notation when the underlying column matches the default mapping.](https://github.com/prisma/prisma/issues/5218) - [Improve Migrate automatic panic reports by including the command ran](https://github.com/prisma/prisma/issues/5239) - [Introspection: Remove comments for unsupported fields](https://github.com/prisma/prisma/issues/5309) - [Improvement: Optionally skip seeding during migrate reset](https://github.com/prisma/prisma/issues/5317) ### Language tools (e.g. VS Code extension) - [Support for Unsupported, dbgenerated() and @@ignore](https://github.com/prisma/language-tools/issues/679) ### Prisma Studio - [Feature Request: Run prisma studio remotely](https://github.com/prisma/studio/issues/601) - [`Open in new tab` for relation lists does not work as expected](https://github.com/prisma/studio/issues/612) ### Prisma Engines - [Change default mapping of scalar type `Float`](https://github.com/prisma/prisma-engines/issues/1287) - [Query Engine is generating `min` and `max` aggregate for Json types to DMMF](https://github.com/prisma/prisma-engines/issues/1496) - [Test `ldd` output of engines](https://github.com/prisma/prisma-engines/issues/1538) ## Credits Huge thanks to @aruld for helping!