Prisma: 4.4.0 Release

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

Major improvements

General improvements

In the last sprint, we focused our efforts on squashing as many bugs as we could. You can find the full list of improvements and bug fixes in the Fixes and improvements section below.

Some of the improvements we made include but are not limited to: - Improved optimistic concurrency control (GitHub issue) - Improved decimal precision - Improved handling of big amounts of prepared statement placeholders: Databases impose limits when they hit a specific number, and when a query (either generated by Prisma Client or provided by the user directly as a raw query) hits it some users ran into a misleading Can't reach database server error message (GitHub issue). The error message will now be more useful (P2035 error code), and Prisma Client should not cause these errors anymore.

If you notice any regression, please make sure to create a GitHub issue. We touched a lot of code in this sprint, and even though we are confident in our tests, something might have slipped through the cracks. We'd like to fix the regressions as soon as possible.

isolationLevel for sequential transaction operations

In version 4.2.0, we added support for setting transaction isolation levels for interactive transactions (Preview). You can now define isolation levels for sequential transaction operations: prisma.$transaction([]).

Isolation levels describe different types of trade-offs between isolation and performance that databases can make when processing transactions. Isolation levels determine what types of data leaking can occur between transactions or what data anomalies can occur. To set the transaction isolation level, use the isolationLevel option in the second parameter of the API. For example:

await prisma.$transaction(
  [
    // sequential operations
    prisma.user.create({ data: {/** args */ } }),
    prisma.post.create({ data: {/** args  */ } })
  ],
  {
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable
  }
)

Prisma Client supports the following isolation levels if they're available in your database provider: - ReadCommitted - ReadUncommitted - RepeatableRead - Serializable - Snapshot

Learn more about it in our documentation.

New P2034 error code for transaction conflicts or deadlocks

When using certain isolation levels, it is expected that a transaction can fail due to a write conflict or a deadlock, throwing an error. One way to solve these cases is by retrying the transaction.

To make this easier, we're introducing a new PrismaClientKnownRequestError with the error code P2034: "Transaction failed due to a write conflict or a deadlock. Please retry your transaction". You can programmatically catch the error and retry the transaction. Here's an example showing how you can retry a transaction:

import { Prisma, PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
async function main() {
  const MAX_RETRIES = 5
  let retries = 0;

  let result;
  while (retries < MAX_RETRIES) {
    try {
      result = await prisma.$transaction(
        [
          prisma.user.deleteMany({ where: { /**  args */ } }),
          prisma.post.createMany({ data: { /**  args */ } })
        ],
        {
          isolationLevel: Prisma.TransactionIsolationLevel.Serializable
        }
      )
    } catch (error) {
      if (error.code === 'P2034') {
        retries++
        continue
      }
      throw error
    }
  }
}

Fixes and improvements

Prisma Client

• Wrong types for fluent API when used along with rejectOnNotFound
• Precision issue when persisting Decimal.js objects
• Decimals lose precision (postgres)
• Weird behavior with "in:" clause on a findMany() with orderBy + take
• decimal lose precision digits to postgres
• updateMany() causes lost-updates
• Error: P1001: Can't reach database server when including large table
• Client shows contains filter on uuid data type despite not being a valid operation
• A model with relation to another model via non-null foreign key has nullable type
• Validation error when inserting data of type List<String | DateTime> into field of type List<String>
• Can't reach database server when doing a large findMany query
• Maximum call stack size exceeded when creating huge amount of related rows
• Unique constraint failed during $transaction([deleteMany, createMany])
• update will break atomicity
• Nested self referential orderby not working
• SQL Server provider generates a wrong query
• MySQL: Optimistic Concurrency Control doesn't work with UpdateMany
• Wrong type when retrieving multiple related records using fluent API
• Fluent API produces runtime error
• Column 'orderby_.....' in on clause is ambiguous
• [PostgreSQL] Cannot create or update row with large Decimal value within numeric range
• Failed transactions trigger multipleResolves
• The provided database string is invalid. Unable to parse URL. in database URL. on invalid (?) connection string
• relation query fails when thousands of records
• Unable to query records with certain values
• orderBy using self-referential relation in a nested relation fails with "table name \"TableName\" specified more than once"
• @updatedAt does not work with @default
• Prisma silently misses nested elements on MariaDB when it triggers a huge IN clause
• createdAt @default(now()) and updatedAt @updatedAt get different times on row creation
• Interactive Transactions: fatal when throwing string instead of Error() since 3.10.0
• Prisma MongoDB can not search for field with value $foo
• groupBy crashes when using enums
• MySQL DECIMAL(X, 5) returns incorrect precision.
• "Join" performance: (Not so) huge data set throws an error
• Error: The provided database string is invalid. Unable to parse URL. in database URL.
• [mongoDB] findRaw does not work within an interactiveTransaction
• Filters max size is exceeded
• Segmentation Fault when using SSL to connect inside a Debian container (node:18)
• 'prisma db seed' command errors out with error: sigsegv sevfault on node v18.3.0 with NextJS and TS
• Prisma fails on Postgres query with findMany
• Update Restrict failed on prisma referentialIntegrity
• invalid character in $let in $lookup pipeline when using cursor and order by query
• Find many returns empty array or null
• prisma studio crashes when connecting to Supabase with: 'Segmentation fault' error
• Once instance has previously been poisoned
• Sanitize error snapshots in new test setup
• The findMany query fails silently when the array length passed through IN parameter exceeds 999
• mongodb cannot use $runCommand Raw in transaction
• parent result: Some(ManyRecords { records: [Record { values: [Int(3), Int(1), DateTime(2022-08-08T12:27:55.310+00:00)], parent_id: None }], field_names: ["id", "userId", "createdAt"] }), relation: Relation { name: "post", model_a_name: "Comment", model_b_name: "Post", model_a: OnceCell((Weak)), model_b: OnceCell((Weak)), field_a: OnceCell((Weak)), field_b: OnceCell((Weak)), manifestation: Inline(InlineRelation { in_table_of_model_name: "Comment" }), internal_data_model: "#InternalDataModelWeakRef#" }
• Given exponent overflowing the maximum accepted scale (255).: TryFromIntError(())
• Once instance has previously been poisoned
• sqlite:cuid():called Result::unwrap() on an Err value: FingerprintError("Could not retrieve hostname")
• Calling findUnique concurrently with a DateTime column causes it to return null
• Maximum Call Stack Size Exceeded When Inserting large Array Object
• Prisma Client is incompatible with TypeScript 4.8
• libquery_engine-debian-openssl-1.1.x.so.node segmentation fault on Node v18.8.0
• This is a non-recoverable error: When creating Item with nested query
• Given exponent overflowing the maximum accepted scale (255).: TryFromIntError(())
• Prisma 4.3.0 takes 100x more time to generate types
• Tracing: prisma:engine spans always get sampled when using probability based samplers
• Datasource providers in prisma init is listing wrong values
• Disconnect with large queries
• N/A error and message with large raw queries
• only one @updatedAt is filled by the QE although multiple are accepted in the schema
• Insert fails if a table has a space in the name of the primary key
• Count method with two level join and two column join fails on SqlServer
• Bug with throwing formatted errors
• called Option::unwrap() on a None value
• Upsert error on MySQL: Query ... is required to return data, but found no record(s)
• Add tests for mongodb's findRaw, aggregateRaw and runCommandRaw in sequential transactions

Prisma

• sqlite: show better error when a permission denied error occurs
• Prisma v4 breaks support for empty dbgenerated() - invalid migration created with no schema change
• Binary engine: $disconnect never returns if engine failed to start

Prisma Migrate

• PostGis views access error

Prisma Studio

• Client error "Can't reach database" for some tables not others

Credits

Huge thanks to @abenhamdine, @miguelgargallo, @Clansty, @panoplied, @MEnnabah, @drzamich, @AndrewSouthpaw, @kt3k for helping!

💼 We're hiring!

If you're interested in joining our growing team to help empower developers to build data-intensive applications, Prisma is the place for you.

We're looking for a Developer Advocate (Frontend / Fullstack) and Back-end Engineer: Prisma Data Platform.

Feel free to read the job descriptions and apply using the links provided.

Prisma Data Platform

We're working on the Prisma Data Platform — a collaborative environment for connecting apps to databases. It includes the:

• Data Browser for navigating, editing, and querying data
• Data Proxy for your database's persistent, reliable, and scalable connection pooling.
• Query Console for experimenting with queries

Try it out and let us know what you think!

📺 Join us for another "What's new in Prisma" livestream

Learn about the latest release and other news from the Prisma community by joining us for another "What's new in Prisma" livestream.

The stream takes place on YouTube on Thursday, September 29 at 5 pm Berlin | 8 am San Francisco.