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

In this short article, I have highlighted the pain points I experienced trying to adapt Sequelize models to an existing database schema. I assumed that the reader is already familiar with Sequelize, however if this is your first time working with Sequelize, you can check out the additional resource links that I have included below. If you have any question related to this article, feel free to reach out. I respond faster via Twitter DM @nedssoft

Additional Resources