Node, Express and PostgreSQL using Sequelize (In Ubuntu 18.04.3 LTS)

A warm welcome to my understanding and learnings on Node, Express and PostgreSQL using Sequelize. My learning is from a blog post and link to that is — https://scotch.io/tutorials/getting-started-with-node-express-and-postgres-using-sequelize

Though it is already posted, the reason for writing about it here is due to my learning and Sequelize version upgrade from v4 to v5, where I installed Sequelize v5 and followed Sequelize v4 blog post. Minor changes are done when upgrading from Sequelize v4 to Sequelize v5. Okay let’s jump into the Node with Sequelize right away

Topics Covered,

1. Node (JS Runtime for Server Side)
2. Express (Web Framework)
3. PostgreSQL (Database)
4. Sequelize (A database Object Relational Mapping that will interface with PostgreSQL)
5. Postman (API testing tool)

Project Setup,

1. Create a folder named postgres-express-react-node-tutorial and subdirectories bin and server
2. In terminal type as mkdir -p node-sequelize/{bin,server}
3. Navigate to root folder by typing in terminal as cd node-sequelize
4. Initialize a NodeJS application by typing in terminal as npm init -y (-y can be omitted to have control over package.json file’s configuration)
5. Now the root directory will look like this,

Directory structure after project setup

Express Setup,

1. Now we will install Express (A web framework and its dependencies)
2. Type in terminal as npm install — save express body-parser morgan
3. This will install Express, body-parser, morgan
4. body-parser extract the entire body portion of an incoming request stream and exposes it on req. body . … This body-parser module parses the JSON, buffer, string and URL encoded data submitted using HTTP POST request
5. Morgan is a HTTP request logger middleware for Node.js. It simplifies the process of logging requests to your application. You might think of Morgan as a helper that generates request logs
6. Now lets create app.js file by typing in terminal as touch app.js
7. Following content goes into the app.js file

app.js file content

1. In the app.js file
2. We are requiring the installed dependencies in our project — Express, Morgan, Body-parser
3. Setup the express app using by making it as a function express() and assign to variable app
4. Now make the express app to use the morgan logger by app.use(logger(‘dev’))
5. Parse the incoming request data
6. Then make the express app to use the body-parser by
7. app.use(bodyParser.json())
8. app.use(bodyParser.urlencoded({extended: false}))
9. Then we will set a default route to the application as mentioned in the image
10. Now we need to create the server for our app.js file
11. Go to bin folder and create a www file by typing in terminal as touch www
12. Following content goes into www file

www file content

1. Now we will install nodemon a package that automatically reloads the page whenever a change in code is saved
2. Type as npm i -D nodemon (-D saves the developer dependencies in our package.json file)
3. Lets create script in package.json file to start our application. Following content goes into package.json file

content goes into package.json file

1. Now in command line enter npm start to start the application
2. Go to the http://localhost:3000 in browser to view the application running

catch-all route which shows welcome message

Sequelize Setup,

1. First we will install Sequelize CLI package by typing in terminal as npm install -g sequelize-cli
2. Next, we need to configure Sequelize for our project. For that, we will create a .sequelizerc file in our project’s root folder. In this file, we are going to be specifying the paths to files required by Sequelize. Following content needs to be pasted in .sequelizerc file,

.sequelizerc file content

1. config.json file contains our application’s configuration such as database authentication
2. models folder holds the application model
3. seeders folder holds the seed data but we are not going to use it in this application
4. migrations folder holds our application’s migration
5. Lets install the Sequelize package with it’s dependencies by typing in terminal as npm install — save sequelize pg pg-hstore
6. pg creates database connection
7. pg-hstore is a module for serializing and deserializing JSON data into Postgres hstore format
8. Now we will initialize sequelize by typing in terminal as sequelize init
9. This will create the folders and files specified in .sequelizerc file
10. Now the root directory will look like following image,

Directory structure after sequelize init

Installing PostgreSQL,

1. Open terminal and type as sudo apt update
2. Now type as sudo apt install postgresql postgresql-contrib
3. Create a new user by typing in terminal by entering as su — postgres
4. Then enter password of root user
5. Enter as createuser — interactive — pwprompt
6. Enter name of role as per your preference
7. Enter password for the role
8. Make it as Superuser
9. Now create a database by typing as createdb -O user_1 todos_dev
10. This will create a todos_dev database under the user_1 user
11. We can see the list of databases present in postgres user by typing \l. It will also show the Users under whom the database is available in Owner column

List of Databases

Generating Models,

1. We are going to have two models namely Todo and TodoItems
2. Relationship between them are Todo can have many TodoItems whereas a TodoItem can belong to One Todo
3. Type this command in terminal sequelize model:create — name Todo — attributes title:string
4. This will create todo.js file in /server/models/ folder and migration file <date>-create-todo.js in /server/migrations folder (<date>-create-todo.js, date is the date the model is generated)

todo.js & <date>-create-todo.js file content

1. Lets generate TodoItem model by typing in terminal as sequelize model:create — name TodoItem — attributes content:string,complete:boolean

todoItem.js & <date>-create-todo-item.js

1. Define relationship between models, (Todo.hasMany…as:todoItems)

todo.js file content

1. allowNull: false — This will not allow us to submit a title without entering any input
2. We also defined the relationship between Todo and TodoItems in the Todo.associate class method
3. The as: ‘todoItems’ means that every time we query for a todo and include it’s todo items, they’ll be included under the key todoItems instead of TodoItems (Sequelize defaults to using the pluralized model name)
4. Given below is the code For the TodoItems which has default value. It means that if we don’t provide a value, the database is going to take the provided default value
5. Also the on delete means that it tells Postgres that if we delete a todo, it’s associated todo items should be deleted as well (cascade the delete action)

todoitem.js file content

1. Given below is the migration file for our models(todo.js & todoItem.js) which defines how we want our models to look like in the database. Below image we have refactored code to ES6

migration file for model — todo.js

migration file for model — todoItem.js

1. When running these migrations up function will be executed. It will take care of creating the table and its associated columns for us
2. If we need to rollback to previous version, the down function will be executed thus returning us the database to the previous state before we performed the migration (up function)
3. Notice we define the relationship between our models in the create-todo-item.js migration file as well.
4. The todoId field was not automatically generated and we’ve had to manually define it.
5. Sequelize automatically generates the id, createdAt and updatedAt fields for you.
6. In addition to that, any time a model is saved, the updatedAt field is automatically updated to reflect the new update time
7. After the models and migrations are in place we are ready to send the models to the database by running the migrations. Run the following command in terminal to do so,
8. sequelize db:migrate
9. After the sequelize db:migrate CLI will show result similar to the image below,

sequelize db:migrate result

Creating Controller and Routing,

1. We will have todosController and todoItemsController. todosController will be responsible for creating, listing, updating and deleting todos. todoItemsController will be responsible for creating, updating and deleting todo items
2. Create a new folder (controller) and a new file (todo.js) in server folder. Now it will be like this — server/controller/todo.js
3. After that go to todo.js file and include the content as below image,

todo.js file content

1. The code mentioned above creates a new todo, if successful return it and if failure it will return the error
2. req — It is the incoming request from the client
3. res — It is the response against the request which we are giving to client
4. Next we will create an index.js file in server/controller folder where we will be exporting our controllers from,

index.js file content

1. Next, we need to add an API route that maps to this functionality. Create a routes folder inside the server folder.
2. Inside the new routes folder, create an index.js file. We are going to place all our routes here.
3. Following code goes into the index.js of routes folder whose path is server/router/

index.js file content

1. The above code snippets will add two routes — /api & /api/todos
2. /api — It is a welcome route
3. When we call this, we are requesting our app to send back JSON object welcoming the user
4. /api/todos — It is a request to create todo
5. When we post a data we are telling our app to run todosController.create function which will take the request object, extract the posted data and create a todo from it
6. Here we say todosController.create function is the POST route handler for the /api/todos endpoint
7. Now we will tell our application that we have added the routes by adding the require in app.js file.

app.js file content after adding routes through require method

1. Start the server by running npm start in terminal
2. By using Postman we will do a POST request to create a new todo. Image is below,

POST request to create a todo

Listing Todos,

1. Lets add the following block of code after create method in server/controller/todo.js

todo.js file should have this content

1. Here we are fetching all todos from our database and sending them back to the user as array in the response
2. If any error is occured while fetching the todos from our database we are sending the error object
3. Next, open up server/routes/index.js and create a new url that maps a todos GET request to the list method right below the POST route we’d added earlier,

index.js file should have this content

1. Let’s open Postman and do a GET request for the new url (route path — /api/todos)

GET request to list todo

Creating Todo Items,

1. Create a todoitems.js file in server/controller folder so that we can add todo items in todo
2. Following content goes into todoitems.js file,

todoitems.js file content

1. Here we are creating a todoitem and associating it with a particular todo by grabbing the id of that particular todo through req.params.todoId
2. Following content goes into index.js file of server/controller folder,

index.js file should have this content

1. Here we are exporting the todoItems controller from this default export file
2. We will setup the route for creating a new todoitem and see how the todoId is specified. Open the index.js file in server/routes/ folder,

index.js file should have this route to create a new todo item

1. :todoId is made available to us by Express in request.params object as todoId and it is the same one we are accessing in our controller
2. We also need to require todoItemsController in server/routes/index.js file

require todoItemsController in index.js file

1. Using Postman we will create todo items,

POST request to create todo item

Listing todo-items inside todos,

1. Now we are able to create todos and todoitem. Next we have to show the todoitems with respective todos so we need to modify todosController.list code which is in todo.js of server/controller/ folder.

modify todo.js file to show todo item under respective todo

1. We require TodoItem model at top of todo.js file in server/controller folder
2. Open Postman and make a get request to 127.0.0.1:3000/api/todos to view the todo and todoitems

GET request to view todo with todo items

Retrieving a Single Todo,

1. In this section, we are going to get a single todo and its todoitems based on a todoId
2. Add the following into todo.js file which is at server/controller/ folder,

todo.js file should have this content to retrieve a single todo

1. In the above code, we are finding the todo whose id matches the todoId which we get from req.params.todoId and along with that we are also including the todoItems of the matching todoId
2. If the todo exists we are sending todo along with todoItems. If not sending a message as Todo Not Found.
3. If we face any error while processing the single retrieve by id we send the error object
4. Now we need to add a route in index.js which is present in server/routes/ folder

index.js should have this route to retrieve a single route

Try the Postman GET request to retrieve the single todo like we have done for others

Updating a Single Todo,

1. Add the following code in todo.js file which is present in server/controller/ folder,

todo.js should have this to update a single todo

1. We are finding the todo by todoId and updating it’s title. If no title is provided, it will default to title which it already had
2. Lets add a route in index.js which is present in server/routes/ folder

index.js should have this route to update a single todo

1. Open Postman and do a PUT request to 127.0.01:3000/2 with,
2. key as title and its value as second todo

PUT request to update a single todo

Deleting Todos,

1. Add the following block of code in todo.js file which is in server/controller folder

todo.js file should have this to delete a todo

1. Above code is similar to update except that we are not including todoitems here.
2. In our models we have specified as onDelete action as CASCADE which will delete the todoitems also
3. Add the route in index.js file which is present in server/routes folder

index.js file should have this route to delete a todo

1. Trying the Postman DELETE request gives the result similar to following image,

DELETE request to delete todo

Updating and Deleting Todo Items,

1. Updating and Deleting Todo Items is similar to Updating and Deleting Todo
2. Add the following code in todoitems.js file which is in server/controller folder

todoitem.js file should have this to update a todo item

todoitem.js file should have this to delete a todo item

1. We are finding the todoitem to either update or delete by two criteria which are mentioned below,
2. id of todoitem from req.params.todoItemId
3. id of todo from req.params.todoId
4. We have the same approach as we update the todo title image when we are updating a todoItem
5. This works for application which has very less fields. If we have more fields we would give the Sequelize model update function the data and then specify the fields it should update.

update function for todo item if we have more fields

1. Using this approach, we pass the whole update object we get from the req.body to the update function.
2. Using ES6’s Object.keys function, we extract the keys from the update object and tell the TodoItem Sequelize model to only update the fields that are present in the update data object.
3. If we have a field in our model that’s missing from the update object, the update operation will leave that field untouched.
4. We will add two routes in index.js file which is in server/routes folder

index.js file should have this route to update and delete todo item

1. We also specify a method not allowed message if there is any other request on todo items

Method Not Allowed message when received other requests

1. Similar to other requests we can do the Postman request for UPDATE and DELETE todo item

Hope my post on Node, Express and Postgres using Sequelize gave you a good understanding. Thank you.