Your First Node API

Async

A Complete Server: Getting Started

A Complete Server: Persistence

A Complete Server: Authentication

A Complete Server: Deployment

Command Line Interfaces

Testing Node.js Applications

Bonus: Persistence with PostgreSQL

Changelog

Enter your name and email address below, then click the **"Send my free chapter"** button to get started

The Complete Guide to Building Production Apps with Node.js

Fullstack Node.js

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 

 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.

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 

 and follow the instructions for your platform. Depending on your platform you may choose to use an 

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 (

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

psql (11.5)
Type "help" for help.

postgres=#

 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 

postgres=# \conninfo
You are connected to database "postgres" as user "dguttman" via socket in "/tmp" at port "5432".
postgres=#

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

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

postgres=# CREATE DATABASE printshop;
CREATE DATABASE

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

postgres=# \c printshop;
You are now connected to database "printshop" as user "dguttman".

printshop=# CREATE TABLE products (
printshop(#     "_id" TEXT PRIMARY KEY,
printshop(#     "description" TEXT NOT NULL,
printshop(#     "imgThumb" TEXT NOT NULL,
printshop(#     "img" TEXT NOT NULL,
printshop(#     "link" TEXT NOT NULL,
printshop(#     "userId" TEXT NOT NULL,
printshop(#     "userName" TEXT NOT NULL,
printshop(#     "userLink" TEXT NOT NULL,
printshop(#     "tags" TEXT[]);
CREATE TABLE

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. 

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

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 

 package. This is a solid, no-frills PostgreSQL client for Node.js. While it is light on abstractions, 

 supports many PostgreSQL features like parameterized queries, named statements with query plan caching, async notifications with 

On the command line, in our project folder, we'll start by installing the 

 module that we'll use to connect to our new database.

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 

 a whirl using just the Node.js REPL. From the command line start the REPL by typing 

❯ node
Welcome to Node.js v12.0.0.
Type ".help" for more information.
>

Next, we'll connect to our new database using 

> const { Client } = require('pg')
undefined
> const client = new Client({ database: 'printshop' })
undefined
> client.connect()
Promise { <pending> }
>

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:

> const insertQuery = `
...   INSERT INTO products (
...     "_id",
...     "description",
...     "imgThumb",
...     "img",
...     "link",
...     "userId",
...     "userName",
...     "userLink",
...     "tags"
...   )
...   VALUES (
...     'fullstackreact',
...     'The up-to-date, in-depth, complete guide to React and friends. Become a ReactJS expert today',
...     'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
...     'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
...     'https://fullstackreact.com',
...     'fsreact',
...     'David Guttman',
...     'https://fullstackreact.com',
...     '{ react, javascript }'
...   )
... `
undefined
>

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

> client.query(insertQuery).then(console.log)
Promise { <pending> }
> Result {
  command: 'INSERT',
  rowCount: 1,
  oid: 0,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

 object. There isn't too much of interest here, but it does show that the 

 because we only affected one row (the one we inserted).

> const selectQuery = 'SELECT * FROM products'
undefined
>

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

> client.query(selectQuery).then(res => console.log(res.rows))
Promise { <pending> }
> [
  {
    _id: 'fullstackreact',
    description: 'The up-to-date, in-depth, complete guide to React and friends. Become a ReactJS expert today',
    imgThumb: 'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
    img: 'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
    link: 'https://fullstackreact.com',
    userId: 'fsreact',
    userName: 'David Guttman',
    userLink: 'https://fullstackreact.com',
    tags: [ 'react', 'javascript' ]
  }
]

 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 

 constructs a nice object for us, and even our tags is a JavaScript array.

---
code: https://gitlab.com/fullstackio/books/newline-course-apps/david-guttman-fullstack-node.js-book-app/-/tree/main/code/src/04b-persistence-postgres?ref_type=heads
---

# 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`](https://npm.im/pg) instead of [`mongoose`](https://mongoosejs.com/). `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](https://www.postgresql.org/download/) and follow the instructions for your platform. Depending on your platform you may choose to use an [interactive installer](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) or a package manager like `apt` or [`homebrew`](https://brew.sh/) (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`):

    psql postgres

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

    psql (11.5)
    Type "help" for help.

    postgres=#

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 `\`):

    postgres=# \conninfo
    You are connected to database "postgres" as user "dguttman" via socket in "/tmp" at port "5432".
    postgres=#

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:

    postgres=# CREATE DATABASE printshop;
    CREATE DATABASE

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

    postgres=# \c printshop;
    You are now connected to database "printshop" as user "dguttman".

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

    printshop=# CREATE TABLE products (
    printshop(#     "_id" TEXT PRIMARY KEY,
    printshop(#     "description" TEXT NOT NULL,
    printshop(#     "imgThumb" TEXT NOT NULL,
    printshop(#     "img" TEXT NOT NULL,
    printshop(#     "link" TEXT NOT NULL,
    printshop(#     "userId" TEXT NOT NULL,
    printshop(#     "userName" TEXT NOT NULL,
    printshop(#     "userLink" TEXT NOT NULL,
    printshop(#     "tags" TEXT[]);
    CREATE TABLE

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

    printshop=# select * from products;
     _id | description | imgThumb | img | link | userId | userName | userLink | tags
    -----+-------------+----------+-----+------+--------+----------+----------+------
    (0 rows)

    printshop=# \quit

W> 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`](https://npm.im/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`](https://npm.im/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:

    ❯ node
    Welcome to Node.js v12.0.0.
    Type ".help" for more information.
    >

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

    > const { Client } = require('pg')
    undefined
    > const client = new Client({ database: 'printshop' })
    undefined
    > client.connect()
    Promise { <pending> }
    >

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:

    > const insertQuery = `
    ...   INSERT INTO products (
    ...     "_id",
    ...     "description",
    ...     "imgThumb",
    ...     "img",
    ...     "link",
    ...     "userId",
    ...     "userName",
    ...     "userLink",
    ...     "tags"
    ...   )
    ...   VALUES (
    ...     'fullstackreact',
    ...     'The up-to-date, in-depth, complete guide to React and friends. Become a ReactJS expert today',
    ...     'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
    ...     'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
    ...     'https://fullstackreact.com',
    ...     'fsreact',
    ...     'David Guttman',
    ...     'https://fullstackreact.com',
    ...     '{ react, javascript }'
    ...   )
    ... `
    undefined
    >

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

    > client.query(insertQuery).then(console.log)
    Promise { <pending> }
    > Result {
      command: 'INSERT',
      rowCount: 1,
      oid: 0,
      rows: [],
      fields: [],
      _parsers: undefined,
      _types: TypeOverrides {
        _types: {
          getTypeParser: [Function: getTypeParser],
          setTypeParser: [Function: setTypeParser],
          arrayParser: [Object],
          builtins: [Object]
        },
        text: {},
        binary: {}
      },
      RowCtor: null,
      rowAsArray: false
    }

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:

    > const selectQuery = 'SELECT * FROM products'
    undefined
    >

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

    > client.query(selectQuery).then(res => console.log(res.rows))
    Promise { <pending> }
    > [
      {
        _id: 'fullstackreact',
        description: 'The up-to-date, in-depth, complete guide to React and friends. Become a ReactJS expert today',
        imgThumb: 'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
        img: 'https://www.newline.co/fullstack-react/assets/images/fullstack-react-hero-book.png',
        link: 'https://fullstackreact.com',
        userId: 'fsreact',
        userName: 'David Guttman',
        userLink: 'https://fullstackreact.com',
        tags: [ 'react', 'javascript' ]
      }
    ]

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.