Postgres clients#

Nominal accepts connections over the Postgres wire protocol, so standard Postgres drivers can run the same SQL as the SQL console and the SQL REST API. Connections are read-only.

Connection settings#

Copy your API URL, workspace RID, and an API key from the API keys page in Settings (see Gather your Nominal credentials).

Setting	Value
Host	Your API URL’s host with `api.` replaced by `postgres.`. For example, API URL `https://api.gov.nominal.io/api` gives host `postgres.gov.nominal.io`.
Port	`5432`
Database	Your workspace RID, e.g. `<ri.security.instance.workspace.identifier>`
User	`nominal`
Password	A Nominal API key
TLS	Required. Set `sslmode=require` or stricter.

Writing queries#

Queries follow all the rules in Writing queries.

Query parameters (? or $1) are supported, except for the required dataset_rid filter on telemetry tables, which must be a string literal: WHERE dataset_rid = '<ri.catalog.instance.dataset.identifier>'.
For pgjdbc, set binaryTransfer=false.
Timestamps come back at microsecond precision. Use the SQL REST API when you need nanoseconds.

Connecting#

Java

Use the Postgres JDBC driver, pgjdbc 42.7 or later. To install this dependency, download the jar or add org.postgresql:postgresql to your Maven or Gradle build. Connect with this URL:

jdbc:postgresql://HOST:5432/WORKSPACE_RID?sslmode=require&binaryTransfer=false

Set user to nominal and password to your API key.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class NominalQuery {
    static final String NOMINAL_API_KEY = "<api key>"; // DO NOT COMMIT THIS
    static final String NOMINAL_WORKSPACE_RID = "<ri.security.instance.workspace.identifier>";
    static final String NOMINAL_DATASET_RID = "<ri.catalog.instance.dataset.identifier>";

    public static void main(String[] args) throws Exception {
        Properties props = new Properties();
        props.setProperty("user", "nominal");
        props.setProperty("password", NOMINAL_API_KEY);
        props.setProperty("sslmode", "require");
        props.setProperty("binaryTransfer", "false");

        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://HOST:5432/" + NOMINAL_WORKSPACE_RID, props);
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery("SELECT ts, channel, value FROM points_double WHERE dataset_rid = '"
                        + NOMINAL_DATASET_RID + "' ORDER BY ts LIMIT 1000")) {
            while (rs.next()) {
                System.out.println(
                        rs.getTimestamp("ts").toInstant() + " " + rs.getString("channel") + " " + rs.getDouble("value"));
            }
        }
    }
}

Run it with the driver jar on the classpath:

java -cp postgresql-42.7.13.jar NominalQuery.java

MATLAB

MATLAB® Database Toolbox connects through JDBC. Download the pgjdbc jar, then add it to MATLAB’s Java class path:

javaaddpath('/path/to/postgresql-42.7.13.jar');

Connect and query:

NOMINAL_API_KEY = '<api key>';  % DO NOT COMMIT THIS
NOMINAL_WORKSPACE_RID = '<ri.security.instance.workspace.identifier>';
NOMINAL_DATASET_RID = '<ri.catalog.instance.dataset.identifier>';

conn = database([NOMINAL_WORKSPACE_RID '?sslmode=require&binaryTransfer=false'], ...
    'nominal', NOMINAL_API_KEY, 'org.postgresql.Driver', 'jdbc:postgresql://HOST:5432/');
assert(isopen(conn), conn.Message);

rows = fetch(conn, sprintf(['SELECT ts, channel, value FROM points_double ' ...
    'WHERE dataset_rid = ''%s'' ORDER BY ts LIMIT 1000'], NOMINAL_DATASET_RID));
head(rows)

close(conn);

Python

Use psycopg 3, and the Nominal Python client to read the profile from API keys setup. To install both, use:

pip install "psycopg[binary]" nominal

Connect and query:

import psycopg
from nominal.config import NominalConfig

profile = NominalConfig.from_yaml().get_profile("default")
NOMINAL_API_KEY = profile.token
NOMINAL_WORKSPACE_RID = profile.workspace_rid
NOMINAL_DATASET_RID = "<ri.catalog.instance.dataset.identifier>"

with psycopg.connect(
    host="HOST",
    port=5432,
    dbname=NOMINAL_WORKSPACE_RID,
    user="nominal",
    password=NOMINAL_API_KEY,
    sslmode="require",
    autocommit=True,
) as conn:
    rows = conn.execute(
        "SELECT ts, channel, value FROM points_double "
        f"WHERE dataset_rid = '{NOMINAL_DATASET_RID}' ORDER BY ts LIMIT 1000"
    ).fetchall()
    for ts, channel, value in rows:
        print(ts, channel, value)

Rust

Use tokio-postgres with postgres-native-tls, and the nominal crate to read the profile from API keys setup. Add these dependencies to Cargo.toml:

[dependencies]
native-tls = "0.2"
nominal = "0.7"
postgres-native-tls = "0.5"
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }
tokio-postgres = "0.7"

In src/main.rs:

use postgres_native_tls::MakeTlsConnector;
use tokio_postgres::{config::SslMode, Config, SimpleQueryMessage};

const NOMINAL_DATASET_RID: &str = "<ri.catalog.instance.dataset.identifier>";

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let nominal_config = nominal::Config::load()?;
    let profile = nominal_config.get_profile("default").ok_or("no profile named default")?;
    let workspace_rid = profile.workspace_rid().ok_or("the profile has no workspace_rid")?;

    let tls = MakeTlsConnector::new(native_tls::TlsConnector::new()?);
    let (client, connection) = Config::new()
        .host("HOST")
        .port(5432)
        .user("nominal")
        .password(profile.token())
        .dbname(workspace_rid)
        .ssl_mode(SslMode::Require)
        .connect(tls)
        .await?;
    tokio::spawn(connection);

    let sql = format!(
        "SELECT ts, channel, value FROM points_double \
         WHERE dataset_rid = '{NOMINAL_DATASET_RID}' ORDER BY ts LIMIT 1000"
    );
    for message in client.simple_query(&sql).await? {
        if let SimpleQueryMessage::Row(row) = message {
            println!("{:?} {:?} {:?}", row.get("ts"), row.get("channel"), row.get("value"));
        }
    }
    Ok(())
}