Enable to research on a specific table

Expected behavior

Being able to research a user

Actual behavior

Server error
I can filter my data by id and I can order the view by id

Failure Logs

While using the research function on the user table I have that error :

2|admin  | [forest] 🌳🌳🌳  Unexpected error: function lower(uuid) does not exist
2|admin  | SequelizeDatabaseError: function lower(uuid) does not exist
2|admin  |     at Query.formatError (/home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/dialects/postgres/query.js:386:16)
2|admin  |     at Query.run (/home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/dialects/postgres/query.js:87:18)
2|admin  |     at processTicksAndRejections (internal/process/task_queues.js:93:5)
2|admin  |     at async /home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/sequelize.js:619:16
2|admin  |     at async PostgresQueryInterface.rawSelect (/home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/dialects/abstract/query-interface.js:994:18)
2|admin  |     at async Function.aggregate (/home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/model.js:1986:19)
2|admin  |     at async Function.count (/home/ubuntu/releases/20210305201057/forest-admin/node_modules/sequelize/lib/model.js:2037:12)

My table definition is

const User = sequelize.define(
    'users',
    {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
      },
      firstname: {
        type: DataTypes.STRING,
        defaultValue: '',
        validate: {
          is: /^[a-zA-Z\p{L}' -]*$/u,
        },
        // This is needed to set empty strings. (see https://community.forestadmin.com/t/cant-save-not-null-fields-with-empty-strings/1091/3.)
        set(val) {
          this.setDataValue('firstname', val || '');
        },
      },
    },
    {
      tableName: 'users',
      underscored: true,
      timestamps: false,
      schema: process.env.DATABASE_SCHEMA,
    },
  );

It is linked to two other tables :

  User.associate = (models) => {
    User.belongsTo(models.accounts);
    User.hasMany(models.consents, {
      foreignKey: 'created_by_user_id',
      as: 'authoredConsent',
    });
    User.belongsToMany(models.patients, { through: models.consents });
    User.belongsToMany(models.locations, { through: models.locationsUsers, foreignKey: 'user_id', otherKey: 'location_id' });
  };

and it has a beforeUpdate rule that does not use the id.

I don’t have the problem with any of my other tables

Context

Please provide any relevant information about your setup.

  • Package Version:3.10.9 (lumber cli)
  • Express Version:7.9.6
  • Sequelize Version: 6.7.7
  • Database Dialect: postgresql
  • Database Version:0.0.1
  • Project Name: Darwin

Hello @Alix_Bouloc,

I can see in the code where it comes from. I’ll try to reproduce your issue on uuids. Can you please also share with us the SQL to create your users table, with the relevant columns?

Here the code :

CREATE TABLE "user"
(
 id uuid NOT NULL DEFAULT uuid_generate_v4(),
    email citext  NOT NULL,
    email_verified boolean NOT NULL DEFAULT false,

    firebase_uid character varying NOT NULL);

ALTER TABLE "user" ADD CONSTRAINT "PK_user_id" PRIMARY KEY (id) NOT DEFERRABLE INITIALLY IMMEDIATE;

Thanks for your answer.

Could you also share with us:

  • The SQL model of your linked tables (maybe only the id and a random text field if possible)
  • The Sequelize model of these tables?

It seems to be related to a UUID field. Can you also check if you don’t have a mismatch between the SQL declaration of a UUID field and the sequelize declaration of its type?

I checked with a simple table users having a uuid field, and I cannot reproduce your issue. So I suppose that the relationship matters in your case to reproduce the issue.

I’ve sent you the liaison table in PM.

Hi @Alix_Bouloc,

Guillaume gave me your information.
From your SQL definition, I’ve created a Forest project but I was not able to reproduce the issue :man_shrugging:

There are 2 things I want to ask you:

  • Did you make some deep changes on the model definitions that were generated automatically by lumber? For instance changing a hasMany to a belongsToMany relationship (I’ve seen some differences that’s why I ask)?
  • Is an upgrade of forest-express-sequelize something doable to see if it fixes the issue?

Thanks.

Hello,

So I tried updating forest-express-sequelize using this : Upgrade to v7 - Documentation (it is quite a difficult documentation to find, it is not well referenced in google and not directly linked in the changelog)
But it doesn’t change anything.

Also, my model definition is the one automatically generated, I did not changed it. I don’t even know how to prevent his regenaration everytime I run my server locally ^^’

Hello @Alix_Bouloc,

I have been trying to reproduce your issue for 2 hours but without any success … I’ve created a project thanks to the dump you share to us and I’m able to search inside the user collection. However I have an idea where your bug could come from. The definition of the model user in my case is different and especially at the level of associations. Could you modify them with the ones below and tell me if it works?

Thanks, :slight_smile:

// This model was generated by Lumber. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models
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 Users = sequelize.define('users', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: Sequelize.literal('uuid_generate_v4()'),
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    emailVerified: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      allowNull: false,
    },
    firebaseUid: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  }, {
    tableName: 'users',
    underscored: true,
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  Users.associate = (models) => {
    Users.belongsTo(models.accounts, {
      foreignKey: {
        name: 'accountIdKey',
        field: 'account_id',
      },
      as: 'account',
    });
    Users.hasMany(models.consents, {
      foreignKey: {
        name: 'createdByUserIdKey',
        field: 'created_by_user_id',
      },
      as: 'createdByUserConsents',
    });
    Users.hasMany(models.consents, {
      foreignKey: {
        name: 'userIdKey',
        field: 'user_id',
      },
      as: 'consents',
    });
  };

  return Users;
};

Hi,

I tried to change my associations with yours, and it works !

But you’re lacking one association which is this one :
User.belongsToMany(models.locations, { through: models.locationsUsers, foreignKey: 'user_id', otherKey: 'location_id' });

And when I add it, again I have the same issue. How should I write it to make it works ?

Thank you !

Great, this is good news! So can you try to replace the association you shared with this one in the model user:

Users.belongsToMany(models.locations, {
      through: 'locationsUsers',
      foreignKey: 'user_id',
      otherKey: 'location_id',
      as: 'locationsThroughLocationsUsers',
    });

And on the location model side I have this association to user:

    Locations.belongsToMany(models.users, {
      through: 'locationsUsers',
      foreignKey: 'location_id',
      otherKey: 'user_id',
      as: 'usersThroughLocationsUsers',
    });

The search works on my end so I suppose that it should do the trick. Let me know if it helps :slight_smile:

Best,

I tried it but it does not solve the problem. As soon as I had the mention to one or the other of by two many to many relationships, the bug appears.

I have another many-to-many relationship on my server that doesn’t cause the problem, I’ll try to compare what I’m doing differently between both. Thanks for your help! It helped me narrow it down!
I have my next step :smiley:

I’ll give you the answer when I’ll solve it.

Hello @Alix_Bouloc,

Thank you for the update :slight_smile:

Please let us know if we can be of any help.