# Bug 3114: Doh, syncing `null` not really working

- Status: closed
- Project: Zero
- Creator: @aboodman
- Assignee: @aboodman
- Labels: alpha-must
- Created: 2024-11-04T18:12:21Z
- Modified: 2024-11-21T07:03:16Z
- URL: https://bugs.rocicorp.dev/p/zero/issue/3114

## Description

I can't believe we didn't notice this in our own work yet, but `null` support is kind of broken:

- If a column has `null` in pg, we sync it as null to the client
- But the type system represents "optional" fields as `|undefined`
- So if we sync such a value type system will claim it is `undefined`, but it will actually be `null`
- And to explicitly set such a field to `null`, the only thing you can do is: `foo.bar = undefined as unknown as null`.

Optional fields should be typed as allowing `T|null|undefined` create/set/update. `undefined` means "make no change from previous value" (or "use default value in the case of create"). `null` means "explicitly set this field to null, overwriting previous value".

See context: https://discord.com/channels/830183651022471199/1300833980706455744/1302451501980975104

Perhaps makes sense to do https://bugs.rocicorp.dev/issue/3028 at same time or nearby this issue.

## Comments (4)

### @aboodman — 2024-11-05T19:40:47Z

Ironically, there is no 'unassign' feature in this here UI, and one can't be added because of this bug! As part of this bug add 'unassign' to zbugs.

### @aboodman — 2024-11-05T23:16:53Z

OK thinking about this there's a few different options:

1. I can have every `optional` field support both `null` _and_ `undefined`. On the read path we'd represent `null` and `undefined` just like we do today. On the write path, users could specify `null` explicitly to mean "overwrite with null" and leave `undefined` to mean "don't change this value". This is a little unfortunate because there are cases where we pass a field of a row someplace and that place now has to accept both `null` and `undefined` (or else caller has to fold). Here's a PR for this one: https://github.com/rocicorp/mono/pull/2930

2. We can represent `optional` fields with `undefined` on the read path (as it is today) and only use `null` for explicitly setting to null on the write path. This would involve modifying the CRUD code to explicitly allow `null`. This is more consistent with our current conventions of standardizing on `undefined`, but feels a little odd – we represent db null as `undefined` on the read path but `null` on the write path.

3. We can change `optional` fields to be represented as `null`. we'd use `null` on the read path for db null and null on the write path to mean "overwrite field to null". Then we'd use `undefiend` on the write path to mean "don't change this field". This feels like it makes more sense to me, but conflicts with the rest of the codebase's convention of preferring `undefined`.

### @rajczi — 2024-11-06T15:27:52Z

Some of the other tooling we are using has some VERY strange behavior around nulls as well.  Even to the point there are cases where prisma will write a string 'null' into the database for a nullable column.  You also can't have a null array value (empty array is fine, null fails).

Their default behavior on writes is that undefined fields are skipped when generating the sql statement which allows them to take the db default value.  Null is more explicit and will send the null to the database to update the field as null (overriding the default).  The string null case is just deeply weird and comes up in json fields mostly.

### @aboodman — 2024-11-21T07:03:16Z

We chose (3)