upsert fails when a spatial field is used on MSSQL

[massivex]

What are you doing?

Calling upsert on a model with a spatial field

class Project extends Sequelize.Model { }
Project.init({
    department: { type: Sequelize.STRING(20), unique: 'ak__project', allowNull: false },
    code: { type: Sequelize.STRING(20), unique: 'ak__project', allowNull: false },
    position: {type: Sequelize.GEOMETRY('POINT') }
}, { sequelize, modelName: 'project' });

// This line throw an error  (Error: Invalid value { type: 'Point', coordinates: [ 0, 0 ] })
Project.upsert({
    department: 'DEV',
    code: 'TEST',
     position: { type: 'Point', coordinates: [ 0, 0 ] }
});

To Reproduce
Steps to reproduce the behavior:

1. Define model with a geometry field.
2. Run an upsert
3. See error

What do you expect to happen?

Object should be translated into a T-SQL geometry function (es. ST_GeomFromText( ..., ... )

What is actually happening?

Raw value it's used instead to escape using model attribute info.
This happens because at

sequelize/lib/dialects/mssql/query-generator.js

Line 420 in f3b5f99

const insertValuesEscaped = insertKeys.map(key => this.escape(insertValues[key])).join(', ');

escape function is called without model info and escape function is unable to stringify correctly field value.

Sequelize returns this error message
Error: Invalid value { type: 'Point', coordinates: [ 0, 0 ] }

Changing above linked code line with

const insertValuesEscaped = insertKeys.map(key => this.escape(insertValues[key], model.rawAttributes[key], { context: 'INSERT'})).join(', ');

all works fine.

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any
  Dialect tedious version: 5.0.3
  Database version: SQL SERVER 2017 - 14.0.3045.24
  Sequelize version: 4.44.2
  Node Version: 8.11.2
  OS: Ubuntu 14

Tested with latest release:

• No
• Yes, specify that version: 5.11.0

[madhums]

This is happening with postgres too. Using "sequelize": "~6.6.2", and postgres 13.2.

To be more specific:

In migration:

await queryInterface.createTable('projects', {
  geo: {
    type: Sequelize.GEOGRAPHY('POINT', 4326),
  },
  ...
}

In seeds:

await queryInterface.bulkInsert('projects', [
  {
    geo: {
      type: 'Point',
      coordinates: [-0.09, 51.505],
    },
  }
  ...
])

when I run sequelize-cli db:migrate all goes well but when I run sequelize-cli db:seed:all, I get

ERROR: Error: Invalid value { type: 'Point', coordinates: [ -0.09, 51.505 ] }
    at Object.escape (/Users/madhu/code/nvc/socialchange/node_modules/sequelize/lib/sql-string.js:65:11)
    at PostgresQueryGenerator.escape (/Users/madhu/code/nvc/socialchange/node_modules/sequelize/lib/dialects/abstract/query-generator.js:994:22)
    at /Users/madhu/code/nvc/socialchange/node_modules/sequelize/lib/dialects/abstract/query-generator.js:282:21
    at Array.map (<anonymous>)
    at PostgresQueryGenerator.bulkInsertQuery (/Users/madhu/code/nvc/socialchange/node_modules/sequelize/lib/dialects/abstract/query-generator.js:274:36)
    at PostgresQueryInterface.bulkInsert (/Users/madhu/code/nvc/socialchange/node_modules/sequelize/lib/dialects/abstract/query-interface.js:834:27)
    at Object.up (/Users/madhu/code/nvc/socialchange/seeders/20210430093821-init-projects.js:5:26)

[madhums]

@papb I think the docs also need improvement, it's not clear how to insert a point if one declares a field as GEOGRAPHY. I followed what's written in tests, unfortunately that fails too.

[madhums]

Ok, so when using with queryInterface.bulkInsert we should use POINT(-0.09 51.505) instead of {type: 'Point', coordinates: [0, 0]}, so changing this from

await queryInterface.bulkInsert('projects', [
  {
    geo: {
      type: 'Point',
      coordinates: [-0.09, 51.505],
    },
  }
  ...
])

to

await queryInterface.bulkInsert('projects', [
  {
    geo: 'POINT(-0.09 51.505)'
  }
  ...
])

seems to work.

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂