title: Sequelize findOne/findAll query not returning association attributes
body: <p>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):</p>
  
  <pre class="lang-js prettyprint-override"><code>// package.json
  {
    ...
    "dependencies": {
      "pg": "^7.18.1",
      "pg-hstore": "^2.3.3",
      "sequelize": "^5.21.4"
    }
  }
  </code></pre>
  
  <pre class="lang-js prettyprint-override"><code>// 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(&lt;database&gt;, &lt;user&gt;, &lt;pass&gt;, options)
  
  module.exports = sequelize
  
  </code></pre>
  
  <pre class="lang-js prettyprint-override"><code>// 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)
  
  </code></pre>
  
  <pre class="lang-js prettyprint-override"><code>// 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)
  
  </code></pre>
  
  <pre class="lang-js prettyprint-override"><code>// 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' })
  
  </code></pre>
  
  <p>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:</p>
  
  <pre class="lang-js prettyprint-override"><code>// 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
  
  </code></pre>
  
  <p>The SQL generated seems to check out in terms of actually fetching the columns of interest, e.g. attempt #1 yields:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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;
  
  </code></pre>
  
  <p>These values are also available if I set the option <code>raw: true</code>. 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 <code>ModelB</code> nor properties for <code>ModelB.model_b_key</code>, <code>ModelB.model_b_value</code> exist on the returned object.</p>
  
  <p>Am I missing something super simple here? Been desperately fiddling with the association <code>targetKey</code>, <code>sourceKey</code>, <code>foreignKey</code> values, along with many more findOne/findAll query permutations like those listed above, but have had zero luck.</p>
  
  <p>Any advice would be greatly appreciated!</p>
  
  <p><strong>Update I</strong></p>
  
  <p>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 <code>as: 'modelb'</code> and query via <code>association: 'modelb'</code>, 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 <code>xxxxxx</code> works fine, <code>xxxxXx</code> results in the original problem described above.</p>
  
  <p>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.</p>
  
  <p><strong>Update II</strong></p>
  
  <p>So it seems all uppercase column references/aliases, although represented as defined (e.g. <code>SELECT "ModelB"."model_b_key" AS "ModelB.model_b_key"</code>) in the constructed SQL query, the returned row object properties from the <code>pg</code> Client are coming back lowercase (e.g. <code>row['modelb.model_b_key']</code>). 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.</p>
  
  <p>Setting the Sequelize option <code>minifyAliases: true</code> seems to do the trick. With this option enabled, column aliases are represented as <code>_01</code>, <code>_02</code>, etc. in the query and then mapped back to their respective string labels, circumventing the possibility of alias names inadvertently being converted to lowercase.</p>
  
  <p><strong>Update III</strong></p>
  
  <p>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 <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_describe_field_name_in_uppercase.html" rel="nofollow noreferrer">describe_field_name_in_uppercase</a>), 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 <code>""</code> column name/alias references.</p>