"Dataless" strings vs. None/NULL -- a possible strategy

Anvil Q&A
1

What I’m trying to do:
Reconcile GUI and DB standards, with regard to empty strings.

In a TextBox or TextArea, an empty string and a None are visually indistinguishable – and to avoid confusing users, they probably should be.

Strategically, GUI code should probably standardize on "" as the “no data” value for such fields, as then the type will always be str, which will always work with the str-related functions and operators. If these are applied to a None value, they will error out (raise an Exception), and the extra code needed to handle that contingency tends to obscure the overall intent.

In relational databases, however, these are two very distinct values, with special meaning for PRIMARY KEYs: None/NULL is not allowed in a PRIMARY KEY column. For UNIQUE and FOREIGN KEYs, None/NULL is also special: it effectively nullifies the entire key, ensuring that it points nowhere, and conflicts with nothing. In effect, it turns off enforcement of the KEY’s normal constraints. This is by design, in SQL databases, and if you want to leverage the strengths of such designs, it makes sense to follow the same rules.

Strategically, then, a database should prefer None/NULL as the representation of such “empty” string values. In fact, this is exactly what Oracle did, in all of the installations I’ve used. And it prevented a lot of problems.

The difficulty comes when GUI and database talk directly with each other. Now empty strings can quietly get into the database, where they don’t behave like None/NULL values, especially with regard to search() and Views.

To avoid this, you need a deliberate strategy: a layer of your own code, between database and GUI domains, to translate between the two preferred representations of “no-data” strings.

This could be as simple as a Model row class, with a @property for the GUI version of the value.

Is this the strategy that you use? Or do you take a different tack?

2

For any column that can be problematic as None, I usually just use column_value or "" or some other default value like {}/ etc. based on what kind of object I’m expecting.

I know that’s not an elegant or simple solution, but without making a bunch of model classes, that’s what i just tend towards as the path of least resistence.

3

I agree, there’s no one-size-fits-all elegant-and-simple solution. A layered architecture, at least, gives us a place to hang whatever solutions we discover we need.

You brought up a good point: boolean and simpleObject columns have their own alternatives to None. (E.g., a CheckBox renders None and False the same way. For most uses, they’re indistinguishable.)

So, in my architectural pattern, I might have to look at those column types, too. Thanks!

1 Like
4

Zod from anvil-extras might be handy


from anvil_extras import zod as z

def falsey_to_none(x):
    return x or None

parser = z.string().optional().transform(falsey_to_none)

parser.parse("foo") # "foo"
parser.parse("") # None
parser.parse(None) # None
parser.parse(123) # ParseError: Expected string, received integer

and if you want to do it on a dict like object


table_schema = z.typed_dict({
    "col_1": z.string().optional().transform(falsey_to_none),
    "col_2": z.boolean().optional().transform(bool)
})

table_schema.parse({"col_1": "", "col_2": None})
# {"col_1": None, "col_2": False}
1 Like