CODEWITHSUNDEEP
node.jspostgresqlmocha.jssequelize.jschai
Ashish Malav
Ashish Malav

Reputation: 81

SequelizeDatabaseError: relation table does not exist

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)

Upvotes: 8

Views: 14453

Answers (4)

Nate
Nate

Reputation: 13242

So, to add one more edge case to this discussion, I was getting this error because the table did not exist. This was because my migration had an error in it.

// ## 20201228120000-create-stores.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('stores', {
      // fields ...
    }, { });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('stores');
  },
};

// ## 20201228130000-create-store-assets.js
//                          ^^^^^^^^^^^^
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('stores', {
//                                    ^^^^^^
//            This is supposed to be 'store_assets'

      // fields ...
    }, { });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('stores');
//                                  ^^^^^^
  },
};

So, check your migrations and make sure you didn't copy-paste a file without fully vetting the changes...

Upvotes: 0

Steve Rock
Steve Rock

Reputation: 372

To change "Executing (default)" use:

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

Upvotes: 1

TakanashiOuken
TakanashiOuken

Reputation: 21

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
});

Upvotes: 2

Nätu
Nätu

Reputation: 332

I got the same error because sequelize wasn't appending the schema name in front of the table name when it generates the sql query. Add your schema name when you 'define' with sequelize. It should work properly.

Upvotes: 0

Related Questions