Can You Communicate With Databases Without Learning SQL | Part #3

Bookshelf.js - A NodeJS ORM with Knex Query Builder

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. It features both Promise-based and traditional callback interfaces, transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

As per the Bookshelf.js Official Documentation

For a better understanding of BookshelfJS in action, we’re going to use the concept of an online shopping system. For simplicity, we will just be playing with the following 2 entities, i) Customer, ii) Order. Where each customer can place one or more orders and each order belongs to a single customer.

1) Installation

You can use npm to install Bookshelf.js. Now, Bookshelf uses knex SQL Query Builder in the background as its database access layer, so you also need to install that.

$ npm install bookshelf knex

You'll also have to manually install one of the following drivers for your database of choice:

$ npm install pg
$ npm install mysql
$ npm install sqlite3

All the examples present in this article will be based on the MySQL driver, i.e., mysql2. Also, the database is running on my local machine.

2) Establish Database Connection

Place the following code in your ./util/database.js

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_username', //replace with your username
    password : 'your_password', //replace with your password
    database : 'your_db_name', //replace with your db name
    charset  : 'utf8'
  }
});

bookshelf = require('bookshelf')(knex);

// Bookshelf supported plugins.
bookshelf.plugin('registry');
bookshelf.plugin('visibility'); 

module.exports = bookshelf;

Remember that with Bookshelf you need to explicitly add plugins to your object as they are not a part of the core Bookshelf module. So, the registry plugin is used to access the .model() method, and the visibility plugin is used to support different visibility modes for files.

3) Define Models and Define Relationship

A model is an abstraction that represents a table in your database. In Bookshelf, it is created by accessing the .model() method on the bookshelf object.

Define the Customer model by placing the following code in your ./models/customer.js

const bookshelf = require('../util/database');

const Customer = bookshelf.model('Customer', {
  tableName: 'customers',
  orders() {
    return this.hasMany("Order")
  }
});

module.exports = Customer;

Now, if you’ve used Sequelize or some other SQL-based ORMs in the past you must’ve noticed something really different and odd. Did you? So, we didn’t define any fields/columns in the definition of our Customer model, so Bookshelf doesn't know anything about the contents of our database tables. This is because with Bookshelf you have to manually create your database, your tables, and the columns in your table, unlike Sequelize.

Now, in our case, each customer has an id, a name, and an email. This represents the 3 fields/columns of the Customer table. So, the Customer model is associated with the Order model by a hasMany relation. One customer can have many orders.

Define the Order model by placing the following code in your ./models/order.js

const bookshelf = require('../util/database');

const Order = bookshelf.model('Order', {
  tableName: 'orders'
})

module.exports = Order;

Each order will have an id and a total purchase price. This represents the 2 fields/columns of the Order table.

4) Writing Queries

In the following code snippet, we perform the following actions:

i) Insert a new customer.

ii) Insert 2 new orders for this customer using the customerId foreign key.

iii) Select/Fetch all the orders for the current customer.

const Customer = require("./models/customer");
const Order = require("./models/order");

let customerId = null;
Customer.forge({ name: "Joey Tribianni", email: "jt@gmail.com" })
  .save()
  .then((customer) => {
    customerId = customer.id;
    return Order.forge({ total: 45, customer_id: customerId }).save();
  })
  .then((order) => {
    return Order.forge({ total: 55, customer_id: customerId }).save();
  })
  .then((order) => {
    return Customer.where({ id: customerId }).fetch({
        withRelated: ['orders'], require: true
    });
  })
  .then(result => {
      console.clear();
      console.log("Result is : ", result.toJSON());
  })
  .catch((err) => {
    console.log(err);
  });

So, that’s it about this useful Node.js ORM. I hope the information provided in this article provides value to you and helps you simplify and optimize your database manipulations.

Additional Resources: