Connecting an external application

Connecting 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

This 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());
        }   
    }
}

This next 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])

This final 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>?sslmode=verify-full";
let caCert = fs.readFileSync('/path/to/cert');

// set up a client with your Databases for EnterpriseDB connection string
let client = new pg.Client({ connectionString: connectionString,
    // set up the TLS options
    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();
            }
        );
    }
});

Driver TLS and self-signed certificate support

All connections to IBM Cloud® Databases for EnterpriseDB 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.

Other Drivers

PostgreSQL has a vast array of language drivers that can also be used to connect to a IBM Cloud® Databases for EnterpriseDB deployment. The table covers a few of the most common.