Sequelize findOne/findAll query not returning association attributes

Question

I am trying to get a basic association / join query working with Sequelize but can't seem to get data returned from the include association. I have tried multiple approaches so far to no avail, but currently am working with the following (simplified for demonstration):

// 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(, , , options)

module.exports = sequelize

// modelA.model.js

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

const schema = 'my_schema'
const tableName = 'my_table_a'
const modelName = 'ModelA'

const attributes = {
    model_a_key: {
        type: DataTypes.CHAR(32)
    },
    model_a_value: {
        type: DataTypes.STRING
    }
    model_b_key: {
        type: DataTypes.CHAR(32)
    },
}

const options = {
    schema
    tableName
}

sequelize.define(modelName, attributes, options)

// modelB.model.js

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

const schema = 'my_schema'
const tableName = 'my_table_b'
const modelName = 'ModelB'

const attributes = {
    model_b_key: {
        type: DataTypes.CHAR(32)
    },
    model_b_value: {
        type: DataTypes.STRING
    }
}

const options = {
    schema
    tableName
}

sequelize.define(modelName, attributes, options)

// associations.js

const { models } = require('./sequelize.js')

ModelA.belongsTo(models.ModelB, { foreignKey: 'model_b_key' })

// Have left this out for now since I can't seem to even leverage the above association properly...
// ModelB.belongsTo(models.ModelA, { foreignKey: 'model_b_key' })

The above files are loaded in sequence of sequelize, models, then associations. Following the Sequelize examples I've seen, I have attempted the following queries to have access to data for both ModelA and it's associated ModelB:

// Attempt #1
await sequelize.models.ModelA.findOne({ include: ['ModelB'] })
// returned data = { model_a_key, model_a_value, model_b_key }; missing all ModelB columns

// Attempt #2
await sequelize.models.ModelA.findOne({ attributes: ['ModelB.model_b_value'], include: ['ModelB'] })
// returned data = {}

// Attempt #3
await sequelize.models.ModelA.findOne({ include: [{ association: 'ModelB', required: true }] })
// returned data = { model_a_key, model_a_value, model_b_key }; missing all ModelB columns

// Attempt #4
await sequelize.models.ModelA.findOne({ include: [{ association: 'ModelB', attributes: ['model_b_value'] }] })
// returned data = { model_a_key, model_a_value, model_b_key }; missing model_b_value

The SQL generated seems to check out in terms of actually fetching the columns of interest, e.g. attempt #1 yields:

SELECT "ModelA"."model_a_key", "ModelA"."model_a_value", "ModelA"."model_b_key", "ModelB"."model_b_key" AS "ModelB.model_b_key", "ModelB"."model_b_value" AS "ModelB.model_b_value"
FROM "my_schema"."my_table_a" AS "ModelA"
LEFT OUTER JOIN "my_schema"."my_table_b" AS "ModelB" ON "ModelA"."model_b_key" = "ModelB"."model_b_key"
LIMIT 1;

These values are also available if I set the option raw: true. But if I attempt to work with the model object, all information about the associated ModelB seems to be lost/discarded - neither a nested object for ModelB nor properties for ModelB.model_b_key, ModelB.model_b_value exist on the returned object.

Am I missing something super simple here? Been desperately fiddling with the association targetKey, sourceKey, foreignKey values, along with many more findOne/findAll query permutations like those listed above, but have had zero luck.

Any advice would be greatly appreciated!

Update I

After some more fiddling, it seems there is some odd behavior with case sensitivity when setting up the associations. If I define the association with an alias as: 'modelb' and query via association: 'modelb', the above fetch statements work as expected and maps the desired ModelB properties on the returned object. The introduction of any uppercase characters to the alias seems to break this functionality - e.g. aliasing as xxxxxx works fine, xxxxXx results in the original problem described above.

The immediate fix seems to be to alias everything with lowercase only, but I would still like to pursue getting this working as intended. I am hoping this is just something to do with Postgres and my Sequelize config versus an actual issue.

Update II

So it seems all uppercase column references/aliases, although represented as defined (e.g. SELECT "ModelB"."model_b_key" AS "ModelB.model_b_key") in the constructed SQL query, the returned row object properties from the pg Client are coming back lowercase (e.g. row['modelb.model_b_key']). As a result, the field labels no longer align with their original association label, and none of the data is mapped on to the model instance.

Setting the Sequelize option minifyAliases: true seems to do the trick. With this option enabled, column aliases are represented as _01, _02, etc. in the query and then mapped back to their respective string labels, circumventing the possibility of alias names inadvertently being converted to lowercase.

Update III

In case anyone is interested, this actually seems to be an AWS Redshift specific behavior. I was not aware that Redshift converts all column names (and aliases) to lower case by default (controlled via describe_field_name_in_uppercase), which explains why all returned row objects had their aliases converted to lowercase. I imagine this would not be a problem for a traditional Postgres implementation which would respect upper/lower case using double quotes "" column name/alias references.

Sequelize findOne/findAll query not returning association attributes

Answers (1)

Related Questions