Today, we are excited to share the `2.17.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.17.0%20%F0%9F%9A%80%0D%0A%0D%0Ahttps://github.com/prisma/prisma/releases/tag/2.17.0) about the release.** 🌟
Overview
- Native types are now stable
- Prisma Migrate now works with cloud-hosted databases (e.g. Heroku)
- Soft resets for cloud-hosted environments
- More improvements and bug fixes for Prisma Migrate
- Improvements and changes for `prisma db push`
- `prisma db seed` now supports custom schema locations
- Improvements and bug fixes in Prisma Client
Note that this release comes with some breaking changes. Read the **Breaking changes** section below to learn more.
Major improvements & new features
Native types are now stable
The `nativeTypes` preview feature flag has first been introduced in [`2.10.0`](https://github.com/prisma/prisma/releases/tag/2.11.0). Thanks to your continuous and awesome [feedback](https://github.com/prisma/prisma/issues/4045) for this feature, we're now able to release usage of native database types in the Prisma schema for General Availability 🎉
Note that this release comes with a few minor breaking changes compared to previous versions. Please read about the **Breaking Changes** below.
If you haven't followed previous releases, expand below to learn more about everything that's now possible with the new native types.
<details><summary>Expand to learn more about the benefits of native types</summary>
Rich column type mapping for Prisma types
Each Prisma type can now map to one of multiple native database types. Native database type attributes are:
- Specific to the underlying provider - for example, PostgreSQL uses **`db.Boolean`** for **`Boolean`** whereas MySQL uses **`db.TinyInt`**
- Written in PascalCase (for example, **`VarChar`** or **`Text`**)
- Prefixed by **`db`**, where **`db`** is the name of the **`datasource`** block in your Prisma schema
Type attributes give you:
- Exact control over **what native type [Prisma Migrate](https://www.prisma.io/docs/concepts/components/prisma-migrate/) creates in the database** - for example, a **`String`** can be **`db.VarChar(200)`** or **`db.Char(50)`**
- An **enriched schema** when you introspect - you can see if **`String`** is **`varchar(200)`** or just **`text`**.
To learn more about all the possible native type attributes, check out the [type mapping reference in the docs](https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings).
Extending Prisma schema beyond supported column types
Column types which are not (yet?) supported by Prisma Migrate can be used with Prisma Migrate and introspection through the Prisma type `Unsupported` which was introduced in Preview in the [last release]((https://github.com/prisma/prisma/releases/tag/2.16.0)):
prisma
model User {
id Int id default(autoincrement())
email String unique
name String? default("")
multilinestringField Unsupported("multilinestring") unique
}
`dbgenerated()` in the `default` directive can now take a String argument that enables developers to reflect database-level `DEFAULT` constraints not yet supported by Prisma Migrate. These default values will be surfaced when introspecting with `prisma introspect` and created/changed when using Prisma Migrate.
Developers can now add `ignore` and `ignore` attributes to models and fields, for fields they want to manage via Prisma Migrate but not surfaced in Prisma Client. These attributes are added by Prisma when introspecting entities which are not supported, e.g. a table with no unique column. They are now also kept in the Prisma schema when re-introspecting a database.
</details>
Prisma Migrate now works with cloud-hosted databases (e.g. Heroku)
Before this release, Prisma Migrate could be used to apply migrations in a cloud-hosted environment (CI/CD pipeline, manual deployment to production, staging, etc.), but it was impossible to create new migrations, due to the [requirement of a shadow database](https://www.prisma.io/docs/concepts/components/prisma-migrate#shadow-database). Prisma Migrate expects to have privileges to create the shadow database using the same credentials, but cloud providers generally do not allow creating logical databases.
Starting from this release, `prisma migrate dev` can now be used in development with cloud-hosted databases by configuring a separate connection URL for the shadow database.
To develop natively in the cloud with Prisma Migrate, developers can create two cloud-hosted databases, one being the development- and the other being the shadow-database.
The connection URI for the shadow database can be configured in the `datasource` block of the Prisma schema file, similarly to the datasource URL, by defining a `shadowDatabaseUrl` variable:
prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
Soft resets for cloud-hosted environments
Another common limitation of cloud-hosted environments is that the database cannot be dropped and re-created using `DROP DATABASE` and `CREATE DATABASE`, due to insufficient privileges. Prisma Migrate so far relied on these statements to ensure the database is empty when it needs to be reset.
Database resets in the context of Prisma Migrate now gracefully fall back to dropping constraints, indexes and tables, if there are insufficient privileges to reset the database using `DROP DATABASE`.
Note that this comes with the caveat that there could be other entities in the database, which Prisma Migrate could fail to clean up.
More improvements and bug fixes for Prisma Migrate
- Prisma Migrate has now a built-in locking functionality to prevent multiple migrations from running concurrently.
- Ensure the Prisma schema is valid before prompting developers to reset the database.
- Better error message when using `migrate dev` - if a non-interactive environment is detected, you'll be suggested to use `prisma migrate deploy` instead.
- Improved error handling when Prisma Migrate finds empty migration directories, e.g. `prisma/migrations/20210119114009_init` (missing `migration.sql` file).
- In some occasions, when dealing with invalid schemas, e.g., duplicate constraint names, a panic in the Migration Engine would be triggered. These errors are now surfaced as validation errors instead.
- In certain cases, when dealing with UUID columns, Prisma Migrate would drop and re-create the columns every time a migration was generated. This has now been fixed.
Improvements and changes for `prisma db push`
- `prisma db push` now handles unexecutable migrations better, offering a path forward by resetting the database. For example, adding a new required field without a default value when there are rows in the table is considered an unexecutable migration; in such situations you will be prompted to first reset the database.
- Changes to command options:
- The flag `—-force` has been renamed to `--accept-data-loss` to be more explicit - this is required for certain changes that involve losing data, e.g. dropping a table or dropping a column if there are rows.
- We've added a new flag `—-force-reset` which first resets the database and then updates the schema - this can be useful to start from scratch and as a way to deal with unexecutable migrations (see above).
`prisma db seed` now supports custom schema locations
You can now point the `prisma db seed` command to a custom schema location using either of two approaches:
- Use the `--schema` option when running the command
- Define a default schema location in your `package.json` which will be picked up every time you run the command.
Improvements and bug fixes in Prisma Client
- **Transaction rollback fix**: We fixed an issue where if there was an error within the Prisma Client's runtime validation, the transaction wouldn't rollback. Learn more in this [issue](https://github.com/prisma/prisma/issues/5616).
- **SQL Server `server_name` fix**: Before we couldn't connect to certain kind of SQL Server instances. If the server was a managed instance from Azure, connecting to it with Prisma would return `Server name cannot be determined`. Additionally, when running a shared Azure SQL database, if the firewall setting was set to `redirect` (the default setting), our connection would first fail with advising the user to connect to a new server, and when changing the connection string regarding the error, the new connection would fail with the same error `Server name cannot be determined`. This is now fixed. Azure managed instances just work, as do redirections (which are done under the hood, automatically).
- **Native type fix for SQL Server**: Our native type validations limits were set too low. We'd consider the maximum of 2000 for `NVarChar`/`NChar` length and a maximum of 4000 for `VarChar`/`Char`/`VarBinary`/`Binary` length. The actual maximums are `4000` for first and `8000` for the latter types.
- **PostgreSQL numeric type fix**: In certain cases, when using `executeRaw` to insert number values to a `numeric` type in PostgreSQL, sometimes the stored value would be zero instead of the user input. An example value of `12345.0` is converted by JavaScript as an integer of 12345, which then is written as an integer to the table. The table column being numeric, the integer representation would always be zero in these cases. Now we should be able to convert integers to `numeric` without any trouble.
Bug fixes in Prisma Studio
- Studio can now display fields that are of type `Byte` and `BigInt`.
- Usage of the `createMany` preview feature doesn't crash Studio any more
Breaking changes
Type mapping from Prisma schema to the database for `Float` has changed from `Decimal` to `Double` in MySQL and PostgreSQL
Overview
If you use the `Float` scalar type in your Prisma schema and used Prisma Migrate to create the database schema in previous Prisam versions, the corresponding database column has the type `DECIMAL(65,30)`.
For example, given the following Prisma schema:
prisma
model Post {
id Int id default(autoincrement())
title String
content String?
reward Float // Previously mapped to DECIMAL(65,30). From 2.17.0 will map to Double
published Boolean default(false)
}
Previous version of Prisma Migrate would generate the following migration:
sql
-- CreateTable
CREATE TABLE "Post" (
"id" SERIAL NOT NULL,
"title" TEXT NOT NULL,
"content" TEXT,
"reward" DECIMAL(65,30) NOT NULL, //
"published" BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY ("id")
);
As of `2.17.0`, the remapping of the `Float` type from `Decimal(65,30)` to `Double` will cause Migrate to attempt to alter the database type of the `reward` column to `Double` the next time you create a migration.
What does this mean for users?
Nothing changes in Prisma Client until the next time you want to make a change to your schema. In that case, you'll need to decide if you want to keep using the `Decimal(65,30)` type in the database:
- If you want to continue using `Decimal(65,30)`, you need to change the type in the Prisma schema from `Float` to `Decimal`. Alternatively, you can also run `prisma introspect` which will automatically remap the previous `Float` fields to `Decimal`. Note that this will also change the type that Prisma Client returns from `Number` to `Decimal.js`.
- If you would like to change the column's type in the database from `Decimal(65,30)` to `Double`, leave the Prisma schema as is and create a new migration. Prisma Migrate will alter the column's type to `Double`. Note that if you have rows with data for the column, they will be cast from `Decimal(65,30)` to `Double`.
Check out this [video guide](https://www.youtube.com/watch?v=OsuGP_xNHco), which covers how to upgrade and address the remapping of Float.
Breaking changes due to strict type diffing and native types
Overview
Prisma has [default mappings](https://www.prisma.io/docs/concepts/components/preview-features/native-types/native-types-mappings) between each scalar type in the Prisma schema to the underlying database type. For example, the `String` scalar type in Prisma schema is mapped to a `TEXT` column on PostgreSQL by default.
Before this release, Prisma supported using a range of database column types for a given Prisma scalar. For example, define a field in Prisma schema as `String` and use `VARCHAR(50)` as the column type in the database using the `db.varchar(50)` type annotation .
With the introduction of native types in General Availability, you can now specify your desired database type for columns in the Prisma schema via the `db.DB_TYPE` field attributes, e.g., `db.varchar(50)`.
Because the `db.DB_TYPE` attribute now exists, Prisma no longer allows the loose mapping of Prisma scalar types to database column types without the specific notation. The only exception to this rule is when you want to use default mapping, e.g., the `String` Prisma scalar will map to `TEXT` on PostgreSQL.
Before
Given the following table in PostgreSQL:
sql
-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"nickName" VARCHAR(50),
"name" TEXT NOT NULL,
PRIMARY KEY ("id")
);
Prisma would introspect the table as follows:
prisma
model User {
id Int id default(autoincrement())
nickName String? //defaults to TEXT on PostgreSQL but works with varchar
name String //defaults to TEXT on PostgreSQL but works with varchar
}
After
Because `VARCHAR(50)` can be expressed in native type notation. The matching Prisma schema for the `User` database table above on PostgreSQL is the following:
prisma
// Example for PostgreSQL
model User {
id Int id default(autoincrement())
nickName String? db.VarChar(50) // Prisma expects the column to be varchar and Prisma Migrate will change it if not
name String // Prisma expects the column to be of type TEXT (default for String) and Prisma Migrate will change it if not
}
What does this mean for users?
Moving forward, if you want specific database column types, which are supported by Prisma, you should make sure to use the native type notation for the corresponding fields in the Prisma schema.
For users of Prisma Migrate with existing databases, you must understand that Prisma Migrate will try to migrate every column of a type different than what's defined in the schema.
If we go back to the previous example with loose type mapping, with this Prisma schema:
prisma
model User {
id Int id default(autoincrement())
nickName String? //defaults to TEXT on PostgreSQL but works with varchar
name String //defaults to TEXT on PostgreSQL but works with varchar
}
and this initial database schema:
sql
-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"nickName" VARCHAR(50),
"name" TEXT NOT NULL,
PRIMARY KEY ("id")
);
On PostgreSQL, from this release on, Prisma will the columns for the fields `nickName` and name to be of type `TEXT` and will generate a migration to alter the type of the `nickName` column:
sql
-- AlterTable
ALTER TABLE "User" ALTER COLUMN "nickName" SET DATA TYPE TEXT;
To avoid unnecessary migrations to change types you may have defined on purpose, you can run introspection once, which will add the native annotations to any fields when they do not match the default mappings by Prisma.
For the initial database schema we used in the example
sql
-- CreateTable
CREATE TABLE "User" (
"id" SERIAL NOT NULL,
"nickName" VARCHAR(50),
"name" TEXT NOT NULL,
PRIMARY KEY ("id")
);
This would be the resulting Prisma schema after running prisma introspect
sql
model User {
id Int id default(autoincrement())
nickName String? db.VarChar(50)
name String
}
Fixes and improvements
Prisma Client
- [postgresql column type date error](https://github.com/prisma/prisma/issues/1541)
- [Fixed-size column types as part of identifiers do not work on MySQL](https://github.com/prisma/prisma/issues/1778)
- [Query engine panics when the ID used to create a record and the actual ID in the database are different](https://github.com/prisma/prisma/issues/2687)
- [[Native Types] Ensure that fields of unsupported types aren't dropped](https://github.com/prisma/prisma/issues/3673)
- [Concurrent updates that don't interfere with each other](https://github.com/prisma/prisma/issues/4224)
- [[MySQL] Throw a better error message when connection is rejected due to self signed ssl certificate](https://github.com/prisma/prisma/issues/4605)
- [$executeRaw throws PostgreSQL ERROR: invalid scale in external "numeric" value or inserts 0.0](https://github.com/prisma/prisma/issues/4828)
- [Change parameter type of prisma.$transaction](https://github.com/prisma/prisma/issues/4949)
- [Weird required array typing](https://github.com/prisma/prisma/issues/4964)
- [Passing null to a string some filter crashes the engine](https://github.com/prisma/prisma/issues/5067)
- [PANIC: index out of bounds: the len is 1 but the index is 1](https://github.com/prisma/prisma/issues/5095)
- [Request URL is too long](https://github.com/prisma/prisma/issues/5126)
- [findFirst with `undefined` value shouldn't return data](https://github.com/prisma/prisma/issues/5149)
- [Prisma not combining queries for GraphQL server, leading to N+1 issues](https://github.com/prisma/prisma/issues/5274)
- [Inconsistent PascalCasing for DMMF type names](https://github.com/prisma/prisma/issues/5302)
- [map not carried over to `makeEnum`](https://github.com/prisma/prisma/issues/5383)
- [ignore and ignore support in the client](https://github.com/prisma/prisma/issues/5442)
- [Stabilize `nativeTypes`](https://github.com/prisma/prisma/issues/5444)
- [Prisma can not connect to `localhost` for MySQL, MariaDB and SQL Server (mostly when running those via Docker)](https://github.com/prisma/prisma/issues/5499)
- [Invalid client generation when use model named "Record"](https://github.com/prisma/prisma/issues/5500)
- [Negative number cannot be entered as string to Decimal input](https://github.com/prisma/prisma/issues/5508)
- [prisma/client version 2.16.x spams [dotnev][DEBUG] logs](https://github.com/prisma/prisma/issues/5562)
- [Add `PRISMA_DISABLE_WARNINGS`](https://github.com/prisma/prisma/issues/5587)
- [$transaction doesn't rollback in case any transaction fails](https://github.com/prisma/prisma/issues/5616)
- [Prisma client looks in root directory (C:\) for engine binary when used with Next.js](https://github.com/prisma/prisma/issues/5082)
- [UUID values are not automatically converted to their binary form on write on mysql and sqlite](https://github.com/prisma/prisma/issues/1776)
- [2.8.0 and above: MySQL, 'tinyint unsigned' field considered as 'boolean'](https://github.com/prisma/prisma/issues/3916)
Prisma Migrate
- [Ignoring table during introspection](https://github.com/prisma/prisma/issues/4510)
- [Prisma Migrate cannot reset the database when the user has insufficient privileges - this makes Prisma Migrate incompatible with some hosted cloud providers](https://github.com/prisma/prisma/issues/4803)
- [Error when migration directory exists but migration script is missing](https://github.com/prisma/prisma/issues/5170)
- [Error: Error in migration engine. Reason: [libs/datamodel/core/src/transform/ast_to_dml/validate.rs:226:34] called `Option::unwrap()` on a `None` value ](https://github.com/prisma/prisma/issues/5171)
- [Schema notation to not surface specific models in the Prisma Client API](https://github.com/prisma/prisma/issues/5217)
- [Migrate creates unnecessary migrations](https://github.com/prisma/prisma/issues/5244)
- [Migrate renames my indexes & throws Drift detected error even when nothing changes in DB/Schema](https://github.com/prisma/prisma/issues/5282)
- [Argument M is out of range for Native type NVarChar(4000) of SQL Server](https://github.com/prisma/prisma/issues/5287)
- [`ALTER TYPE` enum migrations fail in PostgreSQL](https://github.com/prisma/prisma/issues/5290)
- [prisma migrate dev: schema validation happens late in the process ](https://github.com/prisma/prisma/issues/5321)
- [Implement ignore on the field level](https://github.com/prisma/prisma/issues/5330)
- [Keep ignore and ignore during Re-Introspection](https://github.com/prisma/prisma/issues/5331)
- [Preserve `ignore`and `ignore` on re-introspection](https://github.com/prisma/prisma/issues/5395)
- [Migrate: Postgres connection string without port prints `undefined`](https://github.com/prisma/prisma/issues/5429)
- [`db seed` not documented in help output for `prisma db --help` ](https://github.com/prisma/prisma/issues/5431)
- [DB seed does not work with custom schema location from package.json](https://github.com/prisma/prisma/issues/5483)
- [Mention `prisma migrate deploy` in `migrate dev` non interactive error message for better discoverability.](https://github.com/prisma/prisma/issues/5531)
- [`prisma migrate dev --create-only`- if there are data loss warnings, the CLI prompts suggest the new migration will be applied when it is (rightfully)not ](https://github.com/prisma/prisma/issues/5605)
Language tools
- [Ensure `ignore` code completion is supported in VS code extension](https://github.com/prisma/language-tools/issues/696)
- [Remove the feature flag for nativeTypes for stabilization, so the native types logic is always active.](https://github.com/prisma/language-tools/issues/697)
Prisma Studio
- [Cannot edit BigInts](https://github.com/prisma/studio/issues/621)
- [Cannot view/edit Bytes](https://github.com/prisma/studio/issues/622)
- [createMany (preview flag) crashes Studio](https://github.com/prisma/studio/issues/623)
Prisma Engines
- [Implement database locking in the migration engine](https://github.com/prisma/prisma-engines/issues/1118)
- [Apply `clippy` to `migration-engine`](https://github.com/prisma/prisma-engines/issues/1386)
- [Apply `clippy` to `introspection-engine`](https://github.com/prisma/prisma-engines/issues/1388)
- [Apply `clippy` to `libs/datamodel`](https://github.com/prisma/prisma-engines/issues/1389)
- [Apply `clippy` to all `libs/*` (except datamodel)](https://github.com/prisma/prisma-engines/issues/1390)
- [Run clippy on CI](https://github.com/prisma/prisma-engines/issues/1453)
Check out the official Prisma roadmap
You can find all the features that are currently planned and in progress on our [roadmap](https://pris.ly/roadmap).
Credits
Huge thanks to safinsingh for helping!