Cannot persist Read-Only association

[thiagomini]

Describe the bug

TLDR

• Given we are using Entity Schema definitions
• And we have a User entity class with its schema
• And we have an Employee entity class that shares some fields with the User (and resides in the same table)
• And we have an EmployeeDetail class and entity with a One-To-One reference to Employee
• And we persist a new user
• When we try to create and persist a new EmployeeDetailentity, passing the employee as an attribute
• Then we got an error: insert into "user" ("id") values (default) returning "id" - null value in column "email" violates not-null constraint

Consider the following entity relationships:

---
title: Read-Only association example
---
erDiagram
    company ||--o{ user : "employs"
    user ||--o{ address : "resides_at"
    user ||--o{ employee_detail : "has"

    company {
        int id
        string name
    }

    user {
        int id
        string email
        string first_name
        string last_name
        int company_id
        date created_at
        date updated_at
    }

    address {
        int id
        int user_id
        string street
    }

    employee_detail {
        int id
        int user_id
        float salary
    }

Loading

Also, consider that we have an Employee entity that is not described in a table above. That happens because an Employee is a "sub-type" of User, with the association with a company:

export class Employee {

  public readonly id: number;
  public readonly email: string;
  public readonly company: Reference<Company>;

  constructor(props) {
    Object.assign(this, props);
  }
}

⚠️ When we try to create a new EmployeeDetail in the database, pointing to a reference of Employee, Mikro-ORM throws an error: insert into "user" ("id") values (default) returning "id" - null value in column "email" violates not-null constraint

Stack trace

src/mikro-orm/mikro-orm-internal.module.spec.ts > MikroOrmInternalModule > creates a new employee detail
NotNullConstraintViolationException: insert into "user" ("id") values (default) returning "id" - null value in column "email" violates not-null constraint
 ❯ PostgreSqlExceptionConverter.convertException node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:24:24
 ❯ PostgreSqlDriver.convertException node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:197:54
 ❯ node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:201:24
 ❯ PostgreSqlDriver.nativeInsertMany node_modules/@mikro-orm/knex/AbstractSqlDriver.js:303:21
 ❯ ChangeSetPersister.persistNewEntity node_modules/@mikro-orm/core/unit-of-work/ChangeSetPersister.js:85:21
 ❯ ChangeSetPersister.executeInserts node_modules/@mikro-orm/core/unit-of-work/ChangeSetPersister.js:29:13
 ❯ ChangeSetPersister.runForEachSchema node_modules/@mikro-orm/core/unit-of-work/ChangeSetPersister.js:68:13
 ❯ UnitOfWork.commitCreateChangeSets node_modules/@mikro-orm/core/unit-of-work/UnitOfWork.js:739:9
 ❯ UnitOfWork.persistToDatabase node_modules/@mikro-orm/core/unit-of-work/UnitOfWork.js:703:13
 ❯ Parser.parseErrorMessage node_modules/pg-protocol/src/parser.ts:369:69
 ❯ Parser.handlePacket node_modules/pg-protocol/src/parser.ts:188:21
 ❯ Parser.parse node_modules/pg-protocol/src/parser.ts:103:30
 ❯ Socket.<anonymous> node_modules/pg-protocol/src/index.ts:7:48
 ❯ Socket.emit node:events:513:28

To Reproduce
Steps to reproduce the behavior:

1. Clone the minimum reproducible code repository: https://github.com/thiagomini/nest-mikro-orm-example/tree/bug/read-only-association
2. run yarn
3. run docker compose up -d
4. run yarn test

Expected behavior
A new employee_detail record should be created, given that we have the existing user in the database already.

Additional context
You can look at the schemas in the repo linked, but here's both the employee and employee_detail schemas:

Employee Schema

import { BigIntType, EntitySchema } from '@mikro-orm/core';
import { Company } from './company.entity';
import { Employee } from './employee.entity';
import { EmployeeDetail } from './employee-detail.entity';

export const employeeSchema = new EntitySchema<Employee>({
  class: Employee,
  tableName: 'user',
  forceConstructor: true,
  properties: {
    id: {
      type: BigIntType,
      primary: true,
      autoincrement: true,
    },
    email: {
      type: String,
      persist: false
    },
    company: { 
      entity: () => Company,
      reference: 'm:1',
      ref: true,
      persist: false
    },
    detail: {
      entity: () => EmployeeDetail,
      reference: '1:1',
      mappedBy: 'employee',
    }
  },
});

EmployeeDetail Schema:

import { BigIntType, EntitySchema } from '@mikro-orm/core';
import { Company } from './company.entity';
import { Employee } from './employee.entity';
import { EmployeeDetail } from './employee-detail.entity';

export const employeeDetailSchema = new EntitySchema<EmployeeDetail>({
  class: EmployeeDetail,
  tableName: 'employee_detail',
  forceConstructor: true,
  properties: {
    id: {
      type: BigIntType,
      primary: true,
      autoincrement: true,
    },
    employee: {
      entity: () => Employee,
      reference: '1:1',
      ref: true,
      inversedBy: 'detail'
    },
    salary: { 
      type: Number
    }
  },
});

Versions

Dependency	Version
node	18.12.1
typescript	5.1.3
mikro-orm	next
pg	8.11.2

[thiagomini]

Just to add some more context:

That situation is quite common in many applications - we have the same table (and almost always the user table) that represents different entities from different Bounded Contexts. In the case above, the user table represents:

1. IAM users - that have email, password, roles, permissions
2. Employees - That only care about name, email, and associated company

I believe it would be helpful to be able to map that kind of relationship in Mikro-ORM, giving us the power to reuse the same table for different purposes. I know that ultimately this is due to bad data design, but nonetheless, is a pretty common scenario.

EDIT: Pinpointing the issue, it's happening because MikroORM thinks the EmployeeEntity was not yet persisted - and tries to create it in the database. If we had a way to force Mikro-ORM to set an entity as persisted, it would fix that error. Alternatively, since the schema is defining that relationship with persist: false, it should also not try to create a new user.

[B4nan]

to reuse the same table for different purposes.

That is called single table inheritance:

https://mikro-orm.io/docs/inheritance-mapping#single-table-inheritance

Or maybe you have a table-per-type inheritance? That's unfortunately not supported.

[thiagomini]

Hey, Martin, thanks for the reply! However, my use case is similar but isn't precisely table inheritance. There are two main differences:

1. We don't have a discriminator column that is used to differ the types of "user".
2. user and employee entities do not have any inheritance relationship at all. The same row in the database represents two different things depending on the context - either an employee or an IAM user.

Basically, it's not about inheritance, it's about a set of attributes and relationships being used in one context, while another set of attributes and relationships are used in a different context. As I've mentioned, imagine the example:

user table record:
id: 1
firstName: John
lastName: Doe
email: [email protected]
companyId: 1

However, when we think about the user entity in an IAM context, this is what matters:
IAM User:

id: 1
email: [email protected]
roles: ['admin']

On the other hand, in the Company Management context, these are the attributes/relationships that matter:
Employee User:

id: 1
email: [email protected]
company: Acme
details: {
  salary: 10_000
}

So, the same user row is represented by a different set of attributes depending on the Bounded Context. That modeling strategy makes it easier to map only the necessary relationships and attributes for each context instead of reusing the same User table schema for everthing.