Forest Backend Error: syntax error at or near "WHERE"

rvastag · May 20, 2020, 8:28am

Expected behavior

When I use the Edit function on a row of data, I should be able to change a value in the record and save it.

Actual behavior

When I click the SAVE button, I get an error

Failure Logs

[2020-05-19T15:23:41+02:00] (forest-backend/forest-backend/027a16f0-8b46-4247-84fc-7e3f98d68c9e) SequelizeDatabaseError: syntax error at or near “WHERE”

Context

Please provide any relevant information about your setup.

Package Version:
Express Version:
Sequelize Version:
Database Dialect: Postgres
Database Version:
Project Name: UCM

anon94532230 · May 20, 2020, 9:26am

When do you get this error ? When editing in the detailed view or the edit inline feature ?
What are your browser logs ? Is it a smart field or a smart collection ?

rvastag · May 20, 2020, 11:52am

When I click on a record (from the basic table view in a segment), I can see that record either in Summary view/tab or Details view/tab. I click the edit button, make a small change to a field, click the save button, and I get the error.

It is not a smart collection or smart field.

anon94532230 · May 20, 2020, 12:10pm

Ok thanks , can you share your browser logs ?

rvastag · May 20, 2020, 2:14pm

I am not a developer, so … not sure what you need to see?

If i cut and past everything from the Console in browser’s Developer mode, then there are too many links and it is not allowed to be posted here.

anon94532230 · May 20, 2020, 2:45pm

Yes, that’s what I mean by browser logs, sorry!
You can anonymise the output of the console, I’m mainly looking for errors.

rvastag · May 20, 2020, 3:26pm

{isAdapterError: true, stack: “Error: Ember Data Request PUT https://forest-backe…dor-de04b338627c392bb920b14b8c4c0763.js:1297:694)”, description: undefined, fileName: undefined, lineNumber: undefined, …}
code: undefined
description: undefined
errors: Array(1)
0:
detail: “syntax error at or near “WHERE””
status: 500
proto: Object
: (…)
firstObject: (…)
hasArrayObservers: (…)
lastObject: (…)
length: 1
proto: Array(0)
fileName: undefined
isAdapterError: true
lineNumber: undefined
message: “Ember Data Request PUT https://forest-backend.staging.aws.ucastme.de/forest/users/15 returned a 500↵Payload (application/json; charset=utf-8)↵[object Object]”
name: “Error”
number: undefined
stack: "Error: Ember Data Request PUT https://forest-backend.staging.aws.ucastme.de/forest/users/15 returned a 500↵Payload (application/json; charset=utf-8)↵[object Object]↵ at r (https://app.forestadmin.com/assets/vend

anon94532230 · May 20, 2020, 4:11pm

It looks like you are using forest in development on a remote environment.
Did you follow those instructions ?

vince · May 22, 2020, 11:28am

Hey @rvastag,

Could you ask your developer to give the full SQL query. He can normally enable the logs by setting the environment variable NODE_ENV=development.

arnaud · May 25, 2020, 1:18pm

Hi @rvastag,

Do you still observe this issue?

As @vince mentioned, you’ll need to have a developer with you to dig into this issue.
The full error trace should be accessible in your server logs.
Without those logs, it will be hard for us to help you.

Please let us know

rvastag · June 2, 2020, 12:36pm

The Network browser log shows the error message “Cannot GET /forest/department_teams”.

department_teams is a table in my database. I can access it via the Postgres database interface. Also, my application can access and use that table.

This is the .js model definition file:

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 DepartmentTeams = sequelize.define('departmentTeams', {
    code: {
      type: DataTypes.STRING,
    },
    name: {
      type: DataTypes.STRING,
    },
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: "timezone(utc",
    },
    createdBy: {
      type: DataTypes.STRING,
      defaultValue: "CURRENT_USER",
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: "timezone(utc",
    },
    updatedBy: {
      type: DataTypes.STRING,
      defaultValue: "CURRENT_USER",
    },
    uuid: {
      type: DataTypes.STRING,
    },
  }, {
    tableName: 'department_teams',
    underscored: true,
  });

  // This section contains the relationships for this model. See: https://docs.forestadmin.com/documentation/v/v6/reference-guide/relationships#adding-relationships.
  DepartmentTeams.associate = (models) => {
    DepartmentTeams.hasMany(models.projects, {
      foreignKey: {
        name: 'teamIdKey',
        field: 'team_id',
      },
      as: 'teamProjects',
    });
  };

  return DepartmentTeams;
};

These are the table and index definitions from Postgres:

– Table Definition ----------------------------------------------

CREATE TABLE department_teams (
    id BIGSERIAL PRIMARY KEY,
    code character varying(50) NOT NULL,
    name character varying(50) NOT NULL,
    created_at timestamp without time zone DEFAULT timezone('utc'::text, now()),
    created_by character varying(50) DEFAULT CURRENT_USER,
    updated_at timestamp without time zone DEFAULT timezone('utc'::text, now()),
    updated_by character varying(50) DEFAULT CURRENT_USER,
    uuid character varying(50) NOT NULL
);

– Indices -------------------------------------------------------

CREATE UNIQUE INDEX department_teams_pkey ON department_teams(id int8_ops);
CREATE UNIQUE INDEX index_department_teams_on_name ON department_teams(name text_ops);

rvastag · June 2, 2020, 1:53pm

I have a guess.

All the tables I receive the “your server encountered an error” problem with are tables/models with multi-word names (e.g. department_teams, contact_people, job_types). All tables with single word names (e.g. projects, companies) do not encounter this error.

This seems like too much of a coincidence.

Has anyone encountered this kind of situation? Is this some kind of naming conventions/standards problem?

vince · June 3, 2020, 8:29am

Okey this is strange because it should send a request on GET /forest/departmentTeams and not GET /forest/department_teams.

Could you share your .forestadmin-schema.json ?
Do you have a forest/department-teams.js file ? If yes can you share it too please ?

rvastag · June 5, 2020, 7:08am

Here is the part of the .forestadmin-schema.json file related to “department teams”

{
    "name": "department_teams",
    "nameOld": "department_teams",
    "icon": null,
    "integration": null,
    "isReadOnly": false,
    "isSearchable": true,
    "isVirtual": false,
    "onlyForRelationships": false,
    "paginationType": "page",
    "fields": [{
      "field": "code",
      "type": "String",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "createdAt",
      "type": "Date",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "createdBy",
      "type": "String",
      "defaultValue": {
        "val": "CURRENT_USER"
      },
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "id",
      "type": "Number",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "name",
      "type": "String",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "updatedAt",
      "type": "Date",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "updatedBy",
      "type": "String",
      "defaultValue": {
        "val": "CURRENT_USER"
      },
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }, {
      "field": "uuid",
      "type": "String",
      "defaultValue": null,
      "enums": null,
      "integration": null,
      "isFilterable": true,
      "isReadOnly": false,
      "isRequired": false,
      "isSortable": true,
      "isVirtual": false,
      "reference": null,
      "inverseOf": null,
      "validations": []
    }],
    "segments": [],
    "actions": []
  }

The forest/department-teams.js is:

const { collection } = require('forest-express-sequelize');

// This file allows you to add to your Forest UI:
// - Smart actions: https://docs.forestadmin.com/documentation/reference-guide/actions/create-and-manage-smart-actions
// - Smart fields: https://docs.forestadmin.com/documentation/reference-guide/fields/create-and-manage-smart-fields
// - Smart relationships: https://docs.forestadmin.com/documentation/reference-guide/relationships/create-a-smart-relationship
// - Smart segments: https://docs.forestadmin.com/documentation/reference-guide/segments/smart-segments
collection('department_teams', {
    actions: [],
    fields: [],
    segments: [],
});

Steve_Bunlon · June 5, 2020, 7:20am

Hello @rvastag

Thanks for all the info you provided, it helps a lot

I can see that in the forest/department-teams.js the collection name is department_teams, but the model is named departmentTeams instead.

Can you change the name of the collection in the forest/department-teams.js from departement_teams to departmentTeams and check if it works please ?

Keep me in touch

rvastag · June 5, 2020, 7:44am

i will try that. If this works for that collection, probably will work for all the other ‘multi-word’ collection names.

Steve_Bunlon · June 5, 2020, 7:47am

Sure! Little precision, you will need to restart your server to apply the changes

rvastag · June 19, 2020, 2:25pm

ok … inside the forest/department-teams.js file, I changed departement_teams to departmentTeams. This did not solve my problem.

anon34731316 · June 23, 2020, 9:17am

Hi @rvastag,

You are probably not far from resolving your issue!

If you want to use camelCase, you have to do it everywhere.
Looking at what you shared, I think you should try now in your model definition to remove this:

    tableName: 'department_teams',
    underscored: true,

Let me know if playing with those parameters helps!
Thanks.

rvastag · June 23, 2020, 9:58am

@anon34731316
Hi … thanks for getting back to me.

I need to REMOVE those two lines or ADD those two lines?

Yeah. I think this problem totally has to do with our database team changing some syntaxes (to/from camelCase). If I can resolve this one table/model problem, then the same fix can be applied to all the models that are not loading.

Topic		Replies	Views
ForestAdmin panel shows error when I want to change something in the database Help me!	11	593	February 5, 2021
The backend responded with an error Help me!	1	454	February 15, 2022
Cannot edit created_at Help me!	8	632	January 13, 2021
ErrorHTTP422 but no details given from forest-express-sequelize Help me!	7	219	July 12, 2023
Urgent: "Cannot reach your data" Console error - Unexpected error in details view: TypeError: e.getRecord is not a function Help me! setup	5	589	July 30, 2020