/node-sql-template

A repository containing sample code for setting up a Node.js server that fetches data from a SQL database.

Primary LanguageJavaScript

Node.js SQL Template

This repository contains sample code for setting up a Node.js server that uses Express.js to handle HTTP requests and gets data from a SQL backend (postgres in this example).

Setup

Setting up the Postgres Docker Container

If you want to manually install Postgres, go to "Manually Install Postgres".

Otherwise, look at the easy-postgres-docker repository

Setting up the Project

Clone the repository and install the project dependencies

  • You might want to delete package-lock.json before running npm install so your dependencies are up-to-date.
git clone github.com/build-umass/node-sql-template  
cd node-sql-template
npm install

Copy the .env.sample file to a .env file and fill in the missing values

  • When starting up, the server will read the database credentials/other info from the .env file.

Setup the initial database state:

npm run knex seed:run
npm run knex migrate:latest

Start the server:

npm run start

Managing the Database

This template uses knex.js for interacting with the database. knex abstracts over SQL, so instead of writing

select `title`, `author`, `year` from `books`

you write

knex.select('title', 'author', 'year').from('books')

knex will prevent issues such as SQL injection, and it will automatically convert query results into Javascript objects/allow you to use Javascript objects in your queries.

Seeding/Migration

Read the source code/comments.
knexfile.js is used in 2 ways:

  • by "database.js" to connect to the database at runtime. "database.js" exports a "knex" object which is used by the source code to query the database at runtime.
  • by the knex command line tool (CLI) to run migrations/seeds
    • the knex CLI is not accessible from your terminal
    • Run it by doing "npm run knex [your arguments]"
      • npm will find the location of the knex CLI in your node_modules folder
      • "npm run knex" is a script in "package.json"
        • It passes in the location of knexfile.js, if you change the location of "knexfile.js", update the script

knex has the concept of "seeds" and "migrations"

  • "seeds" initialize the database/populate it with some starting data
  • "migrations" allow you to update your database schema/revert database changes

General Understanding

For each request, several factors must be considered:

  • Validation of input
    • Do the relevant url query parameters exist?
    • Is the body correctly formatted?
      • Example: If the request handler parses the request body into JSON, the request body text must be JSON.
  • Error handling

    • Each call to knex.js can result in 3 states:

      • success
      • An execution failure (invalid table name/column name)
      • A fatal failure (the database itself crashed/connection to the database was lost)

      Rather than handling the last result everytime you call knex.js, you can let the exception be thrown and have error handling code (middleware) deal with it.

Links

Misc

Manually Install Postgres

These are some helpful tutorials: