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.
[~]$ node -v
v4.3.2
PostgreSQL
Instructions on how to install PostgreSQL. I use Postico to easily interface with the PostgreSQL.
Before we go on, start the database.
[~]$ postgres -D /usr/local/var/postgres
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.
[~]$ mkdir aws_walkthrough
[~]$ cd aws_walkthrough
[~/aws_walkthrough]$ npm init
Install the node modules that we will need, the -save option will add the modules to the package.json file.
[~/aws_walkthrough]$ npm install knex bookshelf pg dotenv -save
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
{
"name": "aws_walkthrough",
"version": "0.1.0",
"description": "A sample project using AWS",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "Jake Sparling",
"repository" : {
"type" : "git",
"url" : "https://github.com/jsparling/aws_walkthrough"
},
"license": "ISC",
"dependencies": {
"bookshelf": "^0.10.3",
"dotenv": "^4.0.0",
"knex": "^0.12.7",
"pg": "^6.1.2"
}
}
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.
NODE_ENV=development
Hello (node) world
Let’s create a simple javascript file named index.js that will show that we have node working.
'use strict'
console.log("node is working")
Now, make sure that it works
[~/aws_walkthrough]$ node index.js
node is working
[~/aws_walkthrough]$
Create a database
Let’s create our database, and verify that it exists.
[~/aws_walkthrough] $ createdb aws_walkthrough
#verify that the db exists, use '\q' to exit psql
[~/aws_walkthrough]$ psql aws_walkthrough
psql (9.4.0)
Type "help" for help.
aws_walkthrough=#
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.
// Update with your config settings.
require('dotenv').config()
module.exports = {
development: {
client: 'pg',
connection: {
database: 'aws_walkthrough'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
production: {
client: 'pg',
connection: {
host: process.env.DB_HOST,
database: 'aws_walkthrough',
user: process.env.DB_USER,
password: process.env.DB_PASS
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
};
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.
[~/aws_walkthrough] $ knex migrate:make create_table
Using environment: development
Created Migration: /Users/jake/aws_walkthrough/migrations/20170219092242_create_table.js
[~/aws_walkthrough] $
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:
{
"userId": 1,
"id": 1,
"title": "sunt aut facere repellat providen",
"body": "quia et suscipit suscipit recusandae"
}
Edit the XXXXX_create_table.js file to include the columns we want our table to have. Mine looks like this
exports.up = function(knex, Promise) {
return Promise.all([
knex.schema.createTable('articles', function(table) {
table.increments('id').primary();
table.integer('remote_id').unique();
table.text('title');
table.text('body');
table.timestamps();
})
])
};
exports.down = function(knex, Promise) {
return Promise.all([
knex.schema.dropTable('articles')
])
};
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.
~/aws_walkthrough] $ knex migrate:latest
Using environment: development
Batch 1 run: 1 migrations
/Users/jake/aws_walkthrough/migrations/20170219092242_create_table.js
[~/aws_walkthrough] $
[~/aws_walkthrough] $ psql aws_walkthrough
psql (9.4.0)
Type "help" for help.
aws_walkthrough=# \d articles
Table "public.articles"
Column | Type | Modifiers
------------+--------------------------+-------------------------------------------------------
id | integer | not null default nextval('articles_id_seq'::regclass)
remote_id | integer |
title | text |
body | text |
created_at | timestamp with time zone |
updated_at | timestamp with time zone |
Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"articles_remote_id_unique" UNIQUE CONSTRAINT, btree (remote_id)
aws_walkthrough=#
Set up Bookshelf
Start with a bookshelf.js file that will allow us to use bookshelf in other files.
'use strict'
// Set up knex using the config file for the environment
var knex = require('knex')(require('./knexfile')[process.env.NODE_ENV])
// set up bookshelf using the knex setup we created above
var bookshelf = require('bookshelf')(knex)
// make sure bookshelf is available when importing this file
module.exports = bookshelf
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.
'use strict'
var bookshelf = require('./../bookshelf')
// create the Article model, it will include all of the attributes of the table.
// the hasTimestamps: true command will automatically populate the created_at and updated_at columns
var Article = bookshelf.Model.extend({
tableName: 'articles',
hasTimestamps: true
})
module.exports = Article
Insert data into the table
Edit index.js to create a new entry in the articles table
'use strict'
var bookshelf = require('./bookshelf')
var Article = require("./models/article")
var insertArticle = (callback) =>{
// create a new entry in articles database
new Article({
title: "Sample title",
body: "Sample body"
}).save()
.then(function(saved) {
console.log(saved)
const insertedId = saved.attributes.id
callback(insertedId)
})
}
// insert the article, and when we are done, destroy connection and get the inserted article
insertArticle(function(id){
bookshelf.knex.destroy()
console.log("inserted article with id: " + id)
})
Run index.js and make sure that it adds data to the table.
[~/aws_walkthrough] $ node index.js
ModelBase {
attributes:
{ title: 'Sample title',
body: 'Sample body',
updated_at: Sun Feb 19 2017 10:34:22 GMT-0800 (PST),
created_at: Sun Feb 19 2017 10:34:22 GMT-0800 (PST),
id: 6 },
_previousAttributes:
{ title: 'Sample title',
body: 'Sample body',
updated_at: Sun Feb 19 2017 10:34:22 GMT-0800 (PST),
created_at: Sun Feb 19 2017 10:34:22 GMT-0800 (PST),
id: 1 },
changed: {},
relations: {},
cid: 'c1',
_knex: null,
id: 1 }
inserted article with id: 1
[~/aws_walkthrough] $ psql aws_walkthrough
psql (9.4.0)
Type "help" for help.
aws_walkthrough=# select * from articles;
id | remote_id | title | body | created_at | updated_at
----+-----------+--------------+-------------+----------------------------+----------------------------
1 | | Sample title | Sample body | 2017-02-19 10:34:22.967-08 | 2017-02-19 10:34:22.967-08
(1 row)
aws_walkthrough=#
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.
'use strict'
var bookshelf = require('./bookshelf')
var Article = require("./models/article")
var getInsertedArticle = (id, callback) =>{
console.log("\nNow get the article from the db\n")
Article.where('id', id).fetch().then(function(article) {
callback(article)
})
}
var insertArticle = (callback) =>{
// create a new entry in articles database
new Article({
title: "Sample title",
body: "Sample body"
}).save()
.then(function(saved) {
console.log(saved)
const insertedId = saved.attributes.id
callback(insertedId)
})
}
// insert the article, and when we are done, destroy connection and get the inserted article
// insertArticle(function(id){
// bookshelf.knex.destroy()
// console.log("inserted article with id: " + id)
// })
insertArticle(function(id) {
getInsertedArticle(id, function(article) {
bookshelf.knex.destroy()
console.log(article)
})
})
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.
