Connect an application
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 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
The following sections demonstrate how to perform common tasks required for Node.js application development using the PostgreSQL node-postgres driver.
To start building your application, make sure you have met the prerequisites.
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
You can start using the driver in your code.
Step 2: Set up the database connection
The following table describes the connection parameters required to connect.
| Parameter | Description | Default |
|---|---|---|
| host | Hostname of the YugabyteDB instance | localhost |
| port | Listen port for YSQL | 5433 |
| database | Database user | yugabyte |
| user | User connecting to the database | yugabyte |
| password | User password | yugabyte |
Before connecting to the YugabyteDB cluster, 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 table describes the connection parameters required to connect using TLS/SSL.
| Parameter | Description |
|---|---|
| sslmode | SSL mode |
| sslrootcert | path to the root certificate on your computer |
By default, the driver supports the require SSL mode, in which a root CA certificate isn't required to be configured. This enables SSL communication between the Node.js client and YugabyteDB servers.
const config = {
user: ' ',
database: ' ',
host: ' ',
password: ' ',
port: 5433,
// this object will be passed to the TLSSocket constructor
ssl: {
rejectUnauthorized: false,
},
}
If you created a cluster on YugabyteDB Aeon, use the cluster credentials and download the SSL Root certificate.
Refer to Configure SSL/TLS for more information on node-postgresql default and supported SSL modes, and other examples for setting up your connection strings when using SSL.
Step 3: Write your application
Create a new JavaScript file called QuickStartApp.js in your project directory.
Copy the following sample code to set up tables and query the table contents. Replace the connection string parameters with the cluster credentials 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();
});
Run the application QuickStartApp.js using the following command:
node QuickStartApp.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
Step 4: Write your application with SSL (Optional)
Copy the following sample code to QuickStartApp.js and replace the values for the config object as appropriate for your cluster if you're using SSL.
var pg = require('pg');
const async = require('async');
const assert = require('assert');
const fs = require('fs');
const config = {
user: 'admin',
database: 'yugabyte',
host: '22420e3a-768b-43da-8dcb-xxxxxx.aws.yugabyte.cloud',
password: 'xxxxxx',
port: 5433,
// this object will be passed to the TLSSocket constructor
ssl: {
rejectUnauthorized: false,
},
}
var client = new pg.Client(config);
async.series([
function connect(next) {
client.connect(next);
},
function createTable(next) {
// The create table statement.
const create_table = 'CREATE TABLE IF NOT EXISTS 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 (2, 'John', 35, 'NodeJS + SSL');";
// 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 2 and print the results to the console.
const select = 'SELECT name, age, language FROM employee WHERE id = 2;';
client.query(select, function (err, result) {
if (err) return next(err);
var row = result.rows[0];
console.log('Query for id=2 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();
});
Run the application QuickStartApp.js using the following command:
node QuickStartApp.js
You should see output similar to the following if you're using SSL:
Creating table employee
Inserting row with: INSERT INTO employee (id, name, age, language) VALUES (2, 'John', 35, 'NodeJS + SSL');
Query for id=2 returned: name=John, age=35, language=NodeJS + SSL
Shutting down
If there is no output or you get an error, verify the parameters included in the config object.