CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Buddhi
Buddhi

Reputation: 2324

single quote escape - sequelize migration

I'm using NodeJS with Sequelize ORM and Postgress database I'm creating ENUM column named education_level in table using migration file as below and enum value (Bachelor's Degree) has single quote and it gives below error when i try to run migrations, Thank you for helping!

migration file

'use strict';
module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('Education', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      school: {
        type: Sequelize.STRING
      },
      degree: {
        type: Sequelize.STRING
      },
      field_of_study: {
        type: Sequelize.STRING
      },
      year_started: {
        type: Sequelize.STRING(4)
      },
      year_graduated: {
        type: Sequelize.STRING(4)
      },
      education_level: {
        type: Sequelize.ENUM,
        values: ['Diploma','Bachelor\'s Degree','Master\'s Degree','Doctorate Degree'],
      },
      profile_id: {
        type: Sequelize.INTEGER,
        allowNull: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('Education');
  }
};

error

{ SequelizeBaseError: syntax error at or near "s"
migrate_1        |     at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
migrate_1        |     at Query.<anonymous> (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:88:19)
migrate_1        |     at emitOne (events.js:96:13)
migrate_1        |     at Query.emit (events.js:191:7)
migrate_1        |     at Query.handleError (/usr/src/app/node_modules/pg/lib/query.js:108:8)
migrate_1        |     at Connection.<anonymous> (/usr/src/app/node_modules/pg/lib/client.js:171:26)
migrate_1        |     at emitOne (events.js:96:13)
migrate_1        |     at Connection.emit (events.js:191:7)
migrate_1        |     at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:109:12)
migrate_1        |     at emitOne (events.js:96:13)
migrate_1        |     at Socket.emit (events.js:191:7)
migrate_1        |     at readableAddChunk (_stream_readable.js:176:18)
migrate_1        |     at Socket.Readable.push (_stream_readable.js:134:10)
migrate_1        |     at TCP.onread (net.js:563:20)
migrate_1        |   name: 'SequelizeDatabaseError',
migrate_1        |   message: 'syntax error at or near "s"',
migrate_1        |   parent:
migrate_1        |    { error: syntax error at or near "s"
migrate_1        |        at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:539:11)
migrate_1        |        at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:366:17)
migrate_1        |        at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:105:22)
migrate_1        |        at emitOne (events.js:96:13)
migrate_1        |        at Socket.emit (events.js:191:7)
migrate_1        |        at readableAddChunk (_stream_readable.js:176:18)
migrate_1        |        at Socket.Readable.push (_stream_readable.js:134:10)
migrate_1        |        at TCP.onread (net.js:563:20)
migrate_1        |      name: 'error',
migrate_1        |      length: 90,
migrate_1        |      severity: 'ERROR',
migrate_1        |      code: '42601',
migrate_1        |      detail: undefined,
migrate_1        |      hint: undefined,
migrate_1        |      position: '84',
migrate_1        |      internalPosition: undefined,
migrate_1        |      internalQuery: undefined,
migrate_1        |      where: undefined,
migrate_1        |      schema: undefined,
migrate_1        |      table: undefined,
migrate_1        |      column: undefined,
migrate_1        |      dataType: undefined,
migrate_1        |      constraint: undefined,
migrate_1        |      file: 'scan.l',
migrate_1        |      line: '1086',
migrate_1        |      routine: 'scanner_yyerror',
migrate_1        |      sql: 'CREATE TYPE "public"."enum_Education_education_level" AS ENUM(\'Diploma\', \'Bachelor\'s Degree\', \'Master\'s Degree\', \'Doctorate Degree\');' },
migrate_1        |   original:
migrate_1        |    { error: syntax error at or near "s"
migrate_1        |        at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:539:11)
migrate_1        |        at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:366:17)
migrate_1        |        at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:105:22)
migrate_1        |        at emitOne (events.js:96:13)
migrate_1        |        at Socket.emit (events.js:191:7)
migrate_1        |        at readableAddChunk (_stream_readable.js:176:18)
migrate_1        |        at Socket.Readable.push (_stream_readable.js:134:10)
migrate_1        |        at TCP.onread (net.js:563:20)
migrate_1        |      name: 'error',
migrate_1        |      length: 90,
migrate_1        |      severity: 'ERROR',
migrate_1        |      code: '42601',
migrate_1        |      detail: undefined,
migrate_1        |      hint: undefined,
migrate_1        |      position: '84',
migrate_1        |      internalPosition: undefined,
migrate_1        |      internalQuery: undefined,
migrate_1        |      where: undefined,
migrate_1        |      schema: undefined,
migrate_1        |      table: undefined,
migrate_1        |      column: undefined,
migrate_1        |      dataType: undefined,
migrate_1        |      constraint: undefined,
migrate_1        |      file: 'scan.l',
migrate_1        |      line: '1086',
migrate_1        |      routine: 'scanner_yyerror',
migrate_1        |      sql: 'CREATE TYPE "public"."enum_Education_education_level" AS ENUM(\'Diploma\', \'Bachelor\'s Degree\', \'Master\'s Degree\', \'Doctorate Degree\');' },
migrate_1        |   sql: 'CREATE TYPE "public"."enum_Education_education_level" AS ENUM(\'Diploma\', \'Bachelor\'s Degree\', \'Master\'s Degree\', \'Doctorate Degree\');' }
api_migrate_1 exited with code 1

Upvotes: 1

Views: 4334

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You need to escape that single quote with another single quote. So maybe this will work:

education_level: {
    type: Sequelize.ENUM,
    values: ['Diploma','Bachelor\'\'s Degree','Master\'\'s Degree','Doctorate Degree'],
  },

Explanation

Initial query sent to server:

CREATE TYPE "public"."enum_Education_education_level" AS ENUM(
  'Diploma', 'Bachelor's Degree', 'Master's Degree', 'Doctorate Degree'
);

As you can see by syntax coloring, it does not see 'Bachelor's Degree' as a single string, but rather 'Bachelor' and then s Degree'. This happens because that escaped apostrophe was used to build string for JavaScript (apostropes escaped with backslash). It does not escape string for PostgreSQL.

In order to fix this, we need to escape that apostrophe on PostgreSQL side and since PostgreSQL uses apostrophe to escape apostrophe, we aim to get this:

CREATE TYPE "public"."enum_Education_education_level" AS ENUM(
  'Diploma', 'Bachelor''s Degree', 'Master''s Degree', 'Doctorate Degree'
);

So on JavaScript side we construct string with two escaped apostrophes 'Bachelor\'\'s Degree'. JavaScript changes \'\' to '' and PostgreSQL '' to '.

Upvotes: 4

Related Questions