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

anon34731316 · June 23, 2020, 10:04am

@rvastag try to remove the tableName line, so that the table name should default to “departmentTeams”.
Then if it’s still not working, you can try to remove the underscored line too, this way camelCase will be the default syntax.
Don’t hesitate to refer to the sequelize documentation to understand what you are manipulating
Underscore option
Table name

rvastag · June 23, 2020, 11:42am

Hello … @Steve_Bunlon @anon34731316
Ok … all will try to summarize the situation.

My backend team changed the handling of table and field names to user snake_case
now all the tables/models in Forest that are more than one word and include an underscore give me a " Your server encountered an error"
An example of this is my department_teams table/model
there is a file in the forest backend … located at, and called: UCM/forest/department-teams.js
this file contains the following:

const { collection } = require(‘forest-express-sequelize’);
collection(‘departmentTeams’, {
actions: ,
fields: ,
segments: ,
});

there is a file located in the root back end directory, called: UCM/.forestadmin-schema.json
this file contains the following:

{

"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": []

}

and then in the models folder is this file: UCM/models/department-teams.js
which contains this:
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: ‘projects’,
});
};

return DepartmentTeams;
};

anon34731316 · June 23, 2020, 12:42pm

Alright @rvastag
Then I guess you need to update all your models accordingly (snake_case) and then restart your admin backend (then the forestadmin-schema.json file will be automatically updated).

rvastag · June 23, 2020, 3:24pm

all my Forest model/json files need to use snake_case, not camelCase. Right?

that is, all references to the Department Teams table should use snake_case (department_teams).

rvastag · June 23, 2020, 3:30pm

@anon34731316

seems that field names (attributes) can be in camelCase, even though the field is snake_case in the database.

So … i really don’t know what to change to snake_case, and what can be camelCase.

There are over a hundred tables (and associated model files). Wouldn’t it be easier to simply rebuild the models from scratch? But it would be nice if I would not lose the segments I have already created/defined.

jeffladiray · June 24, 2020, 9:13am

Hi @rvastag,

One way that could potentially fix the issue is, you’re right, regenerating models. If most of the fields/tablenames were switched and you have a lot of tables, I might suggest to:

Create a temporary lumber project based on the new format of table definitions in your db
Copy one by one the generated models to your current project, while fixing issues that might appear

This method is not 100% guaranty to work, but would at least generate models based on your current database definition, showing you things that needs to be fixed.

Another solution would be to go on each model manually and fix them.

Sequelize handle a lot of things to match models definitions, as stated by @anon34731316 on a previous message. With the sequelize options, you should be able to match you database with your model definition.

Hope that helps

fabmarc · September 29, 2020, 7:27pm

@rvastag
your “your server encountered an error” problem was because some tables/columns had their names changed and others were deleted.

fabmarc · September 29, 2020, 8:11pm

@rvastag
Getting back to your original issue (Unexpected error: syntax error at or near “WHERE”)…

I was facing the same issue and that was the log on the console, here you are:

Executing (default): UPDATE "public"."user_contracts" SET "archive_id"=$1,"updated_at"=timezone(utc WHERE "id" = $2
[forest] 🌳🌳🌳  Unexpected error: syntax error at or near "WHERE"
SequelizeDatabaseError: syntax error at or near "WHERE"
    at Query.formatError (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)
    at query.catch.err (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)
From previous event:
    at Query.run (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/dialects/postgres/query.js:64:23)
    at runHooks.then.then (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/sequelize.js:641:29)
From previous event:
    at Promise.try.then.connection (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/sequelize.js:641:12)
From previous event:
    at Promise.resolve.retry (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/sequelize.js:637:10)
    at /Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/retry-as-promised/index.js:70:21
    at new Promise (<anonymous>)
    at retryAsPromised (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/retry-as-promised/index.js:60:10)
    at Promise.try (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/sequelize.js:627:30)
From previous event:
    at Sequelize.query (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/sequelize.js:576:23)
    at QueryInterface.update (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/query-interface.js:1012:27)
    at Promise.try.then.then.then (/Users/fabmarc/projects/forest-backend/UCM-3.0/node_modules/sequelize/lib/model.js:3985:52)

My user-contracts.js file:

module.exports = (sequelize, DataTypes) => {
  const { Sequelize } = sequelize;

  const UserContracts = sequelize.define('user_contracts', {
    id: {
      type: DataTypes.BIGINT,
      primaryKey: true,
      defaultValue: Sequelize.literal('nextval(user_contracts_id_seq::regclass)'),
    },
    startDate: {
      type: DataTypes.DATE,
    },
    endDate: {
      type: DataTypes.DATE,
    },
    ...
    createdBy: {
      type: DataTypes.STRING,
      defaultValue: Sequelize.literal('CURRENT_USER'),
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: Sequelize.literal('timezone(utc'),
    },
    updatedBy: {
      type: DataTypes.STRING,
      defaultValue: Sequelize.literal('CURRENT_USER'),
    },
    ...
    archiveId: {
      type: DataTypes.INTEGER,
    },
  }, {
    tableName: 'user_contracts',
    underscored: true,
    schema: process.env.DATABASE_SCHEMA,
  });

  return UserContracts;
};

I noticed the literal timezone(utc assigned to updated_at at the UPDATE statement doesn’t work on Postgres, so I changed it to timezone('utc', now()) and then it started working nicely, like that:

updatedAt: {
  type: DataTypes.DATE,
  defaultValue: Sequelize.literal("timezone('utc', now())"),
},

or like that:

updatedAt: {
  type: DataTypes.DATE,
  defaultValue: Sequelize.literal('now()'),
},

I don’t know why Forest generated all defaultValue for date fields equal to timezone(utc if it doesn’t work on Postgres.

Steve_Bunlon · September 30, 2020, 1:08pm

Hello @fabmarc, @rvastag

I’ve looked into this thread, and indeed, it looks like the default values of date field are not generated correctly. As @fabmarc pointed out, simply replacing the incorrect value Sequelize.literal('timezone(utc') by Sequelize.literal("timezone('utc', now())") fixes the issue.

I have created a ticket for this, thanks for the thread and your feedbacks, we will work on a fix.

Steve.

GuillaumeGautreau · October 9, 2020, 12:06pm

Just a quick update on this issue. I checked the code, and it appears that this bug is originally caused by a bug on sequelize, which we are using to retrieve default values on fields.

I created an issue on Github for this problem: https://github.com/sequelize/sequelize/issues/12749, I’ll work on a solution, but I’ll need some help from the sequelize team.

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

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

Related topics