Intro

You've probably been working with Sequelize following A-Z which means you begin from migrations, models to generating the tables in the database. In this use case, you take advantage of some abstractions that Sequelize ships with such as follows:

  • Not having to specify the name of the tables in the models
  • Not having to specify the field names for createdAt and updatedAt timestamps.

    • Then, you found yourself in a use case where there's an existing database with different naming conventions for tables, fields, etc with Sequelize. Now, you need to take a Z-A approach where you would create models to adapt to the existing database schema.

    This short article explains the steps you need to take in order to work with Sequelize in an existing database schema.

    Step 1

  • Install dependencies 
    • npm install sequelize pg pg-hstore
  • Generate Sequelize scaffolding 
    • touch .sequelizerc

    Copy the snippet below into the .sequelizerc file. Note, here I want to bundle Sequelize related files into a single folder called database , feel free to remove the database from the paths if you want. 

    const path = require('path')
module.exports = {
  config: path.resolve('./database/config', 'config.js'),
  'models-path': path.resolve('./database/models'),
  'seeders-path': path.resolve('./database/seeders'),
  'migrations-path': path.resolve('./database/migrations'),

    Now, generate the scaffolding
    

    npx sequelize-cli init
    

    Next up, update the database/config/config.js with your database connection credentials. 
    

    Step 2 -- Generating models for the exiting database tables
    

    Now let's assume that one of the tables in the existing database with the name foo_bars has the structure below:
    

    # tableName -> foo_bars
id INTEGER AUTOINCREMENT
first_name VARCHAR(200)
last_name VARCHAR(200)
email VARCHAR(200)
date_created DATETIME
date_updated DATETIME
    

    If you generated the model using Sequelize command, the timestamps would be implicitly generated with names createdAt and updatedAt respectively.
    

    Now, let's create a model that will represent the table above.
Create a file named FooBar.js inside the database/models folder. Copy the snippet below into the FooBar.js
    

    const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class FooBar extends Model {
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
    static associate(models) {
      // define association here
  FooBar.init(
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
     first_name: DataTypes.STRING,
     last_name: DataTypes.STRING,
     email: DataTypes.STRING,
     date_created: DataTypes.DATE,
     date_updated: DataTypes.DATE
      // options
      sequelize,
      modelName: 'FooBar',
      tableName: 'foo_bars',
      createdAt: 'date_created',
      updatedAt: 'date_updated',
      underscore: true,
  return FooBar;

    What to note from the snippet above:
    

  • The table name was specified at it is in the existing database.
    • 

  • The createdAt and updatedAt specified as in the existing database
    • 

  • We set underscore: true because the existing schema fields are underscored. Sequelize by default uses camel case.
    • 

    Other important scenarios
    

    When the existing does not have id as the primary key
    

    Sequelize by default assumes that every table has a primary key field with name id. In a situation where the table does not have an id field, we can work around it using the removeAttribute model method as shown below:
    

    const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class FooBar extends Model {
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
    static associate(models) {
      // define association here
  FooBar.init(
     first_name: DataTypes.STRING,
     last_name: DataTypes.STRING,
     email: DataTypes.STRING,
     date_created: DataTypes.DATE,
     date_updated: DataTypes.DATE
      // options
      sequelize,
      modelName: 'FooBar',
      tableName: 'foo_bars',
      createdAt: 'date_created',
      updatedAt: 'date_updated',
      underscore: true,
  FooBar.removeAttribute('id');
  return FooBar;

    When the existing does not have createdAt and updatedAt timestamps
    

      Just like the id field, Sequelize by default expects that every table should have these fields which is the reason they are automatically generated alongside new migrations. The workaround for this is to set the timestamps to false in the model option as shown below:
    
    // options
    sequelize,
    modelName: 'FooBar',
    tableName: 'foo_bars',
    createdAt: 'date_created',
    underscore: true,
    timestamps: false,

    When one of the either createdAt or updatedAt is not needed in the existing database.
    

    The snippet below shows the workaround by modifying the model options:
    
      // options
      sequelize,
      modelName: 'FooBar',
      tableName: 'foo_bars',
      createdAt: 'date_created',
      underscore: true,
      updatedAt: false,

    Conclusion