Keystone: Inconsistent mongo/postgres unique constraint behavior

[pahaz]

Bug report

I have a model:

keystone.createList('Organization', {
        phone: {
            type: Text,
            isUnique: true,
        },
        ...
})

• Mongo: it means that I have only one record with a null phone.
• Postgres: it means that I can have multiple records with a null phone.

CI tests fail with:

      [
        {
          message: 'E11000 duplicate key error collection: main.users index: phone_1 dup key: { phone: null }',
          locations: [ { line: 2, column: 3 } ],
          path: [ 'user' ],
          extensions: {
            code: 'INTERNAL_SERVER_ERROR',
            exception: {
              driver: true,
              name: 'MongoError',
              index: 0,
              code: 11000,
              keyPattern: { phone: 1 },
              keyValue: { phone: null },
              errmsg: 'E11000 duplicate key error collection: main.users index: phone_1 dup key: { phone: null }',

How-to solve

Use sparse index for MongoDB: more info here: https://stackoverflow.com/a/24430345

[MadeByMike]

@molomby This is down to different handling of unique constraints at the DB level right? I imagine we're not looking to homogenise them but rather let adapters decide what unique means?

I'm ok with divergent behaviour between adapters but we should probably document it. Having said that, if a sparse index makes more sense in this case, we should do that.

[molomby]

If you think about what "unique" means, this is actually an extension of null equality (#391).

As discussed in that issue, @MadeByMike is correct -- we've made the conscious decision to embrace the differences of each DB platform brings. The logic being that any one Keystone app has a single DB adapter so, within any one app, the behaviour of any _other_ DB adapter is more or less irrelevant. Also, trying to "smooth off" and abstract away all the differences between different DBs would be a both a _huge_ task and, ultimately, isn't actually desirable.

As such, on Mongo, the isUnique config creates a unique index with the standard Mongo behaviour:

Because of the unique constraint, MongoDB will only permit one document that lacks the indexed field. If there is more than one document without a value for the indexed field or is missing the indexed field, the index build will fail with a duplicate key error.

On Postgres, the resultant index uses the standard behaviour there:

PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with a UNIQUE index.

If, in either case, this wasn't what you were after, you can work around it in Mongo by manually creating a sparse index (as @pahaz mentions) or a partial index could also work.

On Postgres, you can get Mongo-like behaviour by indexing over an expression that converts nulls to some other value.

[pahaz]

I think it's better to drop unique field options and move it to knexOptions and mongooseOptions. If you don't want to homogenise Mongo/Postgres behavior.

It's unexpected for me. And I can't write Keystone modules for Mongo and Postgres. I should choose one!

[molomby]

You make a good point @pahaz!

[lachlanhawthorne]

Thank you @pahaz !! I was able to get this to work using the following:
phoneNumber: { type: Text, isUnique: true, isRequired: false, mongooseOptions: { sparse: true } },

[pahaz]

@lachlanhawthorne I think it's better to put it in docs

[maheraldous]

@lachlanhawthorne I think it's better to put it in docs

What dose this code mongooseOptions: { sparse: true }, do?