Sequelize ORM can't use sync()

Question

This is an extract from a file that uses db as the variable from the db.js file, which is the file which connects to the database. And this works fine.

const Sequelize = require("sequelize")
const db = require("../database/db.js")

module.exports = db.sequelize.define(
  "physics_tbls", {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },...

When I add db.sync() to the end of this file, I get an error. This is the end of file above,

.......
Sub_part: {
      type: Sequelize.STRING
    }
  }, {
    timestamps: false
  }
)

db.sync({
    // force: true
  })
  .then(() => {
    console.log('Connection to database established successfully.');
  })
  .catch(err => {
    console.log('Unable to connect to the database: ', err);
  })

I get the error,

TypeError: db.sync is not a function

If db.sequelize works why does db.sync() not work?

Perhaps db.sync should be in the same file as db.js which contains the variable db.But I do need to define my models before I can sync().

This is db.js

const Sequelize = require("sequelize")
const db = {}
const sequelize = new Sequelize("physics_papers_db", "root", "********", {
  host: "localhost",
  dialect: "mysql",
  // operatorsAliases: false,

  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
})

db.sequelize = sequelize
db.Sequelize = Sequelize


module.exports = db

My Express routes incidentally are in another file called tasks.js

const Sequelize = require('sequelize')
var express = require("express")
var router = express.Router()
const Task = require("../model/Task")

//Test
router.get("/test", (req, res) => {
  Task.findAll({
      //Use attributes to only select specific fields
      attributes: ['Question', 'Marks']
    })
    .then(tasks => {
      console.log("router.get(\"/test\", (req, res) => {...");
      console.log("All tasks:", JSON.stringify(tasks, null, 4));
      res.json(tasks)
    })
    .catch(err => {
      res.send("error: " + err)
    })
})
........... more routes...

module.exports = router

Thanks,

balexandre · Accepted Answer

@l2ysho answer is right, but I would add a bit of work so you can avoid the "mess" you are doing in your code... when using an ORM, the general idea is to help us, not only connect to the database, or not writing any SQL, or protect from SQL injection, but mostly be easy to use and change...

with that in mind, always do this when using Sequelize:

create a models folder in your project
add an index.js inside that folder with:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const _ = require('lodash');

const db = {};
const sequelize = new Sequelize(process.env.DB_DATABASE, process.env.DB_USER, process.env.DB_PWD, {
  host: process.env.DB_HOST,
  dialect: 'mysql',
  logging: process.env.APP_ENVIRONMENT !== 'local' ? null : console.log,
  pool: {
    max: 5,
    min: 0,
    idle: 10000,
  },
  operatorsAliases: false,
});

// add all models
fs.readdirSync(__dirname)
  .filter(file => file.indexOf('.') !== 0 && file !== 'index.js' && file.slice(-3) === '.js')
  .forEach(file => {
    const model = sequelize.import(path.join(__dirname, file));
    db[model.name] = model;
  });

// add all associations
Object.keys(db).forEach(modelName => {
  if ('associate' in db[modelName]) {
    db[modelName].associate(db);
  }
});

module.exports = _.extend(
  {
    sequelize,
    Sequelize,
  },
  db
);

now, add a file per database model, let's imagine a logs table you might have:

called it logs.js and put inside the models folder, together with the index.js

module.exports = (sequelize, DataTypes) => {
  const model = sequelize.define(
    'external_logs',
    {
      id: {
        type: DataTypes.INTEGER.UNSIGNED,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      engine: { type: DataTypes.STRING, allowNull: false },
      response_code: { type: DataTypes.INTEGER.UNSIGNED, allowNull: true },
      origin: { type: DataTypes.STRING, allowNull: true },
      created_at: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: sequelize.literal('CURRENT_TIMESTAMP'),
      },
    },
    {
      timestamps: false,
      tableName: 'frontend_api_external_logs',
    }
  );

  model.associate = models => {
    model.belongsTo(models.campaigns, {
      foreignKey: 'campaign_id',
      allowNull: false,
      onDelete: 'cascade',
      onUpdate: 'cascade',
    });
    model.belongsTo(models.external_audit, {
      foreignKey: 'audit_id',
      allowNull: false,
      onDelete: 'cascade',
      onUpdate: 'cascade',
    });
  };

  return model;
};

as you can see, you can easily create associations with other models very easy, without the need to import any file, and you can then call this model, by the definition you specified, in this case: external_logs ... though the real table name is called, as you can see: frontend_api_external_logs

if you end up needed to call any model or perform any database task, all you need is to call that index.js file, and not only the database, but also all models with their own associations will be available as well... for example, in a utilities folder, with a database.js file, the database.js could simply have:

const db = require('./../models'); // same as './../models/index'

const logApiCall = (action, description) => {
   const logRow = {
       // content of the external logs table
       external_audit: {
           // content of the audit table as there's an association
       }
   }; // the content
   db.external_logs
      .create(logRow, { include: db.external_audit })
        // inserted
      })
      .catch(err => {
        // NOT inserted
        logger.log(`AUDIT ERROR: ${err.message}`);
        utilities.slack.sendMessage({ action, description, error: err.message });
      });
};

exports.logCall = logApiCall;

and this makes it even easier to perform logs with audits to any action performed by the users, and it will as simple as:

const db = require('./../utilities/database');

...

db.logCall('my action', 'my description');

as you can see, I don't need to worry about what models my file is (no need to require each model I'm using) and I simply call one file that handles all.

this is what I've been using since I've started to use Sequielize (when there was only v2 available) and I've been "upgrading" the index.js file when they bump versions, but this one is the exact same for ev4 and the new v5...

give it a try and I'm sure it will help you along your Sequielize projects...

in a new project of mine:

Sequelize ORM can't use sync()

Answers (2)

Related Questions

Sequelize ORM can&#39;t use sync()

Answers (2)

Related Questions

Sequelize ORM can't use sync()