Bookshelf table related example

index.js

'use strict';

const config = require('./knexfile')['development'];
const knex = require('knex')(config);
const bookshelf = require('bookshelf')(knex);

let User = bookshelf.Model.extend({
  tableName: 'users',
  messages: function() {
    return this.hasMany(Messages);
  }
});

let Messages = bookshelf.Model.extend({
  tableName: 'messages',
  tags: function() {
    return this.belongsToMany(Tag);
  }
});

let Tag = bookshelf.Model.extend({
  tableName: 'tags'
})

User.where('id', 1).fetch({withRelated: ['messages.tags']})
.then(function(user) {
  console.log(user.related('messages').toJSON());
  knex.destroy();
})
.catch(function(err) {
  console.error(err);
  knex.destroy();
});

knexfile.js

module.exports = {

  development: {
    client: 'pg',
    connection: {
      host: 'localhost',
      username: 'my_pg_username',
      password: 'my_pg_db_passwrod',
      database: 'bookshelf_test'
    },
    migrations: {
      directory: __dirname + '/migrations'
    },
    seeds: {
      directory: __dirname + '/seeds'
    }
  }

};

migrations_20170415124502_bookshelf_test.js

exports.up = function(knex, Promise) {
  return knex.schema.createTable('users', (table) => {
    table.increments().primary();
    table.string('username').unique().notNullable();
    table.string('email').notNullable();
  })
  .createTable('messages', (table) => {
    table.increments().primary();
    table.string('message').notNullable();
    table.integer('user_id').references('users.id');
  })
  .createTable('tags', (table) => {
    table.increments().primary();
    table.string('name').notNullable();
  })
  .createTable('messages_tags', (table) => {
    table.integer('message_id').references('messages.id');
    table.integer('tag_id').references('tags.id');
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('messages_tags')
    .dropTable('tags')
    .dropTable('messages')
    .dropTable('users');
};

seeds_overallseed.js

exports.seed = function(knex, Promise) {

  return knex('messages_tags').del()
    .then(() => { return knex('tags').del(); })
    .then(() => { return knex('messages').del(); })
    .then(() => { return knex('users').del(); })
    .then(() => {
      return knex('users').insert([
        {id: 1, username: 'Bob', email: '[email protected]'},
        {id: 2, username: 'Tom', email: '[email protected]'},
        {id: 3, username: 'Carl', email: '[email protected]'}
      ]);
    })
    .then(() => {
      return knex('messages').insert([
        {id: 1, message: 'Good message from bob',   user_id: 1  },
        {id: 2, message: 'Bad message from bob',    user_id: 1  },
        {id: 3, message: 'Soso message from bob',   user_id: 1  },
        {id: 4, message: 'Good message from tom',   user_id: 2  },
        {id: 5, message: 'Bad message from tom',    user_id: 2  },
        {id: 6, message: 'Soso message from tom',   user_id: 2  },
        {id: 7, message: 'Good message from carl',  user_id: 3  },
        {id: 8, message: 'Bad message from carl',   user_id: 3  },
        {id: 9, message: 'Soso message from carl',  user_id: 3  }
      ]);
    })
    .then(() => {
      return knex('tags').insert([
        {id: 1, name: 'programming'},
        {id: 2, name: 'javascript'},
        {id: 3, name: 'firmware'},
        {id: 4, name: 'cloud'},
        {id: 5, name: 'generic'}
      ]);
    })
    .then(() => {
        return knex('messages_tags').insert([
          {message_id: 1, tag_id: 1},
          {message_id: 2, tag_id: 5},
          {message_id: 3, tag_id: 1},
          {message_id: 3, tag_id: 3},
          {message_id: 4, tag_id: 2},
          {message_id: 5, tag_id: 2},
          {message_id: 6, tag_id: 5},
          {message_id: 7, tag_id: 1},
          {message_id: 8, tag_id: 5},
          {message_id: 9, tag_id: 1},
          {message_id: 9, tag_id: 4}
        ]);
    });

};