Open Connections Remain After Query Errors, Causing DB Connection Saturation and Crashes

[AmoonPod]

What version of Prisma Dart is running?

5.3.1

What version of Prisma CLI is running?

6.3.1

What type of app are you using?

Flutter

What database are you using?

PostgreSQL

What steps can reproduce the bug?

1. Create a Dart script using the ORM package as shown below:

final prisma = PrismaClient();

void main(List<String> args) async {
  try {
    var query = '''
    SELECT * FROM "Contract"; 
    ''';

    // A series of valid queries
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);

    // Execute multiple queries concurrently, including one with an intentional syntax error
    Future.wait([
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query('SELEC Q FROM Q'), // Intentional syntax error
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
    ]);

    // More queries after the batch
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
  } catch (e) {
    print(e);
  } finally {
    await prisma.$disconnect();
  }
}

2. Run the script and observe that when the erroneous query ('SELEC Q FROM Q') is executed, an error is thrown.
3. Check your PostgreSQL database for open connections. You will notice that connections remain open even after the error, and each subsequent run increases the number of open connections.

What is the expected behavior?

The ORM should handle query errors by properly closing or cleaning up the connection (or rolling back the transaction), ensuring that no open connections remain.
This behavior would prevent the accumulation of database connections and avoid saturating the maximum allowed connections.

What do you see instead?

An error is thrown when a query fails due to syntax issues.
Open connections are not closed after a query error, leading to the creation of additional connections on subsequent queries.
Running the script multiple times results in a continual accumulation of open connections, which eventually overloads and crashes the production database.

Additional information

When executing raw queries using the ORM package with PostgreSQL, queries that produce syntax errors result in open connections that are not properly closed. This issue causes an accumulation of database connections over time, which eventually saturates the maximum allowed connections. In our production environment—where the application is actively used—this leads to connection overloads and system crashes. Resolving this problem is critical to maintain the stability and reliability of our service.

[AndryHTC]

It's true. It seems also that all the queries running in parallel spikes up N as the number of the concurrent queries.

Then the connections count remains that and just remain idle eternally.

I don't even know if it should create N connections for this example. Aren't parallel queries for the same connection just "sessions" in postgres? The dart postgres package, for this exact example keeps only 1 connection, instead of the 12 of prisma

[medz]

I'm investigating, and I'm expected to fix it within three hours.

[medz]

import '../prisma/client/client.dart';

final prisma = PrismaClient(
    datasourceUrl: "postgresql://seven@localhost:5432/prisma?schema=public");

main() async {
  try {
    final query = 'SELECT * FROM "User"';
    // A series of valid queries
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);

    // Execute multiple queries concurrently, including one with an intentional syntax error
    Future.wait([
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query('SELEC Q FROM Q'), // Intentional syntax error
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
      prisma.$raw.query(query),
    ]);

    // More queries after the batch
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
    await prisma.$raw.query(query);
  } catch (e) {
    print(222);
    rethrow;
  } finally {
    print(333);
    await prisma.$disconnect();
  }
}

I found that Future.wait cannot enter the try-catch process normally. Therefore, prisma.$disconnect() is not triggered; can it be changed to await Future.wait?

[medz]

https://www.prisma.io/docs/orm/overview/databases/postgresql#arguments

Additionally, additional URL parameters can be configured to limit the connection pool size.

[medz]

I'm still working on how to cause the Dart main process to exit abnormally and then shut down safely.

[medz]

import 'dart:async';

import '../prisma/client/client.dart';

final prisma = PrismaClient(
    datasourceUrl: "postgresql://seven@localhost:5432/prisma?schema=public");

main() async {
  await runZoned(() async {
    print(000);
    try {
      final query = 'SELECT * FROM "User"';
      // A series of valid queries
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);

      // Execute multiple queries concurrently, including one with an intentional syntax error
      Future.wait([
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query('SELEC Q FROM Q'), // Intentional syntax error
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
        prisma.$raw.query(query),
      ]);

      // More queries after the batch
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
      await prisma.$raw.query(query);
    } catch (e) {
      print(e);
      rethrow;
    } finally {
      print(333);
      await prisma.$disconnect();
    }
  }, onError: (e) async {
    print(e);
    await prisma.$disconnect();
  });

  print(444);
}

@AndryHTC @AmoonPod After unremitting efforts, I finally found a temporary solution.

But this requires your program to complete it; I still haven't successfully completed the method of closing the process when the main program exits abnormally. But wrapping your program in runZoned can safely clean up the process.

In addition, if your program is a web program and it causes an abnormal exit. Then you should set the correct exception response and interception.