Specifying column name

Summary

This discussion covers how to modify column names in a Sails.js Waterline model while keeping a consistent naming convention between JavaScript and the database. The user wanted to convert camelCase or PascalCase property names in their models to snake_case in the database while still referencing them in camelCase within their application.

The steps included:

• Dropping the existing user table using Beekeeper Studio.

• Updating the Waterline model to rename name to firstName and lastName.

• Using the columnName property in the model to map JavaScript property names to snake_case column names in PostgreSQL.

• Running Sails.js to apply the changes through auto-migration.

• Verifying the updates in Beekeeper Studio and ensuring createdAt and updatedAt fields were also renamed to created_at and updated_at.

• Demonstrating that Sails.js allows querying the database using camelCase property names while storing them in snake_case.

• Performing CRUD operations to validate that data integrity is maintained between the application and the database.

This method ensures that Sails applications maintain JavaScript-friendly property names while following best practices for database naming conventions.

Transcript

So perhaps the table name might not be the only thing you want to modify in the physical database, be it PostgreSQL, MySQL, or even MongoDB. Maybe you want to modify the columnName for some reason. And I think this would come up, at least I find myself having to do this, when I have Pascal case or camel case in JavaScript, which is in my Waterline model. And I think it's just convention. And I've observed that, mostly in the database world, if you have multiple words for your columnName, you mostly use snake_case. So this is the convention I stick with. Let me show you what I mean.

So you could see I have already dropped that user table. The way I did that is in BeeKeeper Studio. Just by the way, in case you're wondering, I just did a DROP TABLE public.user. So that dropped the table. Just in case you're wondering about that.

Let's say, for example, the business requirement or your user story changed, and instead of name, you now want firstName and lastName. In JavaScript, you could define it like this:

attributes: {
  firstName: {
    type: 'string'
  },
  lastName: {
    type: 'string'
  }
}

If we go back to Warp now and run Sails, that should do the auto-migration. And if we go back to Beekeeper Studio and check the users table, we see firstName and lastName. But we also see that createdAt and updatedAt are in camelCase.

So let's see how we could modify that. In the firstName property, we could add:

columnName: 'first_name'

This tells Sails.js to store the column as first_name instead of firstName. We can do the same for createdAt and updatedAt:

createdAt: {
  columnName: 'created_at'
},
updatedAt: {
  columnName: 'updated_at'
}

Now, if we restart Sails so the auto-migration runs again, we can refresh Beekeeper Studio and see the updated column names.

One important thing to note is that even though the physical column in the database is snake_case, we can still reference it using camelCase in our Sails.js application. For example, inserting a record:

await User.create({ firstName: 'Kelvin', lastName: 'Omereshone' });

Even though first_name is stored in the database, we can still query it using:

const user = await User.findOne({ firstName: 'Kelvin' });

We also demonstrated deleting a record using .destroyOne() in Waterline, which returns the deleted record.

This approach ensures that teams following a database convention of snake_case can maintain consistency while keeping JavaScript-friendly property names in their Sails applications.