Connect an app

• PostgreSQL node-postgres Driver

The PostgreSQL node-postgres driver is the official Node.js driver for PostgreSQL which can be used to connect with YugabyteDB YSQL API. Because YugabyteDB YSQL API has is fully compatible with PostgreSQL node-postgres (pg) driver, it allows Node.js programmers to connect to the YugabyteDB database to execute DMLs and DDLs using the node-postgres APIs.

CRUD operations

Learn how to establish a connection to YugabyteDB database and begin basic CRUD operations using the steps in Build an Application in the Quick Start section.

The following sections break down the quick start example to demonstrate how to perform common tasks required for Node.js application development using the PostgreSQL node-postgres driver.

Step 1: Install the driver dependency and async utility

Download and install the node-postgres driver using the following command (you need to have Node.JS installed on your system):

npm install pg

To install the async utility, run the following command:

$ npm install --save async

After this, you can start using the driver in your code.

Step 2: Set up the database connection

Before connecting to the YugabyteDB cluster, first import the pg package.

  const pg = require('pg');

Create a client to connect to the cluster using a connection string.

const connectionString = "postgresql://user:password@localhost:port/database"
const client = new Client(connectionString);
client.connect()

Use SSL

The following is an example connection string for connecting to a YugabyteDB cluster with SSL enabled.

const connectionString = "postgresql://user:password@localhost:port/database?ssl=true&sslmode=verify-full&sslrootcert=~/.postgresql/root.crt"
const client = new Client(connectionString);
client.connect()

For other ways to provide connection and SSL-related details, refer to the node-postgres documentation.

If you have created a cluster on YugabyteDB Managed, follow the steps to obtain the cluster connection parameters and SSL Root certificate.

Refer to Configure SSL/TLS for more information on node-postgresql default and supported SSL modes, and examples for setting up your connection strings when using SSL.

Step 3: Query the YugabyteDB cluster from your application

Create a new JavaScript file called yb-ysql-helloworld-ssl.js in your project directory. Copy the following code, which sets up tables and queries the table contents. Replace the connection string yburl parameters with the cluster credentials and SSL certificate if required.

var pg = require('pg');
const async = require('async');
const assert = require('assert');

var connectionString = "postgres://postgres@localhost:5433/postgres";
var client = new pg.Client(connectionString);

async.series([
  function connect(next) {
    client.connect(next);
  },
  function createTable(next) {
    // The create table statement.
    const create_table = 'CREATE TABLE employee (id int PRIMARY KEY, ' +
                                                 'name varchar, ' +
                                                 'age int, ' +
                                                 'language varchar);';
    // Create the table.
    console.log('Creating table employee');
    client.query(create_table, next);
  },
  function insert(next) {
    // Create a variable with the insert statement.
    const insert = "INSERT INTO employee (id, name, age, language) " +
                                        "VALUES (1, 'John', 35, 'NodeJS');";
    // Insert a row with the employee data.
    console.log('Inserting row with: %s', insert)
    client.query(insert, next);
  },
  function select(next) {
    // Query the row for employee id 1 and print the results to the console.
    const select = 'SELECT name, age, language FROM employee WHERE id = 1;';
    client.query(select, function (err, result) {
      if (err) return next(err);
      var row = result.rows[0];
      console.log('Query for id=1 returned: name=%s, age=%d, language=%s',
                                            row.name, row.age, row.language);
      next();
    });
  }
], function (err) {
  if (err) {
    console.error('There was an error', err.message, err.stack);
  }
  console.log('Shutting down');
  client.end();
});

When you run the application using the command node yb-ysql-helloworld-ssl.js, you should see output similar to the following:

Creating table employee
Inserting row with: INSERT INTO employee (id, name, age, language) VALUES (1, 'John', 35, 'NodeJS');
Query for id=1 returned: name=John, age=35, language=NodeJS
Shutting down

If there is no output or you get an error, verify the parameters included in the connection string.

After completing these steps, you should have a working Node.JS app that uses the PostgreSQL node.js driver to connect to your cluster, set up tables, run queries, and print out results.

Next steps

Learn how to build Node.js applications using Sequelize ORM.