Nullable arguments in .drift file queries

[frankvollebregt]

Is it possible to have a nullable argument in a query that I have defined in a .drift file?

For instance, when querying persons, I always pass a first name. If I pass a last_name too I want to use it to filter, but when I don't pass it, I want to return all people with the given first name.

CREATE TABLE persons (
  id INT NOT NULL PRIMARY KEY,
  first_name TEXT NULL,
  last_name TEXT NULL
);

getPersons: SELECT * FROM persons
WHERE first_name = :firstName AND (:lastName IS NULL OR last_name = :lastName);

When building the above, both firstName and lastName automatically get assigned a type String, and passing a String? will result in a syntax error.

I guess I could hack my way out of this by passing and checking for an empty string:

getPersonsEmptyString: SELECT * FROM persons
WHERE first_name = :firstName AND (:lastName = '' OR last_name = :lastName);

Or by something like the following:

getPersonsEqualSelf: SELECT * FROM persons
WHERE first_name = :firstName AND last_name= IFNULL(:lastName, last_name);

(Though come to think of it, I'm not sure what the last query would do if last_name was NULL)

[simolus3]

In cases where type inference fails, you can also declare the types of variables explicitly:

getPersons(:lastName AS TEXT OR NULL): SELECT * FROM persons
WHERE first_name = :firstName AND (:lastName IS NULL OR last_name = :lastName);

But I'm surprised that drift generates non-nullable parameters here since both columns are nullable to begin with. I couldn't reproduce this, are you on a recent drift version?

[frankvollebregt]

Ah, thank you. I tried AS INT? which didn't work, but the OR NULL should do the trick then.

You are correct, in my actual query I use the primary key, which is not nullable. I was just hoping for the build runner to infer that the argument is nullable when I explicitly check if it IS NULL, but at that point I might as well just add the OR NULL to its type.

I guess a check for INT? for nullable parameters, though more similar to Dart syntax, would be confusing if it worked at all since it's used for variables in SQL?

[simolus3]

I was just hoping for the build runner to infer that the argument is nullable when I explicitly check if it IS NULL

Yeah that's a reasonable assumption. I think we infer it as non-nullable due to the direct comparison, our analyzer doesn't realize that it's non-nullable in the RHS of the OR. Whenever we see the pattern x = y, we assume that they must have the same type. I don't think it will be easy to fix without introducing a lot of cases where we'd then make variables nullable that should be non-nullable though.

though more similar to Dart syntax, would be confusing if it worked at all since it's used for variables in SQL?

Yeah the OR NULL syntax feels more SQL-ish so that's what I've chosen here.

[FantasyCheese]

Hi, I have a related but different scenario:

CREATE TABLE categories (
  id        INTEGER   NOT NULL PRIMARY KEY,
  name      TEXT      NOT NULL UNIQUE,
  parentId  INTEGER   REFERENCES categories
);

children(:parentId):
SELECT * FROM categories
WHERE (:parentId IS NULL AND parentId IS NULL)
OR (parentId = :parentId);

When I pass null for parentId, I want to check explicitly the parentId is null,
and since parentId = NULL doesn't work in SQL, I have to write a little boilerplate like above.
It works fine but I'm just wondering if there are better ways to do it.

[simolus3]

I don't think we should add syntactic sugar for that. You're writing queries in SQL, so that's what you're signing up for ;)

In Dart, you could use equalsValue to generate this. So simple queries like children could be written in Dart entirely, but if you prefer SQL for most of the structure you could write SELECT * FROM categories WHERE $predicate and then call the method with (categories) => categories.parentId.equalsNullable(yourParentIdToFilterFor). That's essentially the same thing, but with the $ syntax you're telling drift to expect a Dart component to safely inject into the query.

[FantasyCheese]

Hey thanks for the info. I agree syntax sugar is not needed for this, and I'll look into equalsValue and $predicate.