CODEWITHSUNDEEP
node.jssequelize.js
Abhishek Agarwal
Abhishek Agarwal

Reputation: 175

Change column name Sequilize

I want to change default column name in my final output which comes from Mysql database when i am accessing database using NodeJS and Sequelize connection. I am connecting using following code:-

import Sequelize from "sequelize";

    let dbConnection = new Sequelize('DB', 'abc', 'password', {
        host: '127.0.0.1',
        port: 4242,
        dialect: 'mysql',
        dialectOptions: {
            requestTimeout: 0,
        },
    });

const Listing = dbConnection.define('TableName', {
    tour_title: {
        type: Sequelize.STRING,
    }
}, { freezeTableName: true, timestamps: false });

For example, I want to change tour_title by tour_name in the above column only in the final sequelize output. I do not want any change in database column names.

Upvotes: 4

Views: 15484

Answers (4)

user14900872
user14900872

Reputation:

I'd add something to @Deepak's answer. When I tried it the Step 4: (I am a newbie), it prompted that

"Cannot find config.json Have you run sequelize init? "

Then I executed that init command then it made a config.json file inside the config folder. So the file contained below objects.

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Then I executed the sequelize db:migrate. It prompted another error as

Sequelize CLI [Node: 18.7.0, CLI: 6.4.1, ORM: 6.21.4]
Loaded configuration file "config\config.json".
Using environment "development".

ERROR: Access denied for user 'root'@'localhost' (using password: NO)

Then what I did was I changed the config.json file to something like this -->

{
  "development": {
    "username": "root",
    "password": "<this should include your root password>",
    "database": "<this should include your db>",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

And then migration was done successfully!

Upvotes: 0

Ellebkey
Ellebkey

Reputation: 2301

The above answers only works for the query results. If you want to keep your underscore name convention on the database and use for example camelCase for your javascript environment you need to do this:

const ChildTable = dbConnection.define('ChildTable', { //this table name is how you call it on sequelize
  tourTitle: { //this is the name that you will use on javascript
      field: 'tour_title', // this will be the name that you fetch on your db
      type: Sequelize.STRING,
  },
  parentTableId: { // You need to define the field on the model, otherwise on sequelize you'll have to use the db name of the field
      field: 'parent_table_id', //this is defined above on the association part
      type: Sequelize.INTEGER,
  }
}, { freezeTableName: true, timestamps: false, tableName: 'TableName', //this is the real name of the table on the db
underscored: true, });

ChildTable.associate = (models) => {
  ChildTable.belongsTo(models.ParentTable, {   as: 'ParentTable',   foreignKey: 'parent_table_id', onDelete: 'cascade' });
};

This way you have an "alias" for your fields on all the Sequelize / javascript environment and you keep your names on the db. See also that you have to do the same with the foreign keys on associations, otherwise you'll have to use the original name.

Upvotes: 1

Deepak
Deepak

Reputation: 1433

To change column name only in final output

TableName.findAll({
  attributes: ['id', ['tour_title', 'tour_name']] /
})
.then((data) => {
 console.log(data);
});

To change column name in database

This may help you, follow this steps

Step 1: npm install -g sequelize-cli To install sequelize command line tool

Step 2: sequelize migration:create --name rename-column-tour_title-to-tour_name-in-tabelname

Step 3: Open newly created migration file and add below code

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.renameColumn('tableName', 'tour_title', 'tour_name');
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.renameColumn('tableName', 'tour_name', 'tour_title');
  }
};

Step 4: in command line run this below command

sequelize db:migrate

Now your column name changed successfully.

Upvotes: 19

Rajan Sharma
Rajan Sharma

Reputation: 2273

If you want an alias for a column name in the ouput , you can do something like this:

TableName.findAll({
  attributes: ['id', ['tour_title', 'tour_name']] //id, tour_title AS tour_name
})
.then(function(data) {
  res.json(data);
});

Whenever you fetch any records you can write the column name in a cascaded list and make an alias. Check for the documentation here : http://docs.sequelizejs.com/manual/tutorial/querying.html

Upvotes: 1

Related Questions