# Postgres clients

Nominal accepts connections over the Postgres wire protocol, so standard Postgres drivers can run the same SQL as the [SQL console](https://dev.nominal.io/quickstart/quick/quick-sql.md#in-the-nominal-app) and the [SQL REST API](https://dev.nominal.io/sql-reference/rest-api.md). 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](https://dev.nominal.io/sql-guides/quickstart.md#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](https://dev.nominal.io/quickstart/api-keys-setup.md) |
| TLS | Required. Set `sslmode=require` or stricter. |

## Writing queries

Queries follow all the rules in [Writing queries](https://dev.nominal.io/sql-guides/writing-queries.md).

- 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](https://dev.nominal.io/sql-reference/rest-api.md) when you need nanoseconds.

## Connecting

**Java**

Use the Postgres JDBC driver, [pgjdbc](https://jdbc.postgresql.org/) 42.7 or later. To install this dependency, download the [jar](https://jdbc.postgresql.org/download/) 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.

```java
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:

```shell
java -cp postgresql-42.7.13.jar NominalQuery.java
```

**MATLAB**

MATLAB® Database Toolbox connects through JDBC. Download the [pgjdbc jar](https://jdbc.postgresql.org/download/), then add it to MATLAB’s Java class path:

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

Connect and query:

```matlab
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](https://www.psycopg.org/psycopg3/) 3, and the Nominal Python client to read the profile from [API keys setup](https://dev.nominal.io/quickstart/api-keys-setup.md). To install both, use:

```shell
pip install "psycopg[binary]" nominal
```

Connect and query:

```python
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](https://docs.rs/tokio-postgres) with `postgres-native-tls`, and the `nominal` crate to read the profile from [API keys setup](https://dev.nominal.io/quickstart/api-keys-setup.md). Add these dependencies to `Cargo.toml`:

```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`:

```rust
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(())
}
```