Transaction Malfunction

[Joschiller]

What version of Prisma Dart is running?

^5.1.1

What version of Prisma CLI is running?

5.21.1

What type of app are you using?

Flutter

What database are you using?

SQLite

What steps can reproduce the bug?

Schema

schema-excerpt:

generator client {
  provider   = "dart run orm"
  output     = "../lib/_generated_prisma_client"
  engineType = "flutter"
}

datasource db {
  provider = "sqlite"
  url      = "file:./db.sqlite"
}

model User {
  id   Int @id @default(autoincrement())
  name String @unique

  currentlySelected Boolean
}

prisma.dart and main.dart are initialized as described in https://prisma.pub/getting-started/flutter.html

Query Examples

I tried to run the transactions as described in the official docs (https://prisma.pub/queries/transactions.html).

After restarting my application I can see, that no changes were actually written to the database.

Attempt 1:

Future<List<D>> deleteManyAndReturn<T, D>(
  List<T> itemsToDelete,
  Future<D?> Function(PrismaClient tx, T item) doDelete,
) async {
  final deleted = <D>[];
  await prisma.$transaction(
    (tx) async {
      for (final item in itemsToDelete) {
        final del = await doDelete(tx, item);
        if (del != null) {
          deleted.add(del);
        }
      }
    },
  );
  return deleted;
}

await deleteManyAndReturn(
  ids,
  (tx, id) => tx.user.delete(
        where: UserWhereUniqueInput(id: id),
      ));

Attempt 2:

import 'package:work_time_table_mobile/_generated_prisma_client/client.dart';
import 'package:work_time_table_mobile/prisma.dart';

Future<List<D>> deleteManyAndReturn<T, D>(
  List<T> itemsToDelete,
  Future<D?> Function(PrismaClient tx, T item) doDelete,
) async {
  final deleted = <D>[];
  final tx = await prisma.$transaction.start();
  try {
    for (final item in itemsToDelete) {
      final del = await doDelete(tx, item);
      if (del != null) {
        deleted.add(del);
      }
    }
    await tx.$transaction.commit();
  } catch (err) {
    await tx.$transaction.rollback();
  }
  return deleted;
}

await deleteManyAndReturn(
  ids,
  (tx, id) => tx.user.delete(
        where: UserWhereUniqueInput(id: id),
      ));

What is the expected behavior?

The transactions should run all overhanded deletions and return the deleted elements.

This is successful if running the queries without any transactions.

E.g.:

Future<List<D>> deleteManyAndReturn<T, D>(
  List<T> itemsToDelete,
  Future<D?> Function(PrismaClient tx, T item) doDelete,
) async {
  final deleted = <D>[];
  for (final item in itemsToDelete) {
    final del = await doDelete(prisma, item);
    if (del != null) {
      deleted.add(del);
    }
  }
  return deleted;
}

await deleteManyAndReturn(
  ids,
  (tx, id) => tx.user.delete(
        where: UserWhereUniqueInput(id: id),
      ));

What do you see instead?

Once transactions are used, no change is performed in the database. - When restarting my applicaiton, all formally deleted entries are re-appearing in the UI.

Additional information

I also tried it for several update statements. This leads to the same behavior. (Running queries without transactions works - once transactions are introduced to ensure consistency between interdependent statements, no update is persisted.)

For example, I tried it with the following statements for setting one single user as "currentlySelected":

await prisma.$transaction((prisma) async {
  await prisma.user.updateMany(
    data: const PrismaUnion.$1(UserUpdateManyMutationInput(
      currentlySelected: PrismaUnion.$1(false),
    )),
  );
  await prisma.user.update(
    data: const PrismaUnion.$1(UserUpdateInput(
      currentlySelected: PrismaUnion.$1(true),
    )),
    where: UserWhereUniqueInput(id: id),
  );
});

My app is currently running with:

• sdk: ^3.5.2
• orm_flutter: ^0.2.0
• orm: ^5.1.1

I testet the functionality on Android 12.

[medz]

@Joschiller Thank you for your detailed feedback. I have reproduced the error. Currently, it is located in the engine problem. I will notify you in the comments if there are any fixes or other progress. Thank you again for your contribution. ❤️

[Joschiller]

Thank you very much for the quick reply! Looking forward to the update.

Have a nice weekend :-)

[medz]

@Joschiller Have a great weekend, I fixed it in orm_flutter:0.3.1 version.

Sorry, I can't fix it in 0.2.x, because my time and energy only allow me to maintain the latest version branch at the moment.

[Joschiller]

Just installed and tested the fix. It's all working :-)

Thanks again for your effort!