Don't automatically apply defer_foreign_keys in TableStatements.insertAll

[wild-coffee]

Describe the bug

when postgresql, use insertAll raised exception: "pragma synax error"

the genarate sql:
pragma defer_foreign_keys = on;

in batch is useful!

error:

  await AppDatabase.instance.screw
      .insertAll(listData!.map((e) => ScrewCompanion.insert(
            name: e.name,
            remark: Value(e.remark),
            displayName: Value(e.displayName),
          )));

success:

  await AppDatabase.instance.batch((b) {
    b.insertAll(
        AppDatabase.instance.screw,
        listData!.map((e) => ScrewCompanion.insert(
              name: e.name,
              remark: Value(e.remark),
              displayName: Value(e.displayName),
            )));
  });

[simolus3]

Thanks for the feedback! I don't think we should apply that pragma behind the scenes, it adds an implicit dependency on the dialect used (and since not all database systems support an equivalent option, necessarily leads to platform-specific behavior).

Changing this now is breaking though, so I'll fix the method by only applying that pragma to SQLite databases (2e40917). In the next major drift release, we can remove it entirely. I'll repurpose this issue to remind me of that change.

[wild-coffee]

Thanks!
I'm looking forward to the release of your new version.

Thanks for the feedback! I don't think we should apply that pragma behind the scenes, it adds an implicit dependency on the dialect used (and since not all database systems support an equivalent option, necessarily leads to platform-specific behavior).

Changing this now is breaking though, so I'll fix the method by only applying that pragma to SQLite databases (2e40917). In the next major drift release, we can remove it entirely. I'll repurpose this issue to remind me of that change.

[simolus3]

The first fix described in my last comment is part of drift: ^2.23.1.