Empty string vs null question

[qooleot]

I have a question as regarding how a simple CRUD application handles empty strings awkwardly. I know there are workarounds and this isn't a bug, but it definitely seems like manual work needs too often, so mainly asking if I'm doing something wrong or if we can add a setting. Here is the scenario:

If I select a single row with a null column that has a postgresql type of text, it becomes null in the json. Not a big deal, but I have to convert all optional fields to "" when using a template like ejs so it doesn't actually say "null" on the screen. I know you can skip escaping html, but that would be bad if somehow bad javascript got in and then was executed on other clients.

Now if I submit a form with that field empty, a normal POST will return it as "" for the value (at least in expressjs's body parser). If I do a normal node-postgres insert, it then becomes an empty string instead of null in postgres, which is awkward and not efficient/error-prone for later queries.

Has anyone already wrestled with this manual conversion work? It doesn't seem like a big issue to caste things in the example above, but if you've got tons of CRUD calls and pages it seems like error-prone overhead.

Thanks!

[brianc]

I think what you'll probably want to do is put some cleanup code in your handlers or some global place to remove empty strings on post parameters if you don't want them in your db. I'm assuming you're doing some cleanup of user data or validations since you should never accept anything carte blanche from the user - that would be the best place to munge strings to either all empty strings if you prefer or all nulls if you prefer that. As far as getting data out of postgres - if you prefer it to change all null string values to '' string values you can do that by overriding the type parser for the string OID. https://github.com/brianc/node-postgres/blob/master/lib/types.js#L24