Using PostgreSQL and Sequelize to persist our data

It’s been quite a journey since the part 1 of this series, In this tutorial, we will persist our data with a database using Postgres and Sequelize. Sequelize is an ORM for Nodejs, it supports PostgreSQL, MySQL, SQLite and MSSQL. the source code for this tutorial can be found here

Configure Sequelize

To get started we’ll start by installing some dependencies. First, we'll installsequelize-cli`, sequelize-cli creates a command line interface for us to run Sequelize related commands in our app.

$ npm install -save sequelize-cli

To configure Sequelize for our project we are going to create a .sequelizerc file in the root of our app

$ touch .sequelizerc

inside the file type the following

const path = require('path');

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

The sequelizerc file is going to bootstrap our application with the above paths, the config.json file is going to contain the configuration for our application, the models path will contain our application models, migrations will contain the different migrations for our application. the migration files are used to create our app’s table using the models that we create. the models contain the design for our application tables

The next thing to do is to install Sequelize and some other dependencies,

$ npm install --save sequelize pg pg-hstore

pg is a PostgreSQL client for Node.js, pg is responsible for creating our application’s database connection. pg-hstore is a node package for serializing and deserializing JSON data to hstore format. read more about pg-hstore here.

Next run

$ sequelize init

this is going to bootstrap our application with the path specified in .sequelizerc file. After running it you should have a config folder which contains a config.json file, a model folder which contains some configurations for the models and a migration folder.

let’s go through the index.js file in the models folder, some required node modules were imported, the line with config.use_env_variable checks if any environment variable is set, if it is set then we use the settings for that environment variable otherwise we use the alternative settings provided for that environment. the process.env gives access to the node env, but this will not work unless we have a dotenv package installed, this will give us access to the node environment from our application.

Configure dotenv

$ npm install --save dotenv

after installation, require the dotenv file as early as possible in your application. I’ll require mine at the top of index.js file in the models folder

require('dotenv').config()

we required the dotenv module and call the method config on it. the next thing we need to do is to create .env file, this will contain all our node environment variables. At the root of your app create a .env file

$ touch .env

now to set up Postgres for our application we can either use the settings provided in the config.json file, we’ll just replace the required information with the information we Setup when we installed Postgres or we can use an environment variable. in the development section, delete everything there and replace it with this

"development": {
  "use_env_variable": "DATABASE_URL"
},

now our model configuration is going to make use of this environment variable when we are on development. remember we had something like this in our model:

if (config.use_env_variable) {
  var sequelize = new
  Sequelize(process.env[config.use_env_variable], config);
} else {
  var sequelize = new Sequelize(config.database, config.username,
  config.password, config);
}

so we are checking if our config is set, in this case, our config variable is set to development object, this line of code is doing that.

var env       = process.env.NODE_ENV || 'development';
var config    = require(__dirname + '/../config/config.json')[env];

we set the env to development by default, then we required the config.json file and pull out the development environment. so config variable is equal to our development object set in config.json file. so we then check if use_env_variable is set, if it is we use process.env[config.use_env_variable] which translates to process.env['DATABASE_URL'] to get the value set in that environment. but we don’t have DATABASE_URL` set in our node environment. to do that we will go to our .env file and set it, so the .env file is where we set custom node_env we want to have access to in our code. in your .env file type the following:

DATABASE_URL=postgres://username:password@localhost:5432/database_name

If you don’t have a password setup then your configuration should be in this format:

DATABASE_URL=postgres://username@localhost:5432/database_name

so our models will make use of this DATABASE_URL to set up our database, replace username with your Postgres username, mine is postgres, replace password with your password and database_name with the name of your database. so in my case, we’ll have something like this:

DATABASE_URL=postgres://postgres:mypassword@localhost:5432/todo-app

then create your Postgres database from your terminal like this:

$ createdb todoapp

ensure you have Postgres installed otherwise this will not work.

note that anything we set in our .env file is available in our node environment and can be accessed with process.env.whatWeSet in our case process.env.DATABASE_URL`. NODE_ENV is equal to whatever we set it to be. we could set it to development, production or testing, depending on what environment we want to work on. we could set it in the .env file also. In our case, if it is not set we just default to development: var env = process.env.NODE_ENV || ‘development’.

Creating our models

Now that we have our configurations out of the way, the next thing is to create our models, Each todo is going to have todo items, so a todo can have many items under it, so we can then say that the relationship between a todo and the todo item is a one to many relationship. Now to create this relationship we first have to create our models, run the following command to create our models

Todo models

$ node_modules/.bin/sequelize model:create --name Todo --attributes title:string

• — name refers to the name of the model
• — attribute refers to the attributes the model should have

this will generate a todo.js file in our model and the todo model should look something like this:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var Todo = sequelize.define('Todo', {
  title: DataTypes.STRING
}, {});
Todo.associate = function(models) {
// associations can be defined here
};
return Todo;
};

A migration file will also be automatically generated in the migration folder and it should look like this:

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

TodoItem

We’ll do the same thing for todo items, run the following command in your terminal

$ node_modules/.bin/sequelize model:create --name TodoItem --attributes description:string

the todoItem model should look something like this:

'use strict';
module.exports = (sequelize, DataTypes) => {
  var TodoItem = sequelize.define('TodoItem', {
    description: DataTypes.STRING
  }, {});
TodoItem.associate = function(models) {
// associations can be defined here
};
return TodoItem;
};

the migration file

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

Refactoring

we are going to refactor some part of our code to make use of Es6, change the functions to arrow functions and all vars to const

module.exports = (sequelize, DataTypes) => {
const Todo = sequelize.define('Todo', {
title: {
type: DataTypes.STRING,
allowNull: false,
},
});
Todo.associate = (models) => {
// associations can be defined here
Todo.hasMany(models.TodoItem, {
foreignKey: 'todoId',
});
};
return Todo;
};

in the above code we modified our todo.js file, first we included an extra attribute to the title field allowNull: false what this attribute does is that it made the title field not nullable, meaning that the database is going to throw an error if we attempt to add an empty or null value to the title field, the DataTypes.string means that we only expect a string value in this field, anything other than that the database is going to throw an error

Also, we created a relationship between out Todo and TodoItem, like we said earlier, every single Todo has many todoItem, that relationship is defined with the hasMany many method of the Todo Model. The foreignKey: ‘todoId’, means that todoId is going to be the foreign key column in todoItem, you can read more about foreign keys here.

we will also make the same modification to ur TodoItem

module.exports = (sequelize, DataTypes) => {
const TodoItem = sequelize.define('TodoItem', {
description: {
type: DataTypes.STRING,
allowNull: false,
},
});
TodoItem.associate = (models) => {
// associations can be defined here
TodoItem.belongsTo(models.Todo, {
foreignKey: 'todoId',
onDelete: 'CASCADE',
});
};
return TodoItem;
};

Every single Todo has many TodoItems and each TodoItem belongs to one Todo, that is why we have the TodoItem.belongsTo define in our model above. The onDelete: ‘Cascade’ means if we delete a todo then the associated todoITem should also be deleted

Now that we have our models ready we can now run our migrations, in the migration folder we’ll have a set of migration files, The files contain scripts for creating and dropping our database tables, the migration scripts were created to model what we defined in our models, again, the scripts are responsible for creating and dropping our tables every time we run it. The up function is responsible for creating our tables and its columns, the down function is responsible for undoing what the up function runs.

we are going to be modifying our migration scripts since we modified our model files, this is to ensure consistency between our model and migrations

since we are going to be having a foreingkey: todoId in the TodoItem as we defined in our models, we are going to modify the migration script for TodoItem to include a todoId

we are going to include this in the migration file for TodoItem

todoId: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'Todos',
          key: 'id',
          as: 'todoId',
        },
      },

the references contains a property called model, this tells us the model this ForeignKey refers to, in our case it is the Todos, the next attribute is the key attribute which tells us what the todoId in todoItems maps to in Todos models, in this case it is the id of the Todos table, what this means is that the id of the todo table is the same as the todoId in the TodoItem table

the new migrations file should look something like this:

module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('TodoItems', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
description: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
todoId: {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
references: {
model: 'Todos',
key: 'id',
as: 'todoId',
},
},
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('TodoItems');
}
};

Now we are ready to run our migration, running migrations we run the script in the migrations files and create our tables. Before running the migrations, on your terminal export your database url like this:

export DATABASE_URL=postgres://postgres@localhost:5432/database_name

Run the migration like this:

node_modules/.bin/sequelize db:migrate

you should see something like this run in your terminal

== 20180908120703-create-todo: migrating =======
== 20180908120703-create-todo: migrated (0.025s)
== 20180908121020-create-todo-item: migrating =======
== 20180908121020-create-todo-item: migrated (0.013s)

If you check your database you will see that your table has been created and the relationship has been defined

Now it is time to persist our data, to interact with the database we created we are going to be using the models we created, with the models we have access to enormous methods we can use to interact with our database, we can perform CRUD operations through this methods,

In the controller of our todo app i.e todo.jsimport the model

import models from '../models';

This will go to the index.js fileand import dbexported at the bottom of the file. through this model, we can interact with the database.

Create Todo

Now let’s modify createTodomethod, the method to create todo currently looks like this

createTodo(req, res) {
if (!req.body.title) {
  return res.status(400).send({
    success: 'false',
    message: 'title is required',
});
} else if (!req.body.description) {
  return res.status(400).send({
    success: 'false',
    message: 'description is required',  
});
}
const todo = {
  id: db.length + 1,
  title: req.body.title,
  description: req.body.description,
};
db.push(todo);
return res.status(201).send({
  success: 'true',
  message: 'todo added successfully',
  todo,
});
}

Now instead of using db.push(todo) to push a new todo to our db object we are going to make use of the create method provided by our models, also we’ll remove the description field since the todos tabel does not have a field for description, the description will be added in the todoItem table

Now our createTodo method looks like this:

createTodo(req, res) {
  if (!req.body.title) {
    return res.status(400).send({
      success: 'false',
      message: 'title is required',
    });
}
const todo = {
  title: req.body.title,
};
models.Todo.create(todo).then((todo) => {
  return res.status(201).send({
     success: 'true',
     message: 'todo added successfully',
     todo,
   });
});
}

The .then function runs when the todo has been successfully added to the database, the parameter passed into the function in the .then is the todo response from the database after the create operation is completed, so what we are basically saying in a layman language is that the line of code should create a todo in the database, and because a database interaction will take a few milliseconds to complete we don’t want to wait for that operation to finish before we move to the next line of code, so we continue to the next line of code but when that database operation finish executing the code in the .then should run with the returned result which we passed as a parameter called todo to the function. This whole operation is called Promises in javascript, you can study more on promises here.

Our returned response looks like this:

Now we have a problem, we can create another todo with the same title and it will be added to the database, we need a way to ensure that a todo with same title doesn’t get added to the database. To do this we have to first check if the todo we are trying to add to the database exists already, if it does then we return a message to the user, if it doesn’t exist already then we add it to the database. so we have to search the database for the title that the user provide to us, if the title exists already then we will return a message to the user, otherwise, we’ll add the todo top the database

After implementing this our createTodo should now look like this

createTodo (req, res) {
  if (!req.body.title) {
    return res.status(400).send({
      success: 'false',
      message: 'title is required',
    });
  }
  models.Todo.findOne({
    where: { title: req.body.title }
  })
  .then((todoFound) => {
    if (todoFound) {
      return res.status(403).send({
       success: 'true',
        message: 'A todo with that title exist already', 
      });
    }
    const todo = {
      title: req.body.title,
    };
    models.Todo.create(todo).then((todo) => {
      return res.status(201).send({ 
        success: 'true',
        message: 'todo added successfully',
        todo,
      }); 
    });
  })
}

Now we are using the findOne method to check if a todo with the title provided already exist, if it does the we return a message, otherwise we go ahead to add the todo.

Attempting to add a Todo that already exist our response looks like this

Get All Todos

Now let’s modify our getAllTodos method, the code currently looks like this

getAllTodos(req, res) {
  return res.status(200).send({
    success: 'true',
    message: 'todos retrieved successfully',
    todos: db,
  });
}

Now we need to make use of the findAll method provided by our models to find all todos

the modification looks like this:

getAllTodos(req, res) {
  models.Todo.findAll()
  .then(todos => res.status(200).send({
    success: 'true',
    message: 'todos retrieved successfully',
    todos,
  }));
}

the response looks like this:

Get a single Todo

the old code for get a single todo

getTodo(req, res) {
  const id = parseInt(req.params.id, 10);
  db.map((todo) => {
    if (todo.id === id) {
      return res.status(200).send({
        success: 'true',
        message: 'todo retrieved successfully',
        todo, 
      });  
    }
  });
  return res.status(404).send({
    success: 'false',
    message: 'todo does not exist',
  });
}

The new code looks like this:

getTodo(req, res) {
  const id = parseInt(req.params.id, 10);
  models.Todo.findById(id)
  .then((todo) => {
    if (todo) {
      return res.status(200).send({
        success: 'true',
        message: 'todo retrieved successfully',
        todo,
      });
    }
    return res.status(404).send({
      success: 'false',
      message: 'todo does not exist',
    });
  });
}

We use the findById method and pass the Id of the todo we wish to get, if the todo exist then we get a response and if the todo does not exist we get a response telling us that the todo does not exist

Note: If you are using sequelize v5, findById was replaced by findByPk

Todo that does not exist in the database

Conclusion

This marks the end of this tutorial, as practised and before the next part of this series you can modify the endpoint for update and delete to make use of our models, Also we’ll not be working with the todoItem model we created, you can take that up as a challenge and add description to the todos that we already created. Cheers!!

refrence