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

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
1 Like

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 ?

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.

Ok thanks :+1: , can you share your browser logs ?

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.

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

{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

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

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.

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 :pray:

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);

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?

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 ?

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: [],
});

Hello @rvastag :wave:

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

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 :raised_hands:

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

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

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

:frowning:

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.

@adriguy
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.