CODEWITHSUNDEEP
javascriptknex.jsbookshelf.js
Shaun Scovil
Shaun Scovil

Reputation: 3987

How to save one-to-many relationship after insert in a transaction

I am using Bookshelf v0.10.3 with Knex v0.12.9, in an Express v4.15.2 server on Node v6.9.5.

I have a user table with an associated identity table, joined by an identity_user table. The tables are created using Knex:

Promise.all([
  knex.schema.createTable('user', table => {
    table.increments('id');
    table.string('username');
    table.string('password');
    table.unique('username');
  }),
  knex.schema.createTable('identity', table => {
    table.increments('id');
    table.string('type');
    table.string('value');
  }),
  knex.schema.createTable('identity_user', table => {
    table.integer('user_id').references('user.id');
    table.integer('identity_id').references('identity.id');
    table.unique(['user_id', 'identity_id']);
  })
]);

My Bookshelf models look like this:

bookshelf.plugin([ 'registry', 'bookshelf-camelcase' ]);

const Identity = bookshelf.model('Identity', {
  tableName: 'identity',

  user() {
    return this.belongsTo('User', 'identity_user');
  }
});

const User = bookshelf.model('User', {
  tableName: 'user',

  initialize() {
    this.on('saving', model => {
      if (model.hasChanged('username')) {
        return model.set('username', String(model.attributes.username).toLowerCase());
      }
    });
    this.on('saving', model => {
      if (model.hasChanged('password')) {
        return bcrypt.hash(model.attributes.password, 10)
          .then(hash => model.set('password', hash));
      }
    });
  },

  identities() {
    return this.hasMany('Identity', 'identity_user');
  }
});

In my createUser function, I'm trying to insert a new user and an identity in a single transaction.

function createUser(req, res, next) {
  const { email, username, password } = req.body;

  // some data validation

  return bookshelf.transaction((t) => {
    return new User({ username, password } ).save(null, { transacting: t })
      .then(user => new Identity({ type: 'email', value: email }).save({ userId: user.attributes.id }, { transacting: t }))
      .then(() => res.sendStatus(204))
      .catch(err => {
        // handle PostgreSQL unique violation error
      });
  });
}

When I run the server and attempt to register a new user, I get the following error:

insert into "identity" ("type", "user_id", "value") values ($1, $2, $3) returning "id" - column "user_id" of relation "identity" does not exist { error: insert into "identity" ("type", "user_id", "value") values ($1, $2, $3) returning "id" - column "user_id" of relation "identity" does not exist

This is a PostgreSQL error (code 42703 - Undefined Column), but everything seems to be set up correctly. I could really use another set of eyes on this. What am I missing?

Thanks in advance!

Upvotes: 1

Views: 2590

Answers (1)

flaviodesousa
flaviodesousa

Reputation: 7815

The error was because Bookshelf found 'userId' being inserted into Identity and inferred the column name to be 'user_id'. But for connecting to MxN relationships you need to use attach() instead:

function createUser(req, res, next) {
  const { email, username, password } = req.body;

  // some data validation

  return bookshelf.transaction((t) => {
    return new User({ username, password } )
      .save(null, { transacting: t })
      .then(user => new Identity({ type: 'email', value: email })
        .save(null, { transacting: t }))
      .then(identity => user.identities()
        .attach(identity.id, { transacting: t }));
  })
  .then(() => res.sendStatus(204))
  .catch(err => {
    // handle PostgreSQL unique violation error
  });
}

A variation that also seems to work for me is to directly attach models (e.g. .attach(identity, { transacting: t })), but this approach is not supported by the documentation.

Edit I've overlooked two errors on your model definitions, hasMany() only maps to a MxN relationship if using a through() call, that requires the join table to be mapped as a model. In the case above I think it is enough to use a simple 'belongsToMany()` call. So replace your relationship definitions by:

user() {
  return this.belongsToMany('User');
}

and

identities() {
  return this.belongsToMany('Identity');
}

Edit2: Moved the catch() to outside of the transaction scope, so an exception will automatically trigger a rollback.

Edit3: Flatter then() chain and non-db stuff removed from transaction scope.

Upvotes: 1

Related Questions