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.
PostgreSQL Container and Database
I started by pulling and running the official postgres docker image to my system.
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.
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.
Now I created a database called node_test and switch to it to get out of the default "postgres" database.
On mockaroo.com, I created the sample database table with the properties, and then downloaded the generated file in sql format.
The generated database table as seen in VS Code.
Application UI with EJS and Materialize CSS
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.
-
-
Node.js Server application with Express, EJS and CRUD Operations.
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.
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.
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.
Conclusion
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.
Top comments (0)