Allow to create unique indexes

[sadortun]

New Feature / Enhancement Checklist

• [x] I am not disclosing a vulnerability.
• [x] I am not just asking a question.
• [x] I have searched through existing issues.

Current Limitation

parse-server is currently lacking a way to update DB schema. #7091 should address most of this issue, but unfortunatly, it does not seems like there is a way to add uniqueIndex despite being able to do so with the mongo client and postgre

db.collection.createIndex( <key and index type specification>, { unique: true } )

Feature / Enhancement Description

Add a isUniqueIndex parameter

SchemaController.createIndex(className, index , isUniqueIndex) {} 

Or even better, add an options parameter. The downside of this would be that itwould probably cause problems in inter-compatibility between mongo and postgre

 createIndex(className, index , options)

Example Use Case

schema.createIndex("_User" , {username:1} , true)
// or
schema.createIndex("_User" , {username:1} , {unique: true} )

Alternatives / Workarounds

We need to bypass the Parse Schema logic and implement it with manual migrations ... its a real pain !

3rd Party References

[mtrezza]

Thanks for suggesting.

From your description I assume this would be an enhancement, building on top of #7091. In that case you may want to wait until #7091 is finalized and merged and then look into how to go about a PR, or - if possible - coordinate with @Moumouls while #7091 is still in the works.

[sadortun]

@mtrezza it's related to #7091 but can be done in parallel. 98% of the work is in Parse Schema classes.

The only things ng to add in @Moumouls PR would be aboutn 3 lines :)

If we can get someone to do the PostgreSQL adapter I can probably do the MongoDB.

[cbaker6]

In my parse-hipaa repo, I’m currently able to add indexes to a parse-server by doing the following in my index.js (this works for Postgres and Mongo as it leverages the methods the Parse Storage adapters already have to create indexes):

async function createIndexes(){
    await Parse.Cloud.run('ensureClassDefaultFieldsForParseCareKit');
    let adapter = api.config.databaseController.adapter;
    const indexEntityIdPostfix = '_entityId';
    const indexEffectiveDatePostfix = '_effectiveDate';
    
    const schema = {
      fields: {
        uuid: { type: 'String' }
      },
    };
    
    const versionedSchema = {
      fields: {
        uuid: { type: 'String' },
        entityId: { type: 'String' },
        effectiveDate: { type: 'Date' }
      },
    };
    
    await adapter.ensureUniqueness('Patient', versionedSchema, ['uuid'])
    .catch(error => console.log(error));
    await adapter.ensureIndex('Patient', versionedSchema, ['entityId'], 'Patient'+indexEntityIdPostfix, false)
    .catch(error => console.log(error));
    await adapter.ensureIndex('Patient', versionedSchema, ['effectiveDate'], 'Patient'+indexEffectiveDatePostfix, false)
    .catch(error => console.log(error));
...

Are you suggesting you need a different way?

[sadortun]

Good to know.

Having to get the Adapter directly as a nuisance, but if you are all Ok with this, I won't change the existing Adapter code to add the options parameter. And use the adapter directly in @Moumouls PR.

[epietrowicz]

@sadortun can you share the changes you propose making to the MongoDB adapter to allow for unique indexes? I would prefer using this method rather than calling ensureUniqueness for each schema.

[mtrezza]

Added a bounty due to high demand for this feature.

[Moumouls]

I think we have a data structure challenge here. When you retrieve indexes ( also needed by Parse.Schema) you need to retrieve the uniqueness kind of the index.

Current data structure is{ indexA : { fieldA: 1, fileldB: -1}}; to support the uniquness retrieval, i can suggest a structure like

indexes: {
  indexA: {
    fieldA: 1
    fieldB: -1
  },
  indexB: [{
    fieldA: 1
    fieldB: -1
  }, { unique: true}]
}

By using an array it's easy to keep current interface of Parse.addIndex and Rest endpoint definition.

In that case, the only needed PR is:

• PR on Postgres/Mongo Adapter to support and translate the index array to

      this._mongoCollection.createIndex(
        indexRequest,
        { unique: true, background: true, sparse: true },
        error => {
          if (error) {
            reject(error);
          } else {
            resolve();
          }
        }
      );

[Moumouls]

tell me what you think @mtrezza @sadortun @dblythy :)

Using data structure approach instead of an "options" approach solve many issue as explained above

[mtrezza]

@Moumouls apologies, I didn't see your comment; data structure and type consistency is of course important, especially as we are restarting the efforts to move to TypeScript; @dblythy is already working on a PR, so maybe he can give some insight from a practical standpoint.

[Moumouls]

Thanks for your feedback @mtrezza, yes it's important to keep consistency of usages , and also to keep a global view for each feature / fix, to avoid simple local fixes that will not work globally across SDK/apis