connect-nodejs.md

title	description	services	author	ms.author	manager	editor	ms.service	ms.custom	ms.devlang	ms.topic	ms.date
Connect to Azure Database for PostgreSQL from Node.js	This quickstart provides a Node.js code sample you can use to connect and query data from Azure Database for PostgreSQL.	postgresql	rachel-msft	raagyema	kfile	jasonwhowell	postgresql	mvc, devcenter	nodejs	quickstart	02/28/2018

Azure Database for PostgreSQL: Use Node.js to connect and query data

This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a Node.js application. It shows how to use SQL statements to query, insert, update, and delete data in the database. The steps in this article assume that you are familiar with developing using Node.js, and are new to working with Azure Database for PostgreSQL.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

• Create DB - Portal
• Create DB - CLI

You also need to:

• Install Node.js

Install pg client

Install pg, which is a PostgreSQL client for Node.js.

To do so, run the node package manager (npm) for JavaScript from your command line to install the pg client.

npm install pg

Verify the installation by listing the packages installed.

npm list

Get connection information

Get the connection information needed to connect to the Azure Database for PostgreSQL. You need the fully qualified server name and login credentials.

1. Log in to the Azure portal.
2. From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
3. Click the server name.
4. From the server's Overview panel, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this panel.

Running the JavaScript code in Node.js

You may launch Node.js from the Bash shell, Terminal, or Windows Command Prompt by typing node, then run the example JavaScript code interactively by copy and pasting it onto the prompt. Alternatively, you may save the JavaScript code into a text file and launch node filename.js with the file name as a parameter to run it.

Connect, create table, and insert data

Use the following code to connect and load the data using CREATE TABLE and INSERT INTO SQL statements. The pg.Client object is used to interface with the PostgreSQL server. The pg.Client.connect() function is used to establish the connection to the server. The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

const pg = require('pg');

const config = {
    host: '<your-db-server-name>.postgres.database.azure.com',
    // Do not hard code your username and password.
    // Consider using Node environment variables.
    user: '<your-db-username>',     
    password: '<your-password>',
    database: '<name-of-database>',
    port: 5432,
    ssl: true
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) throw err;
    else {
        queryDatabase();
    }
});

function queryDatabase() {
    const query = `
        DROP TABLE IF EXISTS inventory;
        CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);
        INSERT INTO inventory (name, quantity) VALUES ('banana', 150);
        INSERT INTO inventory (name, quantity) VALUES ('orange', 154);
        INSERT INTO inventory (name, quantity) VALUES ('apple', 100);
    `;

    client
        .query(query)
        .then(() => {
            console.log('Table created successfully!');
            client.end(console.log('Closed client connection'));
        })
        .catch(err => console.log(err))
        .then(() => {
            console.log('Finished execution, exiting now');
            process.exit();
        });
}

Read data

Use the following code to connect and read the data using a SELECT SQL statement. The pg.Client object is used to interface with the PostgreSQL server. The pg.Client.connect() function is used to establish the connection to the server. The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

const pg = require('pg');

const config = {
    host: '<your-db-server-name>.postgres.database.azure.com',
    // Do not hard code your username and password.
    // Consider using Node environment variables.
    user: '<your-db-username>',     
    password: '<your-password>',
    database: '<name-of-database>',
    port: 5432,
    ssl: true
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) throw err;
    else { queryDatabase(); }
});

function queryDatabase() {
  
    console.log(`Running query to PostgreSQL server: ${config.host}`);

    const query = 'SELECT * FROM inventory;';

    client.query(query)
        .then(res => {
            const rows = res.rows;

            rows.map(row => {
                console.log(`Read: ${JSON.stringify(row)}`);
            });

            process.exit();
        })
        .catch(err => {
            console.log(err);
        });
}

Update data

Use the following code to connect and read the data using a UPDATE SQL statement. The pg.Client object is used to interface with the PostgreSQL server. The pg.Client.connect() function is used to establish the connection to the server. The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

const pg = require('pg');

const config = {
    host: '<your-db-server-name>.postgres.database.azure.com',
    // Do not hard code your username and password.
    // Consider using Node environment variables.
    user: '<your-db-username>',     
    password: '<your-password>',
    database: '<name-of-database>',
    port: 5432,
    ssl: true
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) throw err;
    else {
        queryDatabase();
    }
});

function queryDatabase() {
    const query = `
        UPDATE inventory 
        SET quantity= 1000 WHERE name='banana';
    `;

    client
        .query(query)
        .then(result => {
            console.log('Update completed');
            console.log(`Rows affected: ${result.rowCount}`);
        })
        .catch(err => {
            console.log(err);
            throw err;
        });
}

Delete data

Use the following code to connect and read the data using a DELETE SQL statement. The pg.Client object is used to interface with the PostgreSQL server. The pg.Client.connect() function is used to establish the connection to the server. The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

Replace the host, dbname, user, and password parameters with the values that you specified when you created the server and database.

const pg = require('pg');

const config = {
    host: '<your-db-server-name>.postgres.database.azure.com',
    // Do not hard code your username and password.
    // Consider using Node environment variables.
    user: '<your-db-username>',     
    password: '<your-password>',
    database: '<name-of-database>',
    port: 5432,
    ssl: true
};

const client = new pg.Client(config);

client.connect(err => {
    if (err) {
        throw err;
    } else {
        queryDatabase();
    }
});

function queryDatabase() {
    const query = `
        DELETE FROM inventory 
        WHERE name = 'apple';
    `;

    client
        .query(query)
        .then(result => {
            console.log('Delete completed');
            console.log(`Rows affected: ${result.rowCount}`);
        })
        .catch(err => {
            console.log(err);
            throw err;
        });
}

Next steps

[!div class="nextstepaction"] Migrate your database using Export and Import