This lesson is for PRO members.

Unlock this lesson NOW!
Already subscribed? sign in

Sequelize: Model & Column Casing

2:38 Node.js lesson by

Javascript and database systems tend to be at odds with regard to how tables and columns should be named. Sequlize provides all the tools necessary to satisfy naming conventions in both your Node.js code and in your SQL database.

Get the Code Now
click to level up

egghead.io comment guidelines

Avatar
egghead.io

Javascript and database systems tend to be at odds with regard to how tables and columns should be named. Sequlize provides all the tools necessary to satisfy naming conventions in both your Node.js code and in your SQL database.

Avatar
Olga

Name conversion between tables and js objects drives me crazy in Sequelize. Often I have to use quite complex SQL queries, where I have to use 'query' method. The result from query has keys underscored. Does Sequelize provide any helper to convert them to camelCased version ? Right now, I just give an aliases to columns in SQL queries, but it seems tedious. There are also solutions like: https://github.com/domchristie/humps, but still I wonder if there is any better way to do it.

In reply to egghead.io
Avatar
Mike

There is an underscored option on the sequelize.define options object, but it does not do what the documentation says.

The docs state "Converts all camelCased columns to underscored if true", however, that only appears to be for Sequelize generated fields, such as timestamps and foreign keys.

Setting underscoredAll to true will also set underscored to true.

In reply to Olga

I've preconfigured Sequelize for the database blog. I have a blog post model ready to go. The blog post model synch method has the force option set to true, which forces Sequelize to drop and recreate the entire table every time this script runs. Normally, you would not do this, but I'm using it here to help demonstrate configuration changes.

I'm going to go ahead and run this file in Node.js, then use the PSQL tool to inspect the blog post table. The first thing I notice is that all of the column names and the table name are camelCase, exactly as I have defined them in the model definition.

Using camelCase for names is not really a best practice for many database systems, including Postgres, but it is the convention for JavaScript. Luckily, Sequelize has built-in support for mapping fields to different names.

Updating the publishedAt field is straightforward enough. I'll add a field attribute to the publishedAt definition and give it a more appropriate name using snake case. Running the post file again and inspecting the table shows the publishedAt column is now cased properly.

The createdAt and updatedAt fields are a bit different. Sequelize adds them automatically, to the table. To configure them, I'll need to add an options object as the third argument of the define call.

If I want to simply remove the two timestamp fields, I just need to specify timestamps false in the options object. Checking the table again shows that the timestamp columns are now missing.

To rename the fields, there are two options. First, I can specify underscored true, which will rename the automatically added timestamp fields using snake case, or second, I can specify them individually to customize the name.

For example, using createdon and updatedon instead of createdAt and updatedAt. While these two methods are great for changing the timestamp names in the database, they unfortunately change the name of the field on the model as well.

It seems that the only way to get the createdAt and updatedAt fields to be camel case in JavaScript and snake case in the database is to specify timestamps false in the options hash and manually add the fields directly to the model.

Customizing the table is fairly straightforward. One option is to set the oddly named underscoredAll to true, in which case Sequelize will attempt to convert the camel case model name to snake case automatically. This can sometimes produce unexpected table names, especially when model names include acronyms or digits.

The other option is to specify the table name directly using the tableName option. It may be a bit redundant but can prevent unexpected behavior. Looking at the blog post table with PSQL, the table and all columns are now in snake case.

HEY, QUICK QUESTION!
Joel's Head
Why are we asking?