CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
David.M
David.M

Reputation: 23

Error when utilizing column aliases in where conditions for Sequelize find methods

For brief context: I am trying to migrate our app to utilize Sequelize, and was hoping to rely on the usage of column aliases given the actual column names we are dealing with are rather long and convoluted. Unfortunately I seem to be unable to make use of the aliased attributes when constructing where conditions in Sequelize findAll(), findOne(), etc. methods. My (simplified) setup is as follows:

// package.json
{
  ...
  "dependencies": {
    "pg": "^7.18.1",
    "pg-hstore": "^2.3.3",
    "sequelize": "^5.21.4"
  }
}

// sequelize.js

const Sequelize = require('sequelize')

const options = {
    ...
    databaseVersion: '8.0.2', // RedShift Postgres version
    dialect: 'postgres',
    dialectOptions: {
        ssl: {
            require: true,
            rejectUnauthorized: true
        }
    }
    define: {
        timestamps: false,
        freezeTableName: true
    }
}

const sequelize = new Sequelize(<database>, <user>, <pass>, options)

module.exports = sequelize


// MyTable.model.js

const { DataTypes } = require('sequelize')
const sequelize = require('./sequelize')

const schema = 'my_schema'
const tableName = 'my_table'
const modelName = 'MyTable'

const attributes = {
    my_table_key: {
        type: DataTypes.CHAR(32)
    },
    key: {
        field: 'my_table_key',
        type: DataTypes.CHAR(32)
    },
    value: {
        field: 'my_table_value',
        type: DataTypes.STRING
    }
}

const options = {
    schema
    tableName
}

const model = sequelize.define(modelName, attributes, options)

module.exports = model

Executing queries such as the following work just fine:

MyTable.findOne({ attributes: ['key'] })
>> Executing (default): SELECT "my_table_key" AS "key" FROM "my_schema"."my_table" AS "my_table" LIMIT 1;

MyTable.findOne({ attributes: ['key'], where: { key_column: '123' } })
>> Executing (default): SELECT "my_table_key" AS "key" FROM "my_schema"."my_table" AS "my_table" WHERE ("my_table"."my_table_key" = '123') LIMIT 1;

However, as soon as I try to make use of the renamed/aliased attributes I encounter an error:

MyTable.findOne({ attributes: ['key'], where: { key: '123' } })

Unhandled rejection SequelizeDatabaseError: column mytable.key does not exist
at Query.formatError (~/node_modules/sequelize/lib/dialects/postgres/query.js:366:16)
    at query.catch.err (~/node_modules/sequelize/lib/dialects/postgres/query.js:72:18)
    at bound (domain.js:301:14)
    at runBound (domain.js:314:12)
    at tryCatcher (~/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (~/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (~/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (~/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (~/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (~/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (~/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (~/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues (~/node_modules/bluebird/js/release/async.js:15:14)
    at runCallback (timers.js:810:20)
    at tryOnImmediate (timers.js:768:5)
    at processImmediate [as _immediateCallback] (timers.js:745:5)

I've experimented with the modelName and tableName variables (e.g. just keeping the original table name and no custom model name) when defining the model, but encounter the same error. Does anyone know the correct approach (if any) to make use of aliased columns for the where argument?

Thanks!

Upvotes: 0

Views: 1466

Answers (1)

Nick Rameau
Nick Rameau

Reputation: 1318

I've never seen aliases done in this way (during model definition) and I don't recommend you do this.

I recommend you keep the same keys/columns and only when you're fetching, should you use the attributes key to change the column name for usage later:

MyTable.findOne({ attributes: [['LONG_COLUMN_NAME1', 'key1'], ['LONG_COLUMN_NAME2', 'key2']] }) // This will select LONG_COLUMN_NAME1 as key1 and LONG_COLUMN_NAME2 as key2.

Upvotes: 1

Related Questions