Forest new agent - relationship error between my tables after a forest schema:apply

jacques_liao · February 19, 2024, 2:55pm

Hello

After some testing on my local branch everything works.

Example of my beneficiaries table in local branch:

However, when I put my FOREST_AUTH_SECRET and FOREST_ENV_SECRET from the Staging environment, in my .env file, and ran the forest schema:apply command to apply my modifications to my Staging environment, I got of errors.

Example of my error log :

Invalid projection: field not found 'beneficiaries.fk_user'}  
ValidationError: Invalid projection: field not found 'beneficiaries.fk_user'}

Feature(s) impacted

relationship field names

Observed behavior

From what I can see of the error, it’s that the relationship between my beneficiaries and users tables isn’t working properly. because in our database server we have the field fk_userId, whereas on the .forestadmin-schema.json side I have fk_user

database server:
.forestadmin-schema.json:

        {
          "defaultValue": null,
          "enums": null,
          "field": "fk_user",
          "integration": null,
          "inverseOf": "beneficiaries",
          "isFilterable": true,
          "isPrimaryKey": false,
          "isReadOnly": false,
          "isRequired": false,
          "isSortable": true,
          "isVirtual": false,
          "reference": "users.id",
          "relationship": "BelongsTo",
          "type": "Uuid",
          "validations": []
        },

Is it because of this difference that I get the error? (PS: Locally, I don’t have this problem)

Expected behavior

that the fk_user field correctly takes into account the fk_userId field we have in our database server. Because locally it works fine.

Context

Project name: Nostrum Care v3
Team name: Op
Environment name: Staging
Agent (forest package) name & version:
“@forestadmin/agent”: “^1.0.0”,
“@forestadmin/datasource-sequelize”: “^1.5.21”,
“@forestadmin/datasource-sql”: “^1.0.0”,
“dotenv”: “^16.0.1”,
“pg”: “^8.8.0”,
“sequelize”: “^6.33.0”,
“stripe”: “^14.17.0”
Database type: Postgresql

Thanks in advance

morganperre · February 19, 2024, 4:12pm

Hello @jacques_liao,

Thanks for the detailed report.

schema:apply apply the current schema of your repository to the specified environment (using your .forestadmin-schema.json file).

Are you sure that your local database is the same as the remote one? I’m wondering if your models have been corrupted locally: fk_user vs fk_userId ?
Can you share your beneficiaries Sequelize models that cause the issue? Maybe it contains something? Ot this collection is generated with the @forestadmin/datasource-sql datasource ?
Do you set the isProduction agent variable? (It can cause the schema to not recompute locally)

We received a new schema Mon Feb 19 2024 14:44:25 GMT+0100 (Central European Standard Time) with the following 6bd641e7524c2abed3912d22a322563e868ed603 hash and 37 collections delared.

The previous one was sent Wed Jan 24 2024 16:53:55 GMT+0100 (Central European Standard Time) with the following 54e7a077f6a47ba58fedd1a139940d5f7e88c2dd hash and 39 collections delared.

I can’t determine what is happening for sure. But did you use the same @forestadmin/* versions between your local and your remote Staging?

Plus, we tend to restrict the usage of schema:apply command in favor of the normal development cycle. All environments except the production one produce a schema .forestadmin-schema.json and send it to our server.

For your production: You commit your updated .forestadmin-schema.json and restart your production server so it is taken into account.

kind regards?
Morgan

jacques_liao · February 19, 2024, 4:38pm

Hello

Thanks for the clarification! Is it possible to see the difference between the old .forestadmin-schema.json and the one I updated with schema:apply ?

It might help me to see the difference between the 2 if possible.

Thanks in advance

morganperre · February 19, 2024, 5:03pm

Hello @jacques_liao

The thing you’ve already spotted. You old schema was not referencing fk_user but fk_userId.

Where does the beneficiaries collection comes from? @forestadmin/datasource-sql or @forestadmin/datasource-sequelize?

Regards,
Morgan

jacques_liao · February 21, 2024, 9:06am

Hello @morganperre

So it’s just an oversight on my part, since at first everything was working and if I look at the commits we made on our side, I find the fk_user field and not fk_userId in the old file .forestadmin-schema.json file.

What’s more, if I try to search for the line containing the fk_user, I find it in the current version of our .forestadmin-schema.json file.

Otherwise, the beneficiaries collection comes from @forestadmin/datasource-sequelize.

Best regards

morganperre · February 21, 2024, 10:14am

Hey @jacques_liao,

Thanks for your answer.

Can you share your Sequelize model definition? I will try to reproduce such behavior, I’m wondering if there is an issue with BelongsTo in such a case.

Example of configuration to rename relation in Sequelize (the as property dictates field name )

beneficiarieModel.belongsTo(models.user, {
  as: 'fk_user',
  foreignKey: {
    allowNull: false,
     field: 'fk_userId',
  },
});

You may have a code issue with the relation that doesn’t have the same name between your environments.

Kind regards,
Morgan

jacques_liao · February 21, 2024, 10:35am

I currently have this in our code api

    this.models.beneficiary.hasOne(this.models.user, {
            foreignKey: "fk_myBeneficiaryId",
            allowNull: true,
            defaultValue: null,
            constraints: false
        });

and this in forest code:

const gatewayDBPassword = secretReader.readContent(process.env.NOSTRUMCARE_GATEWAY_DATABASE_PASSWORD);
agent.addDataSource(
  createSqlDataSource({
    uri: `${process.env.DATABASE_DIALECT}://${process.env.NOSTRUMCARE_GATEWAY_DATABASE_USERNAME}:${gatewayDBPassword}@${process.env.NOSTRUMCARE_GATEWAY_DATABASE_HOST}:${process.env.NOSTRUMCARE_GATEWAY_DATABASE_PORT}/${process.env.NOSTRUMCARE_GATEWAY_DATABASE_NAME}`,
    schema: process.env.DATABASE_SCHEMA,
    sslMode: process.env.NOSTRUMCARE_GATEWAY_DATABASE_SSL_MODE,
  }),
);

morganperre · February 21, 2024, 10:51am

Hum ok. But you also must have something like that? Can you also share it?

this.models.user.belongsTo(this.models.beneficiary, ...

Again, the as property allows you to have more control around fields names.

  as: 'fk_user',

Regards

morganperre · February 21, 2024, 10:53am

jacques_liao:

agent.addDataSource(
  createSqlDataSource({
    uri: `${process.env.DATABASE_DIALECT}://${process.env.NOSTRUMCARE_GATEWAY_DATABASE_USERNAME}:${gatewayDBPassword}@${process.env.NOSTRUMCARE_GATEWAY_DATABASE_HOST}:${process.env.NOSTRUMCARE_GATEWAY_DATABASE_PORT}/${process.env.NOSTRUMCARE_GATEWAY_DATABASE_NAME}`,
    schema: process.env.DATABASE_SCHEMA,
    sslMode: process.env.NOSTRUMCARE_GATEWAY_DATABASE_SSL_MODE,
  }),
);

Did you also define a @forestadmin/datasource-sequelize datasource?

const agent = createAgent(options).addDataSource(
  createSequelizeDataSource(sequelize),
);

Regards,
Morgan

jacques_liao · February 21, 2024, 11:05am

I have this

   this.models.user.hasOne(this.models.beneficiary, {
            foreignKey: "fk_userId",
        });

But I only have this part:

const agent = createAgent(options).addDataSource(
  createSequelizeDataSource(sequelize),
);

should it be added?

jacques_liao · February 21, 2024, 1:50pm

I seem to have the worry sorted out.

Which is strange because the relationship hasn’t changed, and I’ve only removed the call to the other database and deployed and it’s fine.

morganperre · February 21, 2024, 1:53pm

Hey, again @jacques_liao,

Sequelize models and associations

Well, normally you should have something like modelA.hasOne(modelB) and in the other model modelB.belongTo(modelA).

You can have a quick look at the official documentation. Associations | Sequelize

The A.hasOne(B) association means that a One-To-One relationship exists between A and B, with the foreign key being defined in the target model (B).

The A.belongsTo(B) association means that a One-To-One relationship exists between A and B, with the foreign key being defined in the source model (A).

To create a One-To-One relationship, the hasOne and belongsTo associations are used together;

So there is clearly something wrong with your Sequelize definition.

Datasources working with `@forestadmin/agent`

Well, datasources add collections. If you use the createSqlDataSource (@forestadmin/datasource-sql) one your Sequelize models are a little bit useless since this datasource do an automatic table discovery (called database introspection).

Opposed to that you can use your favorite ORM (Sequelize ).
In this case this case, if you use Sequelize and want your model definition to be the source of truth you MUST use the createSequelizeDataSource (@forestadmin/datasource-sequelize) and most likely remove the createSqlDataSource since you only have one DB.

Let me know if it’s clearer to you.

Kind regards,
Morgan

jacques_liao · February 21, 2024, 2:04pm

to use Sequelize you have to create each model by hand? and how are relationships managed via sequelize? and by creating the sequelize models as in the example in the doc, forest will detect the data you have in the database?

morganperre · February 21, 2024, 2:21pm

Yes, you need to handle them in this case. But this is more suited if you already use Sequelize!

You can have a quick look at the official documentation. Associations | Sequelize

No, either you use @forestadmin/datasource-sequelize and it uses your Sequelize model definition like I said or you use @forestadmin/datasource-sql and it detects the tables (models) you have in the database.

Regards

jacques_liao · February 21, 2024, 2:37pm

I asked the wrong question about relationships. What I wanted to know was how to integrate relationships into forest via sequelize, because there’s no example in the forest doc.

morganperre · February 21, 2024, 4:27pm

We are not gonna provide Sequelize documentation. Sequelize does it perfectly. Take a look at this

As long as you define correctly your models and use in this case the datasource provided by @forestadmin/datasource-sequelize it will be transparent for you.

Kind regards,
Morgan

jacques_liao · February 21, 2024, 4:52pm

for test, I have this:

// Connect to GATEWAY datasource
const gatewayDBPassword = secretReader.readContent(process.env.NOSTRUMCARE_GATEWAY_DATABASE_PASSWORD);
const sequelize = new Sequelize(`${process.env.DATABASE_DIALECT}://${process.env.NOSTRUMCARE_GATEWAY_DATABASE_USERNAME}:${gatewayDBPassword}@${process.env.NOSTRUMCARE_GATEWAY_DATABASE_HOST}:${process.env.NOSTRUMCARE_GATEWAY_DATABASE_PORT}/${process.env.NOSTRUMCARE_GATEWAY_DATABASE_NAME}`);

class User extends Model {}
User.init(
  {
    firstname: DataTypes.STRING,
  },
  { sequelize, modelName: 'users' },
);

// Create agent and import collections from sequelize
agent.addDataSource(
  createSequelizeDataSource(sequelize),
);

But I have this error:

warning: Skipping column 'users.id' (The "key" instance property has been removed.)
warning: Skipping column 'users.firstname' (The "key" instance property has been removed.)
warning: Skipping column 'users.createdAt' (The "key" instance property has been removed.)
warning: Skipping column 'users.updatedAt' (The "key" instance property has been removed.)

I also tried user without s but same result

morganperre · February 22, 2024, 8:58am

jacques_liao:

warning: Skipping column 'users.id' (The "key" instance property has been removed.)
warning: Skipping column 'users.firstname' (The "key" instance property has been removed.)
warning: Skipping column 'users.createdAt' (The "key" instance property has been removed.)
warning: Skipping column 'users.updatedAt' (The "key" instance property has been removed.)

I also tried user without s but same result

You probably still have the SqlDataSource added to your agent…

Remove this part.

agent.addDataSource(
  createSqlDataSource({

Regards,

jacques_liao · February 22, 2024, 9:51am

Hello @morganperre
yes I had already removed this part, but I have this error

dogan.ay · February 29, 2024, 10:09am

Hello @jacques_liao,

Our team has contacted you to set up a call so we can, more efficiently, help you sort out your onboarding issues.

If you encounter any other issue, don’t hesitate to open a new thread.

Best regards,

Topic		Replies	Views
New agent legacy - smartfield Help me! howtos , setup , smart-view	2	289	October 6, 2023
Migration to `@forestadmin/agent` - The new agent tries to load fields / smart fields for relationships that are not defined in the record details page Help me! smart-field , migration-to-agentv2 , relationship	3	37	August 28, 2024
Migration to `@forestadmin/agent` - one-to-many relationship with a computed field Help me!	3	39	August 28, 2024
Cannot update the Smart Relationship (FK) of a record (or even insert it) Help me!	9	249	November 9, 2023
Wrong model name Help me! setup	3	1858	May 4, 2021

Forest new agent - relationship error between my tables after a forest schema:apply

Feature(s) impacted

Observed behavior

Expected behavior

Context

Sequelize models and associations

Datasources working with @forestadmin/agent

Related topics

Datasources working with `@forestadmin/agent`