Upcoming special SQL NULL handling in drift_postgres

[davidmartos96]

Hello!
This is just to open a discussion on how would be the best way to support the upcoming SQL NULL values in the postgres package.

Particularly via the Dart select API and the customSelect method.

Originally the use case for json null was discovered when we were doing some polishing on the ElectricSQL Dart type mapping between Postgres and the drift schema. There were some issues with how the types were being mapped in your PR. Now the tests are run on a real Postgres instance so it's easier to see what's behaving differently.

Context: isoos/postgresql-dart#322

[simolus3]

When binding parameters, you can always write a custom type mapping to a TypedValue and drift won't mess with that, it's passed to the postgres package unchanged. That's probably what you would be doing for JSON/JSONB either way, right? So just setting the relevant flag to not turn that into an SQL null is sufficient.

The other direction is more complicated unfortunately, as we don't have access to the ResultRow in higher layers of drift, only the interpreted map. Do you need a way to distinguish the nulls in this direction as well?

[davidmartos96]

Yes, I was able to do the inserts with TypedValue via drift.
For the rows reading we could actually use the CustomType API if that's a possible solution. But right now, nulls don't go through it. This would be the ideal solution as we already have control over how the values are mapped in/out for all the supported types.

I agree it is a niche case. We don't have an use case other than being able to work with Electric supported values transparently via drift on the backend, same as on the client, which supports the JSON null on SQLite by using a custom serialization/deserialization.