Query AlloyDB Data through ODBC in Node.js

Node.js is a JavaScript runtime environment that allows you to run JavaScript code outside of a browser. With the CData ODBC Driver for AlloyDB, you can access live AlloyDB data from Node.js apps and scripts. In this article, we walk through installing node-odbc and the required tools to create a simple Node.js app with access to live AlloyDB data.

With built-in optimized data processing, the CData ODBC Driver offers unmatched performance for interacting with live AlloyDB data in Node.js. When you issue complex SQL queries from Node.js to AlloyDB, the driver pushes supported SQL operations, like filters and aggregations, directly to AlloyDB and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to AlloyDB Data

If you have not already done so, provide values for the required connection properties in the data source name (DSN). You can use the built-in Microsoft ODBC Data Source Administrator to configure the DSN. This is also the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to using the Microsoft ODBC Data Source Administrator to create and configure a DSN.

The following connection properties are usually required in order to connect to AlloyDB.

• Server: The host name or IP of the server hosting the AlloyDB database.
• User: The user which will be used to authenticate with the AlloyDB server.
• Password: The password which will be used to authenticate with the AlloyDB server.

You can also optionally set the following:

• Database: The database to connect to when connecting to the AlloyDB Server. If this is not set, the user's default database will be used.
• Port: The port of the server hosting the AlloyDB database. This property is set to 5432 by default.

Authenticating with Standard Authentication

Standard authentication (using the user/password combination supplied earlier) is the default form of authentication.

No further action is required to leverage Standard Authentication to connect.

Authenticating with pg_hba.conf Auth Schemes

There are additional methods of authentication available which must be enabled in the pg_hba.conf file on the AlloyDB server.

Find instructions about authentication setup on the AlloyDB Server here.

Authenticating with MD5 Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.

Authenticating with SASL Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.

Authenticating with Kerberos

The authentication with Kerberos is initiated by AlloyDB Server when the ∏ is trying to connect to it. You should set up Kerberos on the AlloyDB Server to activate this authentication method. Once you have Kerberos authentication set up on the AlloyDB Server, see the Kerberos section of the help documentation for details on how to authenticate with Kerberos.

Building node-odbc

In order to connect to AlloyDB through the CData ODBC Driver, you need to build node-odbc manually (after installing the required tools).

Installing the Required Tools

The following commands install the tools required to build node-odbc (note the -g parameter, which installs the tools globally).

npm i -g windows-build-tools
npm i -g node-gyp

Building node-odbc

After installing the required tools, create a directory for the Node.js app and install odbc (which builds the binary for us to use in our Node.js script).

mkdir nodeodbc
cd nodeodbc
npm i -g node

Querying AlloyDB from Node.js

With the ODBC Driver installed, a DSN Configured, and node-odbc built, we are ready to query live AlloyDB data from a Node.js app. The sample code below connects to a specific DSN and queries the Orders table.

myscript.js

const odbc = require('odbc');

async function queryAlloyDB() {
    const connection = await odbc.connect(`DSN=CData AlloyDB Source`);
    const data = await connection.query('SELECT ShipName, ShipCity FROM Orders');
    console.log(data);
}

queryAlloyDB();

Once you write the app, use node to execute the script:

node myscript.js

Download a free, 30-day trial of the CData ODBC Driver for AlloyDB and start working with your live AlloyDB data in Node.js. Reach out to our Support Team if you have any questions.