Develop a JavaScript application with PostgreSQL on Azure

Članak
08/09/2022
5 min. za čitanje

To create, move, or use a PostgreSQL database to Azure, you need an Azure Database for PostgreSQL server resource. Learn how to create the resource and use your database.

Create an Azure Database for PostgreSQL resource

Create a resource with:

Azure CLI command = az postgres server create
Visual Studio Code
Azure portal
@azure/arm-postgresql

Create an Azure Database for PostgreSQL server resource with Azure CLI

Use the following Azure CLI az postgres server create command in the Azure Cloud Shell to create a new PostgreSQL server resource for your database.

az postgres server create \
    --subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
    --resource-group YOUR-RESOURCE-GROUP \
    --location eastus \
    --name YOURRESOURCENAME \
    --admin-user YOUR-ADMIN-USER \
    --ssl-enforcement Disabled \
    --enable-public-network Enabled

This command may take a couple of minutes to complete and creates a publicly available resource in the eastus region.

The response includes your server's configuration details including:

Autogenerated password for the admin account
Connection string

{
  "additionalProperties": {},
  "administratorLogin": "YOUR-ADMIN-USER",
  "byokEnforcement": "Disabled",
  "connectionString": "postgres://YOUR-ADMIN-USER%40YOURRESOURCENAME:123456789@YOURRESOURCENAME.postgres.database.azure.com/postgres?sslmode=require",
  "earliestRestoreDate": "2021-02-08T21:56:20.130000+00:00",
  "fullyQualifiedDomainName": "YOURRESOURCENAME.postgres.database.azure.com",
  "id": "/subscriptions/.../resourceGroups/YOUR-RESOURCE-GROUP/providers/Microsoft.DBforPostgreSQL/servers/YOURRESOURCENAME",
  "identity": null,
  "infrastructureEncryption": "Disabled",
  "location": "eastus",
  "masterServerId": "",
  "minimalTlsVersion": "TLSEnforcementDisabled",
  "name": "YOURRESOURCENAME",
  "password": "123456789",
  "privateEndpointConnections": [],
  "publicNetworkAccess": "Enabled",
  "replicaCapacity": 5,
  "replicationRole": "None",
  "resourceGroup": "YOUR-RESOURCE-GROUP",
  "sku": {
    "additionalProperties": {},
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },
  "sslEnforcement": "Disabled",
  "storageProfile": {
    "additionalProperties": {},
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageAutogrow": "Enabled",
    "storageMb": 51200
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"
}

Before you can connect to the server, you need to configure your firewall rules to allow your client IP address through.

Add a firewall rule for your client IP address to PostgreSQL resource

By default, the firewall rules are not configured. You should add your client IP address so your client connection to the server with JavaScript is successful.

Use the following Azure CLI az postgres server firewall-rule create command in the Azure Cloud Shell to create a new firewall rule for your database.

az postgres server firewall-rule create \
    --subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
    --resource-group YOUR-RESOURCE-GROUP \
    --server YOURRESOURCENAME \
    --name AllowMyIP \
    --start-ip-address 123.123.123.123 \
    --end-ip-address 123.123.123.123

If you don't know your client IP address, use one of these methods:

Use the Azure portal to view and change your firewall rules, which includes adding your detected client IP
Run your Node.js code, the error about your firewall rules violation includes your client IP address

While you can add the full range of internet addresses as a firewall rule, 0.0.0.0-255.255.255.255, this leaves your server open to attacks.

Create a PostgreSQL database on the server with Azure CLI

Use the following Azure CLI az postgres db create command in the Azure Cloud Shell to create a new PostgreSQL database on your server.

az postgres db create \
    --subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
    --resource-group YOUR-RESOURCE-GROUP \
    --server-name YOURRESOURCENAME \
    --name YOURDATABASENAME

Get the PostgreSQL connection string with Azure CLI

Retrieve the PostgreSQL connection string for this instance with the az postgres server show-connection-string command:

az postgres server show-connection-string \
    --subscription YOUR-SUBSCRIPTION-ID-OR-NAME \
    --server-name YOURRESOURCENAME

This returns the connection strings for the popular languages as a JSON object. You need to replace {database}, {username}, and {password} with your own values before using the connection string. Replace YOURRESOURCENAME with your resource name.

{
  "connectionStrings": {
    "C++ (libpq)": "host=YOURRESOURCENAME.postgres.database.azure.com port=5432 dbname={database} user={username}YOURRESOURCENAME password={password} sslmode=require",
    "ado.net": "Server=YOURRESOURCENAME.postgres.database.azure.com;Database={database};Port=5432;User Id={username}@YOURRESOURCENAME;Password={password};",
    "jdbc": "jdbc:postgresql://YOURRESOURCENAME.postgres.database.azure.com:5432/{database}?user={username}@YOURRESOURCENAME&password={password}",
    "node.js": "var client = new pg.Client('postgres://{username}@YOURRESOURCENAME:{password}@YOURRESOURCENAME.postgres.database.azure.com:5432/{database}');",
    "php": "host=YOURRESOURCENAME.postgres.database.azure.com port=5432 dbname={database} user={username}@YOURRESOURCENAME password={password}",
    "psql_cmd": "postgresql://{username}@YOURRESOURCENAME:{password}@YOURRESOURCENAME.postgres.database.azure.com/{database}?sslmode=require",
    "python": "cnx = psycopg2.connect(database='{database}', user='{username}@YOURRESOURCENAME', host='YOURRESOURCENAME.postgres.database.azure.com', password='{password}', port='5432')",
    "ruby": "cnx = PG::Connection.new(:host => 'YOURRESOURCENAME.postgres.database.azure.com', :user => '{username}@YOURRESOURCENAME', :dbname => '{database}', :port => '5432', :password => '{password}')"
  }
}

View and use your PostgreSQL server on Azure

While developing your PostgreSQL database with JavaScript, use one of the following tools:

Azure Cloud Shell - psql CLI is available
pgAdmin
Visual Studio Code extension

Use SDK packages to develop your PostgreSQL server on Azure

The Azure PostgreSQL uses npm packages already available, such as:

Use pg SDK to connect to PostgreSQL on Azure

To connect and use your PostgreSQL on Azure with JavaScript, use the following procedure.

Make sure Node.js and npm are installed.
Create a Node.js project in a new folder:
```
mkdir DbDemo && \
    cd DbDemo && \
    npm init -y && \
    npm install pg && \
    touch index.js && \
    code .
```
The command:
- Creates a project folder named DbDemo
- Changes the Bash terminal into that folder
- Initializes the project, which creates the package.json file
- Installs the pg npm package - to use async/await
- Creates the index.js script file
- Opens the project in Visual Studio Code

Copy the following JavaScript code into index.js:

const { Client } = require('pg')
    
const query = async (connectionString) => {
    
    // create connection
    const connection = new Client(connectionString);
    connection.connect();
    
    // show tables in the postgres database
    const tables = await connection.query('SELECT table_name FROM information_schema.tables where table_type=\'BASE TABLE\';');
    console.log(tables.rows);

    // show users configured for the server
    const users = await connection.query('select pg_user.usename FROM pg_catalog.pg_user;');
    console.log(users.rows);
    
    // close connection
    connection.end();
}

const server='YOURRESOURCENAME';
const user='YOUR-ADMIN-USER';
const password='YOUR-PASSWORD';
const database='postgres';

const connectionString = `postgres://${user}@${server}:${password}@${server}.postgres.database.azure.com:5432/${database}`;

query(connectionString)
.then(() => console.log('done'))
.catch((err) => console.log(err));

Replace the YOUR-ADMIN-USER, YOURRESOURCENAME, and YOUR-PASSWORD with your values in the script for your connection string.
Run the script to connect to the postgres server and see the base tables and users.
```
node index.js
```

View the results.

[
  { table_name: 'pg_statistic' },
  { table_name: 'pg_type' },
  { table_name: 'pg_authid' },
  { table_name: 'pg_user_mapping' },
  { table_name: 'pg_attribute' },
  { table_name: 'pg_proc' },
  { table_name: 'pg_class' },
  { table_name: 'pg_attrdef' },
  { table_name: 'pg_constraint' },
  { table_name: 'pg_inherits' },
  { table_name: 'pg_index' },
  { table_name: 'pg_operator' },
  { table_name: 'pg_opfamily' },
  { table_name: 'pg_opclass' },
  { table_name: 'pg_am' },
  { table_name: 'pg_amop' },
  { table_name: 'pg_amproc' },
  { table_name: 'pg_language' },
  { table_name: 'pg_largeobject_metadata' },
  { table_name: 'pg_aggregate' },
  { table_name: 'pg_rewrite' },
  { table_name: 'pg_largeobject' },
  { table_name: 'pg_trigger' },
  { table_name: 'pg_event_trigger' },
  { table_name: 'pg_description' },
  { table_name: 'pg_cast' },
  { table_name: 'pg_enum' },
  { table_name: 'pg_namespace' },
  { table_name: 'pg_conversion' },
  { table_name: 'pg_depend' },
  { table_name: 'pg_database' },
  { table_name: 'pg_db_role_setting' },
  { table_name: 'pg_tablespace' },
  { table_name: 'pg_pltemplate' },
  { table_name: 'pg_auth_members' },
  { table_name: 'pg_shdepend' },
  { table_name: 'pg_shdescription' },
  { table_name: 'pg_ts_config' },
  { table_name: 'pg_ts_config_map' },
  { table_name: 'pg_ts_dict' },
  { table_name: 'pg_ts_parser' },
  { table_name: 'pg_ts_template' },
  { table_name: 'pg_extension' },
  { table_name: 'pg_foreign_data_wrapper' },
  { table_name: 'pg_foreign_server' },
  { table_name: 'pg_foreign_table' },
  { table_name: 'pg_policy' },
  { table_name: 'pg_replication_origin' },
  { table_name: 'pg_default_acl' },
  { table_name: 'pg_init_privs' },
  { table_name: 'pg_seclabel' },
  { table_name: 'pg_shseclabel' },
  { table_name: 'pg_collation' },
  { table_name: 'pg_range' },
  { table_name: 'pg_transform' },
  { table_name: 'sql_features' },
  { table_name: 'sql_implementation_info' },
  { table_name: 'sql_languages' },
  { table_name: 'sql_packages' },
  { table_name: 'sql_parts' },
  { table_name: 'sql_sizing' },
  { table_name: 'sql_sizing_profiles' }
]
[ { usename: 'azure_superuser' }, { usename: 'YOUR-ADMIN-USER' } ]
done