How to use bookshelf - 10 common examples

if no additional arguments were provided, use the postgres connection.
It's messy, but so it goes...
 */
if (process.env.testing) {
  console.log('Connecting to SQLite3 testing database...');
  config = require('../../test/config/db');
} else if (process.argv[2] === 'sqlite3') {
  console.log('Connecting to SQLite3 development database...');
  config = require('../../config/db_sqlite');
  newDB = !fs.existsSync(config.database.connection.filename);
} else {
  console.log('Connecting to PostgreSQL database...');
  config = require('../../config/db_pg');
}

const db = Bookshelf.initialize(config.database);

if (!process.env.testing) {

  /*
    When the application starts, it will check to see which migrations last ran
    and run any newer migrations. This will also load some base data if this is a
    new database.
   */
  db.knex.migrate.latest(config).then(function () {

    // if running sqlite3 in development on initial load, insert test data
    if(config.database.client === "sqlite3" && newDB) {

      var data = {
        submissions : require('../../test/fixtures/submissions'),
        submission_tags : require('../../test/fixtures/submission_tags'),

/**
 * Created by greg on 27/04/15.
 */

import Knex from 'knex';
import Bookshelf from 'bookshelf';
import Config from '../../knexfile';

const Orm = new Bookshelf(new Knex(Config.development));

// enable Bookshelf plugins
Orm.plugin('registry');
Orm.plugin('virtuals');
Orm.plugin('visibility');

export default Orm;

import dotenv from "dotenv";

dotenv.config();

const knex = _knex({
    client: "mysql",
    connection: {
        host: "127.0.0.1",
        database: process.env.DB,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD
    },
    debug: true
});

const Bookshelf = _bookshelf(knex);

// to resolve circular dependencies with relations
Bookshelf.plugin('registry');

export default Bookshelf;

var ghostBookshelf,
    Bookshelf = require('bookshelf'),
    when      = require('when'),
    moment    = require('moment'),
    _         = require('underscore'),
    uuid      = require('node-uuid'),
    config    = require('../config'),
    Validator = require('validator').Validator,
    unidecode = require('unidecode'),
    sanitize  = require('validator').sanitize;

// Initializes a new Bookshelf instance, for reference elsewhere in Ghost.
ghostBookshelf = Bookshelf.ghost = Bookshelf.initialize(config().database);
ghostBookshelf.client = config().database.client;

ghostBookshelf.validator = new Validator();

// The Base Model which other Ghost objects will inherit from,
// including some convenience functions as static properties on the model.
ghostBookshelf.Model = ghostBookshelf.Model.extend({

    hasTimestamps: true,

    defaults: function () {
        return {
            uuid: uuid.v4()
        };
    },

var Bookshelf = require('bookshelf');
var path = require('path');
// var SQLITE3 = require('../../creds/SQLITE3');

var db = Bookshelf.initialize({
  client: 'sqlite3',
  connection: {
    host: process.env.SQLITE3_HOST,
    user: process.env.SQLITE3_USER,
    password: process.env.SQLITE3_PASSWORD,
    database: process.env.SQLITE3_DATABASE,
    charset: 'utf8',
    filename: path.join(__dirname, 'storage/' + process.env.SQLITE3_DATABASE + '.sqlite')
  }
});

db.knex.schema.hasTable('users').then(function(exists) {
  if (!exists) {
    db.knex.schema.createTable('users', function(user) {
      user.increments('id').primary();
      user.string('username',255).unique();

var Bookshelf = require('bookshelf'),
    when      = require('when'),
    moment    = require('moment'),
    _         = require('lodash'),
    uuid      = require('node-uuid'),
    config    = require('../config'),
    unidecode = require('unidecode'),
    sanitize  = require('validator').sanitize,
    schema    = require('../data/schema'),
    validation     = require('../data/validation'),

    ghostBookshelf;

// Initializes a new Bookshelf instance, for reference elsewhere in Ghost.
ghostBookshelf = Bookshelf.ghost = Bookshelf.initialize(config().database);
ghostBookshelf.client = config().database.client;


// The Base Model which other Ghost objects will inherit from,
// including some convenience functions as static properties on the model.
ghostBookshelf.Model = ghostBookshelf.Model.extend({

    hasTimestamps: true,

    // get permitted attributs from schema.js
    permittedAttributes: function () {
        return _.keys(schema.tables[this.tableName]);
    },

    defaults: function () {
        return {

.then(function() {
    // inject model into promise chain as return value
    return bookshelf.Model.extend({
      tableName: 'genres'
    });
  })
  .catch(function(e) {

(function () {
    "use strict";

    // We should just be able to require bookshelf and have it reference
    // the `Knex` instance bootstraped at the app initialization.
    var Bookshelf = require('bookshelf'),
        Showdown = require('showdown'),
        converter = new Showdown.converter(),

        Post,
        Posts,
        User,
        Setting,
        Settings;

    Post = Bookshelf.Model.extend({

        tableName: 'posts',

        hasTimestamps: true,

        initialize: function () {
            this.on('creating', this.creating, this);
            this.on('saving', this.saving, this);
        },

        saving: function () {
            if (!this.get('title')) {
                throw new Error('Post title cannot be blank');
            }
            this.set('content_html', converter.makeHtml(this.get('content')));

describe('bookshelf-mask', () => {
  const repository = bookshelf(knex(knexfile));

  repository.plugin(['virtuals', mask]);

  const { Author, Post } = fixtures(repository);

  before(async () => {
    await recreateTables(repository);
  });

  afterEach(async () => {
    await clearTables(repository);
  });

  after(async () => {
    await dropTables(repository);
  });

describe('with SQLite client', () => {
  const repository = bookshelf(knex(knexfile));
  const CollectionPrototype = repository.Collection.prototype;
  const ModelPrototype = repository.Model.prototype;

  repository.plugin(jsonColumns);

  before(async () => {
    await recreateTable(repository);
  });

  afterEach(async () => {
    await clearTable(repository);
  });

  after(async () => {
    await dropTable(repository);
  });