Welcome to Part 6 of this review of the Pluralsight course Building Web Applications with Node.js and Express 4.0 by Jonathan Mills.

Jonathan is a JavaScript and Node.js expert working mostly in the MEAN Stack with individuals and companies to help build their technical skills to cope with the constantly changing landscape of software development.

He is also an ASP.NET insider and an international speaker focusing on JavaScript both in the browser and on the server.

In this course we’re building a library application that’s going to keep track of a list of books. In this module we learn how to hook it up to a couple of different types of databases.

Also in this series:

Part 1 – Getting Started
Part 2 – Setting up Express
Part 3 – Setting Up Gulp
Part 4 – Templating Engines
Part 5 – Routing

MS SQL

Jonathan recommends the MS SQL package, saying that it has built in JSON support and does a lot of the hard work for us.

It is written by the software architect Patrik Šimek, who is the CTO of Integromat.

Jonathan also stresses that the documentation for this package is very good.

At the time of the video recording it had 20,049 downloads for the previous month. At the time that I’m writing this, there has been a massive 62,590 downloads in the last month.

And it is time to +1 the download stats:

npm install –save mssql

Patrik has written a quick example to get you started in no time with this. And Jonathan demonstrates this package on a “here’s one I made earlier” Azure database. However there is no need to use Azure if you don’t want to because you can use whatever form of SQL Server you like.

We are just doing some basic magic string type inline SQL queries so don’t expect any ORM wizardry here. The biggest plus point of this technology is the data that we get back is already JSON formatted for us.

We see that everything looks the same as before on our website, but when we look at the console we see our books output there as well.

As Jonathan concludes this lesson saying this package makes querying SQL Server super simple.

Prepared Statements

Next we want to pass in parameters to our query, and we see how to do this with a prepared statement. This ensures that all of our executions are executed on one connection.

There’s full documentation on Prepared Statements but it follows this pattern:

var ps = new sql.PreparedStatement();

ps.input(‘param‘, sql.Int);

ps.prepare(‘select @param as value‘, function(err) {

    // … error checks 

    ps.execute({param: 12345}, function(err, recordset) {

        // … error checks 

        ps.unprepare(function(err) {

            // … error checks 

        });

    });

});

The example that Jonathan shows selects a book by its id. The says that the function is huge and it’s driving him crazy.

Middleware

Express 4 allows middleware to be added using the .all function.

We take the big ugly block of database access code and move it into a function inside .all and do some refactoring to simplify it.

This refactoring allows us to put all of our error handling in the .all function

That’s how to work with SQL Server in a nutshell. Now let’s look at the most popular NoSQL option.

MongoDB

Mongo tends to be something that people talk about alongside Node, partly because the acronym “MEAN stack” has popularized it.

If you are new to MongoDB there’s a few different Pluralsight courses available for you to choose from:

• Introduction to MongoDB by Nuri Halperin
• Introduction to Mongoose for Node.js and MongoDB by Mark Scott
• Using MongoDB with ASP.NET MVC by Wes Higbee

You don’t really need any previous experience to follow along here though. We download it and install it.

MongoD is the server, and Mongo runs on and from the command line.

The most common error is that it needs a directory called /data/db but doesn’t have it.

Just by typing “mongo” into the command prompt we can see if we’re connected to our mongo server.

“show dbs” lists all of our mongo databases. And if you’ve only just installed it you won’t have any!

Admin Routes

Earlier in the course we wrote an array of books, and we’re going to add this into our Mongo database.

We see how to build adminRoutes, creating an addBooks route and hooking it up in app.js.

Adding Books

We first look at the official mongodb driver for Node.js. At the time of writing this package had over 2.2 million downloads last month, making it one of the most popular downloads on NPM.

npm install mongodb –save

Jonathan says a lot of what is covered in this lesson is also available in the documentation on this page and in fact the documentation much covers more than what can be described here.

A couple of things that can go wrong with the installation:

1. If you’re on a Mac and get errors, check that your Xcode is updated to the latest version
2. If you get a node-gyp error, do npm install -g node-gyp

In this lesson we see the insertMany function, and we add our books into MongoDB.

Testing Insert

We run gulp serve to start everything back up again.

There on link yet on the webpage but we can just manually change the URL to localhost:3000/admin/addBooks

We see our books as a JavaScript object.

Select Many

We are querying our MongoDB database to retrieve our books.

Also see Find All Documents

To show that there’s no smoke and mirrors, Jonathan deletes from the database and shows that there are no books to retrieve anymore.

Then we add the books back in again and voila.

We’re also told not to worry about the random face!

Select One

We we just want to select a single book.

Instead of using the find method, we use findOne so that returns just the first one that it finds!

At the end of this module Jonathan mentions that there is Mongoose, which is a sort of ORM and lets us do schemas etc.

Jonathan mentions Mark Scott’s course for that, and also Jonathan covers Mongoose in his RESTful Web Services with Node.js and Express.

Part 7 – Authentication is coming soon