Create sequelize transaction inside Express router

Question

I'm trying to make a rest request using sequelize transaction, unfortunately it doesn't works:

undefined is not a function

This means that sequelize.transaction is undefined, sequelize is imported but not instantiated for using inside my route:

    router.post('/', secret.ensureAuthorized, function(req, res) {

    var sequelize = models.sequelize;   
    var newpost;

    sequelize.transaction({autocommit: false}, function (t) {       

        return models.post.build().updateAttributes({
                title: req.body.title,
                shortdescription: req.body.description.substring(0,255),
                description: req.body.description,
                titleImage: req.body.titleImage, 
                link: req.body.link,
                userid: req.body.userid 
            }, {transaction: t}).then(function(post){
                newpost = post; 
                // create categories
                var tags = req.body.categories;

                models.hashtag.bulkCreate(tags, {transaction: t}).then(function(){
                    newpost.setTags(tags, {transaction: t});
                });         
        });


    }).then(function (result) {
        // Transaction has been committed
        // result is whatever the result of the promise chain returned to the transaction callback is
        if (newpost) {
            res.json(newpost);
        }
        console.log(result);
    }).catch(function (e) {
        // Transaction has been rolled back
        // err is whatever rejected the promise chain returned to the transaction callback is
        throw e;
    });
});

My models works without any issues and the express routes are working too but not with transaction.

My package json

{
  "name": "myapp",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },
  "dependencies": {
    "body-parser": "~1.12.4",
    "cookie-parser": "~1.3.5",
    "debug": "~2.2.0",
    "express": "~4.12.4",
    "jade": "~1.9.2",
    "morgan": "~1.5.3",
    "serve-favicon": "~2.2.1",
     "jsonwebtoken": "^5.0.2",
     "pg": "^4.4.0",
     "pg-hstore": "^2.3.2",
     "crypto-js": "^3.1.5",
    "sequelize": "^3.2.0"
  }
}

My index.js is working fine too, but don't know how to pass the same instance of sequelize here for express routes:

var Sequelize = require('sequelize');
var config    = require('../config');  // we use node-config to handle environments
var fs        = require("fs");
var path      = require("path");
var models = require('../models');
// initialize database connection
var sequelize = new Sequelize(
  config.database.name,
  config.database.username,
  config.database.password, {
      dialect: 'postgres',  
      host: config.database.host,
      port: config.database.port,
      autoIncrement: true,
      omitNull: true,
      freezeTableName: true,
      pool: {
        max: 15,
        min: 0,
        idle: 10000
      },
});

var db        = {};

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf(".") !== 0) && (file !== "index.js");
  })
  .forEach(function(file) {
    var model = sequelize["import"](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if ("associate" in db[modelName]) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

sequelize.sync({
    force: true
}).then(function(){
// load batch
    if (process.env.BATCH) {
        console.log("loading batch");
        var batch = require("../config/batch");
        batch.loadPosts();
    }
});


module.exports = db;

best regards.

UPDATE I changed the code as above explained. Now my error is:

Unhandled rejection Error: commit has been called on this transaction(c457e532-b
164-43dc-9b0e-432be031fe36), you can no longer use it

I'm using Postgres database.

Solution 1

It seems to me that your database initialization code may be stored in a file called sequelize.js, which you are trying to import in your route handler.

However, you're importing the global sequelize module, and not your local one. You need to use a relative path to do that:

router.post('/', function(req, res) {
    var sequelize = require('./sequelize');

    sequelize.transaction(function(t){

        console.log('transaction openned'+t);

    });
});

(that assumes that your sequelize.js file is located in the same directory as your route handler file is; if not, you should change the ./ part)

Solution 2

This is my final solution, I didn't know about returning those promises, now I can do that like this:

var sequelize = models.sequelize;   
    var newpost;
    // create categories
    var tags = req.body.categories;
    sequelize.transaction({autocommit: false}, function (t) {       

        return models.post.build().updateAttributes({
                title: req.body.title,
                shortdescription: req.body.description.substring(0,255),
                description: req.body.description,
                titleImage: req.body.titleImage, 
                link: req.body.link,
                userid: req.body.userid 
            }, {transaction: t}).then(function(post){
                newpost = post;
                for (var i = 0; i < tags.length;i++) {

                    // ({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})

                return models.hashtag.findOrCreate(
                        {where: {description: tags[i].description},
                        defaults: {description: tags[i].description},
                        transaction: t}).spread(function(tag, created) {
                        return newpost.addTag(tag, {transaction: t});
                    });             
                }
            });

    }).then(function (result) {
        // Transaction has been committed
        // result is whatever the result of the promise chain returned to the transaction callback is
        if (newpost) {
            res.json(newpost);
        }
        console.log(result);
    }).catch(function (e) {
        // Transaction has been rolled back
        // err is whatever rejected the promise chain returned to the transaction callback is
         res.status(500).send({
                type: false,
                data: e
            });     
        throw e;
    });