Using drift as a SQL query constructor

[BananaMasterz]

I was wondering if one could use drift only to construct queries for a MariaDB database for example which is not sqlite, because drift is type safe and way better than writing raw queries that return untyped data.

I know drift is not meant to be used like that obviously, but would there be any obvious issues?

[simolus3]

Yes, you can implement a custom QueryExecutor (or, perhaps simpler, a DatabaseDelegate from package:drift/backends.dart) that makes drift talk to a MariaDB database.

I know drift is not meant to be used like that obviously, but would there be any obvious issues?

Most parts of drift are built with the assumption of running against sqlite3. For DQL statements, the differences usually aren't that bad (although things like date and time functions probably won't work), but types or things like WITHOUT ROWID in table definitions will likely not work without further work on your side.

It think it can make sense to try this out, but be aware that you won't get a reliable solution like the one for sqlite3. If you're willing to tackle issues as they appear (PRs to add support for different DBMS implementations are welcome!), you can use drift with MariaDB.

[BananaMasterz]

Feel free to push the hardcoded changes so I know where to look

I have pushed the changes (I hope I didn't miss any, if I did it will hopefully come up sooner or later while I'm running lots of complicated queries, if it doesn't somebody else might find it 😅) as you can see, only in one instance we don't have access to the dialect, at
Column.escapedName

[BananaMasterz]

In mariadb, the following query is not valid:
DELETE FROM `project` WHERE `project_id` IN () AND `user_id` = 15;
The reason is IN () in an empty list throws syntax error. The error I get in my flutter app is:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND `user_id` = ?' at line 1

and I get no information as to where the error comes from in order to handle it as I want (for example I won't run the query if the Iterable I pass in isIn is empty.

Do you think it would be a good idea to do something like this?

Expression<bool> isIn(Iterable<D> values) {
  if (values.isEmpty) {
    throw 'tried to use "isIn" with empty iterable';
  }
  return _InExpression(this, values.toList(), false);
}

Expression<bool> isNotIn(Iterable<D> values) {
  if (values.isEmpty) {
    throw 'tried to use "isNotIn" with empty iterable';
  }
  return _InExpression(this, values.toList(), true);
}

We do not have access to dialect here but maybe it could be okay for SQLite too? Tho from what I understand SQLite doesn't care if it's empty. This way you know where the error is coming from and you can check your Iterable before doing something. Do you have any other solution or other location to implement something similar?

[simolus3]

Tho from what I understand SQLite doesn't care if it's empty

Empty iterables work as expected in sqlite3 though, 3 IN () evaluates to 0 and 3 NOT IN () evaluates to 1. Perhaps we can return const Constant(false) in isIn for empty iterables and const Constant(true) in isNotIn? That way we preserve the current behavior.

[BananaMasterz]

ah you are absolutely right... no idea why I didn't think of (well cuz I didn't think Constant(false) is an Expression<bool> type required by the return type oops... yeah that'll do it. This is great then:

Expression<bool> isIn(Iterable<D> values) {
  if (values.isEmpty) { // mariadb dialect
    return Constant(false);
  }
  return _InExpression(this, values.toList(), false);
}

Expression<bool> isNotIn(Iterable<D> values) {
  if (values.isEmpty) {
    return Constant(true);
  }
  return _InExpression(this, values.toList(), true);
}

Would this also be ok for Sqlite then?

[simolus3]

I think it's ok. I've added tests verifying the current behavior in e98da9c. If those tests still pass with your changes, we're good 😄