How to create a simple PostgreSQL database with Expressjs?
Tags: postgres, express, node, webdev
Published: 2023-07-10 17:10:03

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:


![Creating a simple Express server](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yl489qfvjkkduemavvw1.png)

## 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.


![Creating a PostgreSQL table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gsdx9ryjqd8oe3oc2anq.png)

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*](https://www.npmjs.com/package/pg).


![Installing the node-postgres package with npm and yarn.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5pqu78ryybm9vjhs5bc7.png)

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


![Connecting to the database table with the installed package.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcgtml7kpdvo0zt92w7x.png)

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.


![This is an API for creating new users.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qrvba81doj916ksk45ab.png)

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 are 2 APIs for fetching all users and fetching a single user by their id.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/30k9946ewlxb947me7pf.png)

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:


![An example of passing parameters to an SQL query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yjsp2datt1tlj6gsp031.png)

Let's create the update and delete functions:


![These are 2 APIs that update a user and delete a user by using the user's id.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90vg431v4w4wq8n5ta24.png)

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

![API endpoints for all the queries.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0w6r88274yavvb9guw9k.png)

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

Here are some additional resources:

1. A [tutorial](https://www.postgresqltutorial.com/postgresql-administration/postgresql-roles/) on creating roles in PostgreSQL.
2. [Why use PostgreSQL](https://www.prisma.io/dataguide/postgresql/benefits-of-postgresql)?
3. How does PostgreSQL stack up against another equally popular database like MongoDB? Read [this article](https://www.mongodb.com/compare/mongodb-postgresql) about their differences and how to pick between them.
4. A fan of Sequelize? Learn [how to use](https://www.robinwieruch.de/postgres-express-setup-tutorial/) 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](https://www.bezkoder.com/vue-node-express-postgresql/) application.