Set up Bookshelf.js for Node.js

Intro

I am going to walk through setting up a brand new Node.js project using Bookshelf and PostgreSQL. At the end, we will have a simple Node.js project that can insert and retrieve data from a database.

Here is what this post will cover

  • Set up a new Node.js project
  • Install necessary libraries to interface with PostgreSQL database
  • Set up process to create a database table
  • Use Bookshelf to
    • insert data
    • retrieve data

The code for this post can be found here.

Why Bookshelf.js?

Bookshelf.js is an Object Relational Mapping (ORM) for Node.js that allows developers to more easily interface with a relational database. It allows the dev to focus on higher level functions, rather than low level queries. It takes care of some basic attribute mapping to make data models simpler. It also allows for the same code to be used against different relational databases in case you want to change the underlying database.

I chose Bookshelf.js because it is well maintained, well documented, and it is built on Knex.js, which is a SQL query building framework. I can use Knex for queries that are not easily supported in Bookshelf.

Install necessary software

Node.js

I am using Node.js 4.3.2 because 4.3 is what is supported in AWS Lambda. If you don’t have node installed, follow these instructions.

PostgreSQL

Instructions on how to install PostgreSQL. I use Postico to easily interface with the PostgreSQL.

Before we go on, start the database.

Set up a new Node.js project

Set up the project directory and initialize the project. When you run npm init, it will prompt you to answer some questions, you can use the default values or set your own, at this point they don’t matter too much.

Install the node modules that we will need, the -save option will add the modules to the package.json file.

Here is what each module does

  • bookshelf – the ORM we will be using
  • knex – required by bookshelf, but in the future it may not be included automatically
  • pg – the postgresql module
  • dotenv – a utility that makes dealing with environment variables easy

Here is our current package.json

The dotenv module reads from a .env file in the project directory. This will set the environment variables. Right now we only need to set which environment we are in.

Hello (node) world

Let’s create a simple javascript file named index.js that will show that we have node working.

Now, make sure that it works

Create a database

Let’s create our database, and verify that it exists.

Create table in database

Set up a knexfile.js config file for Knex. This tells Knex how to connect to the database, notice there are entries for development and production. Right now we care most about development.

Now create a migration that will define our table. The resulting XXXXX_create_table.js file is just a blank template that we will fill out. Yours will have a different date and time in the name.

Later, we will be using the test jsonAPI here. To keep it simple, let’s just create a table that replicates the post resource, which has data like this:

Edit the XXXXX_create_table.js file to include the columns we want our table to have. Mine looks like this

This will create a table called ‘articles’.  We have an id field that will automatically increment when we insert new records. The remote_id is the id from the test service, we want to be sure that we don’t have duplicates, so we use the .unique() method. The table.timestamps(); command will add a created_at and updated_at column that are automatically populated when a record is added or updated. I have excluded the user_id that appears in the test API to keep the table simple.

The exports.down function defines what should happen if we rollback the migration, in general this should do the reverse of what the exports.up function does.

Now, run the migration with knex migrate:latest, and make sure the table exists.

Set up Bookshelf

Start with a bookshelf.js file that will allow us to use bookshelf in other files.

Create a model class for the articles table that we can use to interface with the table. By extending the bookshelf.Model, we get functionality that allows us to access the attributes of the table as methods on the Article variable.

Insert data into the table

Edit index.js to create a new entry in the articles table

Run index.js and make sure that it adds data to the table.

Retrieve data from the database

Now, modify index.js to also query the database for the article we just inserted. Below we have added the getInsertedArticle function, commented out the code that just did the insert, and added some new code to insert and then retrieve the record.

Done

That is it. There are many more things we can do with a database, like create relationships, do complex joins, etc., but this is a good starting point.

Here is a link to the code from this post.

Leave a Reply

Your email address will not be published. Required fields are marked *

one + three =