Overview

This article describes how to connect to PostgreSQL from Domino.

PostgreSQL is an open-source relational database that can run on a wide variety of local systems and clouds.

Python and psycopg2

Domino recommends the psycopg2 library for interacting with PostgreSQL databases from Python.

Environment setup

Use the Dockerfile instruction below to install psycopg2 in your environment.

This instruction assumes you already have pip installed.

RUN pip install psycopg2

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your PostgreSQL connection. Set the following as Domino environment variables on your user account:

• POSTGRES_HOST

  Hostname where your DB is running. Make sure your PostgreSQL DB and network firewall are configured to accept connections from Domino.

• POSTGRES_USER

  The PostgreSQL user you want to authenticate as.

• POSTGRES_PASSWORD

  The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the psycopg2 documentation for detailed information on how to use the package. Below is a simple example for connecting to PostgreSQL with psycopg2 where:

• you have set up environment variables noted above with the hostname, username, and password
• your user has access to a database named db1 in the target PostgreSQL instance
• the database contains a table named metrics

import psycopg2
import os

# fetch values from environment variables and set the target database
hostname = os.environ['POSTGRES_HOST']
username = os.environ['POSTGRES_USER']
password = os.environ['POSTGRES_PASSWORD']
dbname = 'db1'

# set up a connection object with parameters for your database
conn = psycopg2.connect(
  host=hostname,
  port=5432,
  user=username,
  password=password,
  database=dbname, )

# create a cursor in your connection
cur = conn.cursor()

# execute a query on the metrics table and store the response
cur.execute("SELECT * FROM metrics;")
results = cur.fetchall()

# display the contents of the response
print(results)

Note that the results object created in the example above is a Python array of entries from the queried table.

R and RPostgreSQL

Domino recommends the RPostgreSQL library for interacting with PostgreSQL databases from R.

Environment setup

Use the Dockerfile instruction below to add RPostgreSQL to your environment.

RUN R -e 'install.packages("RPostgreSQL")'

For a basic introduction to modifying Domino environments, watch this tutorial video.

Credential setup

There are several environment variables you should set up to store secure information about your PostgreSQL connection. Set the following as Domino environment variables on your user account:

• POSTGRES_HOST

  Hostname where your DB is running. Make sure your PostgreSQL DB and network firewall are configured to accept connections from Domino.

• POSTGRES_USER

  The PostgreSQL user you want to authenticate as.

• POSTGRES_PASSWORD

  The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the RPostgreSQL documentation for detailed information on how to use the package. Below is a simple example for connecting to PostgreSQL with RPostgreSQL where:

• you have set up environment variables noted above with the hostname, username, and password
• your user has access to a database named db1 in the target PostgreSQL instance
• the database contains a table named metrics

# load the library
library(RPostgreSQL)

# fetch values from environment variables and set the target database
hostname <- Sys.getenv['POSTGRES_HOST']
username <-  Sys.getenv['POSTGRES_USER']
password <- Sys.getenv['POSTGRES_PASSWORD']
database <- 'db1'

# set up a driver and use it to create a connection to your database
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(
  drv,
  host=hostname,
  port=5432,
  user=username,
  password=password,
  dbname=database )

# run a query and load the response into a dataframe
df_postgres <- dbGetQuery(conn, "SELECT * from metrics;")

# close your connection when finished
dbDisconnect(conn)