Troubleshooting Common Issues in Sequelize Sequelize

Project Description

Sequelize is an open-source Node.js library for interacting with relational databases, such as MySQL, MariaDB, and PostgreSQL. It provides a simple and easy-to-use interface for creating, querying, and modifying data in a database.

It is written in JavaScript and can be used in a Node.js application to access a database through an ORM (Object-Relational Mapping). This allows you to write code in a high-level, abstract way that is easier to read and maintain, rather than having to write raw SQL queries.

Sequelize provides a variety of features, including support for transactions, associations between models, and a simple query builder for constructing complex queries. It also integrates with popular Node.js web frameworks, such as Express and Hapi, making it easy to use in a web application.

Troubleshooting Sequelize Sequelize with the Lightrun Developer Observability Platform

• Instantly add logs to, set metrics in, and take snapshots of live applications
• Insights delivered straight to your IDE or CLI
• Works where you do: dev, QA, staging, CI/CD, and production

Start for free today

The following issues are the most popular issues regarding this project:

Query with nested include and custom join condition

By taking advantage of a condition in the include, only those rows containing Red colors as part of their value will be joined. Yet this does not provide all possible solutions; it is necessary to first filter for parent models that contain at least one “Red” color before making an inclusion without any conditions applied.

var Sequelize = require('sequelize')

var sequelize = new Sequelize('postgres://postgres:postgres@localhost/sequelize_test')

var Bucket = sequelize.define('Bucket', {
  name: { type: Sequelize.TEXT, allowNull: false }
})

var Color = sequelize.define('Color', {
  value: { type: Sequelize.TEXT, allowNull: false }
})

Bucket.hasMany(Color, { as: 'colors', foreignKey: 'BucketId' })
Color.belongsTo(Bucket, { as: 'bucket', foreignKey: 'BucketId' })

sequelize.sync({ force: true }).then(function(){
  return Bucket.create({ name: 'myBucket' })
}).then(function(bucket){
  var colors = ['red', 'geen', 'blue'].map(function(color){
    return { value: color, BucketId: bucket.id }
  })
  return Color.bulkCreate(colors)
}).then(function(){
  return Bucket.find({
    where: {
      // here you can use custom subquery to select only buckets that has AT LEAST one "red" color
      $and: [['EXISTS( SELECT * FROM "Colors" WHERE value = ? AND "BucketId" = "Bucket".id )', 'red']]
    },
    include: [
      // and then join all colors for each bucket that meets previous requirement ("at least one...")
      { model: Color, as: 'colors' }
    ]
  })
}).then(function(bucket){
  console.log(JSON.stringify(bucket, null, 2)) // output
})
output:

{
  "id": 1,
  "name": "myBucket",
  "createdAt": "2015-09-14T17:38:49.817Z",
  "updatedAt": "2015-09-14T17:38:49.817Z",
  "colors": [
    {
      "id": 1,
      "value": "red",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    },
    {
      "id": 2,
      "value": "geen",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    },
    {
      "id": 3,
      "value": "blue",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    }
  ]
}


Postgres proves to be quite an advantageous choice on this front, leveraging the relative performance of its EXISTS() operator. However, it is prudent to consider alternative options such as a different method that may prove equally beneficial for other relational databases like MySQL.

Bucket.find({
    where: {
      // subquery that will select all buckets that have color "red"
      id: { $in: Sequelize.literal('(SELECT DISTINCT "BucketId" FROM "Colors" WHERE value = \'red\')') }
    },
    include: [
      // select all colors per each bucket
      { model: Color, as: 'colors' }
    ]
  })

To achieve optimal results, the first approach involves running an additional query for every entry in the “Buckets” table. Alternatively, a second strategy is to run one extra query before selecting from this table and combining its content with that of Colors.

RequestError: Timeout: Request failed to complete in 15000ms

Struggling with a similar difficulty? Give this avenue of action a shot to see if it helps.

"dialectOptions": {
    options: { "requestTimeout": 300000 }
  },

Cannot access [Model] before initialization

At the heart of this problem lies a requirement for recursive imports. With that in mind, our attention was turned to PR 1206 (https://github.com/RobinBuschmann/sequelize-typescript/pull), which partially resolves the issue; however, it appears further intervention is needed – an exchange from one generic model based on an interface could provide more comprehensive results. Thusly we might glimpse what such a possible resolution would look like as a whole

@Table
export class Player extends Model<IPlayer> {

  @Column
  name!: string;

  @Column
  num!: number;

  @ForeignKey(models => models.Team)
  @Column
  teamId!: number;

  @BelongsTo(models => models.Team)
  team!: Model<ITeam>;
}

@Table
export class Team extends Model<ITeam> {

  @Column
  name!: string;

  @HasMany(models => models.Player)
  players!: Model<IPlayer>[];
}

How do I apply an order to include

Unfortunately, the requested syntax is not available at this time (except with “separate: true” which only works for hasMany relationships). An alternative approach to consider would be the slightly longer and more cumbersome syntax.

findAll({
  ...
  order: [
    [models.Image, 'updated_at', 'asc']
  ]
  ...
});

Is it possible to bulk update using an array of values

Unfortunately, Sequelize and SQL databases won’t be able to handle this task. However, the good news is that creating a custom functional solution should not present too much of a challenge; simply write in some code loops with an update call!

findAndCountAll with the `GROUP BY` clause causes the count to be an array of objects

To ensure accuracy when using the “findAndCountAll” function with a group by parameter, use “result.count.length” rather than simply employing “result.count”.

How to configure a column without a timezone in the timestamp?

If discrepancies in your data’s timestamp are preventing operations from running effectively, Sequelize can facilitate the alteration of your timezone to seamlessly rectify any issues. Utilizing timestamps cautiously allows for a uniform experience regardless of each user’s individual locales.

belongsToMany Association on the same model with a different foreign key and alias fails

Without activity, this issue is set to close. To keep it open and bring further attention to the matter at hand, just add a comment in response.

Sequelize not handling data times correctly when backed by TIMESTAMP WITHOUT TIME ZONE

The PostgreSQL database connection module was experiencing an issue. To resolve it, I altered the date formatter from pg library – consequently remedying this difficulty.

const pg = require('pg');
pg.types.setTypeParser(1114, (str:string) => new Date((str.split(' ').join('T'))+'Z'));

Before creating the necessary database connection, this code was run in order to separate the plain string value from the date column stored in said database and link it with the T flag involved indicating the Date class, even including the UTC time zone’s Z symbol.

Unknown column in the field list

Learning how to properly load attributes from an associated model in Sequelize.js can be a great way of enhancing your database knowledge and understanding the power behind this powerful JavaScript library!

https://stackoverflow.com/questions/59941122/how-to-load-attributes-from-associated-models-with-sequelize-js

Setting timezone

var sequelize = new Sequelize(connStr, {
    dialectOptions: {
        useUTC: false //for reading from database
    },
    timezone: '+08:00' //for writing to database
});

Field with type Sequelize.JSON returns a string when MySQL dialect is used for a MariaDB database

MariaDB connection is still experiencing some hiccups while attempting to couple Sequelize with the driver. In its current state, JSON values are delivered as objects when records are read from MariaDB; however, include statements have been producing string-type results for subitem fields. A code snippet demonstrating this phenomenon has been provided above.

const User = dbService.define(
  'User',
  {
    id: {
      type: sequelize.INTEGER,
      primaryKey: true,
    },
    data: {
      type: sequelize.JSON,
      defaultValue: '',
    },
  },
);

const Project = dbService.define(
  'Project',
  {
    id: {
      type: sequelize.INTEGER,
      primaryKey: true,
    },
    data: {
      type: sequelize.JSON,
      defaultValue: '',
    },
  },
);

Project.belongsTo(User);
User.hasMany(Project, {
  foreignKey: 'userId',
  as: 'projects',
});

(async () => {
  const users = await User.findAll({
    include: [
      {
        model: Project,
        as: 'projects',
      },
    ],
    attributes: ['id', 'data', 'projects.id', 'projects.data']
  });
  console.info(typeof users[0].data); // object
  console.info(typeof users[0].projects[0].data); // string
})();

Uncover the inner workings of a working demo: explore https://github.com/omegascorp/sequelize-mariadb-json-test and experience an advanced demonstration in action!

Error when using the `$contains` operator with JSONB array field

Given JSON structure:

SELECT myJsonCol FROM myTable

{locked: true, browsers: [{name: "chrome"}, {name: "firefox"}] }

Using the provided query, it is possible to access all rows in the browsers array with the name “chrome”.

model.find({
  where: {
    myJsonCol: {
      '$contains': { browsers:[{name: "chrome"}] }
    }
  }
})

Here is the generated SQL statement:

SELECT  ...
FROM "myTable"
WHERE "myTable"."myJsonCol" @> '{"browsers":[{"name":"chrome"}]}';

Raw queries convert Date replacements to the local timezone.

To ensure the best results when working with multiple time zones, it is recommended to store dates in UTC and retrieve them from the database based on your specific time zone. For more detailed advice on constructing queries correctly for different application use cases, refer to StackOverflow answers. As an example of how this can be implemented with a SQL query – if you’re looking for all fields related to a User plus their “createdAt” attribute set within Europe/Kiev’s timeframe then consider using appropriate methods as outlined by developers online!

ELECT "createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev' FROM users WHERE id = 1;

# or with variables
SELECT "createdAt"::timestamptz AT TIME ZONE :timezone FROM users WHERE id = :id;

For Sequelize (for the User model) it will be something like this:

sequelize.findOne({
  where: { id: 1 },
  attributes: {
    include: [
      [sequelize.literal(`"User"."createdAt"::timestamptz AT TIME ZONE 'Europe/Kiev'`), 'createdAt'],
      
      // also you can use variables, of course remember about SQL injection:
      // [sequelize.literal(`"User"."updatedAt"::timestamptz AT TIME ZONE ${timeZoneVariable}`), 'updatedAt'],
    ]
  }
});

Deadlock on multiple transactions

With a little ingenuity, I found an effective workaround utilizing the p-queue system.

const Sequelize = require('sequelize');
const {default: PQueue} = require('p-queue');
const sequelize = new Sequelize('mysql://user:password@dburl:3306/test')

const sequelize.queue = new PQueue({concurrency: (sequelize.connectionManager.pool.maxSize -1)});
const inTransaction = fn => sequelize.queue.add(
  () => sequelize.transaction((transaction) => fn(transaction)),
);

class TestUser extends Sequelize.Model {}
TestUser.init({
  username: Sequelize.STRING,
  birthday: Sequelize.DATE
}, { sequelize, modelName: 'testuser' })

for (var i = 0; i < 5; i++) {
    inTransaction(t => {
        return TestUser.findOne({
            where: { username: 'janedoe' }
        }, { transaction: t })
        .then(user => {
            return TestUser.update(
                { birthday: new Date(1980, 1, 8) },
                {
                    where: { username: 'janedoe' }
                },
                { transaction: t }
            )
        })
    })
    .then(() => console.log('done'))
    .catch(err => console.log(err))
}

It may be worth exploring the possibility of integrating an element into Sequelize.transaction() for streamlined functionality.

SUM and include association gives “..id must appear in GROUP BY clause”

models.user.find({where: { id: 1 }, 
  group:['user.id'], 
  attributes: [[Sequelize.fn('SUM', Sequelize.col('histories.amount')), 'total']], 
  include: [{model: models.history,attributes:[]}],
  raw:true
}).then(function(user) {
    console.log(user);
}).catch(function(error) { console.log(error) });

To ensure the exclusion of all fields from the ‘history’ table, you must include attributes in your query. To execute as a raw query and skip any model instantiation, add “raw:true” to the options. Note that when using this mode of execution, results are returned without getters – here’s an example; If requesting total values only certain access will be granted (i.e user[0].total instead of user[0].get(‘total’) )

Support PostgreSQL `SELECT DISTINCT ON(column) …`

For those seeking a complex resolution, Model.findAll(query) may be the answer! Long known as an effective way to retrieve all objects from the database according to certain criteria, this solution has proven successful in many instances.

query.attributes = [
      db.sequelize.literal('DISTINCT ON("columnName") "TableName".otherColumnName"'),
      'otherColumnName',
      ...restColumns
 ];

query.raw = true;

To ensure accuracy and conformity, be sure to add “TableName”.otherColumnName”’ when using DISTINCT ON(“columnName”), thereby avoiding any superfluous commas added by sequelize.

How to parse models and associations from the raw query?

Through creative problem-solving, I discovered an unconventional approach to expanding the model. Applying Sequelize 4.23.2 enabled me to observe nested associations – a result that could potentially help those in need of similar support!

    const query = 'your query'
    const options = {
      hasJoin: true,
      include: [{
        // include related models
        model: db.models.products,
        include: [{
          model: db.models.discountTags,
          include: [{
            model: db.models.orderArticles,
            include: [{
              model: db.models.orders
            }]
          }]
        }]
      }]
    }

    models.productGroups._validateIncludedElements(options)
    const returnObjs = await models.sequelize.query(query, options)

Columns of the join table are always selected, which breaks aggregation in PostgresSQL

With the simple alteration of adding {includeIgnoreAttributes: false} to my query, I was able to obtain an accurate response.

query =
  includeIgnoreAttributes: false
  include: [
    {model: UserModel, as: "users", where: {id: user.id}}
  ]
  group: "users.id"
LeadModel.min("LastModifiedDate", query)
SQL generated:
SELECT min("LastModifiedDate") AS "min"
  FROM "Leads" AS "Lead"
  INNER JOIN ("user_leads" AS "users.user_lead"
    INNER JOIN "Users" AS "users"
    ON "users"."id" = "users.user_lead"."UserId")
  ON "Lead"."id" = "users.user_lead"."LeadId" AND "users"."id" = 'sadlkjfsaljsl1234'
  GROUP BY users.id;
Result:
2017-03-14T00:28:11.000Z

More issues from Sequelize repos

Troubleshooting Sequelize cli