CS 312 - Software Development

In this practical you will adapt a simple memory-backed server for Simplepedia to use a RDBMS backend. We will test our implementation against the SQLite file-based database, but by using the Knex library. The same code would also work with MySQL or PostgreSQL (Heroku, for instance, provides PostgreSQL).

Goals

• Gain familiarity with RDBMS-backed servers, Knex, SQLite, and the Objection ORM library.
• Implement model validations as an example of aspect-oriented programming (AOP).
• Employ code generators. Code generation (like with CRA) is very a helpful feature for working with complex libraries (that, for example, expect specific files in specific directories).

Prerequisites

1. Click through to the GitHub classroom assignment to create your private repository. Then clone that newly created repository to your local computer as you have done previously.

2. Install the package dependencies by running npm install inside the root directory of the newly cloned repository.

3. Make sure that you have installed sqlite3 as described in Getting Started.

Setup Knex

Knex is both a library and a command line tool (usable via npx). Install Knex and the database clients (for SQLite 3 and PostgreSQL):

npm install knex pg sqlite3

Initialize Knex with npx knex init. This will create a configuration file named ./knexfile.js.

Remove the descriptions of the staging and production servers. Edit the file to look like the snippet below. You are configuring a SQLite-based test environment (using a specific seed and database file), an SQLite-based development environment and a production configuration using PostgreSQL. You won't need to use the production configuration today, but you would use something similar when deploying to Heroku (e.g., for your project).

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

  test: {
    client: 'sqlite3',
    connection: {
      filename: './simplepedia-test.sqlite3',
    },
    useNullAsDefault: true,
    seeds: {
      directory: './seeds/test',
    },
  },

  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    ssl: true,
  },
};

Make a Knex Migration

Recall that migrations are how we automatically configure the database. Each migration has two parts, the "up" function which makes the desired changes to the database, e.g. create a table or add a column, and the "down" function which reverts those changes. For SQLite, running the migration will also create the database file if it doesn't exist.

Create a skeleton for a migration with npx knex migrate:make load-articles. This command will create a new folder called migrations, in which you will find your new migration file (note the date in the file name so the current state of the database schema can be tracked). You will need to fill in the exports.up (which should create your table) and exports.down (which should delete or "drop" the table) using the Knex schema builder API.

In the database, you want to create a table named Article with the four columns that correspond to the Article properties. Recall from the programming assignments that the four properties are: id, title, extract, and edited. The id field should be of type increments, an auto-incrementing integer, ensuring that each article has a unique id number (Knex automatically sets the increments column as the primary key). The remaining columns should be string or text as shown below. The difference between string and text is the intended size: string is typically of shorter, fixed, length, while text implies a longer, variable-length, string. The actual implementation will depend on the underlying database (e.g. SQLite has only variable length strings).

/* eslint-disable func-names */
/* eslint no-unused-vars: ["error", { "args": "none" }] */
exports.up = function (knex, Promise) {
  return knex.schema.createTable('Article', (table) => {
    table.increments('id');
    table.string('title');
    table.text('extract');
    table.string('edited');
  });
};

exports.down = function (knex, Promise) {
  return knex.schema.dropTableIfExists('Article');
};

When defining the schema, you can specify additional constraints on the columns (as chained methods). What additional constraints should exist on the articles? Can there be articles with identical titles? Can the edited time be null? Add additional constraints to your schema.

Once you have completed your migration, run any unperformed migrations (in this case just one) with the following command. Note we are explicitly specifying the environment (and thus the database that should be modified).

npx knex migrate:latest --env development

The migration should have created a simplepedia.sqlite3 file. To check out the current schema, open the database with the SQLite client application via sqlite3 simplepedia.sqlite3 and execute .schema at the interpreter prompt. You should see the schema for your new Article table (as well as a knex table that knex added to keep track of migrations -- don't tinker with this). To get of the sqlite3 command line, type Ctrl+D or .exit.

The sqlite3 client accepts two different types of commands. You can type raw SQL statements to interact with the database directly, or you have a small collection of system commands for working with the client. The system commands all start with a dot (like .schema). You can get a full list of these commands with .help.

Seeding the Article Data

"Seeding" is the process of pre-populating databases. It is worth noting that we frequently do not need to do this. If you are making a blog, online market, scheduling tool, etc, your content comes dynamically from your users. Even Simplepedia could conceptually be started "clean". However, seeding will frequently be used for testing.

Create the Seed File

Seed files are short scripts that populate the database. Create a skeleton seed file with:

npx knex seed:make load-articles --env development

The above command should have created a seeds/load-articles.js file. Modify that file to read in the article from the seed.json file and insert it into the database. For variety (and simplicity), we will use Node's synchronous file I/O interface this time. Update the code generated by Knex to look like the below:

/* eslint-disable func-names */
/* eslint no-unused-vars: ["error", { "args": "none" }] */
const fs = require('fs');

exports.seed = function (knex, Promise) {
  const contents = fs.readFileSync('seed.json');
  const data = JSON.parse(contents);

  // 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' },
      ]);
    });
};

Modify the exports.seed function by changing table_name to match your table name (i.e. Article).

If you followed the directions above, the column names in the database match the property names of the objects in the data Array. Those objects can be passed directly to the insert function (the database itself will generate the id property). However, we have too much data to just pass the array to insert, instead we need to use Knex's batchInsert utility to insert the article in blocks (in this case in blocks of 100 articles).

exports.seed = function (knex, Promise) {
  const contents = fs.readFileSync('./data/seed.json');
  const data = JSON.parse(contents);

  // Deletes ALL existing entries
  // Use batch insert because we have too many articles for simple insert
  return knex('Article')
    .del()
    .then(() => knex.batchInsert('Article', data, 100));
};

Run the Seed

Run the seed with npx knex seed:run --env development to populate your database. Now reopen your database with the SQLite3 command-line tool via sqlite3 simplepedia.sqlite3. Execute the following command in the SQLite interpreter to view the first 10 articles.

A note about SQLite grammar: the semi-colon is required, but the keywords in SQL are not case-sensitive. You will frequently see them in all caps, but the parser will accept them either way.

select * from Article limit 10;

While you have the interpreter open, experiment with a few SQL queries. How could you query for just the article with id of 11, or all the articles with titles that start with 'C' (and thus would be shown in the 'C' section of the Simplepedia client? Specifically what where conditions would you use in your query?

Update the Server

Now it is time to look at the server. If you look at the files in pages/api you will see that all of the important work is being done in a library file: lib/backend-utils.js. This is another instance of separation of concerns. There is no reason for the API routes to know where the data comes from. This makes it easy to swap out one solution for another (switching from a JSON backed solution to a database, say).

This will also make testing easier, since we can focus on the helper functions. This isn't to say that we shouldn't test that the routes do the right thing (that would be the server side of testing how it appears to the user), but it will certainly make this practical easier...

Connect to the database

Before we can use the database in your code, you need to initialize Knex. You can do this at the top of backend-utils.js.

Note the common pattern in which we use process.env.NODE_ENV to specify the environment (e.g. production, development, etc.) or default to development.

import knexConfig from '../../knexfile';
import knexInitializer from 'knex';

const knex = knexInitializer(
  knexConfig[process.env.TESTING || process.env.NODE_ENV || 'development']
);

Update getArticles()

In order to fetch all of the articles, you want to perform a select query with Knex. If you don't give the function any arguments, it is the equivalent of SELECT *, which returns all the columns. As with the queries described earlier, the Knex Query builder creates a Promise so we want to use await.

const rows = await knex('Article').select();

Return the rows from the function.

You can test that this function works by firing up the dev server and using curl or your browser to access http://localhost:3000/api/articles.

You should see a long array of articles.

Testing

The skeleton includes a simple test suite. You will need to update this test suite to work with Knex.

Export knex from backend-utils.js and add it to the import in backend-utils.test.js. This will allow us to use the knex instance in the tests.

The Jest test runner automatically sets NODE_ENV=test; thus Knex will use the test database you defined in knexfile.js (not the development database you just seeded). Note the simpler ./seeds/test/load-articles.js script. In test mode, the database will be seeded with a smaller collection. This is just one example of how being able to define different database environments is a very helpful feature of Knex.

As you should recall, we want our tests to be repeatable and to run independently. So, we need the database to be in the same state at the start of every test.

This is another advantage of using knex -- we can automate migration and seeding. We will "rollback" the latest migration (run the down function in the migration, which will delete the table), run the migration again to get a clean table, and finally re-seed it. We can do all of that in the beforeEach:

beforeEach(async () => {
  await knex.migrate.rollback();
  await knex.migrate.latest();
  await knex.seed.run();
});

Uncomment the lines near the end of the tests that make use of knex.

Now run the tests with npm test. The test getArticles function you implemented you should pass but most every other test should not.

Many of the failures are related to validation. You could successfully implement the Simplepedia API just using Knex. However, you would need to duplicate any validation code and use Knex's "low level" query interface. Instead, we will implement the server using the Objection.js ORM.

Switching to the Objection ORM

Creating the Model

Install the package npm install objection and create a models/Article.js file (in the project root) with the following:

const { Model } = require('objection');

class Article extends Model {
  // Table name is the only required property.
  static get tableName() {
    return 'Article';
  }

  // Objection.js assumes primary key is `id` by default

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['title'],

      properties: {
        id: { type: 'integer' },
        title: { type: 'string' },
        extract: { type: 'text' },
        edited: { type: 'string' },
      },
    };
  }
}

module.exports = Article;

The jsonSchema function specifies the properties of the model using the JSON Schema specification. With just this schema you can implement additional validation, such as numerical limits, etc., beyond what is possible with just SQL. You can further customize the validation methods if needed.

Using the Model to create Queries

To use your newly created Model, require the necessary modules in backend-utils.js and bind your Objection models to the Knex instance (so that the models can execute queries against the database).

import { Model } from 'objection';
import Article from '../../models/Article';

// Bind all Models to a knex instance.
Model.knex(knex);

Update your getArticles query to use your Model instead of Knex as shown below. The default query fetches all the articles from the database.

export async function getArticles() {
  const articles = await Article.query();

  return articles;
}

Your server should now successfully return all of the articles. Rerun your tests. The getArticles test should still be passing.

Now update the rest of the functions to work with your newly created Article model. The relevant Objection.js query methods will likely be insertAndFetch, deleteById and patch. These will all need to be called on a query (Article.query().insertAndFetch(...)) like shown above, not on the raw model (Article). All of your functions should be similar and have just a few lines of code. Those methods will require one or more arguments, e.g. the article you will be inserting into the database or the Id of the article you want to delete.

Rerun your tests and make sure that the basic operations of getting, adding, updating, and deleting all pass (the first one of each test suite).

Refining Your Validations

With all the routes in place, most, but not all, of the tests should pass. The remaining failing test should be testing what happens if an article is created without an extract field.

This test is just about data integrity, thus it is best addressed in the Model. Modify the Article model to include a suitable default (thus fixing that issue everywhere an Article instance might be created) making the test pass (i.e., add a default field to the extract property as described in the JSON Schema documentation).

We would like our server to be even more robust. At present, we allow any string for the edited time, but we should really only allow valid dates. Write a test for an invalid date, and then add to the validation to introduce the necessary constraint. Check out the JSON Schema documentation for strings for relevant examples.

Use Your New Server

When you are all done, you can use your newly implemented server with the Simplepedia client you implemented in assignment 4 (provided you did completed the basic version of assignment 4). You should be able to copy your components and pages over and set the server to point to / instead of basin.

Finishing Up

1. Add and commit your changes to Github. Make sure to add and commit the new files you created.
2. Submit your repository to Gradescope

Extra

Deploy to Heroku

By configuring a production PostgreSQL database, you can deploy your server to Heroku. Add the following properties to your package.json file:

"engines": {
  "node": "10.15.x"
},
"cacheDirectories": [
  "node_modules"
],

and add the following script entry in package.json, "heroku-postbuild": "npm install", to prepare your package for Heroku, then execute the following to create the Heroku application and provision the PostgreSQL database. The latter will define process.env.DATABASE_URL in the Heroku environment (which will be picked up by your Knex configuration).

heroku apps:create
heroku addons:create heroku-postgresql:hobby-dev

Commit all of your changes (don't forget to add the new files you created) and push your commit to Heroku to deploy your application with git push heroku master.

Once you have deployed you application, migrate and seed the database on Heroku with the following commands. heroku run executes the specified command in the context of your application on the Heroku servers.

heroku run 'npx knex migrate:latest'
heroku run 'npx knex seed:run'

Project note: This practical creates just the server (i.e. just the server directory in your project skeleton); your project will have both a client and server directories. When performing the migration and seeding on Heroku for your projects, you will need Heroku to run the above commands in the server directory, i.e.

heroku run 'cd server && npx knex migrate:latest'
heroku run 'cd server && npx knex seed:run'

You should then be able to use curl to test the now deployed API (using the address reported by Heroku), e.g.

curl https://stark-inlet-57532.herokuapp.com/api/articles

You can test your backend without pushing to Heroku. The database Heroku created for you is accessible from anywhere. Use heroku config to obtain the DATABASE_URL variable. Define that variable locally with ?ssl=true appended, e.g.

export DATABASE_URL="postgres://...?ssl=true"

then start your server in production mode, e.g. NODE_ENV=production npm start.

Just like you directly accessed your SQLite database via the sqlite3 client, you can do the same with your PostgreSQL database. Download and install one of the many PostgreSQL clients and use the DATABASE_URL from Heroku for the connection information.