Error relation users does not exist

When you’re running Sequelize code to fetch or manipulate data from a PostgreSQL database, you might encounter an error saying relation <table name> does not exist.

For example, suppose you have a database named User in your PostgreSQL database as shown below:

cakeDB=# dt

          List of relations
 Schema | Name | Type  |    Owner
--------+------+-------+-------------
 public | User | table | nsebhastian
(1 row)

In the above output from psql, the cakeDB database has one table named User that you need to retrieve the data using Sequelize findAll() method.

Next, you create a new connection to the database using Sequelize and create a model for the User table:

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("cakeDB", "nsebhastian", "", {
  host: "localhost",
  dialect: "postgres",
});

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

After that, you write the code to query the User table as follows:

const users = await User.findAll();
console.log(users);

Although the code above is valid, Node will throw an error as follows:

Error
    at Query.run
    ...
  name: 'SequelizeDatabaseError',
  parent: error: relation "Users" does not exist

In PostgreSQL, a relation does not exist error happens when you reference a table name that can’t be found in the database you currently connect to.

In the case above, the error happens because Sequelize is trying to find Users table with an s, while the existing table is named User without an s.

But why does Sequelize refer to Users while we clearly define User in our model above? You can see it in the code below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
});

This is because Sequelize automatically pluralizes the model name User as Users to find the table name in your database (reference here)

To prevent Sequelize from pluralizing the table name for the model, you can add the freezeTableName option and set it to true to the model as shown below:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  freezeTableName: true,
});

The freezeTableName option will cause Sequelize to infer the table name as equal to the model name without any modification.

Alternatively, you can also add the tableName option to tell Sequelize directly the table name for the model:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "User",
});

Once you add one of the two options above, this error should be resolved.

Please note that the model and table names in Sequelize and PostgreSQL are also case-sensitive, so if you’re table name is User, you will trigger the error when you refer to it as user from Sequelize:

const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING,
  },
  lastName: {
    type: Sequelize.STRING,
  },
},
{
  tableName: "user", // relation "user" does not exist
});

The relation does not exist error in Sequelize always happens when you refer to a PostgreSQL database table that doesn’t exist.

When you encounter this error, the first thing to check is to make sure that the Sequelize code points to the right table name.

This error can also occur in your migration code because you might have migration files that create a relationship between two tables.

Always make sure that you’re referencing the right table, and that you’re using the right letter casing.