Sails: sparse unique index for string attributes (base value for type: 'string' attributes)

[Clan-Utility]

Sails version: 1.1.0
Node version: v9.5.0
NPM version: 5.6.0
DB adapter name: sails-mongo
DB adapter version: 1.0.1
Operating system: macOS 10.13.6

---

A string attribute will be set to empty string if no value if provided. Even if I remove the string attribute from mongodb, after sails lift, they appear again as empty string.
This makes it impossible to set an sparse unique index on the attribute.

How to solve this by making undefined as the default value of a string attribute?

[sailsbot]

@Clan-Utility Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[raqem]

I think your on to something with making the attribute default to undefined. Here is how the docs suggest making a default value for modal attributes.
https://sailsjs.com/documentation/concepts/models-and-orm/attributes#?default-values

[rachaelshaw]

@Clan-Utility that doc page (Models and Orm > Attributes) @raqem linked to above explains this in more detail, but attributes with type: 'string' have a base value of '', and Waterline always uses the base value if no value is provided. I don't think there's currently a way of explicitly setting a string attribute to undefined. The closest solution I can think of off the top of my head would be to change your attribute to type: 'json' and set allowNull: true, which would allow you to set the value as null instead of ''. (But using type: 'json' will no longer guarantee that those values will be strings, so be wary of that.)

[rachaelshaw]

@Clan-Utility as a follow-up to my previous comment, I just really want to emphasize that it's probably best to keep your attribute as type: 'string', as having a guaranteed data type will help make the code less error-prone.

[Clan-Utility]

I think your on to something with making the attribute default to undefined. Here is how the docs suggest making a default value for modal attributes.
https://sailsjs.com/documentation/concepts/models-and-orm/attributes#?default-values

I've already tried to use defaultsTo: undefined, which does not work. That is, a string attribute is still set to '' if no value provided.

[Clan-Utility]

@Clan-Utility that doc page (Models and Orm > Attributes) @raqem linked to above explains this in more detail, but attributes with type: 'string' have a base value of '', and Waterline always uses the base value if no value is provided. I don't think there's currently a way of explicitly setting a string attribute to undefined. The closest solution I can think of off the top of my head would be to change your attribute to type: 'json' and set allowNull: true, which would allow you to set the value as null instead of ''. (But using type: 'json' will no longer guarantee that those values will be strings, so be wary of that.)

I tried to set allowNull: true to string attribute, and sails will set the attribute to null by default. However, multiple null values will still violate the unique constraint and cause DuplicateKey error. So this won't solve the problem.

[rachaelshaw]

@Clan-Utility if using null won't work, I'm not really sure what to tell you: there isn't a way to set string attributes to undefined. In the physical (aka database) layer, we don't make a distinction between null and the absence of a property, to keep it consistent between apps. You can always do whatever you like with a native query, but we wouldn't really recommend going down this road if you can avoid it, as sticking with conventions makes it easier to benefit from the ecosystem of other developers.

Unrelated: I just realized I had it backwards before: type: 'json' _doesn't_ accept null and type: 'string' _does_, so just wanted to mention that for anyone who finds this issue in the future.

[Clan-Utility]

I can make the attribute absent by removing it in beforeCreate() and beforeUpdate() if it has null or '' value. The only problem is with sails lift in development environment, where waterline will normalize existing data and add back the null or '' value, ignoring beforeCreate() and beforeUpdate().

Can we add support of allowUndefined: true or defaultsTo: undefined to attributes? Would that be OK for all databases?

[mikermcneil]

Hey @Clan-Utility, thanks for following up. I see where you're coming from, but something like allowUndefined wouldn't work well for us across the board, because much like defaultsTo: undefined, it would break our type safety guarantees in Waterline.

re: automigrations, one option that might work for you is to develop locally with sails console --safe (or otherwise to set migrate: 'safe' in config/models.js)

If you went with that approach, reckon the beforeCreate/beforeUpdate workaround you suggested would do the trick?

If not, I think your other best option would be to change your app so it tolerates null or (better yet, imo) empty string literals instead of depending on properties being absent. (I think if you go with that last approach, you might enjoy from some of the benefits @rachaelshaw mentioned above.)

[Clan-Utility]

@mikermcneil Good to get confirmed that defaultsTo: undefined is not suitable for Waterline.

Setting migrate: 'safe' will work since it won't normalize existing data. But I am using a hook called sails-hook-mongoat, which will create indexes in mongodb by definition in models during automigration. I will not enjoy its conveniency when migrate: 'safe' in dev environment.

The last option is of course something I should consider. Moving the ensurance of sparse uniqueness from database level to application level means I should employ transaction or mutual exclusion in the application code which will bring more complexity. But that will solve the problem.

My current solution is:
1) in prod environment:
a) create sparse indexes manually or by tools like sails-db-migrate
b) use beforeCreate/beforeUpdate to remove null and ''
2) in dev environment:
Work without the sparse indexes