Aniket

Posted on Oct 7, 2019 • Updated on Jul 24, 2020

A quick starter for Bookshelf.js

#database #node #javascript #hacktoberfest

With the ongoing event of Hacktoberfest, I have found many projects (Like bookbrainz from metabrainz) using bookshelf.js as their ORM instead of something more mainstream like sequelize. Diversity is great, but the only problem is that although the official docs are comprehensive, other resources for learning bookshelf are scarce, outdated, or do not contain information about handling relations. The goal of writing this blog is to tackle these problems. So without any further ado.

Basic Setup

We'll be making a very basic CRUD API in express using bookshelf and later move on to relationships.

First installing the basic requirements

npm i express body-parser bookshelf knex pg

Bookshelf relies on Knex which is a database connection library, to well, connect to databases.
We also install pg as in this example, as I'm using postgres as my database.
If you're using some other database, you can swap pg for sqlite3/mysql/oracle.

Initializing index.js

First, some code for initializing express

var app = require("express")();
var bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({ extended: true }));
app.listen(PORT);

To connect the database to our server, we'll have to write the config for it

var config = {
  client: "pg",
  connection: {
    host: "*****",
    user: "*****",
    password:"****",
    database: "*****",
    ssl: "true",
    charset: "utf8"
  }
};

Using this config for initializing knex, which in turn initializes bookshelf.

var knex = require("knex")(config);
var bookshelf = require("bookshelf")(knex);

We now have successfully connected bookshelf with our database, and can now use it for making different requests.

Creating models

To interact with each table, we'll have to first make models for each table.
To keep things simple, we'll be interacting with a table named user.

var User = bookshelf.Model.extend({
  tableName: "user"
});

tableName is the minimum requirement for getting started, although we can add things like hasTimestamps (boolean) and custom functions to the model.

Now we can use this model to access the user table.

Making different routes

HTTP	CRUD	Route
GET	Read	/users/
GET	Read	/users/:email
POST	Create	/users/
PUT	Update	/users/:email
DELETE	Delete	/users/:email

GET /users/

app.get("/users", async (req, res) => {
  var users = await new User().fetchAll();
  res.json(users);
});

Response

[
    {
        "name": "john",
        "email": "john@example.com"
    },
    {
        "name": "bob",
        "email": "bob@example.com"
    }
]

GET /users/:email

app.get("/users/:email", async (req, res) => {
  var user = await User.where("email", email).fetch();
  res.json(user);
});

Response

{
    "name": "john",
    "email": "john@example.com"
}

POST /users/

app.post("/users", async (req, res) => {
  var user = await User.forge({
    name: req.query.name,
    email: req.query.email
  }).save();
  res.json(user);
});

Response

{
    "name": "doe",
    "email": "doe@example.com"
}

PUT /users/:email

app.put("/users/:email", async (req, res) => {
  var user = await User.where("email", req.params.email).save(
    { ...req.body },
    { patch: true }
  );
  res.json(user);
});

Response

{
    "name": "doe1",
    "email": "doe@example.com"
}

DELETE /users/:email

app.delete("/users/:email", async (req, res) => {
  var user = await User.where("email", req.params.email).destroy();
  res.json(user);
});

Response

{}

Relations

For explaining relations, we'll need another table, let it be a table named post.

Here the field email is a foreign key which refers to the email field of user table.

Let the data in post table initially be

[ 
 {  "id": "2",
    "title": "test2",
    "email": "john@example.com",
    "content": "lorem20" },
  { "id": "1",
    "title": "test",
    "email": "john@example.com",
    "content": "lorem10" 
  } 
]

To access the post table, we'll have to create a model for it. Since we also want to incorporate relations in it, we'll add a function named user to it.

var Post = bookshelf.Model.extend({
  tableName: "post",
  user() {
    return this.belongsTo(User, "email", "email");
  }
});

In the belongsTo(/belongsToMany/hasMany) method, the first argument is the Model with which our current model is associated with. The second and third arguments specify the fields through which the models are associated with (Foreign Key, Primary Key). In this case, both the fields are named email.

Now to access a post with its related data, we'll write

var posts = await Post.where("id", 1).fetch({ withRelated: ["user"] });
console.log(posts.related("user").toJSON());

Here "user" is the name of the function we made inside the Post model.

Response

{ 
   "name": "john",
   "email": "john@example.com"
}

Similarly, we can edit User model to reflect the relationship with post.

var User = bookshelf.Model.extend({
  tableName: "user",
  posts() {
    return this.hasMany(Post, "email", "email");
  }
});

To access posts related to a user

  var user = await User.where("email", "john@example.com").fetch({
    withRelated: ["posts"]
  });
  console.log(user.related("posts").toJSON());

Response

[ 
 {  "id": "2",
    "title": "test2",
    "email": "john@example.com",
    "content": "lorem20" },
  { "id": "1",
    "title": "test",
    "email": "john@example.com",
    "content": "lorem10" 
  } 
]

Repository

A fully working implementation of the application is available on my Github profile if anyone wants to refer.

projectescape / blogs-reference

A repository which contains the source complementing all the blogs I write

A crash course to Bookshelf.js

Code for this blog can be accessed here

Programmatic Navigation in React

Code for this blog can be accessed here

The Comprehensive Sequelize Cheatsheet

Markdown for this blog can be accessed here

View on GitHub

P.S.

This post officially marks the beginning of my blog writing endeavour to help me learn about things at a deeper level by writing blogs to help explain them to others(If that makes any sense!!). Any suggestions, edits, recommendations are more than welcome!