Bonus: Persistence with PostgreSQL

In this book we've covered setting up a production API server using MongoDB. Of course, there are many other database options out there. In particular, SQL databases are a common choice.

When compared to SQL databases like MySQL or PostgreSQL, MongoDB has many advantages. However, it is not always the best tool for the job, and it's useful to know how to incorporate SQL databases like PostgreSQL into our apps.

In this chapter we're going to modify our app to use PostgreSQL instead of MongoDB for our products model. We'll start with the version of our app as it exists at the end of Chapter 4: A Complete Server: Persistence. From there, we'll change our product model methods so that they use pg instead of mongoose. pg is the most widely used PostgreSQL client for Node.js.

Once we've successfully migrated our products model away from MongoDB and on to PostgreSQL, we can discuss a number of quality of life improvements we can introduce to make working with PostgreSQL nicer.

Getting Started#

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. One thing to keep in mind is that this level of maturity is a double-edged sword.

On one hand, it's exceedingly unlikely that you'll run into an issue in production that hasn't already been encountered. There's an incredibly large community of people who have been running production systems with PostgreSQL for years, and it's relatively straightforward to find people who can help diagnose issues and tune performance.

On the other hand, the implementation of PostgreSQL began in 1986, almost 10 years before JavaScript was created and over 20 years before Node.js was introduced. While PostgreSQL is more than capable of working together with Node.js apps and services, it was not designed with JavaScript data objects in mind (unlike MongoDB). This means that more care needs to be taken when communicating with the database (e.g. constructing SQL queries as opposed to calling JavaScript functions). Additionally, PostgreSQL is much more strict about how it needs to be set up before it can be used. Unlike MongoDB, PostgreSQL needs tables and columns to be set up before data can be stored and can't be set up on the fly.

Before we begin, please make sure that you have PostgreSQL installed. If you have not installed PostgreSQL, please visit the PostgreSQL Downloads page and follow the instructions for your platform. Depending on your platform you may choose to use an interactive installer or a package manager like apt or homebrew (e.g. brew install postgresql).

After installation, to ensure that the PostgreSQL database is running and you can connect to it via the interactive terminal on the command line, run this command to connect to the default database (postgres):

If this is successful, we should see the following prompt:

By typing \conninfo and hitting enter, we should be able to verify that we are connected to the database and commands are working properly (terminal commands begin with a backslash \):

When using the interactive terminal, there are some useful commands to keep in mind:

  • \quit or \q will quit

  • \list or \l will list all databases

  • \dt will list the current database's tables

  • \du will list all roles/users

Before we quit out of the interactive terminal, let's go ahead and create our database:

Next, we'll connect to the new database instead of the default postgres:

After that, we'll create a new products table with the columns that we need:

Finally, we'll verify that our table is set up correctly with the right columns and quit:

When creating columns we need to make sure that the column name is double-quoted. If not, it will change to all lower case (e.g. imgThumb will become imgthumb).

Now that our database, table, and columns are all set up, we can change our app to start using them.

PostgreSQL & Node.js#

As mentioned earlier, we're going to modify our existing app to use PostgreSQL instead of MongoDB for our products model. We're going to start with the version of our app as it exists at the end of Chapter 4: A Complete Server: Persistence.

To allow Node.js to talk to our new database, we're going to use the pg package. This is a solid, no-frills PostgreSQL client for Node.js. While it is light on abstractions, pg supports many PostgreSQL features like parameterized queries, named statements with query plan caching, async notifications with LISTEN/NOTIFY, and bulk import and export with COPY TO/COPY FROM.

On the command line, in our project folder, we'll start by installing the pg package with npm install pg. This will give us access to the pg module that we'll use to connect to our new database.

Interactive Introduction#

Before we start modifying our app and worrying about how to change our models, let's get the hang of the basics. We'll give pg a whirl using just the Node.js REPL. From the command line start the REPL by typing node and hitting enter:

Next, we'll connect to our new database using pg:

Once we've told the client to connect, we can start sending queries to our database. We'll start by creating a SQL query to insert a new product row:

Then we'll use our client to send it to the database and log the response object:

We can see that the return value of our client.query() call is a pending Promise, and once it resolves, we can see the Result object. There isn't too much of interest here, but it does show that the command was an INSERT as we'd expect and the rowCount is 1 because we only affected one row (the one we inserted).

Next, we can create a SQL query to list all rows:

Then we'll send that to the database and log the rows from the result:

This time we only log part of the result object--the rows array. This contains the database rows that match our query. In this case, the only one in the table is the one we just inserted. Notice that pg constructs a nice object for us, and even our tags is a JavaScript array.

Now, we'll clean up after ourselves by removing that row, and log to make sure that a single row was deleted:



Start a new discussion. All notification go to the author.