This topic describes how to connect to PostgreSQL from Domino. You must have network connectivity between PostgreSQL and your Domino deployment.
The easiest way to connect to PostgreSQL from Domino is to create a Domino Data Source as described below.
-
From the navigation pane, click Data > Data Sources.
-
Click Create a Data Source.
-
In the New Data Source window, from Select Data Store, select PostgreSQL.
-
Enter the unique storage account name in Account Name.
-
Enter the Host, Port, and Database name.
-
Enter the Data Source Name.
-
Optional: Enter a Description to explain the purpose of the Data Source to others.
-
Click Next.
-
Specify the credentials for authenticating to Postgres.
Basic authentication is supported by default. IAM credential propagation might also be available if your administrator has enabled it.
NoteIAM-authenticated connections can be used only for executing jobs and workspaces. Other execution types, such as scheduled jobs and Domino endpoints, require basic authentication. -
Click Next (or Skip for Now to configure authentication later).
-
Enter the users and organizations who can view and use the Data Source in projects.
-
Click Finish Setup.
Warning
| This section describes an alternate method to connect to the Postgres Data Source. Domino does not officially support this method. |
This instruction assumes you already have pip installed.
-
For Python and psycopg2, Domino recommends the psycopg2 library to interact with PostgreSQL databases from Python.
-
Use the following Dockerfile instruction to install psycopg2 in your environment.
RUN pip install psycopg2
-
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 Store Project credentials to learn more about Domino environment variables.
-
-
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.
-
R and RPostgreSQL
-
To connect to R and RPostgreSQL, Domino recommends the RPostgreSQL library to interact with PostgreSQL databases from R.
-
Use the following Dockerfile instruction to add RPostgreSQL to your environment.
RUN R -e 'install.packages("RPostgreSQL")'
-
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 Store Project credentials to learn more about Domino environment variables.
-
-
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)
-
-
After connecting to your Data Source, learn how to Use Data Sources.
-
Share this Data Source with your collaborators.