About Domino
Domino Data LabKnowledge BaseData Science BlogTraining

Connect to PostgreSQL

This topic describes how to connect to PostgreSQL from Domino. You must have network connectivity between PostgreSQL and your Domino deployment.

Warning
 Domino does not officially support this method. We provide this information as a courtesy.
Connect to Postgres

This instruction assumes you already have pip installed.

  1. For Python and psycopg2, Domino recommends the psycopg2 library to interact with PostgreSQL databases from Python.

  2. Use the following Dockerfile instruction to install psycopg2 in your environment.

    +

RUN pip install psycopg2

  1. Set the following as Domino environment variables to store secure information about your PostgreSQL connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  2. See the psycopg2 documentation for detailed information about how to use the package. The following is an example to connect to PostgreSQL with psycopg2 where:

    • You have set up environment variables 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)

      The results object created in the previous example is a Python array of entries from the queried table.

Connect to R and RPostgreSQL

  1. To connect to R and RPostgreSQL, Domino recommends the RPostgreSQL library to interact with PostgreSQL databases from R.

  2. Use the following Dockerfile instruction to add RPostgreSQL to your environment.

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

  3. Set up the Domino environment variables to store secure information about your PostgreSQL connection.

    • 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 previously.

      See Secure Credential Storage to learn more about Domino environment variables.

  4. See the RPostgreSQL documentation for information about how to use the package. The following is an example for connecting to PostgreSQL with RPostgreSQL where:

    • You have set up environment variables 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)

Next steps