Node.js Drivers YSQL
For production, use the latest stable version (v2024.1).
The PostgreSQL node-postgres driver is the official Node.js driver for PostgreSQL. The YSQL API is fully compatible with the PostgreSQL node-postgres (pg) driver, so you can connect to the YugabyteDB database to execute DMLs and DDLs using the node-postgres APIs. The driver supports the SCRAM-SHA-256 authentication method.
For details on installing and using node-postgres, see the node-postgres documentation.
For a tutorial on building a Node.js application with node-postgres, see Connect an application.
Fundamentals
Learn how to perform common tasks required for Node.js application development using the PostgreSQL node-postgres driver.
Install the driver dependency and async utility
The PostgreSQL Node.js driver is available as a Node module, and you can install the driver using the following command:
npm install pg
To install the async utility, run the following command:
$ npm install --save async
Connect to YugabyteDB database
Node.js applications can connect to and query the YugabyteDB database using the pg
module. The pg.Client
is the client we can create for the application by passing the connection string as parameter in the constructor. The Client.connect()
can be used to connect with database with that connection string. Use new Client(<connection-string>)
to create a new client with the connection string.
The following table describes the connection parameters required to connect to the YugabyteDB database.
Parameters | Description | Default |
---|---|---|
hostname | hostname of the YugabyteDB instance | localhost |
port | Listen port for YSQL | 5433 |
database | database name | yugabyte |
user | database user | yugabyte |
password | user password | yugabyte |
The following is an example connection string for connecting to YugabyteDB.
var connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte";
var client = new Client(connectionString);
Create tables
Tables can be created in YugabyteDB by passing the CREATE TABLE DDL statement as a parameter to the client.query(<SQL_Statement>)
function.
CREATE TABLE IF NOT EXISTS employee (id int primary key, name varchar, age int, language text)
var connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte";
var client = new Client(connectionString);
await client.connect()
var createTableStr = 'CREATE TABLE IF NOT EXISTS employee (id int primary key, name varchar, age int, language text);';
client
.query(createTableStr)
.catch((err) => {
console.log(err);
})
Read more on designing Database schemas and tables.
Read and write data
Insert data
To write data to YugabyteDB, execute the INSERT
statement using the client.query()
function.
INSERT INTO employee VALUES (1, 'John', 35, 'Java')
var connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte";
var client = new Client(connectionString);
await client.connect()
var insertRowStr = "INSERT INTO employee VALUES (1, 'John', 35, 'Java')";
client
.query(insertRowStr)
.catch((err) => {
console.log(err);
})
Query data
To query data from YugabyteDB tables, execute the SELECT
statement using the Client.query()
interface. Query results are returned which has rows
array ; results contain rows returned from the query, rowCount
has number of rows returned and a command
field which stores the SQL command of the query being executed.
SELECT * from employee;
var connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte";
var client = new Client(connectionString);
await client.connect()
var selectStr = 'SELECT * from employee';
client
.query(selectStr)
.then((res) => {
console.log("Number of Employees:" , res.rowCount);
console.log(res.rows[0]);
})
Configure SSL/TLS
To build a Node.js application that communicates securely over SSL, get the root certificate (ca.crt
) of the YugabyteDB Cluster. If certificates are not generated yet, follow the instructions in Create server certificates.
Because a YugabyteDB Aeon cluster is always configured with SSL/TLS, you don't have to generate any certificate but only set the client-side SSL configuration. To fetch your root certificate, refer to Download your cluster certificate.
The node-postgres driver allows you to avoid including the parameters like sslcert
, sslkey
, sslrootcert
, or sslmode
in the connection string. You can pass the object which includes connectionString
and ssl
object which has various fields including the following:
rejectUnauthorized
boolean variable tells the driver to throw the error when SSL connectivity fails (default: true).ca
string of root certificate read byfs
module and converted into a string.
The following example shows how to build a connection string for connecting to a YugabyteDB.
var client = new Client({
connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte",
ssl: {
rejectUnauthorized: false,
ca: fs.readFileSync('<path_to_root_crt>').toString(),
}
});
SSL modes
SSL mode | Client driver behavior |
---|---|
disable | Supported |
allow | Not supported |
prefer | Not supported |
require (default) | Supported |
verify-ca | Supported (Self-signed certificates aren't supported.) |
verify-full | Supported |
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,
},
}
To enable verify-ca
SSL mode, you need to provide the path to the root CA certificate. You also need to set the rejectUnauthorized
property to true
to require root certificate chain validation.
const config = {
user: ' ',
database: ' ',
host: ' ',
password: ' ',
port: 5433,
// this object will be passed to the TLSSocket constructor
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync('~/.postgresql/root.crt').toString(),
},
}
In this mode, the driver throws an exception if the server certificate presents as a self-signed certificate:
There was an error self signed certificate in certificate chain Error: self signed certificate in certificate chain
To enable verify-full
mode, you need to provide the path to the Root CA certificate, as well as the server name:
const config = {
user: ' ',
database: ' ',
host: ' ',
password: ' ',
port: 5433,
// this object will be passed to the TLSSocket constructor
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync('/Users/my-user/.postgresql/root.crt').toString(),
servername: 'yugabyte-cloud/my-instance'
},
}
The following is another way of passing the 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()
Transaction and isolation levels
YugabyteDB supports transactions for inserting and querying data from the tables. YugabyteDB supports different isolation levels for maintaining strong consistency for concurrent data access.
Node-postgres driver allows transaction using the queries, executed using client.query()
.
BEGIN
INSERT INTO employee VALUES (2, 'Bob', 33, 'C++');
INSERT INTO employee VALUES (3, 'Jake', 30, 'JS');
COMMIT/ROLLBACK;
var connectionString = "postgresql://yugabyte:yugabyte@127.0.0.1:5433/yugabyte";
var client = new Client(connectionString);
await client.connect()
var insertRowStr1 = "INSERT INTO employee VALUES (2, 'Bob', 33, 'C++');";
var insertRowStr2 = "INSERT INTO employee VALUES (3, 'Jake', 30, 'JS');";
try {
await client.query('BEGIN');
await client.query(insertRowStr1);
await client.query(insertRowStr2);
await client.query('COMMIT');
} catch (err) {
console.log('Error in executing transaction', err, 'Rolling back');
client
.query('ROLLBACK')
.catch((err) => {
console.log("Error in Rolling back", err);
})
}