Build a Node.js App with CockroachDB and the Node.js pg Driver

Warning:
Cockroach Labs will stop providing Assistance Support for this version on November 12, 2021. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

This tutorial shows you how build a simple Node.js application with CockroachDB and the Node.js pg driver.

We have tested the Node.js pg driver enough to claim beta-level support. If you encounter problems, please open an issue with details to help us make progress toward full support.

Step 1. Start CockroachDB

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --empty
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster.

  3. Take note of the (sql/tcp) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (console) http://127.0.0.1:61009
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require    
    

    You will use it in your application code later.

Step 2. Create a database

  1. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    icon/buttons/copy
    > CREATE USER <username> WITH PASSWORD <password>;
    

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    icon/buttons/copy
    > GRANT ALL ON DATABASE bank TO <username>;
    

Step 3. Install client driver

To let your application communicate with CockroachDB, install the Node.js pg driver:

icon/buttons/copy
$ npm install pg

Step 4. Get the code

Download the sample code directly, or clone the code's GitHub repository.

Step 5. Update the connection parameters

Open the app.js file, and edit the connection configuration parameters:

  • Replace the value for user with the user you created earlier.
  • Replace the value for password with the password you created for your user.
  • Replace the value for port with the port to your cluster.
  • At the top of the file, uncomment the const fs = require('fs'); line.

    This line imports the fs Node module, which enables you to read in the CA cert that you downloaded from the CockroachCloud Console.

  • Replace the value for user with the user you created earlier.

  • Replace the value for password with the password you created for your user.

  • Replace the value for host with the name of the CockroachCloud Free host (e.g., host: 'free-tier.gcp-us-central1.cockroachlabs.cloud').

  • Replace the value for port with the port to your cluster.

  • Replace the value for database with the database that you created earlier, suffixed with the name of the cluster (e.g., database: '{cluster_name}.bank').

  • Remove the existing ssl object and its contents.

  • Uncomment the ssl object with the ca key-value pair, and edit the fs.readFileSync('/certs/ca.crt').toString() call to use the path to the cc-ca.crt file that you downloaded from the CockroachCloud Console.

Step 6. Run the code

The sample code creates a table, inserts some rows, and then reads and updates values as an atomic transaction.

Here are the contents of app.js:

icon/buttons/copy
const parse = require("pg-connection-string").parse;
const { Pool } = require("pg");
const prompt = require("prompt");
const { v4: uuidv4 } = require("uuid");

var accountValues = Array(3);

// Wrapper for a transaction.  This automatically re-calls the operation with
// the client as an argument as long as the database server asks for
// the transaction to be retried.
async function retryTxn(n, max, client, operation, callback) {
  await client.query("BEGIN;");
  while (true) {
    n++;
    if (n === max) {
      throw new Error("Max retry count reached.");
    }
    try {
      await operation(client, callback);
      await client.query("COMMIT;");
      return;
    } catch (err) {
      if (err.code !== "40001") {
        return callback(err);
      } else {
        console.log("Transaction failed. Retrying transaction.");
        console.log(err.message);
        await client.query("ROLLBACK;", () => {
          console.log("Rolling back transaction.");
        });
        await new Promise((r) => setTimeout(r, 2 ** n * 1000));
      }
    }
  }
}

// This function is called within the first transaction. It inserts some initial values into the "accounts" table.
async function initTable(client, callback) {
  let i = 0;
  while (i < accountValues.length) {
    accountValues[i] = await uuidv4();
    i++;
  }

  const insertStatement =
    "INSERT INTO accounts (id, balance) VALUES ($1, 1000), ($2, 250), ($3, 0);";
  await client.query(insertStatement, accountValues, callback);

  const selectBalanceStatement = "SELECT id, balance FROM accounts;";
  await client.query(selectBalanceStatement, callback);
}

// This function updates the values of two rows, simulating a "transfer" of funds.
async function transferFunds(client, callback) {
  const from = accountValues[0];
  const to = accountValues[1];
  const amount = 100;
  const selectFromBalanceStatement =
    "SELECT balance FROM accounts WHERE id = $1;";
  const selectFromValues = [from];
  await client.query(
    selectFromBalanceStatement,
    selectFromValues,
    (err, res) => {
      if (err) {
        return callback(err);
      } else if (res.rows.length === 0) {
        console.log("account not found in table");
        return callback(err);
      }
      var acctBal = res.rows[0].balance;
      if (acctBal < amount) {
        return callback(new Error("insufficient funds"));
      }
    }
  );

  const updateFromBalanceStatement =
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2;";
  const updateFromValues = [amount, from];
  await client.query(updateFromBalanceStatement, updateFromValues, callback);

  const updateToBalanceStatement =
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2;";
  const updateToValues = [amount, to];
  await client.query(updateToBalanceStatement, updateToValues, callback);

  const selectBalanceStatement = "SELECT id, balance FROM accounts;";
  await client.query(selectBalanceStatement, callback);
}

// This function deletes the third row in the accounts table.
async function deleteAccounts(client, callback) {
  const deleteStatement = "DELETE FROM accounts WHERE id = $1;";
  await client.query(deleteStatement, [accountValues[2]], callback);

  const selectBalanceStatement = "SELECT id, balance FROM accounts;";
  await client.query(selectBalanceStatement, callback);
}

// Run the transactions in the connection pool
(async () => {
  prompt.start();
  const URI = await prompt.get("connectionString");
  var connectionString;
  // Expand $env:appdata environment variable in Windows connection string
  if (URI.connectionString.includes("env:appdata")) {
    connectionString = await URI.connectionString.replace(
      "$env:appdata",
      process.env.APPDATA
    );
  }
  // Expand $HOME environment variable in UNIX connection string
  else if (URI.connectionString.includes("HOME")){
    connectionString = await URI.connectionString.replace(
      "$HOME",
      process.env.HOME
    );
  }
  var config = parse(connectionString);
  config.port = 26257;
  config.database = "bank";
  const pool = new Pool(config);

  // Connect to database
  const client = await pool.connect();

  // Callback
  function cb(err, res) {
    if (err) throw err;

    if (res.rows.length > 0) {
      console.log("New account balances:");
      res.rows.forEach((row) => {
        console.log(row);
      });
    }
  }

  // Initialize table in transaction retry wrapper
  console.log("Initializing accounts table...");
  await retryTxn(0, 15, client, initTable, cb);

  // Transfer funds in transaction retry wrapper
  console.log("Transferring funds...");
  await retryTxn(0, 15, client, transferFunds, cb);

  // Delete a row in transaction retry wrapper
  console.log("Deleting a row...");
  await retryTxn(0, 15, client, deleteAccounts, cb);

  // Exit program
  process.exit();
})().catch((err) => console.log(err.stack));

Note that all of the database operations are wrapped in the retryTxn function. This function attempts to commit statements in the context of an explicit transaction. If a retry error is thrown, the wrapper will retry committing the transaction, with exponential backoff, until the maximum number of retries is reached (by default, 15).

To run the code:

icon/buttons/copy
$ node app.js

The output should be:

Initializing table...
New account balances:
{ id: '1', balance: '1000' }
{ id: '2', balance: '250' }
Transferring funds...
New account balances:
{ id: '1', balance: '900' }
{ id: '2', balance: '350' }

What's next?

Read more about using the Node.js pg driver.

You might also be interested in the following pages:

YesYes NoNo