3.0.1
Today, we are excited to share the 3.0.1
stable release 🎉
As previously announced, Prisma has adopted SemVer strictly and this is the first major release which means it has some breaking changes.
For all the breaking changes, there are guides and documentation to assist you with the upgrade.
This release promotes many Preview features to General Availability. This means that they are ready for production use and have passed rigorous testing both internally and by the community.
We recommend that you read through the breaking changes below carefully and make sure that you've correctly upgraded your application.
🌟 Help us spread the word about Prisma by starring the repo ☝️ or tweeting about the release.
Major improvements
Today's release is packed with many new features that are now Generally Available!
Here's a summary:
- Referential Actions
- Named Constraints
- Microsoft SQL Server and Azure SQL Connector
- Seeding with
prisma db seed
has been revamped - Node-API
- Order by Aggregate in Group By
- Order by Relation
- Select Relation Count
Read along to dive deeper into all the new Generally Available improvements.
To read more about what Generally Available means, check out the maturity levels in the Prisma docs.
Referential Actions is now Generally Available
Referential Actions is a feature that allows you to control how relations are handled when an entity with relations is changed or deleted. Typically this is done when defining the database schema using SQL.
Referential Actions allows you to define this behavior from the Prisma schema by passing in the onDelete
and onUpdate
arguments to the @relation
attribute.
For example:
model LitterBox {
id Int @id @default(autoincrement())
cats Cat[]
full Boolean @default(false)
}
model Cat {
id String @id @default(uuid())
boxId Int
box LitterBox @relation(fields: [boxId], references: [id], onDelete: Restrict)
}
Here, you would not be able to delete a LitterBox
as long as there still is a Cat
linked to it in your database, because of the onDelete: Restrict
annotation. If we had written onDelete: Cascade
, deleting a LitterBox
would also automatically delete the Cat
s linked to it.
Referential Actions was first released in in 2.26.0 with the referentialActions
Preview flag. Since then, we've worked to stabilize the feature.
Today, we're delighted to announce that Referential Actions is now General Available, meaning it is enabled by default. 🐱
On PostgreSQL, MySQL, SQLite and Microsoft SQL Server, referential actions will be enforced by the database.
If you use Prisma Migrate to manage your database schema, you can use introspection with prisma db pull
to automatically fill in the existing referential actions from your database. Please read the upgrade guide for all the details.
🚨 Please note that the defaults change in this release. In some cases, this can lead to queries behaving differently in Prisma 3.x compared to Prisma 2.x, since the database will be enforcing referential actions, and not Prisma Client anymore. This means that if you only update your client without changing your Prisma schema, you will lose Prisma-level protections against cascading deletes. Read more in the Breaking Changes section below and the upgrade guide for a detailed explanation and steps to follow. 🚨
Named Constraints is now Generally Available
One of the core concepts in Prisma is the Prisma schema which serves as a single source of truth for your database schema and application models. For Prisma to automatically generate migrations and Prisma Client, it's necessary for the Prisma schema to represent the database schema as accurately as possible.
Named Constraints allows the Prisma schema to more accurately represent the names of all currently supported constraints such as unique constraints, primary keys, etc.
By having this representation in the Prisma schema, you now have finer control over two things:
- The names of constraints in the database schema which are generated by Prisma Migrate and the
prisma db push
command. - The name mapping for the constraint in Prisma Client for
findUnique
queries on compound indices.
Named Constraints was initially released in Preview in 2.29.0. Since then, we've made minor fixes and improvements to ease the upgrade experience.
Today, we're happy to launch Named Constraints to General Availability, meaning it will be enabled by default.
Named Constraints are about reflecting another aspect of the database in your Prisma schema: many objects in your schema, like indexes, unique constraints, and foreign keys, have a name in the database. That was not properly reflected in the Prisma Schema Language in earlier versions. The addition of Named Constraints fills this gap and improves overall correctness.
Given the following example:
model Post {
id Int @id(map: "pk_of_Post")
blog_id Int
blog Blog @relation(fields: [blog_id], references: [id], map: "Post_blog_fk")
title String
@@unique([title, blog_id], name: "Post_title_blog_id_unique", map: "Post.title_blog_id_unique")
}
model Blog {
id Int @id
posts Post[]
}
You can see that @id
, @relation
and @@unique
now can take a map
argument corresponding to the database name of the primary key/foreign key/constraint.
@@unique
can also take a name
argument to control the naming of the WhereUnique
argument in Prisma Client.
A quick way to remember: The new API is uniform. map: "..."
always corresponds to a name in the database, whereas name: "..."
always corresponds to names in the Prisma Client API.
🚨 Please note that you will have to make conscious decisions about constraint names when you upgrade to Prisma 3. Prisma will help you with the upgrade process using introspection with prisma db pull
and Prisma Migrate. Read more in the Breaking Changes section below and the upgrade guide for a detailed explanation and steps to follow. 🚨
Microsoft SQL Server and Azure SQL Connector is now Generally Available
Today we are excited to announce that Prisma support for Microsoft SQL Server and Azure SQL is Generally Available and ready for production!
Since we released Prisma Client for General Availability over a year ago with support for PostgreSQL, MySQL, SQLite, and MariaDB, we've heard from thousands of engineers about how the Prisma ORM is helping them be more productive and confident when building data-intensive applications.
After passing rigorous testing internally and by the community over the last year since the Preview release in version 2.10.0, we are thrilled to bring Prisma's streamlined developer experience and type safety to developers using Microsoft SQL Server and Azure SQL in General Availability 🚀.
Learn more in the release blog post 🤓
Seeding with prisma db seed
has been revamped and is now Generally Available
When developing locally, it's common to seed your database with initial data to test functionality. In version 2.15 of Prisma, we initially introduced a Preview version of seeding using the prisma db seed
command.
Today, we're excited to share that the prisma db seed
command has been revamped and simplified with a better developer experience and is now Generally Available.
The seeding functionality is now just a hook for any command defined in "prisma"."seed"
in your package.json
.
For example, here's how you would define a TypeScript seed script with ts-node
:
- Open the
package.json
of your project - Add the following example to it:
// package.json
"prisma": {
"seed": "ts-node prisma/seed.ts"
}
Expand to view an example seed script
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
const alice = await prisma.user.upsert({
where: { email: 'alice@prisma.io' },
update: {},
create: {
email: 'alice@prisma.io',
name: 'Alice',
},
})
console.log({ alice })
}
main()
.catch((e) => {
console.error(e)
process.exit(1)
})
.finally(async () => {
await prisma.$disconnect()
})
This approach gives you more flexibility and makes fewer assumptions about how you choose to seed. You can define a seed script in any language as long as you it's just a terminal command.
For example, here's how you would seed using an SQL script and the psql
CLI tool.
// package.json
"prisma": {
"seed": "psql --dbname=mydb --file=./prisma/seed.sql"
}
🚨 Please note that if you already have a seed script that worked created in versions prior, you will need to add the script to prisma.seed
in your package.json
and adapt the script to the new API. Read more in the Breaking Changes section below and the seeding docs for a complete explanation and walkthroughs of common use cases.
Node-API is Generally Available
Node-API is a new technique for binding Prisma's Rust-based query engine directly to Prisma Client. This reduces the communication overhead between the Node.js and Rust layers when resolving Prisma Client's database queries.
Earlier versions of Prisma (since version 2.0.0) used the Prisma Query Engine binary, which runs as a sidecar process alongside your application and handles the heavy lifting of executing queries from Prisma Client against your database.
In 2.20.0 we introduced a Preview feature, the Node-API library, as a more efficient way to communicate with the Prisma Engine binary. Using the Node-API library is functionally identical to running the Prisma engine binary while reducing the runtime overhead by making direct binary calls from Node.js.
Starting with today's 3.0.1 release we are making the Node-API library engine the default query engine type. If necessary for your project, you can fall back to the previous behavior of a sidecar Prisma Engine binary, however, we don't anticipate a reason to do so.
If you've been using this preview feature, you can remove the nApi
flag from previewFeatures
in your Prisma Schema.
Learn more about the Query Engine in our documentation.
Order by Aggregate in Group By is Generally Available
Let's say you want to group your users by the city they live in and then order the results by the cities with the most users. Order by Aggregate Group allows you to do that, for example:
await prisma.user.groupBy({
by: ['city'],
_count: {
city: true,
},
orderBy: {
_count: {
city: 'desc',
},
},
}),
Expand to view the underlying Prisma schema
model User {
id Int @id @default(autoincrement())
email String @unique
city String
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
author User? @relation(fields: [authorId], references: [id])
authorId Int?
}
Order by Aggregate Group was initially released as a Preview feature in 2.21.0.
Starting with today's release it is Generally Available 🤩
If you've been using this Preview feature, you can remove the orderByAggregateGroup
flag from previewFeatures
in your Prisma Schema.
Learn more about this feature in our documentation.
Order by Relation is Generally Available
Ever wondered how you can query posts and have the results ordered by their author's name?
With Order by Relations, you can do this with the following query:
await prisma.post.findMany({
orderBy: {
author: {
name: 'asc',
},
},
include: {
author: true,
},
})
Expand to view the underlying Prisma schema
```tsx
model User {
id Int @id @default(autoincrement())
email String @unique
city String
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
author User? @relation(fields: [authorId], references: [id])
authorId Int?
}
```
Order by Relation was initially released in Preview in 2.16.0.
Starting with today's 3.0.1 release it is Generally Available 🧙
If you've been using this preview feature, you can remove the orderByRelation
flag from previewFeatures
in your Prisma Schema.
Learn more about this feature in our documentation.
Select Relation Count is Generally Available
Select Relation Count allows you to count the number of related records by passing _count
to the select
or include
options and then specifying which relation counts should be included in the resulting objects via another select
.
Select Relation Count helps you query counts on related models, for example, counting the number of posts per user:
const users = await prisma.user.findMany({
include: {
_count: {
select: { posts: true },
},
},
})
Expand to view the structure of the returned `users`
```ts [ { id: 2, email: 'bob@prisma.io', city: 'London', name: 'Bob', _count: { posts: 2 }, }, { id: 1, email: 'alice@prisma.io', city: 'Berlin', name: 'Alice', _count: { posts: 1 }, }, ] ```If you've been using this Preview feature, you can remove the selectRelationCount
flag from previewFeatures
in your Prisma Schema.
Learn more about this feature in our documentation.
Breaking Changes
Some of the features above introduce breaking changes. In this section, we cover the breaking changes in more detail with links to upgrade guides in the documentation. We recommend that you read through the breaking changes carefully and make sure that you've upgraded and tested your application.
Named Constraints
Starting with Prisma 3, the names of database constraints and indexes are reflected in the Prisma schema. This means that Introspection with db pull
as well as migrate
and db push
will work towards keeping your constraint and index names in sync between your schema and your database.
Additionally, a new convention for default constraint names is now built into the Prisma Schema Language logic. This ensures reasonable, consistent defaults for new greenfield projects. The new defaults are more consistent and friendlier to code generation. It also means that if you have an existing schema and/or database, you will either need to migrate the database to the new defaults, or introspect the existing names.
Referential Actions
The default referential actions behaviors when you update or delete data changes between Prisma 2 and Prisma 3. This will lead to queries behaving differently in Prisma 3.x compared to Prisma 2.x.
In some cases, a delete operation will delete more data than previously. This is ****because in Prisma 2, cascading deletes and updates defined at the database level were prevented by Prisma Client, but will be allowed to happen in Prisma 3.
On relational databases, Prisma now relies on the database for referential actions — using db pull
after you upgrade will reflect the actual behavior in your Prisma Schema.
Please read the Referential Actions upgrade guide for a detailed explanation and steps to follow.
Seeding with prisma db seed
, prisma migrate dev
, prisma migrate reset
The mechanism through which seeds are discovered changes. If you were already using seeding since Prisma 2.15.0, you will see a warning with steps to follow the next time seeding is triggered.
In summary, you will need to:
- Add the script to
prisma.seed
in yourpackage.json
- Adapt the script to the new API.
See the example above in the Major Improvements section and read the seeding docs for a complete explanation.
prisma db seed
. You will have to switch to the new mechanism.
$queryRaw
API changes
We’ve taken this opportunity to cleanup $queryRaw
and $executeRaw
. In Prisma 2.x, the Prisma Client had different behavior depending on how you called $queryRaw
or $executeRaw
:
- Tagged Template:
prisma.$queryRaw
...``. This API sent a prepared statement and was safe from SQL injections. - Function Call:
prisma.$queryRaw(
...)
. This API sent a raw query string and was not safe from SQL injections.
This was too subtle. Starting with Prisma 3, we've split our raw query APIs into "safe" and "unsafe":
$queryRaw
...``: Safe from SQL injections. Sends a prepared statement and returns the resulting rows.$executeRaw
...``: Safe from SQL injections. Sends a prepared statement and returns the result count.$queryRawUnsafe(
...)
: Not safe from SQL injections. Sends the query as a string and returns the resulting rows. Useful for queries that can't be prepared, like using a variable for the table name.$executeRawUnsafe(
...)
: Not safe from SQL injections. Sends the query as a string and returns the result count. Useful for queries that can't be prepared, like altering a column's data type.
To update your application, you can do a "Find All" in your codebase for $queryRaw(
and $executeRaw(
. Then you can either turn them into tagged templates or use the unsafe functions.
Changes to how you query Null values on JSON fields
While Filtering on a Json field was in Preview, we learned from a community member that you couldn't filter a Json field by the JSON value null.
This is because { equals: null }
checks if the value in the database is NULL
, not if the value inside the column is a JSON null
.
To fix this problem, we decided to split "null" on Json fields into JsonNull
, DbNull
and AnyNull:
- JsonNull: Selects the null value in JSON.
- DbNull: Selects the NULL value in the database.
- AnyNull: Selects both null JSON values and NULL database values.
Given the following model in your Prisma Schema:
model Log {
id Int @id
meta Json
}
Starting in 3.0.1, you'll see a TypeError if you try to filter by null
on a Json
field:
prisma.log.findMany({
where: {
data: {
meta: {
equals: null
// ^ TypeError: Type 'null' is not assignable to type
}
},
},
});
To fix this, you'll import and use one of the new null types:
import { Prisma } from '@prisma/client'
prisma.log.findMany({
where: {
data: {
meta: {
equals: Prisma.AnyNull,
},
},
},
})
This also applies to create, update and upsert. To insert a null
value into a Json field, you would write:
import { Prisma } from '@prisma/client'
prisma.log.create({
data: {
meta: Prisma.JsonNull,
},
})
And to insert a database NULL
into a Json field, you would write:
import { Prisma } from '@prisma/client'
prisma.log.create({
data: {
meta: Prisma.DbNull,
},
})
Learn more about JSON filtering in our documentation.
Renamed Aggregate Fields
In 2.23.0, we announced that aggregate fields like count
will be deprecated in favor of the prefixed notation, i.e. _count
in order to avoid clashing with model fields names in your application.
For example:
const result = await prisma.user.groupBy({
by: ['name'],
- count: true,
+ _count: true,
})
In this release, we're removing the deprecated old notation. You now must prefix your aggregate fields with an underscore.
To help with this transition, we suggest searching your codebase for .aggregate({
and .groupBy({
, and prefixing count
, max
, min
, avg
and sum
are all prefixed with an underscore, i.e. _count
, _max
, _min
, _avg
, and _sum
.
You can learn more about the original deprecation in the 2.23.0 Release Notes.
The minimum required version of Node.js is v12.6
Up until this release, Prisma supported versions 12.2 of Node.js and up
Starting with this release, the minimum required version of Node.js is 12.6.
Fixes and improvements
Prisma Migrate
- Remove
microsoftSqlServer
preview flag db seed
tests need to run on multiple platforms, not just Ubuntu- Validate
referentialActions
with Introspection and Migration CI - Introspection adds an extra space to map properties in enums in prisma.schema
- Remove
referentialActions
preview flag - [Introspection] @@unique name with spaces are not supported
- EPIC: re-design of
db seed
feature - MySQL: Support SSL with system certificate store
- Enable TLS by default on SQL Server
- Make order of introspected foreign keys stable
Prisma Client
- Prisma Client Node-API Support
- [BREAKING CHANGE] Disconnect/connect (for relations) throws errors needlessly
- Error classes are wrapped twice
$queryRaw
availability isn't derived from the DMMF- Can't filter by
null
values on JSONB fields - Remove preview flag for
orderByRelations
- Remove preview flag for
orderByAggregateGroup
- Remove preview flag for
selectRelationCount
- Make Node-API library default engine
- Add runtime check to
$queryRaw("...")
to throw for JS calls - Write a codemod to rename
$queryRaw(...)
to$queryRawUnsafe(...)
- Using invalid connection string parameters leads to suboptimal error message with Node-API library
- Bump Node.js requirement from to >=12.2 to >=12.6
- Running a statement results in different prepared statements
- A
NOT
within aNOT
creates a SQL query with only a singleNOT
in 2.30.0 - Remove non-underscored fields (e.g.
count
should be_count
)
Prisma
- Json filters
array_starts_with
andarray_ends_with
don't work on MariaDB - SQL Server cycle detection doesn't detect multiple paths correctly
- Adapt CLI update notifier for major upgrade
Language tools (e.g. VS Code)
Prisma Studio
Credits
Huge thanks to @saintmalik, @benkenawell, @ShubhankarKG, @hehex9 for helping!
📺 Join us for another "What's new in Prisma" livestream
Learn about the latest 3.0.1 release and other news from the Prisma community by joining Matt Muller and Daniel Norman from the Prisma team for another "What's new in Prisma" livestream.
The stream takes place on Youtube on Thursday, September 9 at 5pm Berlin | 8am San Francisco.