iot-dc3/docs/en/drivers/postgresql.md

---
title: PostgreSQL Driver
---

# PostgreSQL Driver

`dc3-driver-postgresql` onboards a PostgreSQL database into IoT DC3 as a data source: it runs a `SELECT` per polling
cycle and uses the queried value as the reading, and supports writing values via the `UPDATE`/`INSERT` write query
configured on a point. By the end you can treat columns of an existing table as polled points, and understand the
boundaries of the read and write SQL paths.

## Protocol background

Not all data comes from a fieldbus device. A lot of business data, historical data, and results accumulated by
third-party systems simply live in a PostgreSQL table—an MES work-order status, the settled cumulative quantity from a
metering system, a database view that an upstream platform exposes and nothing else. This kind of data has no fieldbus
protocol like Modbus or OPC UA to ride on, yet it is a genuine extension of the device world and needs to be brought
into the unified point model to be managed and consumed.

PostgreSQL is an open-source object-relational database (ORDBMS) known for strict SQL-standard compliance, strong
transactions (MVCC), and rich data types (JSON/JSONB, arrays, ranges, geometry). It listens on port `5432` by default,
and clients connect over standard JDBC (`org.postgresql.Driver`). Onboarding it into an IoT platform essentially means "
treating a database as a class of device": one row, one column of a table becomes the source of a point's value.

In the four-layer IoT reference architecture, a database-bridge driver straddles the boundary between the network and
platform layers—it parses no fieldbus frames, but re-admits "data already landed in a table" into the polling pipeline
as points. For how this path connects to time-series storage and stream processing,
see [Time-Series Data & Stream Processing](../foundations/data-pipeline).

::: info How it differs from fieldbus drivers
Drivers like Modbus and OPC UA face "live physical quantities"—registers change in real time, and what you read is the
current field value. A database driver faces "data already persisted"—what you read is a snapshot written by some
upstream system, possibly already stale. When treating a column as a point, align the polling cadence with the upstream
write cadence, or you will repeatedly read the same old value.
:::

## Attribute configuration

Onboarding uses three kinds of attributes: **driver attributes** (device level—which database, which account), **point
attributes** (the read/write SQL per point), and **command attributes** (a reserved item, currently inert). Every
default comes from the driver's `application.yml`; you fill in concrete values on
the [Device](../introduction/concepts/device)/[Point](../introduction/concepts/point). For where attributes come from
across the three layers, see [Attributes & Config](../introduction/concepts/attribute-config).

### Driver attributes (device-level `driver-attribute`)

When onboarding a PostgreSQL database, fill in these [attributes](../introduction/concepts/attribute-config) on
the [Device](../introduction/concepts/device). They decide which database to connect to, which account to use, and the
connection timeout:

| Attribute     | code           | Type   | Default     | Remark                                                                            |
|---------------|----------------|--------|-------------|-----------------------------------------------------------------------------------|
| Host          | `host`         | STRING | `localhost` | PostgreSQL host                                                                   |
| Port          | `port`         | INT    | `5432`      | PostgreSQL port                                                                   |
| Database      | `database`     | STRING | (empty)     | Database name to connect to                                                       |
| Username      | `username`     | STRING | `root`      | Connection account                                                                |
| Password      | `password`     | STRING | (empty)     | Account password                                                                  |
| Query Timeout | `queryTimeout` | INT    | `30`        | Connection-acquire timeout (seconds); used as the Hikari pool `connectionTimeout` |

The driver builds the JDBC URL from `host`, `port`, and `database` (in the form `jdbc:postgresql://host:port/database`).
Device validation (`validate()`) checks that all five of `host`, `port`, `database`, `username`, `password` are
present—any empty one fails; `queryTimeout` is optional and defaults to `30` seconds. Each device maps to its own
HikariCP connection pool inside the driver (`maximumPoolSize=5`, `minimumIdle=1`), cached and reused by device ID.

### Point attributes (`point-attribute`)

On each polled [Point](../introduction/concepts/point), fill in its read/write SQL:

| Attribute   | code         | Type   | Default | Remark                                                                                                     |
|-------------|--------------|--------|---------|------------------------------------------------------------------------------------------------------------|
| Read Query  | `readQuery`  | STRING | (empty) | `SELECT` statement for reading the point value                                                             |
| Write Query | `writeQuery` | STRING | (empty) | `UPDATE`/`INSERT` for writing, using a single `?` placeholder for the written value (bound as a parameter) |

::: tip Read Query takes the first column of the first row
`readQuery` is a plain `SELECT`; after running it the driver takes the **first column of the first row** (
`rs.getObject(1)`) as the point's value, then `toString()`s it for the point to parse by its data
type ([Point](../introduction/concepts/point) `pointTypeFlag`). So a single-row, single-column query like
`SELECT temperature FROM sensor WHERE id = 1` is the safest form. `readQuery` is required on a point; point validation (
`validatePoint()`) fails without it.
:::

### Command attributes (`command-attribute`)

The write command keeps one attribute, but it is not consumed by the implementation:

| Attribute     | code           | Type   | Default | Remark                                    |
|---------------|----------------|--------|---------|-------------------------------------------|
| Execute Query | `executeQuery` | STRING | (empty) | SQL to execute for the command (reserved) |

::: warning `executeQuery` is currently not consumed by the implementation
Writing a value actually goes through the point's `writeQuery`: the driver's `write()` reads the `point-attribute`
`writeQuery` and runs the `UPDATE`/`INSERT` with prepared-statement parameter binding. The `executeQuery` on
`command-attribute` is kept only as a configuration item—no code in the current driver reads or executes it; there is no
separate "execute a SQL string per command" path. To write values, put the SQL in the point's `writeQuery`; configuring
`executeQuery` has no effect.
:::

### Polling & health

- **Polling interval**: default cron `0/30 * * * * ?` (read once every 30 seconds).
- **Custom interval**: the driver also has a custom schedule, default cron `0/5 * * * * ?` (every 5 seconds); the JDBC
  database driver's `schedule()` is an empty implementation, so this schedule currently does nothing.
- **Health/online**: device health check defaults to cron `0/15 * * * * ?` with a lease timeout of `45 seconds`. The
  health check borrows a connection from the pool and uses `conn.isValid(5)` to decide reachability—if it can't connect,
  the device goes offline. See [Device](../introduction/concepts/device) for the online-state mechanism.

## Troubleshooting

When a database-bridge onboarding fails, the cause is rarely the protocol itself—it is the connection parameters, the
SQL shape, or the upstream database's state. Ordered by frequency:

::: warning Device stays offline / pool won't come up
The health check decides with `conn.isValid(5)`. If a device is always offline, first confirm `host`/`port` is
reachable (don't use `localhost` from inside a container to reach the host), `database` name case is correct, and the
`username`/`password` account can log into that database. PostgreSQL is also gated by `pg_hba.conf`—if the source IP or
auth method isn't allowed it rejects the connection outright; that error is on the database side, so open it up there.
:::

::: warning Read Query must be read-only and resolve to a single value
The driver takes only the first column of the first row of the `readQuery` result, so the query should return a single
row and column and pinpoint the target row with a `WHERE` primary-key condition. With multiple rows/columns only the
first is used and may not be the row you meant; with no result the point value is `null`. Never put `UPDATE`/`DELETE` in
`readQuery`—reading is a read-only path, and writing there corrupts data. Write the filter fully so you don't pick the
wrong row as the table's data changes.
:::

::: warning Write Query uses a `?` placeholder, not `${value}`
When writing, `writeQuery` uses a single `?` for the value (e.g. `UPDATE sensor SET temperature = ? WHERE id = 1`), and
the driver binds the command parameter as a JDBC parameter via `ps.setString(1, value)`—prepared-statement binding, not
string concatenation, inherently safe from SQL injection. Do not concatenate the value by hand, and do not use template
syntax like `${value}`: it would neither be substituted nor give you injection protection. A write is judged successful
by "affected rows > 0", so an `UPDATE` that hits 0 rows (the `WHERE` matched nothing) counts as a write failure.
:::

::: tip Identifier case follows PostgreSQL rules
PostgreSQL folds unquoted identifiers to lowercase and matches double-quoted ones literally. A wrong-case `database`
won't connect; table/column names in `readQuery`/`writeQuery` that don't match the case used at creation will report "
does not exist". Fill them in with the real case, and double-quote identifiers in the SQL when needed.
:::

::: tip Query timeout / slow SQL
`queryTimeout` (default 30 seconds) is used as the Hikari pool `connectionTimeout`—the upper bound on waiting to acquire
a connection. For large tables or slow SQL, optimize the SQL, add indexes, and narrow the `WHERE` rather than simply
raising the timeout—a slow query holds a pool connection (only 5 exist) and drags down polling of the whole batch.
:::

## How it lands in IoT DC3

- **Driver name / code**: `PostgreSQL Driver` / `PostgresqlDriver` (`dc3.driver.code` is a stable routing identifier the
  platform routes messages by—don't change it casually).
- **Type**: `DRIVER_CLIENT`—the driver actively connects and issues queries; it does not listen for pushes.
- **Read / write / subscribe**: consistent with the [driver capability matrix](./matrix)—read ✓, write ✓, subscribe —.
  Reads take the first value of a `SELECT`; writes use the prepared binding of `writeQuery`; a database has no change
  subscription, so values are pulled by periodic polling.

::: info Implementation status: available (not a skeleton)
The PostgreSQL driver is an **available implementation**, not a skeleton. Connection, read, write, and health check are
all provided by the shared `dc3-common-sql` abstract base service `AbstractJdbcDriverCustomService` (the same logic
shared with MySQL, Oracle, and SQL Server); the PostgreSQL subclass only supplies JDBC URL construction, the driver
class name `org.postgresql.Driver`, and the default port `5432`. The one caveat is the `command-attribute`
`executeQuery`, which is reserved but unwired (see Attribute configuration above).
:::

### Minimal onboarding example

Onboard the `temperature` column of the `id=1` row in a `sensor` table as a temperature point:

1. Create a [Device](../introduction/concepts/device) with `PostgreSQL Driver`, and set the driver attributes
   `host=192.168.1.10`, `port=5432`, `database=iot`, `username=root`, `password=******`.
2. Add a temperature [Point](../introduction/concepts/point) (`pointTypeFlag=FLOAT`, `READ_ONLY`) to
   the [Profile](../introduction/concepts/profile) bound to the device, and set the point attribute
   `readQuery=SELECT temperature FROM sensor WHERE id = 1`.
3. Start the driver, and within 30 seconds the queried temperature shows up in
   the [PointValue](../introduction/concepts/point-value).

For a writable point, also set `writeQuery=UPDATE sensor SET temperature = ? WHERE id = 1` on the same point and make
its `rwFlag` writable. For a complete walkthrough, see [Device Onboarding](../operation/device-onboarding).

## Further reading

- [Drivers Overview](./index) — categorization and selection map of all drivers
- [Driver Capability Matrix](./matrix) — read/write/subscribe capabilities at a glance
- [Device Onboarding](../operation/device-onboarding) — a complete onboarding walkthrough
- [Time-Series Data & Stream Processing](../foundations/data-pipeline) — how polled point values land in the time-series
  store and feed stream processing
- [MySQL Driver](./mysql) — another JDBC database data source with an identical configuration structure