Sample Node.js Application with PostgreSQL CRUD operations
Tags: beginners, programming, node, database
Published: 2023-03-16 17:27:53


This article catalogs the development of a sample Node.js application with the express framework, that interacts with a containerized PostgreSql database via CRUD operations. The database is run in a docker container, and I made use of a sample dataset generated with Mockaroo. Th generated dataset is a simple list of users with name, gender and favorite color as properties. The application lists these users in table format, with a button to delete users. There is also a button to add users at the top of the page that redirects to a data entry form for a new user. The application is designed using materialize css for a quick UI design.



<u>PostgreSQL Container and Database</u>

I started by pulling and running the official postgres docker image to my system.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4budzzq96lom0t5e051e.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pwv272ytxivu21o5lg0v.png)


I named the docker container "postgres" and mapped the port 5432 (default postgres port) of the container to the same port on my system (the host). I passed in the default postgres password as admin in the command as well.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nnxszo0wc5cs3sin3gaq.png)

With the database application now running in the container on port 5432, I connected to the container using the psql CLI. The psql utility was previously downloaded on my system, but if needed, it can be downloaded via "apt-get" for debian linux systems. 
A "postgres" (user) prompt means the connection into the container is successful.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nruywvxa57t57obmasdp.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3yxzx29j0ul77197wjzw.png)


Now I created a database called node_test and switch to it to get out of the default "postgres" database.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ebpey8uh4nptj8middol.png)

On mockaroo.com, I created the sample database table with the properties, and then downloaded the generated file in sql format.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ozbld1jmiokjnyzzwtbb.png)
The generated database table as seen in VS Code.




<u>Application UI with EJS and Materialize CSS</u>

For a quick UI, I used materialize css which is a flexible framework for quickly generating UI components and behavior. I used the EJS node package as a templating engine for rendering, with 2 views. One for displaying the table of users, and the other view for data entry of the new user.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p53ddc20zk4rd353xt7v.png)


![Table View](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u85cz4as4u6btzuclwbs.png)
<figcaption>Table View</figcaption>
-




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hgwd0lhc1ufvk695cxq2.png)


![New User entry view.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uuu59gtbn89r5u4b4i3f.png)
<figcaption>New User entry view</figcaption>
-




<u>Node.js Server application with Express, EJS and CRUD Operations.</u>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t2whtksprysnfeta5un5.png)


I wrote the nodejs application with express, and also using the 'pg' package to interact with a postgres database. The code also made use of a random number generator to randomly assign a user ID for the database for any new users added. The postgres connection makes use of a "postgres client" with parameters that specify the postgres connection credentials.
For the CRUD operations, I made use of the 'get' and 'post' routes provided by the express framework. An issue of complexity that arose was for the 'delete' operation. Because the actual delete operation is done by the postgres client via the delete query, it was tricky to use use the actual 'delete' method by express. I researched further as to how that could be accomplished but was not successful, so settled on a work-around to treat the delete operation as a 'get' request to the postgres client which could be easily accomplished. I will update this if I eventually find out how to use a the delete method.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8lfr9vpzku95ioty51v.png)

Clicking the red delete user button removes from the database, and re-renders the UI with the updated table. Demo of the top 2 users on the above table with 'delete user' clicked, and UI re-rendered (below image) with users removed from the table.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kft642k3fk77u3exi6fs.png)

Clicking on the green 'add' button redirects to the '/adduser' route for entry, and then a new user is added to postgres on submit. The  UI renders the new updated table. Demo for 2 new users added below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y6v03lh2ggka0549d2k1.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/clhrqrg5xc8p1b8w0z9b.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5kd9pgwgxjiimw7bkp9v.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ydbyna4wwa17ka0nvkmj.png)




<u>Conclusion</u>

This was a demonstration on how to run a node.js application that interacts with a containerized postgres database. It highlights the basics of a CRUD operation with a database, and demonstrates the speed, ease and convenience of running containerized applications on docker. On high level production systems, the application is also containerized and both containers can interact with each other in a container orchestration system like docker-compose or kubernetes. The Postgres database would also need to have its data persisted on the host my mapping the volume on the container to a volume on the host.