Building an Express API with Sequelize CLI and Unit Testing!

11 min readApr 16, 2020

Unit testing is a useful habit for software developers to adopt. For any project whose code might grow more complex, unit testing can help ensure that the application’s core functionality is maintained even as changes are made.

Even for a relatively small-scale Node.js app, it’s possible to include unit testing by using npm packages like Jest and SuperTest. In this walkthrough, we’ll build a basic API using Sequelize and Express, then add unit tests to ensure that our CRUD endpoints remain intact.

Creating the Sequelize application

We’ll move as quickly as possible through our initial setup in order to get to our unit tests— but we’ll include notes along the way for those who want to know more about using the Sequelize CLI with Express.

Let’s start by installing Postgres, Sequelize, and the Sequelize CLI in a new project folder we’ll call express-api-unit-testing:

mkdir express-api-unit-testing
cd express-api-unit-testing
git init
npm init -y && npm i sequelize pg && npm i --save-dev sequelize-cli

Let’s also add a .gitignore file to ease deployment later:

echo "
/node_modules
.DS_Store
.env" >> .gitignore

Next we will initialize a Sequelize project, then open the directory in our code editor:

npx sequelize-cli init
code .

To learn more about the Sequelize CLI commands below, see:
Getting Started with Sequelize CLI

Let’s configure our Sequelize project to work with a Postgres database. Find config.json in the /config directory and change the code to look like this:

{
 "development": {
 "database": "wishlist_api_development",
 "dialect": "postgres"
 },
 "test": {
 "database": "wishlist_api_test",
 "dialect": "postgres"
 },
 "production": {
 "use_env_variable": "DATABASE_URL",
 "dialect": "postgres",
 "dialectOptions": {
 "ssl": {
 "rejectUnauthorized": false
 }
 }
 }
}

Note: For production we use use_env_variable and DATABASE_URL. We are going to deploy this app to Heroku. Heroku is smart enough to replace DATABASE_URL with the production database, which we’ll see in action later.

Now we can tell Sequelize CLI to create the Postgres database:

npx sequelize-cli db:create

Defining models and adding seed data

Our demonstration app will associate users with items on a wishlist. Let’s start by creating a User model using the Sequelize CLI:

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string,password:string

Running model:generate automatically creates both a model file and a migration with the attributes we’ve specified. Now we can execute the migration to create the Users table in our database:

npx sequelize-cli db:migrate

Now let’s create a seed file:

npx sequelize-cli seed:generate --name users

You will see a new file in the/seeders directory. In that file, paste the code below, which will create entries in your database for three users:

module.exports = {
 up: (queryInterface, Sequelize) => {
 return queryInterface.bulkInsert('Users', [{
 firstName: 'Bruno',
 lastName: 'Doe',
 email: 'bruno@doe.com',
 password: '123456789',
 createdAt: new Date(),
 updatedAt: new Date()
 },
 {
 firstName: 'Emre',
 lastName: 'Smith',
 email: 'emre@smith.com',
 password: '123456789',
 createdAt: new Date(),
 updatedAt: new Date()
 },
 {
 firstName: 'John',
 lastName: 'Stone',
 email: 'john@stone.com',
 password: '123456789',
 createdAt: new Date(),
 updatedAt: new Date()
 }], {});
 }, down: (queryInterface, Sequelize) => {
 return queryInterface.bulkDelete('Users', null, {});
 }
};

In our API, each of these users can have many wishlist items. Let’s create an Item model to give these users something to wish for:

npx sequelize-cli model:generate --name Item --attributes title:string,link:string,userId:integer

Now we’ll create associations between the two models.

To learn more about creating Sequelize associations, see:
Creating Sequelize Associations with Sequelize CLI

First, find item.js in the /models subdirectory and replace the code with this:

module.exports = (sequelize, DataTypes) => {
 const Item = sequelize.define('Item', {
 title: DataTypes.STRING,
 link: DataTypes.STRING,
 userId: {
 type: DataTypes.INTEGER,
 references: {
 model: 'User',
 key: 'id',
 as: 'userId',
 }
 }
 }, {});
 Item.associate = function (models) {
 // associations can be defined here
 Item.belongsTo(models.User, {
 foreignKey: 'userId',
 onDelete: 'CASCADE'
 })
 };
 return Item;
};

Now find user.js in the the same directory and replace the code with this:

module.exports = (sequelize, DataTypes) => {
 const User = sequelize.define('User', {
 firstName: DataTypes.STRING,
 lastName: DataTypes.STRING,
 email: DataTypes.STRING,
 password: DataTypes.STRING
 }, {});
 User.associate = function(models) {
 // associations can be defined here
 User.hasMany(models.Item, {
 foreignKey: 'userId'
 })
 };
 return User;
};

Perform the migration to create the Items table in the Postgres database:

npx sequelize-cli db:migrate

Let’s create some items for our users to wish for:

npx sequelize-cli seed:generate --name items

You’ll see a new file in your /seeders subdirectory that ends with items.js. Change the code in that file to the following:

module.exports = {
 up: (queryInterface, Sequelize) => {
 return queryInterface.bulkInsert('Items', [{
 title: 'Moped',
 link: 'https://detroitmopedworks.com',
 userId: 1,
 createdAt: new Date(),
 updatedAt: new Date()
 },
 {
 title: 'iPad Mini',
 link: 'https://www.apple.com/ipad-mini',
 userId: 3,
 createdAt: new Date(),
 updatedAt: new Date()
 },
 {
 title: 'Electric Scooter',
 link: 'https://swagtron.com/electric-scooter',
 userId: 1,
 createdAt: new Date(),
 updatedAt: new Date()
 },
 {
 title: 'Monitor',
 link: 'https://www.asus.com/us/Monitors/MB168B',
 userId: 2,
 createdAt: new Date(),
 updatedAt: new Date()
 }], {});
 }, down: (queryInterface, Sequelize) => {
 return queryInterface.bulkDelete('Items', null, {});
 }
};

Now we’ll run both seed files to add our users and our wishlist items to the database:

npx sequelize-cli db:seed:all

Make sure the data exists on the database:

psql wishlist_api_development
SELECT * FROM "Users" JOIN "Items" ON "Users".id = "Items"."userId";

Setting up Express

Great, our Sequelize project is ready to roll. Now we can incorporate Express and set up routes to serve our data. First, let’s install Express, along with nodemon to monitor changes in our files and body-parser to handle information from user requests:

npm install express --save
npm install nodemon -D
npm install body-parser

Now let’s set up the architecture by creating two new directories and three new files:

mkdir routes controllers
touch server.js routes/index.js controllers/index.js

Now we’ll modify the package.json file to support nodemon. Also, we can facilitate development by creating a new command: npm run db:reset. We’ll set this up to drop the database, create the database, run the migrations, and seed anew whenever we need!

....
 "scripts": {
 "test": "echo \"Error: no test specified\" && exit 1",
 "start": "nodemon server.js",
 "db:reset": "npx sequelize-cli db:drop && npx sequelize-cli db:create && npx sequelize-cli db:migrate && npx sequelize-cli db:seed:all"
 },
....

Now let’s start building our Express app. Inside the server.js file, add the following:

const express = require('express');
const routes = require('./routes');
const bodyParser = require('body-parser')const PORT = process.env.PORT || 3000;const app = express();app.use(bodyParser.json())app.use('/api', routes);app.listen(PORT, () => console.log(`Listening on port: ${PORT}`))

Here we’ve created a basic Express server set to listen on port 3000. But rather than defining the routes in this file, we’ve added app.use('/api', routes) to refer any requests beginning with api to the index.js file in our /routes subdirectory.

To learn more about basic Express setup with Sequelize, see:
Sequelize CLI and Express

Express Router and controllers

We’ll start by setting up the root route. Open the ./routes/index.js file and add the following code:

const { Router } = require('express');
const controllers = require('../controllers');
const router = Router();router.get('/', (req, res) => res.send('This is root!'))module.exports = router

Test the route:

npm start

Now open the root endpoint in your browser: http://localhost:3000/api/

Good, our Express app works, but now we need to make it deliver data from Sequelize. We’ll do this by creating a controller to handle all of our logic — our pathways for creating new users and projects, updating users, etc.

To learn more about using controllers with Sequelize and Express Router, see:
Build an Express API with Sequelize and Express Router

Now open ./controllers/index.js and add the following:

const { User } = require('../models');const createUser = async (req, res) => {
 try {
 const user = await User.create(req.body);
 return res.status(201).json({
 user,
 });
 } catch (error) {
 return res.status(500).json({ error: error.message })
 }
}module.exports = {
 createUser
}

Here we’ve incorporated the User model we defined in Sequelize create a new database entry based on the information in the API request. To make this work, we’ll create a route on our server to connect the request with the controller:

In ./routes/index.js, add a new line after your “This is root!” route:

router.post('/users', controllers.createUser)

This directs POST requests at /api/users to the createUser function in our controller. To test it, you’ll need to use a REST client (like Postman or Insomnia). Use the POST method to send the following JSON body to http://localhost:3000/api/users:

{
 "firstName": "Jane",
 "lastName": "Smith",
 "email": "jane@smith.com",
 "password": "123456789"
}

So now we’ve used Router and a controller to deliver data from Sequelize to our API users. We can use the same strategy to connect any Sequelize query to an Express endpoint.

To learn more about customizing Sequelize queries, see: Using the Sequelize CLI and Querying

Let’s add controllers to perform four more tasks: get all users with their associated wishlist items, get a specific user and wishlist, update a user, and delete a user.

Replace what you currently have in ./controllers/index.js with the following:

const { User, Item } = require('../models');const createUser = async (req, res) => {
 try {
 const user = await User.create(req.body);
 return res.status(201).json({
 user,
 });
 } catch (error) {
 return res.status(500).json({ error: error.message })
 }
}const getAllUsers = async (req, res) => {
 try {
 const users = await User.findAll({
 include: [
 {
 model: Item
 }
 ]
 });
 return res.status(200).json({ users });
 } catch (error) {
 return res.status(500).send(error.message);
 }
}const getUserById = async (req, res) => {
 try {
 const { id } = req.params;
 const user = await User.findOne({
 where: { id: id },
 include: [
 {
 model: Item
 }
 ]
 });
 if (user) {
 return res.status(200).json({ user });
 }
 return res.status(404).send('User with the specified ID does not exists');
 } catch (error) {
 return res.status(500).send(error.message);
 }
}const updateUser = async (req, res) => {
 try {
 const { id } = req.params;
 const [updated] = await User.update(req.body, {
 where: { id: id }
 });
 if (updated) {
 const updatedUser = await User.findOne({ where: { id: id } });
 return res.status(200).json({ user: updatedUser });
 }
 throw new Error('User not found');
 } catch (error) {
 return res.status(500).send(error.message);
 }
};const deleteUser = async (req, res) => {
 try {
 const { id } = req.params;
 const deleted = await User.destroy({
 where: { id: id }
 });
 if (deleted) {
 return res.status(204).send("User deleted");
 }
 throw new Error("User not found");
 } catch (error) {
 return res.status(500).send(error.message);
 }
};module.exports = {
 createUser,
 getAllUsers,
 getUserById,
 updateUser,
 deleteUser
}

We’ll also set up each route to hit the correct controller. Change the /routes/index.js file to look like this:

const { Router } = require('express');
const controllers = require('../controllers')
const router = Router();router.get('/', (req, res) => res.send('This is root!'))router.post('/users', controllers.createUser)
router.get('/users', controllers.getAllUsers)
router.get('/users/:id', controllers.getUserById)
router.put('/users/:id', controllers.updateUser)
router.delete('/users/:id', controllers.deleteUser)module.exports = router;

Test some of these endpoints making a GET, POST, PUT, or DELETE request in Postman along with the appropriate request body for each endpoint. The body for a PUT request at http://localhost:3000/users/3, for instance, might look something like this:

{
 "firstName": "Johnny",
 "lastName": "Stone",
 "email": "john.e@stone.com"
}

If your manual testing goes well, it’s time to start building automated unit tests!

Logging

First, though, this is a good point to integrate better logging. Right now, if we check our terminal when we hit the http://localhost:3000/api/users/2 endpoint, we’ll see the raw SQL that was executed. For debugging purposes and overall better logging let’s install an Express middleware called morgan:

npm install morgan

Modify your server.js file to use Morgan (and also to add module.exports = app, which we will be using later in testing):

const express = require('express');
const bodyParser = require('body-parser');
const logger = require('morgan');const routes = require('./routes');const PORT = process.env.PORT || 3000;const app = express();
app.use(bodyParser.json())
app.use(logger('dev'))app.use('/api', routes);app.listen(PORT, () => console.log(`Listening on port: ${PORT}`))module.exports = app

Let’s see the result:

npm start
open http://localhost:3000/api/users/2

You should now see in your terminal something like this:

GET /api/users/2 304 104.273 ms

That’s morgan!

Unit Testing

Now we’re going to configure our Express JSON API for unit tests. Let’s install Jest — a delightful JavaScript testing framework with a focus on simplicity:

npm install jest --save-dev

We are also going to use SuperTest for testing our HTTP endpoints on our Express API:

npm install supertest --save-dev

We need to make two changes in package.json to configure Jest. First, under the "scripts" attribute, let’s edit our test command to use Jest:

...
"test": "jest",
...

We need Jest to ignore our ./node_modules folder, so we also need to add this snippet of code:

....
 "jest": {
 "testEnvironment": "node",
 "coveragePathIgnorePatterns": [
 "/node_modules/"
 ]
 },
....

Great! Let’s write a simple test to make sure our setup works. First, we’ll make a new directory for our tests, then we’ll make a file called base.test.js:

mkdir tests
touch tests/base.test.js

Now add the following code in base.test.js to create a test that will test whether 1 + 1 = 2:

describe('Initial Test', () => {
 it('should test that 1 + 1 === 2', () => {
 expect(1+1).toBe(2)
 })
})

Take a look at the syntax here. We pass strings to give the test a title and to describe what it does. Then, if the expression passed to expect() evaluates to the value within .toBe(), the test will pass. Let’s make sure our setup works:

npm test

OK, that’s a relief! Jest checked it out for us, and it turns out that1+1 does equal 2. Soon we’ll start putting Jest to practical use.

Setting up a test environment

First, we need to set up our test scripts to use a test database — it wouldn’t be a good idea to let our tests manipulate our real data. We’ll arrange this by using an npm package called cross-env:

npm install cross-env --save-dev

Cross-env lets us pass environment variables in npm scripts, which in this case we’ll use to specify a test environment. Let’s again configure the “scripts” section of our package.json to do this:

"test": "cross-env NODE_ENV=test jest --testTimeout=10000",
"pretest": "cross-env NODE_ENV=test npm run db:reset",
"db:create:test": "cross-env NODE_ENV=test npx sequelize-cli db:create",

Try it.

npm run db:create:test
npm test

The pretest script builds your test database afresh before running the tests — and using NODE_ENV=test lets our script do all that without altering our normal database. In addition to all that, your terminal should also show that our base test in Jest has passed.

Writing tests with Jest and SuperTest

Time to write our first route test. Let’s create the a test file for routes:

touch tests/routes.test.js

We are going to write this using the Jest framework and call the HTTP methods using SuperTest.

Let’s test the /api/users endpoint. When we do a GET request to that endpoint we should get back a list of all users in the database, right? Open up tests/routes.test.js and add this code:

const request = require('supertest')
const app = require('../server.js')describe('User API', () => {
 it('should show all users', async () => {
 const res = await request(app).get('/api/users')
 expect(res.statusCode).toEqual(200)
 expect(res.body).toHaveProperty('users')
 }),
})

Test it!

npm test

You should see two passing tests now in two test suites.

Next, we’ll add another test to the same User API test suite, this one for the /api/users/3 endpoint. At this endpoint, a GET request should return a specific user from the database. In tests/routes.test.js, add the following just below the previous test:

 it('should show a user', async () => {
 const res = await request(app).get('/api/users/3')
 expect(res.statusCode).toEqual(200)
 expect(res.body).toHaveProperty('user')
 }),

Test it!

npm test

We can write more tests for other aspects of our API. The code below includes tests for creating, updating and deleting users in our app:

const request = require('supertest')
const app = require('../server.js')describe('User API', () => {
 it('should show all users', async () => {
 const res = await request(app).get('/api/users')
 expect(res.statusCode).toEqual(200)
 expect(res.body).toHaveProperty('users')
 }), it('should show a user', async () => {
 const res = await request(app).get('/api/users/3')
 expect(res.statusCode).toEqual(200)
 expect(res.body).toHaveProperty('user')
 }), it('should create a new user', async () => {
 const res = await request(app)
 .post('/api/users')
 .send({
 firstName: 'Bob',
 lastName: 'Doe',
 email: 'bob@doe.com',
 password: '12345678'
 })
 expect(res.statusCode).toEqual(201)
 expect(res.body).toHaveProperty('user')
 }), it('should update a user', async () => {
 const res = await request(app)
 .put('/api/users/3')
 .send({
 firstName: 'Bob',
 lastName: 'Smith',
 email: 'bob@doe.com',
 password: 'abc123'
 })
 expect(res.statusCode).toEqual(200)
 expect(res.body).toHaveProperty('user')
 }), it('should delete a user', async () => {
 const res = await request(app)
 .del('/api/users/3')
 expect(res.statusCode).toEqual(204)
 })
})

You’ll see that the new tests use the .post(), .put(), and .del() methods, and that the body of a test request can be defined within .send().

Each of our example tests uses Jest’s .toEqual() and/or .toHaveProperty() methods. To see other Jest “matchers,” check out the Jest documentation.

Run the tests: