How do I select a column using an alias

Question

How do I perform an sql query such as this?

SELECT column_name AS alias_name FROM table_name;

Example: I want the column 'first' to be selected as 'firstname'

Table.findAll({
      attributes: [id,"first"]
    })
    .then(function(posts) {
        res.json(posts);
    })

Answer 1

Table.findAll({
  attributes: ['id', ['first', 'firstName']] //id, first AS firstName
})
.then(function(posts) {
  res.json(posts);
});

Answer 2

You need to use row.get('newname') to access columns aliased by attributes

Doing just row.newname, or row.oldname, will not work like it does for non-aliased names for some reason:

• https://github.com/sequelize/sequelize/issues/10592

• https://sequelize.org/v5/manual/models-usage.html documents it:

  Project.findOne({
    where: {title: 'aProject'},
    attributes: ['id', ['name', 'title']]
  }).then(project => {
    // project will be the first entry of the Projects table with the title
    // 'aProject' || null
    // project.get('title') will contain the name of the project
  })
  

  but https://sequelize.org/master/class/lib/model.js~Model.html doesn't mention it, which is confusing:

  instance.field
  // is the same as
  instance.get('field')
  

Related: Sequelize cannot access alias. Alias is undefined

Minimal runnable example:

const assert = require('assert');
const path = require('path');
const { Sequelize, DataTypes, Op } = require('sequelize');
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: path.basename(__filename) + '.sqlite',
});
(async () => {
const Int = sequelize.define('Int', {
  value: {
    type: DataTypes.INTEGER,
  },
  name: {
    type: DataTypes.STRING,
  },
}, {});
await Int.sync({force: true})
await Int.create({value: 2, name: 'two'});
let row;
row = await Int.findOne({
  where: { value: 2 },
  attributes: [ 'id', [ 'value', 'newvalue' ] ],
});
assert.strictEqual(row.id, 1);
assert.strictEqual(row.value, undefined);
assert.strictEqual(row.newvalue, undefined);
assert.strictEqual(row.get('newvalue'), 2);
await sequelize.close();
})();

The generated query does exactly what we wanted then:

SELECT `id`, `value` AS `newvalue` FROM `Ints` AS `Int`
  WHERE `Int`.`value` = 2 LIMIT 1;

tested on sequelize 6.5.1, sqlite3 5.0.2.

Answer 3

Also Sequelize supports defining column names directly on the model definition too.

Sequelize Docs On that they mention about field attribute on column definition.

ex: (Taken from Docs itself)

const { Model, DataTypes, Deferrable } = require("sequelize");

class Foo extends Model { }
Foo.init({
    // You can specify a custom column name via the 'field' attribute:
    fieldWithUnderscores: {
        type: DataTypes.STRING, 
        field: 'field_with_underscores'
    },
}, {
    sequelize,
    modelName: 'foo'
});

thanks to this answer

Answer 4

As Jan Aagaard Meier, this can be done.

Table.findAll({
  attributes: ['id', ['first', 'firstName']] //id, first AS firstName
})
.then(function(posts) {
  res.json(posts);
});

But sometimes, it can be tiring to list all attributes just to rename one or two and everytime a model changes, the code needs to be changed to.

So inorder to rename or alias few columns without defining all attributes, use include

Model.findAll({
  attributes: {
    include: [
      [sequelize.fn('COUNT', sequelize.col('hats')), 'n_hats']
    ]
  }
});

https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#specifying-attributes-for-select-queries