Node.js + PostgreSQL: CRUD example
Tags: node, postgres, javascript, webdev
Published: 2021-01-04 09:02:50

In this tutorial, I will show you step by step to build Node.js + PostgreSQL CRUD example using Express and Sequelize ORM.

Full Article: [https://bezkoder.com/node-express-sequelize-postgresql/](https://bezkoder.com/node-express-sequelize-postgresql/)

<h2>Node.js PostgreSQL CRUD example overview</h2>
We will build Rest Apis that can create, retrieve, update, delete and find Tutorials by title.

First, we start with an Express web server. Next, we add configuration for PostgreSQL database, create <code>Tutorial</code> model with Sequelize, write the controller. Then we define routes for handling all CRUD operations (including custom finder).

The following table shows overview of the Rest APIs that will be exported:
<div class="table-responsive">
<table class="table table-hover  table table-hover" width="100%">
<thead>
<tr>
<th>Methods</th>
<th>Urls</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<tr>
<td>GET</td>
<td>api/tutorials</td>
<td>get all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>api/tutorials/:id</td>
<td>get Tutorial by <code>id</code></td>
</tr>
<tr>
<td>POST</td>
<td>api/tutorials</td>
<td>add new Tutorial</td>
</tr>
<tr>
<td>PUT</td>
<td>api/tutorials/:id</td>
<td>update Tutorial by <code>id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>api/tutorials/:id</td>
<td>remove Tutorial by <code>id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>api/tutorials</td>
<td>remove all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>api/tutorials/published</td>
<td>find all published Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>api/tutorials?title=[kw]</td>
<td>find all Tutorials which title contains <code>'kw'</code></td>
</tr>
</tbody>
</table>
</div>
Finally, we're gonna test the Rest Apis using Postman.

This is our project structure:

![node-js-postgresql-crud-example-project-structure](https://dev-to-uploads.s3.amazonaws.com/i/88d07ipvppgo2rznrdpk.png)

<h2>Demo Video</h2>
This is our Node.js PostgreSQL CRUD example using Express & Sequelize application demo, test Rest Apis with Postman.

{% youtube x1pZHN_sjGk %}

<h2>Test the APIs</h2>
Run our Node.js application with command: <code>node server.js</code>.

Using Postman, we're gonna test all the Apis above.

- Create a new Tutorial using <code>POST /tutorials</code> Api

![node-js-postgresql-crud-example-create](https://dev-to-uploads.s3.amazonaws.com/i/hqvz8ra9p21z927iwzph.png)

After creating some new Tutorials, you can check PostgreSQL table:
<pre><code class="language-bash">testdb=# select * from tutorials;
 id |    title    |    description    | published |         createdAt          |         updatedAt
----+-------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1 | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  2 | Node Tut #2 | Tut#2 Description | f         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:43:05.131+07
  3 | Node Tut #3 | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  4 | Js Tut #4   | Tut#4 Desc        | f         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:45:40.016+07
  5 | Js Tut #5   | Tut#5 Desc        | f         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:45:44.289+07
</code></pre>

- Retrieve all Tutorials using <code>GET /tutorials</code> Api

![node-js-postgresql-crud-example-retrieve-all](https://dev-to-uploads.s3.amazonaws.com/i/m9razjm1njgww58er3as.png)

- Retrieve a single Tutorial by id using <code>GET /tutorials/:id</code> Api

![node-js-postgresql-crud-example-retrieve-one](https://dev-to-uploads.s3.amazonaws.com/i/0kuojvc596i5u423od2b.png)

- Update a Tutorial using <code>PUT /tutorials/:id</code> Api

![node-js-postgresql-crud-example-update](https://dev-to-uploads.s3.amazonaws.com/i/3buqfz0by0lu2z4kf3uq.png)

Check <code>tutorials</code> table after some rows were updated:
<pre><code class="language-bash">testdb=# select * from tutorials;
 id |     title      |    description    | published |         createdAt          |         updatedAt
----+----------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
  4 | Js Tut #4      | Tut#4 Desc        | t         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:54:17.468+07
  5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07
</code></pre>

- Find all Tutorials which title contains 'js': <code>GET /tutorials?title=js</code>

![node-js-postgresql-crud-example-search](https://dev-to-uploads.s3.amazonaws.com/i/u2hbmz5r35o7uo09y3z5.png)

- Find all published Tutorials using <code>GET /tutorials/published</code> Api

![node-js-postgresql-crud-example-search-status](https://dev-to-uploads.s3.amazonaws.com/i/dbo753wfqibt0b93d82d.png)

- Delete a Tutorial using <code>DELETE /tutorials/:id</code> Api

![node-js-postgresql-crud-example-delete-one](https://dev-to-uploads.s3.amazonaws.com/i/pyos3wq4tchb8ixuyj1c.png)

Tutorial with id=4 was removed from <code>tutorials</code> table:
<pre><code class="language-bash">testdb=# select * from tutorials;
 id |     title      |    description    | published |         createdAt          |         updatedAt
----+----------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
  5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07
</code></pre>

- Delete all Tutorials using <code>DELETE /tutorials</code> Api

![node-js-postgresql-crud-example-delete-all](https://dev-to-uploads.s3.amazonaws.com/i/ga42747jorssl20ywyug.png)

Now there are no rows in <code>tutorials</code> table:
<pre><code>testdb=# select * from tutorials;
 id | title | description | published | createdAt | updatedAt
----+-------+-------------+-----------+-----------+-----------
</code></pre>

For more details, implementation and Github, please visit:
[https://bezkoder.com/node-express-sequelize-postgresql/](https://bezkoder.com/node-express-sequelize-postgresql/)

## Further Reading
Server side Pagination:
<a href="https://bezkoder.com/node-js-pagination-postgresql/">Node.js Express Pagination with PostgreSQL example</a>

Fullstack:
- <a href="https://bezkoder.com/vue-node-express-postgresql/">Vue.js + Node.js + Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-node-express-postgresql/">Angular 8 + Node.js Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-10-node-express-postgresql/">Angular 10 + Node.js Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-11-node-js-express-postgresql/">Angular 11 + Node.js Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-12-node-js-express-postgresql/">Angular 12 + Node.js Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-13-node-js-express-postgresql/">Angular 13 + Node.js Express + PostgreSQL example</a>
- <a href="https://bezkoder.com/react-node-express-postgresql/">React + Node.js + Express + PostgreSQL example</a>

Security:
<a href="https://bezkoder.com/node-js-jwt-authentication-postgresql/">Node.js JWT Authentication & Authorization with PostgreSQL example</a>