CS312 - Assignment six

Topics

• Get experience writing a Node server
• Learn how to work with Knex and SQLite

Simplepedia Server

For this assignment you will write the server side of Simplepedia. Since you are all engaged full time on your projects this will be done entirely in class. I've also decided to do this rather than finishing my lecture on SQLite, so the assignment will have a little bit of a tutorial flavor (and correspondingly be worth a little less).

Step one: Basic setup

Start by creating a new directory called simplepedia-server. Go inside that directory and start a new Node project with npm init. This will walk you through creating a new package.json file. You can accept most of the default values, though you may want to set your name. Also, when it asks for a root name, call it server.js instead of index.js.

Use npm to install sqlite, knex, express, cors, and body-parser. Remember to use --save so these dependancies are stored in the package.json file.

Step two: Setup knex

Knex is both a library and a command line tool, as I showed you in class. To use knex on the command line, you can type node_modules/.bin/knex. This is a bit of a pain, so we can install Knex globally as well using npm install knex -g. You should now have the knex command line tool in your path. You don't need to do this step, but I will write commands as if you did.

Initialize Knex with knex init. This will create a file called knexfile.js.

Remove the descriptions of the staging and production servers. Set the name of the database to ./simplepedia.db. You should also add the option useNullAsDefault to the development object and set it to true.Your file should look like this:

module.exports = {
  development: {
    client: 'sqlite3',
    connection: {
      filename: './simplepedia.db'
    },
    useNullAsDefault: true,
  }
};

Step three: Make a migration

The migration will be our way to automatically configure the database. For SQLite, running the migration will even create our database file if it doesn't exist already.

Type knex migrate:make simplepedia-articles. This will create a new folder called migrations, and you will find your new migration file inside. You need to fill in the exports.up (which should add your schema changes to the database) and exports.down (which should undo the changes).

In our database, we want a table with four fields ('Articles' would be a good name for it). The four fields are: _id, title, extract, edited. The _id field should be of type increments, which will mean that it auto increments, making sure we always have unique id numbers. The other three should all be strings.

Use the migration file we wrote for the SQL version of the film-explorer for reference.

Run the migration with knex migrate:latest.

You should now have a simplepedia.db file. To check the schema, start the sqlite client (sqlite3 simplepedia.db), and then type .schema. To leave the sqlite client, just type .exit,

Step four: Seed the database with the article collection

Type knex seed:make load-articles. This will create a seeds folder with a new seed file inside.

Background explanation

The seed file contains a basic template of what the system is expecting from you. It looks like this:

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('table_name').del()
    .then(function () {
      // Inserts seed entries
      return knex('table_name').insert([
        {id: 1, colName: 'rowValue1'},
        {id: 2, colName: 'rowValue2'},
        {id: 3, colName: 'rowValue3'}
      ]);
    });
};

There are a couple of things to explain in here.

The first thing is knex('table_name'). Knex is a query building library. Most of the commands will start with knex('table_name'), where table_name is the name of a table in the database. This tells Knex that we are querying this table. Almost everything in Knex is built on chaining, so you will use this structure a lot. You get the table and then you do something to it.

The command knex('table_name').del() will delete every row from the table_name table in the database.

The command knex('table_name').insert() will insert rows. The insert function requires an argument. That argument could be a simple object with properties that match the column names in the database, or, as is the case here, it could be an array of such objects.

The final thing to note here is that all of our Knex commands return Promises. So, they need to have a then to run. The insert command does not have a then, because the seed function is expected to return a Promise.

Loading the article data

Download the seed.js file.

You can retain much of the seed file.

You need to start by reading in the contents of the seed file.

const fs = require('fs');
const contents = fs.readFileSync('seed.json');
const data = JSON.parse(contents);

Change table_name to match your table name.

If you followed the directions above, the column names match the property names of the objects now stored in the array called data. This means we can pass them in directly to the insert() function. They don't need an _id field, because the database will provide that.

The simplest thing would be to just pass data to the insert function, as it is an array of appropriately configured objects. Sadly, if you do this, you will hit again SQLite's variable limit (which is caused by how Knex builds the actual SQL, which it does as one large statement).

Instead, use map on the data to create an array of insert statements, one per article. This will give you an array of promises, so you can just pass this to Promise.all to run all of them. Since the seed function needs to return a Promise, just put return in front of your Promise.all() command to return that.

Run and check

Run the seed with knex seed:run. This should populate your database.

Go back into your database using the command line tool.

Type select * from articles;, and, if you called your table 'articles', you should see all of the articles (and yes, you need the semicolon).

Step five: Write the server

Use our simple server and MongoDB based film explorer server as examples (obviously ignoring the MongDB parts). My sample client is configured to look for the server on port 3001.

Start by setting up the routes.

As a reminder, the server protocol looks like this:

In the case of both PUT and POST, the server will send back the new article as confirmation of success. For the DELETE, we will just send back the status code 200, meaning the operation was a success (response.sendStatus(200)).

Do not worry about the responses initially, just get the routes set up. You can send back status code 500 (server error) if you like, or you can send back nothing. Focus just on printing out enough information to the console so you are sure that the route is being activated and you have the information you need.

Use the simplepedia client to test your server (you are also welcome to use your own, just reconfigure the server variable to point to your new server).

Connect to the database

As with most Node libraries we have looked at, getting Knex ready to use consists of a require statement and some configuration. We have already created our configuration in the the knexfile.js file, so we will just re-use that:

const knexConfig = require('./knexfile');
const knex = require('knex')(knexConfig.development);

Implement the routes

Work through the routes one at a time. Each route should only take a couple of lines of code.

Start with the GET. In order to fetch all of the articles, you want to perform a select query. If you don't give the function any arguments, it is the equivalent of SELECT *, which is what we want. As with the queries described earlier, the Knex function creates a Promise, which you need to call with a then(). In the then() function, you will pass a handler function that takes one argument, which will be the array of all of the results. You can pass this directly back to the client with response.send().

The next route you tackle should be POST, which adds a new article. This uses the insert statement you have already used in the seed. The challenge is that you need to return the full article after the object has been inserted. The then() which you need to make the insert actually run can take a callback function. The argument to this function will be the id of the newly inserted object. You will use this to issue a second query to fetch the entire article. We only want the one article that has the matching id, so you will use the where function. The where function is basically performing a SELECT with a condition. The condition can be expressed as an object, using the property names as column names, and the values being the values you want matched. As an example, imagine that we have a database containing all kinds of Daleks. This would print out all of the records for the red Daleks:

knex('daleks').where({color:'red'}).then(results=>{console.log(results)});

You will be looking for a particular id (remembering that we are calling it _id to match our client), so you only need the first result (and there should only be one). Don't send the whole array, since the client is expecting a record, not an array of length 1.

The PUT, which updates an existing article shouldn't be much more difficult. Instead of using insert(), you will use update(), which takes in an object of the fields to change. In this instance, you can pass the whole article in and update everything.

But be careful! If you just copy your code from POST, and swap replace the insert with update, the code will run, but you will not get the results you want. The update will change every single article in the database. To target just the article we want to change, we are going to make use of Knex's chaining. If you look at the first example in the linked documentation, you will see that we first use where to narrow down the selection to just the article we want, and then we apply the update to that.

As you will see in the documentation, in SQLite, the response we get back after the update is just [1] (basically, it worked). To maintain our REST contract, you need to return the updated article, so perform another search just like you did for the PUT (though this time, you can use the original id).

One the three of those functions are working you can implement delete. Delete works just like update. We use where to narrow down the collection to just the records we want to delete, and then we call delete.

Make sure to test, tests, and test some more. If you make a mess of the database, you can always re-run the seed function to reset everything back to the pristine state.

Grading

To hand in your work, please make a copy of you working directory, rename the copy username_hw06, remove node_modules from it, and then zip it up. I would rather regenerate node_modules when I look at your work rather than having you submit the huge directory, so really, please don't include it.

You can then submit the zipped folder on Canvas.