Connecting an external application to your PostgreSQL deployment

Connecting to your PostgreSQL deployment with a language's driver

All the information a driver needs to make a connection to your deployment is in the "postgres" section of your connection strings. The table contains a breakdown for reference.

• 0... indicates that there might be one or more of these entries in an array.

Many PostgreSQL drivers are able to make a connection to your deployment when given the URI-formatted connection string found in the "composed" field of the connection information. For example,

postgres://ibm_cloud_30399dec_4835_4967_a23d_30587a08d9a8:$PASSWORD@981ac415-5a35-4ac7-b6bb-fb609326dc42.8f7bfd8f3faa4218aec56e069eb46187.databases.appdomain.cloud:32704/ibmclouddb?sslmode=verify-full

The following example uses the information from your connection string and the Java driver jdbc to connect to your database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.*;

public class PGConnect {

    private final static Logger LOGGER = Logger.getLogger(Logger.GLOBAL_LOGGER_NAME);

    private Connection connect() {

        final String url = "jdbc:postgresql://host:port/ibmclouddb";

        Properties props = new Properties();
        props.setProperty("user","admin");
        props.setProperty("password","mypassword123");
        props.setProperty("ssl","true");
        props.setProperty("sslmode","verify-full");
        props.setProperty("sslrootcert", "/path/to/cert");

        Connection conn = null;
        while (conn == null) {
            try {
                conn = DriverManager.getConnection(url, props);
                System.out.println("Connected to PG");
            } catch (SQLException e) {
                System.out.printf("%s\n", e);
                LOGGER.info("Not connected, retying ...");
            }
        }

        return conn;

    }

    public static void main(String[] args) {

        PGConnect icd = new PGConnect();

        try {
            Connection connection = icd.connect();
            Statement stmt = connection.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * from pg_database");
            while (rs.next()) {
                System.out.println("DB Name: " + rs.getString(1));
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }   
    }
}

The following example uses the information from your connection string and the Python driver Psycopg2 to connect to your database. This is just a simple connection example, without error handling or retry logic and might not be suitable for production.

import psycopg2

try:
    conn = psycopg2.connect(
      host="hostname.databases.appdomain.cloud",
      port= 31525,
      user="username",
      password="password",
      sslmode="verify-full",
      sslrootcert="/path/to/cert/ca-certificate.crt",
      database="ibmclouddb")
except: 
    print("Unable to connect to database")

cur = conn.cursor()
cur.execute("SELECT datname FROM pg_database")
rows = cur.fetchall()

print("List of databases:")
for row in rows:
    print("  ",row[0])

The following example uses the information from your connection string and the Node driver node-postgres to connect to your database.

const pg = require("pg");
const fs = require("fs");

let connectionString = "postgres://<username>:<password>@<host>:<port>/<database>";
let caCert = fs.readFileSync('/path/to/cert');

// set up a client with your PostgreSQL connection string and TLS options
let client = new pg.Client({ 
    connectionString: connectionString,
    ssl: {
    ca: caCert,
    rejectUnauthorized: true
    }
});


client.connect(function(err) {
    if (err) {
        console.log(err);
        process.exit(1);
    } else {
        // query for the names of the databases
        client.query(
            "SELECT datname FROM pg_database;",
            function(err, result) {
                if (err) {
                    console.log(err);
                }
                // return the names of the databases
                console.log(result.rows);
                client.end();
            }
        );
    }
});

To use the node-postgres driver, remove the sslmode parameter from the deployment's connection string. If not, this parameter overrides the ssl: {...} parameters, preventing the CA certificate from properly loading.

Driver TLS and self-signed certificate support

All connections to Databases for PostgreSQL are TLS 1.2 enabled, so the driver you use to connect needs to be able to support encryption. Your deployment also comes with a self-signed certificate so the driver can verify the server upon connection.

For more information, see Cloud Databases Certificates FAQ.

Using the self-signed certificate

1. Copy the certificate information from the Endpoints panel or the Base64 field of the connection information.
2. If needed, decode the Base64 string into text.
3. Save the certificate to a file. (You can use the name that is provided or your own file name).
4. Provide the path to the certificate to the driver or client.

Other Drivers

PostgreSQL has a vast array of language drivers. The table covers a few of the most common.