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
):
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
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 becomeimgthumb
).
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:
❯ 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.
Now, we'll clean up after ourselves by removing that row, and log to make sure that a single row was deleted:
Loading...