Cannot create entries with relations

Help me!
1

I am trying to create a new customer entry using local free forest admin project. The customer has a relation with an address table. All sequelize models were automatically generated.

Expected behavior

Clicking “Create” with all inputs filled should create a record.

Actual behavior

A red error notification appears with text “Customer creation failed: notNull Violation: customer.mailAddress cannot be null”. Backend returns status 422. An entry is not created.

Failure Logs

The payload:

{
  "data": {
    "attributes": {
      "customerId": 4,
      "dateOfBirth": "2021-01-03",
      "externalPartnerId": "4",
      "firstName": "qwewqe",
      "salute": "asd",
      "secondName": "zxc",
      "title": "zxcc"
    },
    "relationships": {
      "linkedMailAddress": {
        "data": {
          "type": "addresses",
          "id": "2132213"
        }
      }
    },
    "type": "customers"
  }
}

Customer model fragments (generated with lumber):

...
    mailAddress: {
      type: DataTypes.INTEGER,
      field: 'MailAddress',
      primaryKey: true,
      allowNull: false,
    },
...
  Customer.associate = (models) => {
    Customer.belongsTo(models.addresses, {
      foreignKey: {
        name: 'mailAddressKey',
        field: 'MailAddress',
      },
      targetKey: 'idAdresses',
      as: 'linkedMailAddress',
    });
...

The target address does exist in the database, was created using a form earlier this day. I can create a customer entry manually in the database.

Context

Please provide any relevant information about your setup.

  • Package Version: ^6.0.0
  • Express Version: ~4.17.1
  • Sequelize Version: ~5.15.1
  • Database Dialect: mysql
  • Database Version: 5.7.32 linux
  • Project Name: testowny4
2

Hello @jligeza,

Thanks for your message and welcome to our community! :raised_hands:

So that we can reproduce your problem as quick as possible, could you please share:

  • The complete model definitions of customers and addresses?
  • Your sql data structure?

And just to make a quick test, could you please try to put targetKey: 'MailAddress' instead of targetKey: 'idAdresses' in the customer association to address, and let me know if it makes it work?

Thank you!

3

Hello.
The quick test failed with an error:

Error: Unknown attribute "MailAddress" passed as targetKey, define this attribute on model "addresses" first
    at new BelongsTo (/[...]/testowny4/node_modules/sequelize/lib/associations/belongs-to.js:59:13)
    [...]

The sequelize models and sql tables are below:

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Customer = sequelize.define(
    'customer',
    {
      customerId: {
        type: DataTypes.INTEGER,
        field: 'CustomerID',
        primaryKey: true,
        allowNull: false,
      },
      externalPartnerId: {
        type: DataTypes.STRING,
        field: 'externalPartnerID',
      },
      salute: {
        type: DataTypes.STRING,
        field: 'Salute',
        allowNull: false,
      },
      title: {
        type: DataTypes.STRING,
        field: 'Title',
      },
      firstName: {
        type: DataTypes.STRING,
        field: 'FirstName',
        allowNull: false,
      },
      secondName: {
        type: DataTypes.STRING,
        field: 'SecondName',
        allowNull: false,
      },
      dateOfBirth: {
        type: DataTypes.DATEONLY,
        field: 'DateOfBirth',
      },
      mailAddress: {
        type: DataTypes.INTEGER,
        field: 'MailAddress',
        primaryKey: true,
        allowNull: false,
      },
    },
    {
      tableName: 'customer',
      timestamps: false,
    }
  )

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Customer.associate = (models) => {
    Customer.belongsTo(models.addresses, {
      foreignKey: {
        name: 'mailAddressKey',
        field: 'MailAddress',
      },
      targetKey: 'idAdresses',
      as: 'linkedMailAddress',
    })
    Customer.belongsToMany(models.policyfolder, {
      through: 'customerHasPolicyfolder',
      foreignKey: 'customer_CustomerID',
      otherKey: 'policfolder.customerId',
      as: 'policyfolderThroughCustomerHasPolicyfolders',
    })
  }

  return Customer
}

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize
  // This section contains the fields of your model, mapped to your table's columns.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const Addresses = sequelize.define(
    'addresses',
    {
      idAdresses: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        allowNull: false,
      },
      street: {
        type: DataTypes.STRING,
        field: 'Street',
      },
      streetNo: {
        type: DataTypes.STRING,
        field: 'StreetNo',
      },
      postalCode: {
        type: DataTypes.STRING,
        field: 'PostalCode',
      },
      city: {
        type: DataTypes.STRING,
        field: 'City',
      },
      country: {
        type: DataTypes.STRING,
        field: 'Country',
      },
    },
    {
      tableName: 'Addresses',
      timestamps: false,
    }
  )

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Addresses.associate = (models) => {
    Addresses.hasMany(models.broker, {
      foreignKey: {
        name: 'mailAddressKey',
        field: 'MailAddress',
      },
      sourceKey: 'idAdresses',
      as: 'mailAddressBrokers',
    })
    Addresses.hasMany(models.broker, {
      foreignKey: {
        name: 'billingAddressKey',
        field: 'BillingAddress',
      },
      sourceKey: 'idAdresses',
      as: 'billingAddressBrokers',
    })
    Addresses.hasMany(models.customer, {
      foreignKey: {
        name: 'mailAddressKey',
        field: 'MailAddress',
      },
      sourceKey: 'idAdresses',
      as: 'mailAddressCustomers',
    })
    Addresses.hasMany(models.policy, {
      foreignKey: {
        name: 'mailAddressKey',
        field: 'MailAddress',
      },
      sourceKey: 'idAdresses',
      as: 'mailAddressPolicies',
    })
    Addresses.hasMany(models.policy, {
      foreignKey: {
        name: 'billingAddressKey',
        field: 'BillingAddress',
      },
      sourceKey: 'idAdresses',
      as: 'billingAddressPolicies',
    })
    Addresses.hasMany(models.policyHasSectionlocated, {
      foreignKey: {
        name: 'locatioAddressKey',
        field: 'LocatioAddress',
      },
      sourceKey: 'idAdresses',
      as: 'locatioAddressPolicyHasSectionlocateds',
    })
  }

  return Addresses
}

CREATE TABLE `Addresses` (
  `idAdresses` int(11) NOT NULL,
  `Street` varchar(70) DEFAULT NULL,
  `StreetNo` varchar(45) DEFAULT NULL,
  `PostalCode` varchar(45) DEFAULT NULL,
  `City` varchar(70) DEFAULT NULL,
  `Country` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idAdresses`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `customer` (
  `CustomerID` int(11) NOT NULL,
  `externalPartnerID` varchar(45) DEFAULT NULL COMMENT 'External Key',
  `Salute` varchar(4) NOT NULL,
  `Title` varchar(5) DEFAULT NULL,
  `FirstName` varchar(60) NOT NULL,
  `SecondName` varchar(60) NOT NULL,
  `DateOfBirth` date DEFAULT NULL,
  `MailAddress` int(11) NOT NULL,
  PRIMARY KEY (`CustomerID`,`MailAddress`),
  UNIQUE KEY `externalPartnerID_UNIQUE` (`externalPartnerID`),
  KEY `fk_customer_Adresses1_idx` (`MailAddress`),
  CONSTRAINT `fk_customer_Adresses1` FOREIGN KEY (`MailAddress`) REFERENCES `Addresses` (`idAdresses`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='List of Partners like Customer and Brokers';
4

Thanks for your quick answer @jligeza.

Ok, so you are in the particular case of trying to reference a foreign key that is not a primary key.
We’ve already identified that it causes issues for creation and update on Forest Admin.

We are currently working on a fix :wrench:
I can’t give you a clear ETA but it will be released in coming weeks.

We’ll keep you updated!
Thanks for your patience.

1 Like
5

After deeper investigation @jligeza, it seems that the problem is not what we were thinking.
It’s probably due to the fact that you have a field that is both primary and foreign key.
We’re working on a fix and we’ll update you as soon as we have some news.

Thanks for bringing that up!

1 Like