bookshelf-json-example

Example of DB with JSON object inside a column

This example shows how to put an entire JSON object into a single jsonb column in a relational database

Dependencies

We are going to need the following dependencies

• sqlite3 is the actual lightweight database. We could have used other databases, like Postgres if we wanted to
• knex converts logical functions into SQL queries
• bookshelf is an ORM on top of database to hide SQL
• bookshelf-json-columns automatically parses the JSON column from the database into objects

Connection

See how we make a connection to the database in connection.js, database.js and knexfile.js. Because we are going to store JSON columns, we should load the bookshelf-json-columns plugin.

'use strict'
var knex = require('./connection')
var bookshelf = require('bookshelf')(knex)
var jsonColumns = require('bookshelf-json-columns')
bookshelf.plugin(jsonColumns)
module.exports = bookshelf

Making a table

To create a table in our database, we need our first migration. I placed commands into scripts in package.json which call to Knex migrations

$ npm run migrate:make -- users

> bookshelf-json-example@1.0.0 migrate:make /git/bookshelf-json-example
> knex migrate:make "users"

Using environment: development
Created Migration: /git/bookshelf-json-example/migrations/20170809124258_users.js

It is a good idea to upgrade database before running, thus I call migrate:latest before start script runs.

{
  "scripts": {
    "prestart": "npm run migrate:latest",
    "start": "node index.js",
    "migrate:make": "knex migrate:make",
    "migrate:latest": "knex migrate:latest"
  }
}

For now the migration is empty. Let us create a table to store user information: name, email and some meta object. The meta column should use jsonb format for efficiency.

// migrations/20170809124258_users.js
// uses JSONB column "meta"
// http://knexjs.org/#Schema-jsonb
exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTable('users', function(table) {
      table.increments();
      table.string('email').unique();
      table.string('name');
      table.jsonb('meta');
      table.timestamps();
    })
  ]);
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTable('users')
};

Let us run the migration from command line

$ npm run migrate:latest

> bookshelf-json-example@1.0.0 migrate:latest /git/bookshelf-json-example
> knex migrate:latest

Using environment: development
Batch 1 run: 1 migrations
/git/bookshelf-json-example/migrations/20170809124258_users.js

This should create a file dev.sqlite3 that will be our database.

User model

Let us create a "user" model class that extends Bookshelf model. We must list all JSON columns in the table to turn on automatic serialization.

// user.js
const Bookshelf = require('./database')
const User = Bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: true,
}, {
  jsonColumns: ['meta']
})
module.exports = Bookshelf.model('User', User)

Let us insert a user into the database. I will use index.js for this

const User = require('./user')
const connection = require('./connection')
function exit() {
  console.log('closing DB')
  connection.destroy()
    .then(() => 'closed DB')
    .catch(console.error)
}
function addUser () {
  return User.forge({
    email: 'foo@gmail.com',
    name: 'Mr Foo',
    meta: {
      foo: 'bar'
    }
  }).save()
    .then(console.log, console.error)
}
addUser().then(exit)

Run this code once to insert a user

$ npm start

Fetching users

I need to fetch all users from the database. I could keep a wrapped item, but I would like to just fetch all properties, so I will convert models into pain objects.

function fetchEach (collection) {
  const Bluebird = require('bluebird')
  const list = []
  collection.each(item => {
    // and convert to JSON right away
    list.push(item.fetch().then(b => b.toJSON()))
  })
  return Bluebird.all(list)
}

function selectUsers () {
  return User.fetchAll()
  .then(fetchEach)
  .then(console.log, console.error)
}

selectUsers().then(exit)

Running the code shows that the JSON values were parsed back into an object

$ npm start
[ { id: 1,
    email: 'foo@gmail.com',
    name: 'Mr Foo',
    meta: { foo: 'bar' },
    created_at: 1502297699402,
    updated_at: 1502297699402 } ]
closing DB

Updating JSON property

To update a property deep inside the meta object, we need to fetch the specific user, get "meta" attribute, update its value, then save the user model.

function changeMetaFoo (newValue) {
  return User.where('email', 'foo@gmail.com')
    .fetch()
    .then(user => {
      const meta = user.get('meta')
      meta.foo = newValue
      user.set('meta', meta)
      return user.save()
    })
}
changeMetaFoo('new bar').then(exit)

After running the update, if we fetch the users again, we see updated JSON object

[ { id: 1,
    email: 'foo@gmail.com',
    name: 'Mr Foo',
    meta: { foo: 'new bar' },
    created_at: 1502297699402,
    updated_at: 1502297699402 } ]
closing DB