Prisma: 2.17.0 Release

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 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. Thanks to your continuous and awesome feedback 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 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.

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:

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. 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:

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.
• 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:

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:

-- 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, 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 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:

-- 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:

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:

// 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:

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:

-- 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:

-- 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

-- 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

model User {
  id       Int      @id @default(autoincrement())
  nickName String?  @db.VarChar(50)
  name     String   
}

Fixes and improvements

Prisma Client

• postgresql column type date error
• Fixed-size column types as part of identifiers do not work on MySQL
• Query engine panics when the ID used to create a record and the actual ID in the database are different
• [Native Types] Ensure that fields of unsupported types aren't dropped
• Concurrent updates that don't interfere with each other
• [MySQL] Throw a better error message when connection is rejected due to self signed ssl certificate
• $executeRaw throws PostgreSQL ERROR: invalid scale in external "numeric" value or inserts 0.0
• Change parameter type of prisma.$transaction
• Weird required array typing
• Passing null to a string some filter crashes the engine
• PANIC: index out of bounds: the len is 1 but the index is 1
• Request URL is too long
• findFirst with undefined value shouldn't return data
• Prisma not combining queries for GraphQL server, leading to N+1 issues
• Inconsistent PascalCasing for DMMF type names
• @map not carried over to makeEnum
• @ignore and @@ignore support in the client
• Stabilize nativeTypes
• Prisma can not connect to localhost for MySQL, MariaDB and SQL Server (mostly when running those via Docker)
• Invalid client generation when use model named "Record"
• Negative number cannot be entered as string to Decimal input
• @prisma/client version 2.16.x spams [dotnev][DEBUG] logs
• Add PRISMA_DISABLE_WARNINGS
• $transaction doesn't rollback in case any transaction fails
• Prisma client looks in root directory (C:) for engine binary when used with Next.js
• UUID values are not automatically converted to their binary form on write on mysql and sqlite
• 2.8.0 and above: MySQL, 'tinyint unsigned' field considered as 'boolean'

Prisma Migrate

• Ignoring table during introspection
• Prisma Migrate cannot reset the database when the user has insufficient privileges - this makes Prisma Migrate incompatible with some hosted cloud providers
• Error when migration directory exists but migration script is missing
• 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
• Schema notation to not surface specific models in the Prisma Client API
• Migrate creates unnecessary migrations
• Migrate renames my indexes & throws Drift detected error even when nothing changes in DB/Schema
• Argument M is out of range for Native type NVarChar(4000) of SQL Server
• ALTER TYPE enum migrations fail in PostgreSQL
• prisma migrate dev: schema validation happens late in the process
• Implement @ignore on the field level
• Keep @@ignore and @ignore during Re-Introspection
• Preserve @@ignoreand @ignore on re-introspection
• Migrate: Postgres connection string without port prints undefined
• db seed not documented in help output for prisma db --help
• DB seed does not work with custom schema location from package.json
• Mention prisma migrate deploy in migrate dev non interactive error message for better discoverability.
• 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

Language tools

• Ensure @ignore code completion is supported in VS code extension
• Remove the feature flag for nativeTypes for stabilization, so the native types logic is always active.

Prisma Studio

• Cannot edit BigInts
• Cannot view/edit Bytes
• createMany (preview flag) crashes Studio

Prisma Engines

• Implement database locking in the migration engine
• Apply clippy to migration-engine
• Apply clippy to introspection-engine
• Apply clippy to libs/datamodel
• Apply clippy to all libs/* (except datamodel)
• Run clippy on CI

Check out the official Prisma roadmap

You can find all the features that are currently planned and in progress on our roadmap.

Credits

Huge thanks to @safinsingh for helping!