Sequelize Error: Relation does not exist

Question

I am able to use sequelize.js to do an INSERT INTO command for a table in my development database, but not in my test database.

Despite researching thoroughly, I have not been able to resolve the issue.

A similar question has been posted here, though I have not been able to answer my question with the answers: sequelize with postgres database not working after migration from mysql

Here is my relevant migration file:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Trees', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false
      },
      content: {
        type: Sequelize.STRING(10000),
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Trees');
  }
};

Here is the model file:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var Tree = sequelize.define('Tree', {
    title: {
      type: DataTypes.STRING,
      allowNull: false
    },
    content: {
      type: DataTypes.STRING(10000),
      allowNull: false
    }
  }, {});
  Tree.associate = function(models) {
    // associations can be defined here
  };
  return Tree;
};

Here is the code that accesses the database:

const setTree = (treeVal, callback) => {
  console.log(`this would be the part`);
  Tree.create({
    title: 'Tree',
    content: JSON.stringify(treeVal)
  })
  .then((treeStr) => {
    let primaryTopics = JSON.parse(treeStr.content);
    callback(null, primaryTopics);
  })
  .catch((err) => {
    callback(err);
  });
}

This is exported in the module.exports method:

  callTree(callback) {
    return Tree.findOne({
      where: {
        title: 'Tree'
      }
    })
    .then((treeStr) => {
      if (treeStr === null) {
        return callback(`not defined yet`);
      }
      let primaryTopics = treeStr.content;
      primaryTopics = JSON.parse(primaryTopics);
      callback(null, primaryTopics);
    })
    .catch((err) => {
      callback(err);
    });
  }

And I'm pulling this method for an integration test here (the PrimaryTopic table is in the same database, and I receive no errors trying to run it):

  beforeEach((done) => {
    this.primaryTopic;
    sequelize.sync({force: true}).then((res) => {

      PrimaryTopic.create({
        title: 'Title: Hello World',
        content: '<p>Content: Hello World</p>'
      })
      .then((primaryTopic) => {
        this.primaryTopic = primaryTopic;
        treeQueries.buildTree((err, res) => {
          if (err) {
            console.error(err);
          }
        });
        done();
      })
      .catch((err) => {
        console.log(err);
        done();
      });

    });
  });

I've searched through all the code for possible errors, but haven't found anything yet.

I can use psql to access the Trees table in the test database, though it is empty.

I can use the same code to insert a value into the Trees table in the development database with no issues.

Here is the error I receive when I try to run a test (using jasmine.js for testing):

{ SequelizeDatabaseError: relation "Trees" does not exist
    at Query.formatError (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/sequelize/lib/dialects/postgres/query.js:363:16)
    at query.catch.err (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
    at tryCatcher (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues [as _onImmediate] (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:756:18)
    at tryOnImmediate (timers.js:717:5)
    at processImmediate [as _immediateCallback] (timers.js:697:5)
  name: 'SequelizeDatabaseError',
  parent: 
   { error: relation "Trees" does not exist
    at Connection.parseE (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:160:13)
    at addChunk (_stream_readable.js:269:12)
    at readableAddChunk (_stream_readable.js:256:11)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onread (net.js:599:20)
     name: 'error',
     length: 104,
     severity: 'ERROR',
     code: '42P01',
     detail: undefined,
     hint: undefined,
     position: '13',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_relation.c',
     line: '1160',
     routine: 'parserOpenTable',
     sql: 'INSERT INTO "Trees" ("id","title","content","createdAt","updatedAt") VALUES (DEFAULT,\'Tree\',\'[{"title":"Title: Hello World","id":1,"secondaryTopics":[]}]\',\'2018-08-14 06:38:37.243 +00:00\',\'2018-08-14 06:38:37.243 +00:00\') RETURNING *;' },
  original: 
   { error: relation "Trees" does not exist
    at Connection.parseE (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/home/siddhartha/Documents/01-Studio/01-Commercial-Public/01-Komodo/2018-resources/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:160:13)
    at addChunk (_stream_readable.js:269:12)
    at readableAddChunk (_stream_readable.js:256:11)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onread (net.js:599:20)
     name: 'error',
     length: 104,
     severity: 'ERROR',
     code: '42P01',
     detail: undefined,
     hint: undefined,
     position: '13',
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'parse_relation.c',
     line: '1160',
     routine: 'parserOpenTable',
     sql: 'INSERT INTO "Trees" ("id","title","content","createdAt","updatedAt") VALUES (DEFAULT,\'Tree\',\'[{"title":"Title: Hello World","id":1,"secondaryTopics":[]}]\',\'2018-08-14 06:38:37.243 +00:00\',\'2018-08-14 06:38:37.243 +00:00\') RETURNING *;' },
  sql: 'INSERT INTO "Trees" ("id","title","content","createdAt","updatedAt") VALUES (DEFAULT,\'Tree\',\'[{"title":"Title: Hello World","id":1,"secondaryTopics":[]}]\',\'2018-08-14 06:38:37.243 +00:00\',\'2018-08-14 06:38:37.243 +00:00\') RETURNING *;' }

Here's a link to the full repository.

Somewhere you used `Trees` and `Tree`, just verify that you have given same name everywhere to resolve this issue — abdulbarik, Aug 14 '18 at 06:49
I'm searched over it a few times, and am not seeing anything. I tried adding an item manually to the `test` database table `Trees`. Here's the result. Not sure if I'm doing this correctly.: komodo-resources-test=# INSERT INTO "Trees" (id, title, content, createdAt, updatedAt) VALUES (DEFAULT, "Test", "testing more", "2018-08-13 22:54:24.228-08", "2018-08-13 22:54:24.228-08"); ERROR: column "createdat" of relation "Trees" does not exist LINE 1: INSERT INTO "Trees" (id, title, content, createdAt, updatedA... — Siddhartha Komodo, Aug 14 '18 at 07:21
Yes (if by `pg` you mean `postgres`). I can see the table by logging in to `psql`, and then executing `SELECT * FROM "Trees";`. `psql` returns an empty "Trees" table -- it's supposed to be empty. It only gets used during the test. I haven't been able to add anything to it, though, as I'm a bit unfamiliar with doing things manually. I tried a few variations on the command in the comment above, but without any luck yet. — Siddhartha Komodo, Aug 14 '18 at 07:30
Trying to just add something manually. Proving more difficult than one would imagine. `komodo-resources-test=# INSERT INTO "Trees" ("id","title","content","createdAt","updatedAt") VALUES (DEFAULT,"Title: Hello World","
Content: Hello World
","2018-08-14 07:49:08.289 +00:00","2018-08-14 07:49:08.289 +00:00"); ERROR: column "Title: Hello World" does not exist LINE 1: ...content","createdAt","updatedAt") VALUES (DEFAULT,"Title: He...` — Siddhartha Komodo, Aug 14 '18 at 07:57
Okay, I was finally able to get something added manually to the database. `komodo-resources-test=# INSERT INTO "Trees" (id, title, content, "createdAt", "updatedAt") VALUES (1, 'test', 'testing', '2018-08-14 07:49:08.289 +00:00', '2018-08-14 07:49:08.289 +00:00'); INSERT 0 1 komodo-resources-test=# SELECT * FROM "Trees"; id | title | content | createdAt | updatedAt ----+-------+---------+----------------------------+---------------------------- 1 | test | testing | 2018-08-13 23:49:08.289-08 | 2018-08-13 23:49:08.289-08 (1 row)` — Siddhartha Komodo, Aug 14 '18 at 08:04
Okay, I found out what was wrong. I had a function in my controllers that was leftover and shouldn't have been there. I don't understand how this affected Sequelize the way it did, but once I removed that function, everything else corrected. Thank you for your help, @abdul — Siddhartha Komodo, Aug 14 '18 at 08:22

score 6 · Answer 1 · answered Mar 12 '21 at 22:52

I know this is old but it could help somebody else facing same issue. I migrated from mysql to postgres and got same issue because the name of my table started with upper case ("Home") and I was trying to add a constraint with table name "home" which starts with lowercase.

score 0 · Answer 2 · answered Oct 14 '20 at 14:19

You need to set freezeTableName to true in the options of the data model. otherwise sequelize will try to search for Trees table

module.exports = (sequelize, DataTypes) => {
  var Tree = sequelize.define('Tree', {
    title: {
      type: DataTypes.STRING,
      allowNull: false
    },
    content: {
      type: DataTypes.STRING(10000),
      allowNull: false
    }
  }, {
freezeTableName: true
});
  Tree.associate = function(models) {
    // associations can be defined here
  };
  return Tree;
};

for full documentation: https://sequelize.org/v3/docs/models-definition/#configuration

2

Your answer is not helping. – junnyea Oct 21 '20 at 09:23

score 0 · Answer 3 · answered Mar 31 '21 at 07:13

If you use the extend model interface, you can simply append tableName: "Trees" to your model configuration.

The easiest way I found to solve, is to explicitly set the tableName on the model. As others have mentioned, sequelize defaults to the plural form of a model as the table name. For instance Tree, becomes Trees.

When you query, sequelize looks after a table with the same name as your model Tree. By defining the tableName in the model, sequelize should search the correct table. Append tableName: "Trees" to your model configuration i.e:

module.exports = (sequelize, DataTypes) => {
    class Tree extends Model {
        ...
    }
    Tree.init(
        {
            title: DataTypes.STRING,
            ...
        },
        {
            sequelize,
            modelName: 'Tree',
            tableName: 'Trees',
        }
    );
    return Tree;
};

Sequelize Error: Relation does not exist

3 Answers3

Linked