Many-to-Many seqelize-typescript not unique constraint key issue

Question

I have some issue with save the entity in postgres db with the same constraint key via my rest api service.

I have a pre-condtition - structure of entities should looks like this:

• Customer has many Resources ( 1 to n)
• Many Resources belongs to 1 Datacenter (n to 1)

As I correct understand pre-condition I implement many-to-many association for customer-resource-datacenter, where Resources is junstion table many-to-many diagram

When I try to create resource POST /api/resources- its proceed ok.

{"app": "test_app", "customerId": "0001", "datacenterId": "0002"}

But when I try do next POST /api/resources request with the same pair of key

{"app": "test_app_1",  "customerId": "0001", "datacenterId": "0002" }

I get 500 Error because of Key ("customerId", "datacenterId")=(0001, 0002) already exists.

So my first question is it actually acceptable - store the same pairs of foreign keys in junction table or it is impossible? And if yes - How I can reach it? Have I actually correctly defined the structure of the relationship between entities based on pre-condition???

I am using sequlize-typescript for orm and there is my code ->

Customer entity model:

@Table
export class Customer extends Model {
@Column({
    type: DataType.UUID,
    primaryKey: true,
    defaultValue: DataType.UUIDV4,
})
uuid: string;

@Column({
    type: DataType.STRING,
    allowNull: false,
})
name: string;

@BelongsToMany(() => Datacenter, () => Resource, 'customerId')
datacenters: Datacenter[]

@HasMany(() => Resource)
resources: Resource[]
}

Datacenter entity model:

@Table
export class Datacenter extends Model {
@Column({
    type: DataType.UUID,
    primaryKey: true,
    defaultValue: DataType.UUIDV4,
})
uuid: string;

@Column({
    type: DataType.STRING,
    allowNull: false,
})
name: string;

@BelongsToMany(() => Customer,() => Resource, 'datacenterId')
customers: Customer[]

@HasMany(() => Hardware)
hardware: Hardware[]
}

Resource entity model

@Table
export class Resource extends Model {
@Column({
    type: DataType.UUID,
    primaryKey: true,
    defaultValue: DataType.UUIDV4,
})
uuid: string;

@Column({
    type: DataType.STRING,
    allowNull: false,
})
app_identifier: string;

@ForeignKey(() => Customer)
@Column({
    type: DataType.UUID,
    allowNull: false,
})
customerId: string;

@BelongsTo(() => Customer)
customer: Customer;

@ForeignKey(() => Datacenter)
@Column({
    type: DataType.UUID,
    allowNull: false,
})
datacenterId: string;
}

Function to persist entity:

async create(resource: ResourceDto): Promise<Resource> {
    return await this.resourceRepository.create<Resource>({...resource});
}

I also tried do something like this:

    @BelongsToMany(() => Datacenter, {through: {model: Resource, unique: false}})
    datacenters: Datacenter[]

But it doesnt even compile.

Answer 1

Your pre-conditions don't lead to many-to-many. You would need many-to-many only when Resource will be a reference table with unique resources that you can link to certain customers as many times as you want (in this case you also will need a junction table CustomerResource).

In your case, you have customer resources that each have a link to a datacenter as a link to a reference table with unique datacenter records that can be used many times in different resources. So you need exactly the same relationships that are declared in pre-conditions:

User.hasMany(Resource)
Resource.belongsTo(User)
Resource.belongsTo(Datacenter)

That said you need to remove both @BelongsToMany definitions and use already defined @BelongsTo and @HasMany.