Sequelize: underscore / camelcase option should control all fields and table name generation

[alexey2baranov]

It would be great if sequelize will generate PostgreSQL DDL with lower_case_underscored table name and column names. By now _underscore_ option manage foreign keys and create_at, modifed_at fields only.

In short

CREATE TABLE IF NOT EXISTS "Male" ("CamelCase" VARCHAR(255), "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL);

should become

CREATE TABLE IF NOT EXISTS male (camel_case VARCHAR(255), "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL);

my global config is

{
    "username": "",
    "password": "",
    "database": "",
    "host": "127.0.0.1",
    "dialect": "postgres",
    "define":{
      "paranoid":false,
      "timestamps":true,
      "freezeTableName": true,
      "underscored": true
    }
}

[sushantdhiman]

Sounds like a reasonable feature to me, @janmeier ?

[janmeier]

I don't think this is needed - If users want underscored names, they can simply define them as such, or use the field option.

The underscored option is for controlling auto-generated column names, but when a user tells us about the column fooBar, we call it fooBar ;)

This can easily be solved with a beforeDefine hook

[sushantdhiman]

Correct, but there is lots of work involved with that :laughing: , I would prefer one single option based control.

If anyone can share their experiences from other ORMs like ActiveRecord (Rails) or Eloquent (Laravel / PHP), that will be really helpful to make this decision.

[felixfbecker]

I also prefer underscored columns and tables and camelCase attributes and PascalCase model names. I think the ORM should allow to map this easily.

[maxsummers]

It took me a long time to figure out why underscored option didn't convert my camelCase column names to under_scored ones.

I would also prefer to declare my columns as camelCased ones to be automatically converted later to underscored ones in SQL.

[elvisgs]

+1

[quocvu]

I also agree w/ @alexey2baranov. I know we use of the field option, but that makes the model more verbose. Typically, when we name a field fooBar, it is to keep it consistent w/ the rest of the javascript code and not that we really want fooBar as a column name.

The current behavior is not desirable. Who would want mixed column naming scheme in their tables?

At the minimum, the documentation should be fixed. It doesn't say underscored only applies to auto-generated fields.

Converts all camelCased columns to underscored if true. Will not affect timestamp fields named explicitly by model options and will not affect fields with explicitly set field option

[pgollangi]

+1

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[alexey2baranov]

Up

[elvisgs]

+1

[maxnachlinger]

I took @janmeier 's sugggestion and accomplished this via the following beforeDefine hook:

const decamelize = require('decamelize');

// convert camelCase fields to underscored
sequelize.addHook('beforeDefine', (attributes) => {
  Object.keys(attributes).forEach((key) => {
    // typeof check provided by @devalnor 
    if (typeof attributes[key] !== "function" ) {
       attributes[key].field = decamelize(key);
    }
  });
});

[dagumak]

The ORM should be in camel case because we are working with JavaScript here so it's more convenient to write everything using camel case. However, it makes sense to store the database names, table names, and column names as underscore.

[dagumak]

@felixfbecker Is this something in the roadmap?

[felixfbecker]

I stepped down as a maintainer don't know

[tomgao365]

+1

[devalnor]

+1

[devalnor]

@maxnachlinger your hook will not work with data-types functions.

This will prevent the bug, but it's not a solution.

// Convert camelCase fields to underscored
sequelize.addHook('beforeDefine', (attributes, options) => {
    Object.keys(attributes).forEach((key) => {
        if (typeof attributes[key] !== "function" ) {
            attributes[key].field = decamelize(key);
        }
    });
});

[netsgnut]

Would really like an option to convert column names to snake cases, as mentioned by @sushantdhiman here.

For the record, the above beforeDefine hook works like a charm (thanks!). However one of the minor downsides is that if one uses sequelize-cli as well, in migrator definitions we would have to define columns in snake case manually instead of allowing the hook to automatically convert to snake case - so that would be models in camelCase but migrations in snake_case.

[kurtzilla]

The beforeDefine hook is not working for me. Could someone show me an implementation? Perhaps I am using the wrong sequelize version (4.13.6)?

[napalm272]

I agree with @quocvu, please at least fix the documentation to not say

Converts all camelCased columns to underscored if true.

[leodutra]

This is the only "wtf" stuff I found in the whole Sequelize.
"Converts all camelCased columns to underscored if true." ... made me crazy.

I, like @felixfbecker said on https://github.com/sequelize/sequelize/issues/6423#issuecomment-325084520, would like to have camel and pascal on my js and underscored on DB.

BTW, underscored db is the most common thing I've ever seen and camelized models are what we expect for JS. It's not about Mongoosification, it's about JS conventions.

Most Node are being migrated from Java and Python, where underscored tables are very common.

[darrentorpey]

My team just started using Sequelize and this was also our first major WTF moment with Sequelize.

• Majorly misleading and incomplete docs
• Default does not work the way most DBs and JS codebases work
• No way simple way to implement this ourselves (no hooks, callbacks, etc. to affect this behavior)

Maybe one of us lovely community members steps in to fill in this gap. Could we get a summary of how the features actually work (since the docs are so wrong) to aid this?

[zhcsyncer]

+1

[leodutra]

Why is this important?
Postgres Identifiers and Key Words

When creating column names with field attributes and camelCase JS model attribute names, DB will use the field definition and everything is ok.

The problem starts with associations.

If I declare underscored, both model attribute and database column become underscored. Providing an camelCase as alias on associate function, it will override both the database column underscoring and the model attribute (to the camelCase alias).

Humm... any workaround? Could find none using target key or anything similar.

[abelosorio]

+1

[vschoener]

Agree this is confusing, I was expected to have my CamelCase converted to underscore and then i found this thread.
Can you provide a global option ?

[quocvu]

Can we move this one from discussion stage to implementation? It looks like the majority of people on this thread are asking for a consistent underscore naming.

[leodutra]

Do your move, champion :smile:
I'm totally f* up on my private project.

I'd love not to be brazilian and be like these amazing people on community with 100+ packages. But life gave me this country, with those taxes and Sisyphus' balls as gift.

[superbull]

+1

[esindger]

+1

[GiancarlosIO]

+1

[NoxPhoenix]

+1

[yeshengfei]

+1

[rogyvojevic]

+1

[mansona]

Howdy folks 👋

I would also like to reflect @quocvu's request to move into the "implementation phase" of this request.

In the meantime I would like to confirm the workaround that @maxnachlinger and @devalnor have been discussing. My understanding is that if you set sequelize to deal with underscores on the database level by adding the following snippet you can still use camelCase on the javascript level: https://github.com/sequelize/sequelize/issues/6423#issuecomment-322618371

(I just noticed that you updated your code @maxnachlinger so both your snippets are functionally the same)

Or, alternatively, does this mean you leave sequelize trying to set up things with camelCase and before it gets to the database it will make sure things are underscored?

Also, where is this hook supposed to be defined? do you do it globally or on a pre-model basis? If you want to do it globally should it be a "global" hook? http://docs.sequelizejs.com/manual/tutorial/hooks.html#global-universal-hooks

[mariotacke]

@maxnachlinger's and @devalnor's workaround works for me, as long as I also set the underscored option in my config. It looks like the auto-timestamps are not part of attributes when the beforeDefine hook is called. Below is my implementation for reference:

models/index.js, loads all models from my models/ directory.

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const decamelize = require('decamelize');

const config = require('../config')[process.env.NODE_ENV || 'development'];

const db = {};
const modelsPath = path.join(__dirname);
const sequelize = new Sequelize(config);

sequelize.addHook('beforeDefine', (attributes) => {
  Object.keys(attributes).forEach((key) => {
    if (typeof attributes[key] !== 'function') {
      attributes[key].field = decamelize(key);
    }
  });
});

fs
  .readdirSync(modelsPath)
  .filter((file) => file.indexOf('.') !== 0 && file !== 'index.js')
  .forEach((file) => {
    const model = sequelize.import(path.join(modelsPath, file));

    db[model.name] = model;
  });

Object.keys(db).forEach((model) => {
  if ('associate' in db[model]) {
    db[model].associate(db);
  }
});

db.Sequelize = Sequelize;
db.sequelize = sequelize;

module.exports = db;

db/config.js. I use this file for the migration cli and regular usage.

require('dotenv').config();

const { Op } = require('sequelize');

module.exports = {
  development: {
    host: process.env.POSTGRES_HOST,
    port: process.env.POSTGRES_PORT,
    username: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    database: process.env.POSTGRES_DB,

    dialect: 'postgres',
    operatorsAliases: Op,

    define: {
      freezeTableName: true,
      underscored: true,
    },
  },
};

Like @netsgnut pointed out, we still need to define the migrations in snake case for this to work. It would be great if the config could apply to migrations as well.

[imranzaidi]

+1

[asap]

👍

[pateketrueke]

This would affect also FKs being generated?

A model could have UserId as FK and user_id as another field.

What could be wrong using UserId if we set everything as underscored?

[dw1284]

I agree with everybody else's sentiments. Convert field names from camel to snake on the way in, and snake to camel on the way out. This feature is pretty standard in other ORMs these days.

[rogerc]

In case the pull request doesnt go in, you can use the code below which is similar to the hook mentioned several times but it uses the config settings rather than assume all needs to be snake_case.

sequelize.addHook('beforeDefine', (attributes) => {
    Object.keys(attributes).forEach((name) => {
        if (typeof attributes[name] !== 'function') {
            attribute = attributes[name];
            const _underscored = attribute.underscored === undefined ? sequelize.options.define.underscored : attribute.underscored;
            if (attribute.field === undefined && _underscored !== undefined) {
                attribute.field = sequelize.Utils.underscoredIf(name, _underscored);
            }
        }
    });
});

[knoxcard]

@rogerc - I currently use MariaDB, but good to know if I ever switch to PostgreSQL, one less thing to worry about!

[didac-wh]

I'm afraid that, although those cool hooks work when"force: true", they do not when using alter: true.
Since Sequelize checks the db schema usinc describeTable() it can't do the decamelize/camelize process without editing the package code.

Does anyone have a solution for this (apart from editing the Sequelize source code)?

[sushantdhiman]

I would like to propose / share how I envision implementation of this change. I would love to hear comments before this actually gets implemented.

---

There will be only one option underscored (underscoredAll and underscored will be merged)

Attributes

Setting Model.options.underscored: true will

1. set field to a name constructed by underscoring attribute. So attribute myColumn will have field my_column
2. If an attribute already got field defined, underscore will not override field for that attribute

Model.tableName

1. If Model.options.tableName is defined use it
2. if Model.options.freezeTableName: true set table name to model name
3. Otherwise pluralize Model.name then underscore it if underscored: true

Auto-generated Attributes (timestamps / version / foreignKeys)

OLD (Current)

If Model.options.underscored: true, then all attributes are generated with underscored name. like Model.rawAttributes.created_at / updated_at / foreign_key` etc

NEW (Proposed)

All attributes will be generated with camelcased name like Model.rawAttributes.createdAt / updateAt / foreignKey etc, but field will be set to underscored name like created_at / updated_at / foreign_key if Model.options.underscored: true

TLDR;

All attributes will be generated with camelcase naming by default, specifying underscore: true will set field by underscoring attribute name

---

Still using custom attribute name

1. Foreign keys, you will be able to pass association.options.foreignKey where applicable that attribute name will be used.

// define Task.user_id, use of `user_id` attribute as foreignKey on Task

User.hasMany(Task, { foreignKey: 'user_id' });
Task.belongsTo(User, { foreignKey: 'user_id' });

1. Timestamps

sequelize.define('Model', {
  name: Sequelize.STRING
}, {
  createdAt: 'created_at', // will define attribute created_at on Model
  updatedAt: 'updated_at' // will define attribute updated_at on Model
})

[rogerc]

Sounds good. My PR I think covered all attributes except foreignKeys. Table name was left untouched.

[pickfire]

I am surprised that underscored does not affect association and user still need to use { as: 'something_underscored' }, maybe this specification should mentioned it? It is not really underscored mode that what have been expected.

[up9cloud]

Memo for whom upgrade v4 to v5 with underscored: true and get unexpected field names (createdAt, updatedAt, deletedAt) from query result:

TL; DR

In v4 you can just set underscored: true and ignore those

{
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    deletedAt: 'deleted_at'
}

In v5, you HAVE TO add them to your define options, otherwise you'll get camel cased field names in your query result 😭 .

Details

v4 original

$ npx sequelize --version
Sequelize CLI [Node: 10.15.3, CLI: 5.4.0, ORM: 4.43.2]

// db/foo.js
module.exports = function (sequelize, DataTypes) {
  const tableName = 'foo'
  return sequelize.define(tableName, {
    id: { type: DataTypes.INTEGER.UNSIGNED, primaryKey: true, autoIncrement: true }
  })
}

// ./db/index.js
const fs = require('fs')
const path = require('path')
const Sequelize = require('sequelize')
const sequelize = new Sequelize({
  ...
  define: {
    paranoid: true,
    underscored: true,
    freezeTableName: true
    // no need to add createdAt, ... options
  }
})
let db = { sequelize }
fs
  .readdirSync(__dirname)
  .filter(file => {
    if (
      file.indexOf('.') === 0 ||
      file === path.basename(__filename)
    ) {
      return false
    }
    return true
  })
  .forEach(file => {
    let model = sequelize.import(path.join(__dirname, file))
    db[model.name] = model
  })
module.exports = db

v5

$ npx sequelize --version
Sequelize CLI [Node: 12.3.1, CLI: 5.4.0, ORM: 5.8.6]

// ./db/foo.mjs
import Sequelize from 'sequelize'
export default function (sequelize, DataTypes) {
  const tableName = 'foo'
  class model extends Sequelize.Model { }
  model.init({
    id: { type: DataTypes.INTEGER.UNSIGNED, primaryKey: true, autoIncrement: true }
  }, {
    sequelize,
    modelName: tableName,
    tableName
  })
  return model
}

// ./db/config.mjs
export default {
  ...
  define: {
    paranoid: true,
    underscored: true,
    freezeTableName: true
  }
}

// ./db/index.mjs
import Sequelize from 'sequelize'
import config from './config.mjs'
import foo from './foo.mjs'
const sequelize = new Sequelize(config)
const DataTypes = Sequelize.DataTypes
let db = {
  sequelize,
  foo: foo(sequelize, DataTypes)
}
export default db

v5 fixed

// ./db/fixedDb.mjs
import Sequelize from 'sequelize'
import config from './config.mjs'
import foo from './foo.mjs'

const fixedConfig = {
  ...config,
  define: {
    ...config.define,
    // You HAVE TO add those...
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    deletedAt: 'deleted_at'
  }
}
const sequelize = new Sequelize(fixedConfig)
const DataTypes = Sequelize.DataTypes
let db = {
  sequelize,
  foo: foo(sequelize, DataTypes)
}
export default db

Test

// ./test.mjs
import db from './db/index.mjs'
import fixedDb from './db/fixedDb.mjs'
async function main () {
  await db.foo.create()

  let r = await db.foo.findOne()
  console.log(r.get({ plain: true }))

  let rr = await fixedDb.foo.findOne()
  console.log(rr.get({ plain: true }))
}
main()

$ node --experimental-modules ./test.mjs
{
  id: 1,
  createdAt: 2019-05-27T17:33:17.000Z,
  updatedAt: 2019-05-27T17:33:17.000Z,
  deletedAt: null
}
{
  id: 1,
  created_at: 2019-05-27T17:33:17.000Z,
  updated_at: 2019-05-27T17:33:17.000Z,
  deleted_at: null
}

[leodutra]

The underscore is not common in JavaScript properties, so makes more sense to consider camel case first.

[papb]

Related: #11225

[CarlosDanielDev]

module.exports = {
  dialect: 'mysql',
  host: 'localhost',
  username: 'root',
  password: 'root',
  database: 'awesome',
  port: 3306,
  define: {
    timestamps: true,
   cameCased: true,
   underscoredAll: true,
  }
};

How can I change underscoredAll to Camel Case ?