How to create a simple PostgreSQL database with Expressjs?

PostgreSQL is a powerful database with features like multi-version concurrency control, a customizable storage interface for tables, a robust access-control system, support for full-text search among many others. It can be used with a variety of back-end languages like Nodejs, Rust, Python, Java, etc.

It is not difficult to setup a PostgreSQL database with a framework like Expressjs. This is a simple guide on creating a set of CRUD APIs. As long as you know the basics of Node and Express, you can follow along. Some experience with SQL databases (especially PostgreSQL) will help you understand this guide much better.

Creating an Express server

I will be creating a basic Express server:

A PostgreSQL table

We have our express server. Let's create a table in PostgreSQL. I will log in to the default database with my role and create a new database for our table. There will be a link at the end of this article about creating roles in PostgreSQL.

Here, with -d we specify a database. postgres is the default one. And -U specifies our role name. My role's name is personal. I created a database named userAPI and switched to it.

We have an empty users table in the userAPI database. We need to access this database from Express.

Connecting to the database

To communicate with our PostgreSQL database and table, we will need a package. The package we will be using is called node-postgres.

Let's use this package to start a connection with our database.

We have put the required configuration in a separate file. A new pool of connections is created with the following properties:

1. user - This is the user account/role we created that can access the userAPI database.
2. host - Since we are hosting Postgres on our own machine, localhost would be used. If you are connecting to a remote PostgreSQL database, then use the remote machine's address. For eg - http://86.152.120.12.
3. database - This is the database we would like to access.
4. password - The corresponding password for the user account/role.
5. port - By default the port is 5432. If you have configured a different port or are connecting to your remote machine, use the appropriate port number.

We have our Express server, a PostgreSQL database, an empty table, and the server can access the database. Let's create some APIs.

Simple CRUD

We will perform simple CRUD operations on our database - creating a new user, reading data about a user, updating a user's data, and deleting a user.

We will keep them in a separate file as well.

After extracting the name and email sent in the request, we use the query function to create a new record in the database. If you have used MySQL with Express before, this syntax will be very familiar.

We can read the data for all users or a single one:

These three functions also demonstrate how parameters are passed to SQL statements in node-postgres. If you have five parameters, then each parameter will be picked from the array in order. For example:

Let's create the update and delete functions:

Make sure to export the queries so we can use them to create endpoints:

Conclusion

If you have never worked with PostgreSQL before, I hope you try it.

Here are some additional resources:

1. A tutorial on creating roles in PostgreSQL.
2. Why use PostgreSQL?
3. How does PostgreSQL stack up against another equally popular database like MongoDB? Read this article about their differences and how to pick between them.
4. A fan of Sequelize? Learn how to use it with PostgreSQL and Express.
5. After creating a CRUD API with Express and PostgreSQL, you can build a front-end with Vue and have a full-stack application.