A question about the SqliteException: too many SQL variables

[HasanAlqaisi]

Hi,
I had a query like this:

await batch(
        (batch) => batch.deleteWhere(
          visitsTable,
          (tbl) => tbl.serverId.isNotNull() & tbl.serverId.isNotIn(serverIds),
        ),
      );

Where serverIds is an array of integers that can have +30,000 elements
For this reason I am getting:
SqliteException(1): while preparing statement, too many SQL variables, SQL logic error (code 1)

After searching about this I found out that SQLite has a limited number of variables in SQL query.
Then I tried to transform the query using customStatement

await batch((batch) => batch.customStatement('''
  DELETE FROM ${visitsTable.actualTableName}
  WHERE server_id IS NOT NULL AND server_id NOT IN (${serverIds.join(", ")});
'''));

And it works. But I have 2 questions:

1. Is it the right way?
2. Is there a way to make a query in drift API don't rely on variables (to inject numbers directly in the SQL statement) rather than using customStatement?

[simolus3]

Is there a way to make a query in drift API don't rely on variables

You can use isNotInExp([for (final id in serverIds) Constant(id)]).

But I don't recommend running these huge statements - since you're already using a batch, you could delete a single row at a time:

await batch((batch) {
  for (final id in serverIds) {
    batch.deleteWhere(visitsTable, (tbl) => tbl.serverId.isNotNull() & tbl.serverId.equals(id));
  }
});

Since these all generate the same SQL (with different values being bound to the variable), that might be better for the database.

[HasanAlqaisi]

Thanks! Could you please explain or send a reference shows how multiple statements in a batch can be better than one huge statement? So I can understand what to use in the future as well.

[simolus3]

There won't actually be multiple statements - just DELETE FROM table WHERE server_id IS NOT NULL AND server_id = ?. By using a batch, we can tell the database to just compile that statement once and then run it many times with different values. This is likely cheaper in the database, but it requires us to generate many statements in Dart only to realize they're all the same.

[HasanAlqaisi]

Thanks for the details! But now I saw that part of the query you're writing is AND server_id = ? but I am checking server_id against bunch of ids, if server_id is not one of these ids we delete the record.
This way I guess there's only one choice which is the first one isNotInExp([for (final id in serverIds) Constant(id)])?

[simolus3]

Oh, my bad 🤦 You're right, I've misread your original query. In that case there is no easy workaround for the IS NOT IN.