Build a Hello World App with CockroachDB and the node-postgres Driver

This tutorial shows you how build a simple Hello World Node.js application with CockroachDB and the node-postgres driver.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachCloud Free (beta). The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachCloud account.
  2. Log in to your CockroachCloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select CockroachCloud Free.

    Note:

    This cluster will be free forever.

  5. (Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.

  6. (Optional) Select a region in the Additional configuration section. For optimal performance, select the cloud provider region closest to the region in which you are running your application.

  7. Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:

    icon/buttons/copy

    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
    

    Your cert file will be downloaded to %APPDATA%/.postgres/root.crt.

  2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

    Warning:

    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

    icon/buttons/copy

    cockroach sql --url 'postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'
    

    icon/buttons/copy
    cockroach sql --url 'postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'
    
    icon/buttons/copy
    cockroach sql --url "postgresql://<user>@<free-tier-host>.<region>.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>"
    

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

    icon/buttons/copy
    $ cockroach demo \
    --no-example-database
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

    Note:

    If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

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

    # Connection parameters:
    #   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
    #   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
    #   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
    

Step 2. Get the code

Clone the code's GitHub repo:

icon/buttons/copy
$ git clone https://github.com/cockroachlabs/hello-world-node-postgres/

The app.js file contains all of the code for the sample Hello World app:

icon/buttons/copy
const parse = require("pg-connection-string").parse;
const { Client } = require("pg");
const prompt = require("prompt");

(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 = 'defaultdb';
  const client = new Client(config);

  // Connect to database
  try {
    await client.connect();
    console.log("Hey! You successfully connected to your CockroachDB cluster.")
  } catch (err) {
    console.log(`error connecting: ${err}`)
  }

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

This program does the following:

  1. Attempts to connect to a running cluster, given a connection string.
  2. Prints a message to the terminal about the connection status.

Step 3. Run the code

Initialize and run the app:

icon/buttons/copy
$ npm install
icon/buttons/copy
$ node app.js

The program will prompt you for a connection string to the database:

prompt: connectionString:

Enter the (sql/unix) connection URL provided in the demo cluster's SQL shell welcome text.

Enter the connection string provided in the Connection info window of the CockroachCloud Console.

Note:

You need to provide a SQL user password in order to securely connect to a CockroachCloud cluster. The connection string should have a placeholder for the password (<ENTER-PASSWORD>).

After entering the connection string, the program will execute.

The output should look like this:

Hey! You successfully connected to your CockroachDB cluster.

See also

You might also be interested in the following pages:

YesYes NoNo