A Note on Performance

The simplicity of NoSQL databases is a double-edged sword. Carefully planning a relational database can be an involved task, but the benefit of that careful planning is a database that offers excellent performance. Don’t be fooled into thinking that because NoSQL databases are generally simpler, there isn’t an art and a science to tuning them for maximum performance.

Relational databases have traditionally relied on their rigid data structures and decades of optimization research to achieve high performance. NoSQL databases, on the other hand, have embraced the distributed nature of the internet and, like Node, have instead focused on concurrency to scale performance (relational databases also support concurrency, but this is usually reserved for the most demanding applications).

Planning for database performance and scalability is a large, complex topic that is beyond the scope of this book. If your application requires a high level of database performance, I recommend starting with Kristina Chodorow and Michael Dirolf’s MongoDB: The Definitive Guide (O’Reilly).

Abstracting the Database Layer

In this book, we’ll be implementing the same features and demonstrating how to do that with two databases (and not just two databases but two substantially different database architectures). While the objective in this book is to cover two popular options for database architecture, it reflects a real-world scenario: switching a major component of your web application midproject. This could happen for many reasons. Usually it boils down to discovering that a different technology is going to be more cost-effective or allow you to implement necessary features more quickly.

Whenever possible, there is value in abstracting your technology choices, which refers to writing some kind of API layer to generalize the underlying technology choices. If done right, it reduces the cost of switching out the component in question. However, it comes at a cost: writing the abstraction layer is one more thing you have to write and maintain.

Happily, our abstraction layer will be very small, as we’re supporting only a handful of features for the purposes of this book. For now, the features will be as follows:

• Returning a list of active vacations from the database

• Storing the email address of users who want to be notified when certain vacations are in season

While this seems simple enough, there are a lot of details here. What does a vacation look like? Do we always want to get all the vacations from the database, or do we want to be able to filter them or paginate them? How do we identify vacations? And so on.

We’re going to keep our abstraction layer simple for the purposes of this book. We’ll contain it in a file called db.js that will export two methods that we’ll start by just providing dummy implementations:

module.exports = {
  getVacations: async (options = {}) => {
    // let's fake some vacation data:
    const vacations = [
      {
        name: 'Hood River Day Trip',
        slug: 'hood-river-day-trip',
        category: 'Day Trip',
        sku: 'HR199',
        description: 'Spend a day sailing on the Columbia and ' +
          'enjoying craft beers in Hood River!',
        location: {
          // we'll use this for geocoding later in the book
          search: 'Hood River, Oregon, USA',
        },
        price: 99.95,
        tags: ['day trip', 'hood river', 'sailing', 'windsurfing', 'breweries'],
        inSeason: true,
        maximumGuests: 16,
        available: true,
        packagesSold: 0,
      }
    ]
    // if the "available" option is specified, return only vacations that match
    if(options.available !== undefined)
      return vacations.filter(({ available }) => available === options.available)
    return vacations
  },
  addVacationInSeasonListener: async (email, sku) => {
    // we'll just pretend we did this...since this is
    // an async function, a new promise will automatically
    // be returned that simply resolves to undefined
  },
}

This sets an expectation about how our database implementation should look to the application…and all we have to do is make our databases conform to that interface. Note that we’re introducing the concept of vacation “availability”; we’re doing this so we can easily disable vacations temporarily instead of deleting them from the database. An example use case for this would be a bed and breakfast that contacts you to let you know they are closed for several months for remodeling. We’re keeping this separate from the concept of being “in season” because we may want to list out-of-season vacations on the website because people like to plan in advance.

We also include some very generic “location” information; we’ll be getting more specific about this in Chapter 19.

Now that we have an abstraction foundation for our database layer, let’s look at how we can implement database storage with MongoDB.

Setting Up MongoDB

The difficulty involved in setting up a MongoDB instance varies with your operating system. For this reason, we’ll be avoiding the problem altogether by using an excellent free MongoDB hosting service, mLab.

Getting started with mLab is simple. Just go to https://mlab.com and click Sign Up. Fill out the registration form and log in, and you’ll be at your home screen. Under Databases, you’ll see “no databases at this time.” Click “Create new,” and you will be taken to a page with some options for your new database. The first thing you’ll select is a cloud provider. For a free (sandbox) account, the choice is largely irrelevant, though you should look for a data center near you (not every data center will offer sandbox accounts, however). Select SANDBOX, and choose a region. Then choose a database name, and click through to Submit Order (it’s still an order even though it’s free!). You will be taken back to the list of your databases, and after a few seconds, your database will be available for use.

Having a database set up is half the battle. Now we have to know how to access it with Node, and that’s where Mongoose comes in.

Mongoose

While there’s a low-level driver available for MongoDB, you’ll probably want to use an object document mapper (ODM). The most popular ODM for MongoDB is Mongoose.

One of the advantages of JavaScript is that its object model is extremely flexible. If you want to add a property or method to an object, you just do it, and you don’t need to worry about modifying a class. Unfortunately, that kind of freewheeling flexibility can have a negative impact on your databases because they can become fragmented and hard to optimize. Mongoose attempts to strike a balance by introducing schemas and models (combined, schemas and models are similar to classes in traditional object-oriented programming). The schemas are flexible but still provide some necessary structure for your database.

Before we get started, we’ll need to install the Mongoose module:

npm install mongoose

Then we’ll add our database credentials to our .credentials.development.json file:

"mongo": {
    "connectionString": "your_dev_connection_string"
  }
}

You’ll find your connection string on the database page in mLab. From your home screen, click the appropriate database. You’ll see a box with your MongoDB connection URI (it starts with mongodb://). You’ll also need a user for your database. To create one, click Users, and then “Add database user.”

Notice that we could establish a second set of credentials for production by creating a .credentials.production.js file and using NODE_ENV=production; you’ll want to do this when it’s time to go live!

Now that we have all the configuration done, let’s actually make a connection to the database and do something useful!

Database Connections with Mongoose

We’ll start by creating a connection to our database. We’ll put our database initialization code in db.js, along with the dummy API we created earlier (ch13/00-mongodb/db.js in the companion repo):

const mongoose = require('mongoose')
const { connectionString } = credentials.mongo
if(!connectionString) {
  console.error('MongoDB connection string missing!')
  process.exit(1)
}
mongoose.connect(connectionString)
const db = mongoose.connection
db.on('error' err => {
  console.error('MongoDB error: ' + err.message)
  process.exit(1)
})
db.once('open', () => console.log('MongoDB connection established'))

module.exports = {
  getVacations: async () => {
    //...return fake vacation data
  },
  addVacationInSeasonListener: async (email, sku) => {
    //...do nothing
  },
}

Any file that needs to access the database can simply import db.js. However, we want the initialization to happen right away, before we need the API, so we’ll go ahead and import this from meadowlark.js (where we don’t need to do anything with the API):

require('./db')

Now that we’re connecting to the database, it’s time to consider how we’re going to structure data that we’re transferring to and from the database.

Creating Schemas and Models

Let’s create a vacation package database for Meadowlark Travel. We start by defining a schema and creating a model from it. Create the file models/vacation.js (ch13/00-mongodb/models/vacation.js in the companion repo):

const mongoose = require('mongoose')

const vacationSchema = mongoose.Schema({
  name: String,
  slug: String,
  category: String,
  sku: String,
  description: String,
  location: {
    search: String,
    coordinates: {
      lat: Number,
      lng: Number,
    },
  },
  price: Number,
  tags: [String],
  inSeason: Boolean,
  available: Boolean,
  requiresWaiver: Boolean,
  maximumGuests: Number,
  notes: String,
  packagesSold: Number,
})

const Vacation = mongoose.model('Vacation', vacationSchema)
module.exports = Vacation

This code declares the properties that make up our vacation model, and the types of those properties. You’ll see there are several string properties, some numeric properties, two Boolean properties, and an array of strings (denoted by [String]). At this point, we can also define methods on our schema. Each product has a stock keeping unit (SKU); even though we don’t think about vacations being “stock items,” the concept of an SKU is pretty standard for accounting, even when tangible goods aren’t being sold.

Once we have the schema, we create a model using mongoose.model: at this point, Vacation is very much like a class in traditional object-oriented programming. Note that we have to define our methods before we create our model.

We are exporting the Vacation model object created by Mongoose. While we could use this model directly, that would be undermining our effort to provide a database abstraction layer. So we will choose to import it only from the db.js file and let the rest of our application use its methods. Add the Vacation model to db.js:

const Vacation = require('./models/vacation')

All of our structures are now defined, but our database isn’t very interesting because there’s nothing actually in it. Let’s make it useful by seeding it with some data.

Seeding Initial Data

We don’t yet have any vacation packages in our database, so we’ll add some to get us started. Eventually, you may want to create a way to manage products, but for the purposes of this book, we’re just going to do it in code (ch13/00-mongodb/db.js in the companion repo):

Vacation.find((err, vacations) => {
  if(err) return console.error(err)
  if(vacations.length) return

  new Vacation({
    name: 'Hood River Day Trip',
    slug: 'hood-river-day-trip',
    category: 'Day Trip',
    sku: 'HR199',
    description: 'Spend a day sailing on the Columbia and ' +
      'enjoying craft beers in Hood River!',
    location: {
      search: 'Hood River, Oregon, USA',
    },
    price: 99.95,
    tags: ['day trip', 'hood river', 'sailing', 'windsurfing', 'breweries'],
    inSeason: true,
    maximumGuests: 16,
    available: true,
    packagesSold: 0,
  }).save()

  new Vacation({
    name: 'Oregon Coast Getaway',
    slug: 'oregon-coast-getaway',
    category: 'Weekend Getaway',
    sku: 'OC39',
    description: 'Enjoy the ocean air and quaint coastal towns!',
    location: {
      search: 'Cannon Beach, Oregon, USA',
    },
    price: 269.95,
    tags: ['weekend getaway', 'oregon coast', 'beachcombing'],
    inSeason: false,
    maximumGuests: 8,
    available: true,
    packagesSold: 0,
  }).save()

  new Vacation({
      name: 'Rock Climbing in Bend',
      slug: 'rock-climbing-in-bend',
      category: 'Adventure',
      sku: 'B99',
      description: 'Experience the thrill of climbing in the high desert.',
      location: {
        search: 'Bend, Oregon, USA',
      },
      price: 289.95,
      tags: ['weekend getaway', 'bend', 'high desert', 'rock climbing'],
      inSeason: true,
      requiresWaiver: true,
      maximumGuests: 4,
      available: false,
      packagesSold: 0,
      notes: 'The tour guide is currently recovering from a skiing accident.',
  }).save()
})

There are two Mongoose methods being used here. The first, find, does just what it says. In this case, it’s finding all instances of Vacation in the database and invoking the callback with that list. We’re doing that because we don’t want to keep re-adding our seed vacations: if there are already vacations in the database, it’s been seeded, and we can go on our merry way. The first time this executes, though, find will return an empty list, so we proceed to create two vacations and then call the save method on them, which saves these new objects to the database.

Now that data is in the database, it’s time to get it back out!

Retrieving Data

We’ve already seen the find method, which is what we’ll use to display a list of vacations. However, this time we’re going to pass an option to find that will filter the data. Specifically, we want to display only vacations that are currently available.

Create a view for the products page, views/vacations.handlebars:

<h1>Vacations</h1>
{{#each vacations}}
  <div class="vacation">
    <h3>{{name}}</h3>
    <p>{{description}}</p>
    {{#if inSeason}}
      <span class="price">{{price}}</span>
      <a href="/cart/add?sku={{sku}}" class="btn btn-default">Buy Now!</a>
    {{else}}
      <span class="outOfSeason">We're sorry, this vacation is currently
      not in season.
      {{! The "notify me when this vacation is in season"
          page will be our next task. }}
      <a href="/notify-me-when-in-season?sku={{sku}}">Notify me when
      this vacation is in season.</a>
    {{/if}}
  </div>
{{/each}}

Now we can create route handlers that hook it all up. In lib/handlers.js (don’t forget to import ../db), we create the handler:

exports.listVacations = async (req, res) => {
  const vacations = await db.getVacations({ available: true })
  const context = {
    vacations: vacations.map(vacation => ({
      sku: vacation.sku,
      name: vacation.name,
      description: vacation.description,
      price: '$' + vacation.price.toFixed(2),
      inSeason: vacation.inSeason,
    }))
  }
  res.render('vacations', context)
}

We add a route that calls the handler in meadowlark.js:

app.get('/vacations', handlers.listVacations)

If you run this example, you’ll see only the one vacation from our dummy database implementation. That’s because we’ve initialized the database and seeded its data, but we haven’t replaced the dummy implementation with a real one. So let’s do that now. Open db.js and modify getVacations:

module.exports = {
  getVacations: async (options = {}) => Vacation.find(options),
  addVacationInSeasonListener: async (email, sku) => {
    //...
  },
}

That was easy! A one-liner. Partially this is because Mongoose is doing a lot of the heavy lifting for us, and the way we’ve designed our API is similar to the way Mongoose works. When we adapt this later to PostgreSQL, you’ll see we have to do a little more work.

Most of this should be looking pretty familiar, but there might be some things that surprise you. For instance, how we’re handling the view context for the vacation listing might seem odd. Why did we map the products returned from the database to a nearly identical object? One reason is that we want to display the price in a neatly formatted way, so we have to convert it to a formatted string.

We could have saved some typing by doing this:

const context = {
  vacations: products.map(vacations => {
    vacation.price = '$' + vacation.price.toFixed(2)
    return vacation
  })
}

That would certainly save us a few lines of code, but in my experience, there are good reasons not to pass unmapped database objects directly to views. The view gets a bunch of properties it may not need, possibly in formats that are incompatible with it. Our example is pretty simple so far, but once it starts to get more complicated, you’ll probably want to do even more customization of the data that’s passed to a view. It also makes it easy to accidentally expose confidential information or information that could compromise the security of your website. For these reasons, I recommend mapping the data that’s returned from the database and passing only what’s needed onto the view (transforming as necessary, as we did with price).

We’ve come a long way at this point. We’re successfully using a database to store information about our vacations. But databases wouldn’t be very useful if we couldn’t update them. Let’s turn our attention to that aspect of interfacing with databases.

Adding Data

We’ve already seen how we can add data (we added data when we seeded the vacation collection) and how we can update data (we update the count of packages sold when we book a vacation), but let’s take a look at a slightly more involved scenario that highlights the flexibility of document databases.

When a vacation is out of season, we display a link that invites the customer to be notified when the vacation is in season again. Let’s hook up that functionality. First, we create the schema and model (models/vacationInSeasonListener.js):

const mongoose = require('mongoose')

const vacationInSeasonListenerSchema = mongoose.Schema({
  email: String,
  skus: [String],
})
const VacationInSeasonListener = mongoose.model('VacationInSeasonListener',
  vacationInSeasonListenerSchema)

module.exports = VacationInSeasonListener

Then we’ll create our view, views/notify-me-when-in-season.handlebars:

<div class="formContainer">
  <form class="form-horizontal newsletterForm" role="form"
      action="/notify-me-when-in-season" method="POST">
    <input type="hidden" name="sku" value="{{sku}}">
    <div class="form-group">
      <label for="fieldEmail" class="col-sm-2 control-label">Email</label>
      <div class="col-sm-4">
        <input type="email" class="form-control" required
          id="fieldEmail" name="email">
      </div>
    </div>
    <div class="form-group">
      <div class="col-sm-offset-2 col-sm-4">
        <button type="submit" class="btn btn-default">Submit</button>
      </div>
    </div>
  </form>
</div>

Then the route handlers:

exports.notifyWhenInSeasonForm = (req, res) =>
  res.render('notify-me-when-in-season', { sku: req.query.sku })

exports.notifyWhenInSeasonProcess = (req, res) => {
  const { email, sku } = req.body
  await db.addVacationInSeasonListener(email, sku)
  return res.redirect(303, '/vacations')
}

Finally, we add a real implementation to db.js:

const VacationInSeasonListener = require('./models/vacationInSeasonListener')

module.exports = {
  getVacations: async (options = {}) => Vacation.find(options),
  addVacationInSeasonListener: async (email, sku) => {
    await VacationInSeasonListener.updateOne(
      { email },
      { $push: { skus: sku } },
      { upsert: true }
    )
  },
}

What magic is this? How can we “update” a record in the VacationInSeasonListener collection before it even exists? The answer lies in a Mongoose convenience called an upsert (a portmanteau of “update” and “insert”). Basically, if a record with the given email address doesn’t exist, it will be created. If a record does exist, it will be updated. Then we use the magic variable $push to indicate that we want to add a value to an array.

We’ve certainly covered the important bases by now! We learned how to connect to a MongoDB instance, seed it with data, read that data out, and write updates to it! However, you may prefer to use an RDBMS, so let’s shift gears and see how we can do the same thing with PostgreSQL instead.

PostgreSQL

Object databases like MongoDB are great and are generally quicker to get started with, but if you’re trying to build a robust application, you may put as much work—or more—into structuring your object databases as you would planning out a traditional relational database. Furthermore, you may already have experience with relational databases, or you might have an existing relational database you want to connect with.

Fortunately, there is robust support for every major relational database in the JavaScript ecosystem, and if you want or need to use a relational database, you shouldn’t have any problem.

Let’s take our vacation database and reimplement it using a relational database. For this example, we’ll use PostgreSQL, a popular and sophisticated open source relational database. The techniques and principles we’ll use will be similar for any relational database.

Similar to the ODM we used for MongoDB, there are object-relational mapping (ORM) tools available for relational databases. However, since most readers interested in this topic are probably already familiar with relational databases and SQL, we’ll use a Node PostgreSQL client directly.

Like MongoDB, we’ll use a free online PostgreSQL service. Of course, if you’re comfortable installing and configuring your own PostgreSQL database, you are welcome to do that as well. All that will change is the connection string. If you do use your own PostgreSQL instance, make sure you’re using 9.4 or later, because we will be using the JSON data type, which was introduced in 9.4 (as I write this, I am using 11.3).

There are many options for online PostgreSQL; for this example, I’ll be using ElephantSQL. Getting started couldn’t be simpler: create an account (you can use your GitHub account to log in), and click Create New Instance. All you have to do is give it a name (for example, “meadowlark”) and select a plan (you can use their free plan). You’ll also specify a region (try to pick the one closest to you). Once you’re all set up, you’ll find a Details section that lists information about your instance. Copy the URL (connection string), which includes the username, password, and instance location all in one convenient string.

Put that string in your .credentials.development.json file:

"postgres": {
  "connectionString": "your_dev_connection_string"
}

One difference between object databases and RDBMSs is that you typically do more up-front work to define the schema of an RDBMS and use data definition SQL to create the schema before adding or retrieving data. In keeping with this paradigm, we’ll do that as a separate step instead of letting our ODM or ORM handle it, as we did with MongoDB.

We could create SQL scripts and use a command-line client to execute the data definition scripts that will create our tables, or we could do this work in JavaScript with the PostgreSQL client API, but in a separate step that’s done only once. Since this is a book about Node and Express, we’ll do the latter.

First, we’ll have to install the pg client library (npm install pg). Then create db-init.js, which will be run only to initialize our database and is distinct from our db.js file, which is used every time the server starts up (ch13/01-postgres/db.js in the companion repo):

const { credentials } = require('./config')

const { Client } = require('pg')
const { connectionString } = credentials.postgres
const client = new Client({ connectionString })

const createScript = `
  CREATE TABLE IF NOT EXISTS vacations (
    name varchar(200) NOT NULL,
    slug varchar(200) NOT NULL UNIQUE,
    category varchar(50),
    sku varchar(20),
    description text,
    location_search varchar(100) NOT NULL,
    location_lat double precision,
    location_lng double precision,
    price money,
    tags jsonb,
    in_season boolean,
    available boolean,
    requires_waiver boolean,
    maximum_guests integer,
    notes text,
    packages_sold integer
  );
`

const getVacationCount = async client => {
  const { rows } = await client.query('SELECT COUNT(*) FROM VACATIONS')
  return Number(rows[0].count)
}

const seedVacations = async client => {
  const sql = `
    INSERT INTO vacations(
      name,
      slug,
      category,
      sku,
      description,
      location_search,
      price,
      tags,
      in_season,
      available,
      requires_waiver,
      maximum_guests,
      notes,
      packages_sold
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
  `
  await client.query(sql, [
    'Hood River Day Trip',
    'hood-river-day-trip',
    'Day Trip',
    'HR199',
    'Spend a day sailing on the Columbia and enjoying craft beers in Hood River!',
    'Hood River, Oregon, USA',
    99.95,
    `["day trip", "hood river", "sailing", "windsurfing", "breweries"]`,
    true,
    true,
    false,
    16,
    null,
    0,
  ])
  // we can use the same pattern to insert other vacation data here...
}

client.connect().then(async () => {
  try {
    console.log('creating database schema')
    await client.query(createScript)
    const vacationCount = await getVacationCount(client)
    if(vacationCount === 0) {
      console.log('seeding vacations')
      await seedVacations(client)
    }
  } catch(err) {
    console.log('ERROR: could not initialize database')
    console.log(err.message)
  } finally {
    client.end()
  }
})

Let’s start at the bottom of this file. We take our database client (client) and call connect() on it, which establishes a database connection and returns a promise. When that promise resolves, we can take actions against the database.

The first thing we do is invoke client.query(createScript), which will create our vacations table (also known as a relation). If we look at createScript, we’ll see this is data definition SQL. It’s beyond the scope of this book to delve into SQL, but if you’re reading this section, I assume you have at least a passing understanding of SQL. One thing you may note is that we use snake_case to name our fields instead of camelCase. That is, what was “inSeason” has become “in_season.” While it is possible to use camelCase to name structures in PostgreSQL, you have to quote any identifiers with capital letters, which ends up being more trouble than it’s worth. We’ll come back to that a little later.

You’ll see we’re already having to put more thought into our schema. How long can a vacation name be? (We’re arbitrarily capping it at 200 characters here.) How long can category names and the SKU be? Notice we’re using PostgreSQL’s money type for the price and making the slug be our primary key (instead of adding a separate ID).

If you’re already familiar with relational databases, there won’t be anything surprising about this simple schema. However, the way we’ve handled “tags” might have jumped out at you.

In traditional database design, we would probably create a new table to relate vacations to tags (this is called normalization). And we could do that here. But here is where we might decide to strike some compromises between traditional relational database design and doing things in the “JavaScript way.” If we went with two tables (vacations and vacation_tags, for example), we’d have to query data from both tables to create a single object that contains all the information about a vacation, as we had in our MongoDB example. And there may be performance reasons for that extra complexity, but let’s assume there isn’t, and we just want to be able to quickly determine the tags for a particular vacation. We could make this a text field and separate our tags with commas, but then we would have to parse out our tags, and PostgreSQL gives us a better way in JSON data types. We’ll see shortly that by specifying this as JSON (jsonb, a binary representation that’s usually higher performance), we can store this as a JavaScript array, and a JavaScript array comes out, just as we had in MongoDB.

Finally, we insert our seed data into the database by using the same basic concept as before: if the vacations table is empty, we add some initial data; otherwise, we assume we’ve already done that.

You’ll note that inserting our data is a little more unwieldy than it was with MongoDB. There are ways to solve this problem, but for this example, I want to be explicit about the use of SQL. We could write a function to make insert statements more naturally, or we could use an ORM (more on this later). But for now, the SQL gets the job done, and it should be comfortable for anyone who already knows SQL.

Note that although this script is designed to be run only once to initialize and seed our database, we’ve written it in a way that it’s safe to run multiple times. We included the IF NOT EXISTS option, and we check to see whether the vacations table is empty before adding seed data.

We can now run the script to initialize our database:

$ node db-init.js

Now that we have our database set up, we can write some code to use it in our website.

Database servers can typically handle only a limited number of connections at a time, so web servers usually implement a strategy called connection pooling to balance the overhead of establishing a connection with the danger of leaving connections open too long and choking the server. Fortunately, the details of this are handled for you by the PostgreSQL Node client.

We’ll take a slightly different strategy with our db.js file this time. Instead of a file we just require to establish the database connection, it will return an API that we write that handles the details of communicating with the database.

We also have a decision to make about our vacation model. Recall that when we created our model, we used snake_case for our database schema, but all of our JavaScript code uses camelCase. Broadly speaking, we have three options here:

• Refactor our schema to use camelCase. This will make our SQL uglier because we have to remember to quote our property names correctly.

• Use snake_case in our JavaScript. This is less than ideal because we like standards (right?).

• Use snake_case on the database side, and translate to camelCase on the JavaScript side. This is more work that we have to do, but it keeps our SQL and our JavaScript pristine.

Fortunately, the third option can be done automatically. We could write our own function to do that translation, but we’ll rely on a popular utility library called Lodash, which makes it extremely easy. Just run npm install lodash to install it.

Right now, our database needs are very modest. All we need to do is fetch all available vacation packages, so our db.js file will look like this (ch13/01-postgres/db.js in the companion repo):

const { Pool } = require('pg')
const _ = require('lodash')

const { credentials } = require('./config')

const { connectionString } = credentials.postgres
const pool = new Pool({ connectionString })

module.exports = {
  getVacations: async () => {
    const { rows } = await pool.query('SELECT * FROM VACATIONS')
    return rows.map(row => {
      const vacation = _.mapKeys(row, (v, k) => _.camelCase(k))
      vacation.price = parseFloat(vacation.price.replace(/^$/, ''))
      vacation.location = {
        search: vacation.locationSearch,
        coordinates: {
          lat: vacation.locationLat,
          lng: vacation.locationLng,
        },
      }
      return vacation
    })
  }
}

Short and sweet! We’re exporting a single method called getVacations that does as advertised. It also uses Lodash’s mapKeys and camelCase functions to convert our database properties to camelCase.

One thing to note is that we have to handle the price attribute carefully. PostgreSQL’s money type is converted to an already-formatted string by the pg library. And for good reason: as we’ve already discussed, JavaScript has only recently added support for arbitrary precision numeric types (BigInt), but there isn’t yet a PostgreSQL adapter that takes advantage of that (and it might not be the most efficient data type in any event). We could change our database schema to use a numeric type instead of the money type, but we shouldn’t let our frontend choices drive our schema. We could also deal with the preformatted strings that are being returned from pg, but then we would have to change all of our existing code, which is relying on price being a number. Furthermore, that approach would undermine our ability to do numeric calculations on the frontend (such as summing the prices of the items in your cart). For all of these reasons, we’re opting to parse the string to a number when we retrieve it from the database.

We also take our location information—which is “flat” in the table—and turn it into a more JavaScript-like structure. We’re doing this only to achieve parity with our MongoDB example; we could use the data structured as it is (or modify our MongoDB example to have a flat structure).

The last thing we need to learn to do with PostgreSQL is to update data, so let’s fill in the “vacation in season” listener feature.

Adding Data

As with the MongoDB example, we’ll use our “vacation in season” listener example. We’ll start by adding the following data definition to the createScript string in db-init.js:

CREATE TABLE IF NOT EXISTS vacation_in_season_listeners (
  email varchar(200) NOT NULL,
  sku varchar(20) NOT NULL,
  PRIMARY KEY (email, sku)
);

Remember that we took care to write db-init.js in a nondestructive fashion so we could run it at any time. So we can just run it again to create the vacation_in_season_listeners table.

Now we can modify db.js to include a method to update this table:

module.exports = {
  //...
  addVacationInSeasonListener: async (email, sku) => {
    await pool.query(
      'INSERT INTO vacation_in_season_listeners (email, sku) ' +
      'VALUES ($1, $2) ' +
      'ON CONFLICT DO NOTHING',
      [email, sku]
    )
  },
}

PostgreSQL’s ON CONFLICT clause essentially enables upserts. In this case, if the exact combination of email and SKU is already present, the user has already registered to be notified, so we don’t need to do anything. If we had other columns in this table (such as the date of last registration), we might want to use a more sophisticated ON CONFLICT clause (see the PostgreSQL INSERT documentation for more information). Note also that this behavior is dependent on the way we defined the table. We made email and SKU a composite primary key, meaning that there can’t be any duplicates, which in turn necessitated the ON CONFLICT clause (otherwise, the INSERT command would result in an error the second time a user tried to register for a notification on the same vacation).

Now we’ve seen a complete example of hooking up two types of databases, an object database and an RDBMS. It should be clear that the function of the database is the same: storing, retrieving, and updating data in a consistent and scalable fashion. Because the function is the same, we were able to create an abstraction layer so we could choose a different database technology. The last thing we might need a database for is for persistent session storage, which we hinted at in Chapter 9.