Use `NULL` for Empty Field Values

[emozio]

By default, Retool formats empty field values as an empty string. However, in the context of a database, this may lead to undesired results. One of which is certain database columns expect a specific data type (e.g. number) and therefore the query fails.

What's adding to the frustration is that, at least in our case, the database doesn't really clarify which column exactly is causing the issue, it only indicates "invalid input syntax for type date / number", for example.

I think there should be a general setting that lets you determine how to handle empty values: either use an empty string, a NULL value or perhaps not add the data to the Form data at all (but, this last bit may actually cause issues).

Anyway, I am curious to what has lead to making this technical decision, rather than simply accepting that NULL is the most generic and versatile.

[emozio]

By the way, one way to fix this seems to be by manually providing a fallback value when inserting/updates data in the database. That is, using key-value pairs, rather than one single {{ Form.data }} object.

However, I would argue that this is not quite efficient, especially with large forms. There is another way, but this involves using some JavaScript in order to swap out the empty strings in favor of NULL's. Please refer to the example below:

{{ _.mapValues(Form.data, (value) => value === "" ? null : value) }}

This would ensure empty strings are treated as NULL which I think is always a good idea.

Note: it's important to use a strict comparison ("===") to ensure values like "0" are not formatted as NULL, for example.

I can even imagine using a custom (global) JS function for DRY-ness sake:

window.nullify = function(data) {
    return _.mapValues(data, (value) => value === "" ? null : value);
}

And then simply refer to this function using: {{ nullify(Form.data) }}

[Tess]

Hi @emozio,

Thanks for the detailed post & feedback! I will share this feedback internally with the UI team.

As far as the current product decision, we intentionally handle empty field values differently based on the input type.

An empty number input can be null, but an empty text input, used to display strings, can only be evaluated as "":

[emozio]

That's odd, when I try to use the {{ null }} as a default value for a Number input, it appears to be converted automatically to a 0.

I would expect number inputs to be able to be empty.

[Tess]

It will default to null if you check on Allow null in the Validation section: