Make a field unique within a collection without it being the Primary Key

[leggetter]

Is your feature request related to a problem? Please describe.

Given the following:

import {
  Field,
  PrimaryKey,
  TigrisCollection,
  TigrisDataTypes,
} from "@tigrisdata/core";

@TigrisCollection("users")
class User {
  @PrimaryKey(TigrisDataTypes.INT64, { order: 1, autoGenerate: true })
  id?: string;

  @Field()
  username!: string;

  @Field(TigrisDataTypes.DATE_TIME, { timestamp: "createdAt" })
  createdAt?: Date;
}

export default User;

I would like to be able to force the username field also to be unique.

Although username can be changed, it should still be globally unique within the application that I'm building. I believe this is also a reasonable requirement in applications where a user has a human-readable, globally unique identifier.

This is potentially similar to something like UNIQUE in MySQL.

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

Describe the solution you'd like

Something like:

import {
  Field,
  PrimaryKey,
  TigrisCollection,
  TigrisDataTypes,
} from "@tigrisdata/core";

@TigrisCollection("users")
class User {
  @PrimaryKey(TigrisDataTypes.INT64, { order: 1, autoGenerate: true })
  id?: string;

  @Field(TigrisDataTypes.STRING, { unique: true })
  username!: string;

  @Field(TigrisDataTypes.DATE_TIME, { timestamp: "createdAt" })
  createdAt?: Date;
}

export default User;

See @Field(TigrisDataTypes.STRING, { unique: true }).

Describe alternatives you've considered

Open to alternatives, but I couldn't think of any. Happy to discuss.

[ovaistariq]

@garrensmith @himank this could be done as an extension of secondary indexes.

[ovaistariq]

@leggetter what you can do to get this behavior is what I have done here: https://github.com/tigrisdata/tigris-examples-ts/blob/main/rest-express/src/api/routes/signup.ts

[himank]

@garrensmith @himank this could be done as an extension of secondary indexes.

That's the plan and it would be an easy extension for us.

[leggetter]

@leggetter what you can do to get this behavior is what I have done here: https://github.com/tigrisdata/tigris-examples-ts/blob/main/rest-express/src/api/routes/signup.ts

Yes, specifically this https://github.com/tigrisdata/tigris-examples-ts/blob/main/rest-express/src/api/routes/signup.ts#L47-L58

The email, in this case, has to be unique.

In my case, I want the username to be unique.

[garrensmith]

@leggetter you should be able to replace the email with email with username and make username the primary key and it will work the same

[leggetter]

@leggetter you should be able to replace the email with email with username and make username the primary key and it will work the same

@garrensmith - Gotcha. I know I can:

1. manage this myself if I want to, as per the linked example
2. change the field I'm using as a primary key to username

However, I'm suggesting a scenario where the developer wants to keep a separate ID as the primary key because the username could change but would like one field (the ID) to be consistent. My suggestion with this ticket is that Tigris supports a unique constraint on non-primary key fields.

Examples:

• Twitch
• Twitter
• YouTube (handle)
• GitHub

All of these places and types of apps and more, I believe, will have a unique ID that doesn't change along with a username that is also globally unique on the platform, but you can change it. Having the database manage this globally unique constraint seems to be a common requirement.

[ovaistariq]

I agree that we need support for the field's uniqueness. These suggestions are just a workaround until we have that feature available.