SequelizeDatabaseError: relation table does not exist

node.js postgresql mocha.js sequelize.js chai

10,859

Solution 1

Please reference to https://stackoverflow.com/a/39581304/4869679

What I did was:

const model = sequelize.define('modelName', {
id: {
  type: Sequelize.INTEGER,
  primaryKey: true,
  autoIncrement: true,
},
name: {
  type: Sequelize.STRING,
}
}, 
{
 schema: "schemaName",
 tableName: "tabelName
});

Solution 2

To change "Executing (default)" use:

await sequelize.sync({ force: false }); // for change to Executing (default): CREATE TABLE IF NOT EXISTS "users"

10,859

Author by

Admin

Updated on June 14, 2022

Comments

Admin almost 2 years

I am working on chat application. I am using Node.js, Express, Sequelize and PostgreSQL for backend. Can someone help me to understand, why I am able to seed db, but not able to test models?

create db:

const Sequelize = require('sequelize');

const db = new Sequelize(
  process.env.DATABASE_URL ||
  'postgres://localhost:5432/slack-app', {
    logging: false
    }
  );

module.exports = db;

Author Model:

const Sequelize = require('sequelize');
const db = require('../db');

const Author = db.define('author', {
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  image: {
    type: Sequelize.STRING,
  },
});

module.exports = Author;

Message Model:

const Sequelize = require('sequelize');
const Author = require('./author');
const db = require('../db');

const Message = db.define('message', {
  content: {
    type: Sequelize.STRING,
    allowNull: false,
  }
}, {
  defaultScope: {
    include: [
      {
        model: Author,
      }
    ],
  }
});

module.exports = Message;

Channel Model:

const Sequelize = require('sequelize');
const db = require('../db');


const Channel = db.define('channel', {
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true,
  },
});


module.exports = Channel;

Association between models:

const Author = require('./author');
const Message = require('./message');
const Channel = require('./channel');


Author.hasMany(Message);
Message.belongsTo(Author);
Channel.hasMany(Message, {
  onDelete: 'cascade',
  hooks: true,
});
Message.belongsTo(Channel);

module.exports = {
  Author,
  Message,
  Channel,
};

sync db:

const path = require('path');
const express = require('express');
const morgan = require('morgan');
const bodyParser = require('body-parser');
const db = require('./db');
const app = express();
const PORT = process.env.PORT || 3000;

module.exports = app;

//logging middleware
app.use(morgan('dev'));

//body parsing middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true}));

//static middleware
app.use(express.static(path.join(__dirname, '..', 'node_modules')));
app.use(express.static(path.join(__dirname, '..', 'public')));

//API routes
app.use('/api', require('./api'));

//error handling middleware
app.use((err, req, res, next) => {
  res.status(err.status || 500).send(err.message || 'Internal server error');
})

//start server
app.listen(PORT, () => {
  console.log(`Server listening on ${PORT}`);
})

//sync db
db.sync()
  .then(() => {
    console.log('DB synced');
  });

seed db:

const {Author, Message, Channel} = require('./server/db/models');
const db = require('./server/db');


const authors = [
  {
    name: 'Anna',
    image: 'images/girl.jpeg',
  },
  {
    name: 'Helen',
    image: 'images/bee.jpeg',
  },
  {
    name: 'Tom',
    image: 'images/crab.jpeg',
  },
  {
    name: 'Sheila',
    image: 'images/mermaid.jpeg',
  },
  {
    name: 'Michael',
    image: 'images/rooster.jpeg',
  },
  {
    name: 'Tess',
    image: 'images/tweety.jpeg',
  },
  {
    name: 'Shane',
    image: 'images/minion.jpeg',
  },
  {
    name: 'Janine',
    image: 'images/cow.jpeg',
  },
  {
    name: 'John',
    image: 'images/clown.jpeg',
  },
];


const channels = [
  {
    name: 'random',
  },
  {
    name: 'tigers',
  },
  {
    name: 'cavaliers',
  },
  {
    name: 'vikings',
  },
  {
    name: 'indians',
  },
];

const getAuthorId = () => Math.floor(Math.random() * (authors.length - 1)) + 1;

const getChannelId = () => Math.floor(Math.random() * (channels.length - 1)) + 1;

const messages = [
  {
    authorId: getAuthorId(),
    content: 'I like React!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Redux!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Express!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Node.js!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Semantic UI!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Bulma!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Victory.js!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like D3.js!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like PostgreSQL!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I like Sequelize.js!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I love tacos!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I love enchilladas!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I love refried beans!',
    channelId: getChannelId(),
  },
   {
    authorId: getAuthorId(),
    content: 'I love burritos!',
    channelId: getChannelId(),
  },
];

const seed = () => {
  return Promise.all(authors.map(author => Author.create(author))
  )
  .then(() => Promise.all(channels.map(channel => Channel.create(channel))
  ))
  .then(() => Promise.all(messages.map(message => Message.create(message))
  ))
};

const main = () => {
  console.log('Syncing db....');

  db.sync({force: true})
    .then(() => {
      console.log('Seeding db....');
      return seed();
    })
    .catch(err => {
      console.log('Error while seeding');
      console.log(err.stack);
    })
    .then(() => {
      console.log('Closing db connection....');
      db.close();
      return null;
    })
};

main();

After seeding the db, I am able to create relations in db and there is no error. Then I wrote test cases for testing models. I configured package.json file to set up test db.

{
  "name": "slack-app",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "NODE_ENV='test' DATABASE_URL='postgres://localhost:5432/slack-app-test' mocha ./**/*.spec.js",
    "start": "nodemon server",
    "seed": "node seed.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.2",
    "express": "^4.16.2",
    "morgan": "^1.9.0",
    "pg": "^7.4.0",
    "pg-hstore": "^2.3.2",
    "sequelize": "^4.22.5",
    "socket.io": "^2.0.4"
  },
  "devDependencies": {
    "babel-core": "^6.26.0",
    "babel-loader": "^7.1.2",
    "babel-preset-es2015": "^6.24.1",
    "babel-register": "^6.26.0",
    "chai": "^4.1.2",
    "css-loader": "^0.28.7",
    "file-loader": "^1.1.5",
    "mocha": "^4.0.1",
    "node-sass": "^4.6.1",
    "nodemon": "^1.12.1",
    "sass-loader": "^6.0.6",
    "sinon": "^4.1.2",
    "style-loader": "^0.19.0",
    "supertest": "^3.0.0",
    "webpack": "^3.8.1"
  }
}

Here is how my test cases look for all models.

Message Model Test:

/* describe(), beforEach() and it() are available globally  */

const {expect} = require('chai');
const db = require('../../server/db');
const Message = db.model('message');

describe('Message Model', () => {
  beforeEach(() => {
    db.sync({force: true})
      .then(() => {
        console.log('Test DB synced');
      });
  });

  describe('create message', () => {
    let msg;
    beforeEach(() => {
      return Message.create({
        content: 'Hello World!',
      })
      .then(message => {
        msg = message;
      });
    });

    it(`should create message with content 'Hello World!'`, () => {
      expect(msg.content).to.be.equal('Hello World!');
    })
  });
});

Author Model Test:

const {expect} = require('chai');
const db = require('../../server/db');
const Author = db.model('author');

describe('Author Model', () => {
  beforeEach(() => {
    db.sync({force: true})
      .then(() => {
        console.log('Test DB synced');
      });
  });

  describe('create author', () => {
    let michael;
    beforeEach(() => {
      return Author.create({
        name: 'Michael',
        image: 'michaelimage',
      })
      .then(author => {
        michael = author;
      });
    });

    it('should create author with name Michael', () => {
      expect(michael.name).to.be.equal('Michael');
    })
  });
});

Channel Model Test:

const {expect} = require('chai');
const db = require('../../server/db');
const Channel = db.model('channel');

describe('Channel Model', () => {
  beforeEach(() => {
    db.sync({force: true})
      .then(() => {
        console.log('Test DB synced');
      });
  });

  describe('create channel', () => {
    let ch;
    beforeEach(() => {
      return Channel.create({
        name: 'favorite',
      })
      .then(channel => {
        ch = channel;
      });
    });

    it('should create channel with name favorite', () => {
      expect(ch.name).to.be.equal('favorite');
    })
  });
});

But when I am testing models, I am getting following error:

1) Message Model
   create message
     "before each" hook for "should create message with content 'Hello World!'":
 SequelizeDatabaseError: relation "messages" does not exist
  at Query.formatError (node_modules/sequelize/lib/dialects/postgres/query.js:356:16)
  at query.catch.err (node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
  at tryCatcher (node_modules/bluebird/js/release/util.js:16:23)
  at Promise._settlePromiseFromHandler (node_modules/bluebird/js/release/promise.js:512:31)
  at Promise._settlePromise (node_modules/bluebird/js/release/promise.js:569:18)
  at Promise._settlePromise0 (node_modules/bluebird/js/release/promise.js:614:10)
  at Promise._settlePromises (node_modules/bluebird/js/release/promise.js:689:18)
  at Async._drainQueue (node_modules/bluebird/js/release/async.js:133:16)
  at Async._drainQueues (node_modules/bluebird/js/release/async.js:143:10)
  at Immediate.Async.drainQueues (node_modules/bluebird/js/release/async.js:17:14)

Unhandled rejection SequelizeDatabaseError: relation "authors" does not exist
at Query.formatError (/Users/ashishmalav/My Projects/Slack-App/node_modules/sequelize/lib/dialects/postgres/query.js:356:16)
at query.catch.err (/Users/ashishmalav/My Projects/Slack-App/node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
at tryCatcher (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/promise.js:689:18)
at Async._drainQueue (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/async.js:133:16)
at Async._drainQueues (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/async.js:143:10)
at Immediate.Async.drainQueues (/Users/ashishmalav/My Projects/Slack-App/node_modules/bluebird/js/release/async.js:17:14)
at runCallback (timers.js:781:20)
at tryOnImmediate (timers.js:743:5)
at processImmediate [as _immediateCallback] (timers.js:714:5)