Skip to content

Instantly share code, notes, and snippets.

@Zikoel
Created April 16, 2017 08:38
Show Gist options
  • Save Zikoel/cdeebd72260b649d9bbc4c04877db8ce to your computer and use it in GitHub Desktop.
Save Zikoel/cdeebd72260b649d9bbc4c04877db8ce to your computer and use it in GitHub Desktop.
Bookshelf table related example
'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();
});
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'
}
}
};
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');
};
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: 'bob@mail.com'},
{id: 2, username: 'Tom', email: 'tom@mail.com'},
{id: 3, username: 'Carl', email: 'carl@mail.com'}
]);
})
.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}
]);
});
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment