2.21.0

[Jolg42]

Today, we are excited to share the 2.21.0 stable release 🎉

🌟 Help us spread the word about Prisma by starring the repo ☝️ or tweeting about the release.

Major improvements & new features

Order by an aggregate in groupBy is now in Preview

Whew, that's a tongue-twister for a neat feature.

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. In 2.21.0, now you can!

const userRatingsCount = await prisma.user.groupBy({
  by: ['city'],
  count: {
    city: true,
  },
  orderBy: {
    _count: {
      city: 'desc',
    },
  },
})

Click to view the underlying model

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  city  String
}

The query returns the following:

[
  { city: 'Berlin', count: { city: 3 } },
  { city: 'Paris', count: { city: 2 } },
  { city: 'Amsterdam', count: { city: 1 } },
]

Enable this feature with the orderByAggregateGroup preview flag:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["orderByAggregateGroup"]
}

Breaking Changes

Aggregates are now nullable

Before 2.21.0, aggregations on nullable fields always returned 0, even when there was no record or if all aggregated records were nulls. Returning 0 for null values made it impossible to differentiate between these two different results.

We are changing aggregates to follow what the database returns:

• All aggregated fields are now nullable. Aggregated fields can return null when there's either no record in the database or if all the aggregated records are null.
• The only exception is count, which still returns 0, even if records are null or if there's no record.

For example, previously, if there's no record in the database or if all records are nulls, the following aggregation:

const result = await prisma.post.aggregate({
  sum: { amount: true },
})

Results in:

{
  sum: {
    amount: 0
  }
}

The result.sum type is currently of type { amount: number }.

Starting this release, the same query returns:

{
  sum: {
    amount: null
  }
}

And result.sum is of type { amount: number | null }

disconnect no longer throws an error on unconnected records

Prior to 2.21.0, if you ran the following code:

const user = await prisma.user.update({
  where: { email: 'bob@prisma.io' },
  data: {
    profile: {
      disconnect: true,
    },
  },
})

And no profile was connected to Bob, the client would throw with this error:

The records for relation `UserToProfile` between the `User` and `Profile` models are not connected.

We learned from you that handling this added unnecessary boilerplate to your applications. As of 2.21.0, we've removed this error. Now, if you try disconnecting an unconnected record, the operation does nothing and passes through.

This change is unlikely to affect you unless you explicitly handle the disconnect error. In that case, adjust your code because the command no longer throws an error.

@default(dbgenerated("")) is no longer permitted

The dbgenerated() function allows you to define default values generated directly by the database and cannot yet be represented in the Prisma schema.

Previously, you could pass an empty string, i.e., @default(dbgenerated("")), which would fail since the contents are added into the migration SQL as columnName COLUMN_TYPE DEFAULT <contents of dbgenerated>

As of 2.21.0, if a value is present, it cannot be an empty string.

If you want an empty string default, the correct syntax is @default(dbgenerated("''") (or other quotation marks, depending on the database provider).

Fixes and improvements

Prisma Client

• Prisma2 packages causes Yarn install devDependencies on --prod
• Disconnect on a null foreign key should not error
• count throws an error if include is specified
• SQLite: Dates before 1970 panic. PANIC in /root/.cargo/registry/src/github.com-1ecc6299db9ec823/chrono-0.4.19/src/naive/datetime.rs:117:18invalid or out-of-range datetime
• Conversion failed when converting from a character string to uniqueidentifier.
• Elevated number of Timed out fetching a new connection from the pool
• Prisma migrate CLI overrides the NODE_ENV env var and always sets it to production
• "Error validating: The relation field ... uses the scalar fields .... At least one of those fields is required. Hence the relation field must be required as well." is too restrictive when using composite keys
• Count queries are very inefficient and costly
• stack depth limit exceeded
• Test float values over N-API bridge
• N-API Query Engine doesn't allow more than one instance at the same time
• [DEP0131] DeprecationWarning: The legacy HTTP parser is deprecated in Node 12.22.0

Prisma Migrate

• On PostgreSQL, when changing column types that are also in an index, Migrate would generate a DROP COLUMN ..., ADD COLUMN ... sequence of SQL statements. These silently drop the indexes present on the column, and migrate would only re-create them with the next migration. With this release, the issue has been fixed, and Migrate recreates the index in the same migration.
• Not prettified error This line is invalid. It does not start with any known Prisma schema keyword. in introspection for an invalid schema.
• Improve error message in Prisma Migrate when a provider mismatch is detected
• SQL Server Introspection fails on Tables with Geography Data Type
• Error when introspecting Azure SQL Server database
• db push crashes when used on specific Postgres connection string with non existing database
• Migrate leaks database password when Migration Engine binary crashes
• Changed the phrasing of error messages for P3006. It wrongly mentioned "temporary database", it's now been corrected to mentions the shadow database.

Prisma Studio

We worked on shipping some minor improvements and bug fixes:

• Switch theme based on OS preferences
• Link to error documentation is not clickable
• Error: Too many clients
• Error when changing the number of related records

prisma-engines

• Make CI run tests against pgBouner again
• Fix check for orderBy aggregate

Credits

Huge thanks to @Sytten, @endor, @iBluemind, @schiller-manuel, @mongolyy, @matthewmueller, @paularah, @Iamshankhadeep, @meeq, @safinsingh for helping!

---

This discussion was created from the release 2.21.0.