Enable to research on a specific table

Alix_Bouloc · March 8, 2021, 3:19pm

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

GuillaumeGautreau · March 8, 2021, 3:23pm

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?

Alix_Bouloc · March 8, 2021, 3:41pm

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;

GuillaumeGautreau · March 8, 2021, 4:06pm

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.

Alix_Bouloc · March 8, 2021, 4:34pm

I’ve sent you the liaison table in PM.

anon34731316 · March 9, 2021, 5:36pm

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

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.

Alix_Bouloc · March 10, 2021, 2:04pm

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 ^^’

anon16419211 · March 10, 2021, 4:04pm

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,

// 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;
};

Alix_Bouloc · March 10, 2021, 4:15pm

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 !

anon16419211 · March 10, 2021, 5:03pm

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

Best,

Alix_Bouloc · March 12, 2021, 9:52am

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

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

anon90145840 · March 12, 2021, 10:34am

Hello @Alix_Bouloc,

Thank you for the update

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

Antoine_Jubin · August 31, 2021, 2:25pm

Hi,

I’m in Alix_Bouloc team,

here’s a quick update on this issue, we found the cause. Maybe sharing the solution will help someone in a similar case.

Symptom

Unexpected error: function lower(uuid) does not exist

By looking at the error log, we can see a sql key in the error object.

query was looking like the following snippet (simplified version)

SELECT
  *
FROM
  public.users AS users
  LEFT OUTER JOIN public.accounts AS account ON users.account_id = account.id
WHERE
  lower(users.medok_sub) LIKE lower('antoine')

By running the query on my database, I’ve got the same error.

Indeed, users.medok_sub is a uuid column, so there’s no like functions on uuid type

Solution

On models, we turned this

const User = sequelize.define(
    'users',
   { medokSub: {type: DataTypes.STRING})

into this

const User = sequelize.define(
    'users',
   { medokSub: {type: DataTypes.UUID})

Open points

Why there was a mismatch ? It’s surely on our side
How to debug this faster, ask the complete error log

Anyway guys, thanks for your help and your tool

jeffladiray · August 31, 2021, 2:57pm

Hey @Antoine_Jubin, and welcome to our community.

Good to know you found the culprit!

The mismatch can be caused by a lot of things. We try to constantly improve our project generation process, so maybe you generated the project before we were able to handle UUID as a column datatype.

For the future, we also provide a forest schema:update command (In our forest-cli package), that can re-generate your models from your database definition. Maybe that could have helped here.

Anyway, I’m closing this thread since the issue seems solved on your end. Don’t hesitate to open a new thread if you encounter any issue

Topic		Replies	Views
Search on collection is sending an error - "function lower(uuid) does not exist" Help me!	4	1329	May 27, 2020
Search error on tables with PostGIS fields Help me!	1	340	August 21, 2020
Search on collection is sending an error - sequelize-typescript UUID are interpreted as string Help me! typescript	12	2256	February 3, 2022
Unable to search in a specific table due to field renaming : Unexpected error: function lower(boolean) does not exist Help me!	8	377	October 21, 2022
Woodshop tutorial break sv7 Help me!	2	339	May 25, 2021

Enable to research on a specific table

Expected behavior

Actual behavior

Failure Logs

Context

Symptom

Solution

Open points

Related topics