快速入门:使用 Node.js 连接到 Azure Database for PostgreSQL 并查询其中的数据 - 单一服务器Quickstart: Use Node.js to connect and query data in Azure Database for PostgreSQL - Single Server

在本快速入门中,我们使用 Node.js 应用程序连接到 Azure Database for PostgreSQL。In this quickstart, you connect to an Azure Database for PostgreSQL using a Node.js application. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本文中的步骤假定你熟悉如何使用 Node.js 进行开发,但不熟悉如何使用 Azure Database for PostgreSQL。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

安装 pg 客户端Install pg client

安装 pg,这是 Node.js 的 PostgreSQL 客户端。Install pg, which is a PostgreSQL client for Node.js.

为此,请从命令行运行适用于 JavaScript 的节点包管理器 (npm),以便安装 pg 客户端。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

获取连接到 Azure Database for PostgreSQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for PostgreSQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

  1. Azure 门户中,搜索并选择已创建的服务器(例如 mydemoserver)。In the Azure portal, search for and select the server you have created (such as mydemoserver).

  2. 从服务器的“概览”面板中记下“服务器名称”和“管理员用户名”。From the server's Overview panel, make a note of the Server name and Admin username. 如果忘记了密码,也可通过此面板来重置密码。If you forget your password, you can also reset the password from this panel.

    Azure Database for PostgreSQL 连接字符串

在 Node.js 中运行 JavaScript 代码Running the JavaScript code in Node.js

可以通过键入 node 从 bash shell、终端或 Windows 命令提示符启动 Node.js,然后以交互方式运行示例 JavaScript 代码(只需将其复制并粘贴到提示符处即可)。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. 也可将 JavaScript 代码保存到文本文件中,然后以文件名作为运行所需的参数来启动 node filename.jsAlternatively, 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

通过以下代码进行连接,然后使用 CREATE TABLEINSERT INTO SQL 语句加载数据。Use the following code to connect and load the data using CREATE TABLE and INSERT INTO SQL statements. pg.Client 对象用作 PostgreSQL 服务器的接口。The pg.Client object is used to interface with the PostgreSQL server. pg.Client.connect() 函数用来建立到服务器的连接。The pg.Client.connect() function is used to establish the connection to the server. pg.Client.query() 函数用来针对 PostgreSQL 数据库执行 SQL 查询。The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

将 host、dbname、user 和 password 参数替换为创建服务器和数据库时指定的值。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.chinacloudapi.cn',
    // 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

使用以下代码进行连接,并使用 SELECT SQL 语句来读取数据。Use the following code to connect and read the data using a SELECT SQL statement. pg.Client 对象用作 PostgreSQL 服务器的接口。The pg.Client object is used to interface with the PostgreSQL server. pg.Client.connect() 函数用来建立到服务器的连接。The pg.Client.connect() function is used to establish the connection to the server. pg.Client.query() 函数用来针对 PostgreSQL 数据库执行 SQL 查询。The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

将 host、dbname、user 和 password 参数替换为创建服务器和数据库时指定的值。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.chinacloudapi.cn',
    // 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

使用以下代码进行连接,并使用 UPDATE SQL 语句读取数据。Use the following code to connect and read the data using a UPDATE SQL statement. pg.Client 对象用作 PostgreSQL 服务器的接口。The pg.Client object is used to interface with the PostgreSQL server. pg.Client.connect() 函数用来建立到服务器的连接。The pg.Client.connect() function is used to establish the connection to the server. pg.Client.query() 函数用来针对 PostgreSQL 数据库执行 SQL 查询。The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

将 host、dbname、user 和 password 参数替换为创建服务器和数据库时指定的值。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.chinacloudapi.cn',
    // 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

使用以下代码进行连接,并使用 DELETE SQL 语句读取数据。Use the following code to connect and read the data using a DELETE SQL statement. pg.Client 对象用作 PostgreSQL 服务器的接口。The pg.Client object is used to interface with the PostgreSQL server. pg.Client.connect() 函数用来建立到服务器的连接。The pg.Client.connect() function is used to establish the connection to the server. pg.Client.query() 函数用来针对 PostgreSQL 数据库执行 SQL 查询。The pg.Client.query() function is used to execute the SQL query against PostgreSQL database.

将 host、dbname、user 和 password 参数替换为创建服务器和数据库时指定的值。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.chinacloudapi.cn',
    // 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