Step 4

Adding a database

Let's add a database connection. The database we will be using is called "sqlite3" because it is small and self contained in a single .sqlite3 file. There is a library called "sequelize" that will take care of interacting with the database.

Install the packages:

$ npm i -S sequelize sqlite3
$ npm i -D sequelize-cli

This section is a little complicated, so add the following code:

src/config/config.json

{
	"development": {
		"dialect": "sqlite",
		"storage": "./database.sqlite3"
	},
	"test": {
		"dialect": "sqlite",
		"storage": "./database.sqlite3"
	},
	"production": {
		"dialect": "sqlite",
		"storage": "./database.sqlite3"
	}
}

This will provide the sequelize the configuration it needs for our sqlite database.

.sequelizerc

const path = require('path');

module.exports = {
	config: path.resolve('./src/config/config.json'),
	'migrations-path': path.resolve('./src/migrations'),
	'seeders-path': path.resolve('./src/seeders'),
	'models-path': path.resolve('./src/models')
};

Since the default path sequelize uses is the root of the project, we need to provide it different paths to our files

src/migrations/create-book.js

'use strict';
module.exports = {
	up: (queryInterface, Sequelize) => {
		return queryInterface.createTable('Books', {
			id: {
				allowNull: false,
				autoIncrement: true,
				primaryKey: true,
				type: Sequelize.INTEGER
			},
			title: {
				type: Sequelize.STRING
			},
			author: {
				type: Sequelize.STRING
			},
			createdAt: {
				allowNull: false,
				type: Sequelize.DATE
			},
			updatedAt: {
				allowNull: false,
				type: Sequelize.DATE
			}
		});
	},
	down: (queryInterface, Sequelize) => {
		return queryInterface.dropTable('Books');
	}
};

This allows sequelize to create and delete the Books table with the correct columns

src/models/book.js

const sequelize = require('./sequelize');

const Book = sequelize.define(
	'Book',
	{
		title: sequelize.Sequelize.STRING,
		author: sequelize.Sequelize.STRING
	},
	{}
);

module.exports = Book;

This creates the Book model, which is an abstraction for interacting with the database

src/models/sequelize.js

const Sequelize = require('sequelize');
const config = require('../config/config.json');

const sequelize = new Sequelize(config.development);

module.exports = sequelize;

This creates an instance of sequelize and exports it

src/seeders/initialize.js

'use strict';

module.exports = {
	up: (queryInterface, Sequelize) => {
		/*
			Add altering commands here.
			Return a promise to correctly handle asynchronicity.

			Example:
			return queryInterface.bulkInsert('People', [{
				name: 'John Doe',
				isBetaMember: false
			}], {});
		*/

		return queryInterface.bulkInsert(
			'Books',
			[
				{
					title: 'Harry Potter',
					author: 'JK Rowling',
					createdAt: new Date(),
					updatedAt: new Date()
				},
				{
					title: 'Game of Thrones',
					author: 'George R.R. Martin',
					createdAt: new Date(),
					updatedAt: new Date()
				},
				{
					title: 'Othello',
					author: 'Shakespeare',
					createdAt: new Date(),
					updatedAt: new Date()
				}
			],
			{}
		);
	},

	down: (queryInterface, Sequelize) => {
		/*
			Add reverting commands here.
			Return a promise to correctly handle asynchronicity.

			Example:
			return queryInterface.bulkDelete('People', null, {});
		*/
		return queryInterface.bulkDelete('Books', null, {});
	}
};

This allows sequelize to populate our database with data

package.json

...
		"scripts": {
				"start": "node src/index.js",
				"dev": "nodemon -w src",
				"initialize": "sequelize --options-path=./src/config/options.js db:migrate && sequelize --options-path=./src/config/options.js db:seed:all",
				"test": "echo \"Error: no test specified\" && exit 1"
		},
...

Let's setup our database and populate it with data:

$ npm run initialize

Now that we have a database with data in it, let's use our Book model in our HTTP server

src/routes.books.js

const express = require('express');
const Book = require('../models/book');
const router = express.Router();

router.get('/', async (req, res) => {
	const books = await Book.findAll();
	res.send(books);
});

module.exports = router;

Here, we import our Book model and find all the books in our database and send them as a response. Notice that we have new syntax "async/await". This is because NodeJS is naturally asynchronous, so we have to wait until the call to the database is finished before we send the response, otherwise we would be sending an empty response. You can read more about NodeJS's concurrency model here: https://medium.com/codebuddies/getting-to-know-asynchronous-javascript-callbacks-promises-and-async-await-17e0673281ee