PostgreSQL CSV API
The node registers the route GET /api/opendata/v1/:model.csv in Node-RED and serves datasets from PostgreSQL as CSV files through it. Which schemas, tables and columns are published is defined in a model registry; the database connection lives in its own config node. The node is intended for open data offerings where an API gateway such as APISIX handles public access.
- Node type
- postgresql-csv-api (config nodes: postgresql-csv-api-registry, postgresql-csv-api-connection)
- Package
- @htag/node-red-contrib-hypertegrity-endpoints
- Inputs / outputs
- 0 / 0
- Target
- PostgreSQL (optionally with PostGIS)
When to use this node
Municipalities often provide the measurement data of their data platform as open data. The PostgreSQL CSV API node makes tables of the platform database, for example the temporal storage of the context broker, available as CSV downloads without you having to maintain your own SQL queries or HTTP nodes.
Typical use cases are:
- Publishing monthly time series: Weather, water level or air quality data of one month as CSV for an open data portal.
- Providing yearly datasets: Making yearly aggregated tables retrievable with a single call per year.
- Excluding sensitive columns and devices: Individual columns or
entity_idvalues are excluded from the export and column names are renamed for the public.
How the node works
Register route
On deploy the node registers GET /api/opendata/v1/:model.csv on the Node-RED HTTP server and obtains models and connection from the model registry.
Validate request
The model name from the URL is looked up in the registry; only active models are served. The time parameters month or year are validated.
Query database
A parameterized SELECT query reads the rows of the requested period, excludes entity IDs and adds PostGIS coordinates if configured.
Deliver CSV
Excluded columns are removed, headers renamed and the file sent as a download with content type text/csv.
Result: One CSV file per model and period, generated live from PostgreSQL without intermediate storage.
The node is request-driven: it has no inputs or outputs in the flow and acts exclusively on HTTP requests. The route can be registered only once per Node-RED instance; a second node of the same type reports an error. The database query runs on every request.
Configuration
The configuration is spread across three nodes: the config node PostgreSQL CSV API: Connection, the config node PostgreSQL CSV API: Model Registry and the actual node PostgreSQL CSV API.
PostgreSQL CSV API: Connection (postgresql-csv-api-connection)
| Field | Type | Default | Meaning |
|---|---|---|---|
| Name | Text | empty | Display name; otherwise <host>:<port>/<database>. |
| Host | Text | ${DB_HOST} | Host name or IP of the PostgreSQL server. Placeholders of the form ${VAR} are resolved from the environment; empty means the environment variable DB_HOST. |
| Port | Number | 5432 | Port of the server; empty means DB_PORT, otherwise 5432. |
| Database | Text | ${DB_NAME} | Name of the database; empty means DB_NAME. |
| User | Text (credential) | empty | Database user, stored encrypted in flows_cred.json; empty means DB_USER. |
| Password | Password (credential) | empty | Password, stored encrypted; empty means DB_PASSWORD. |
The connection pool (at most 10 connections, connection timeout 10 s, application_name = node-red-postgresql-csv-api) is created on first access and shared by all nodes that reference this connection. The connection to the database is made without SSL.
PostgreSQL CSV API: Model Registry (postgresql-csv-api-registry), Database tab
| Field | Type | Default | Meaning |
|---|---|---|---|
| Name | Text | empty | Display name of the registry. |
| PostgreSQL | Config node | none | Connection for export and editor lookups. Without a selection the environment variables DB_HOST, DB_PORT, DB_NAME, DB_USER and DB_PASSWORD are used. |
| Test connection | Button | – | Verifies the connection through the admin endpoint /postgresql-csv-api/test-connection and shows database name and server version. The connection must already be deployed. |
Model Registry, Models tab
On the left the dialog lists all models with a search field and the Add model button; on the right you edit the selected model. Models are stored as JSON in the node property models.
| Field | Type | Default | Meaning |
|---|---|---|---|
| Public model name | Text | empty | Forms the path of the endpoint, for example weather-observed. Lower-case letters, digits and hyphens are allowed (^[a-z0-9]+(?:-[a-z0-9]+)*$); names must be unique. |
| Status: Active | Checkbox | enabled | Only active models are served; inactive ones are answered with 400. |
| Schema | Text | empty | PostgreSQL schema of the table. |
| Table | Text | empty | Table name. |
| Timestamp column | Text | empty | Column with the timestamp used for filtering and sorting. |
| Frequency | Select | Monthly | month: export per calendar month, parameter month. year: export per calendar year, parameter year. |
| Point column (optional) | Text | empty | PostGIS geometry column; the export derives the columns longitude and latitude from it (ST_X/ST_Y), missing geometries yield 0.0. |
| Exclude columns | Sub-view | empty | Load columns reads the table columns via /postgresql-csv-api/columns; checked columns are missing from the CSV. Renamed columns cannot be excluded. |
| Exclude entities | Sub-view | empty | Load entity IDs reads via /postgresql-csv-api/entities the distinct, non-empty values of the column entity_id from the previous complete month or year; checked IDs are not exported. IDs cannot be entered manually; stored IDs that are absent from the loaded period remain visible. |
| Rename columns | Sub-view | empty | Mappings source column → public header. Source columns must be valid identifiers, headers may contain arbitrary text but must not be empty or duplicated. longitude and latitude are reserved. |
Schema, table, timestamp column, point column and excluded columns must match the pattern ^[a-z_][a-z0-9_]*$. The dialog refuses to save invalid models ("Please use unique model names and valid PostgreSQL identifiers"); the runtime checks the same rules again on deploy.
PostgreSQL CSV API (postgresql-csv-api)
| Field | Type | Default | Meaning |
|---|---|---|---|
| Name | Text | empty | Display name of the node in the flow. |
| Registry | Config node | – | Model registry with models and connection (required). |
For information the dialog shows the registered route GET /api/opendata/v1/:model.csv.
The database user requires USAGE on every configured schema and SELECT on every published table, plus read access to information_schema.columns. Write privileges are not required. Use a dedicated, read-only database user.
Input
The node has no input in the flow. It is triggered by HTTP requests to the registered route:
| Element | Type | Meaning |
|---|---|---|
GET /api/opendata/v1/<model name>.csv | Route | <model name> is the public model name of an active model in the registry. |
?month=YYYY-MM | Query parameter | Frequency Monthly only: requested calendar month. Without the parameter the previous complete month is exported. |
?year=YYYY | Query parameter | Frequency Yearly only: requested calendar year. Without the parameter the previous complete year. |
| Authentication | – | The node itself checks neither credentials nor API keys. The route lives on httpNode and is therefore subject only to the httpNodeAuth or httpNodeMiddleware settings configured in settings.js. An API gateway is intended for public access (see Notes). |
Period boundaries are calculated in the Europe/Berlin time zone. Future months or years are rejected with 400.
Output
The node sends no message into the flow. The HTTP response is a CSV file with Content-Type: text/csv; charset=utf-8, Content-Disposition: attachment; filename="<model name>-<period>.csv" and Cache-Control: no-store.
"entity_id","time_index","temperature","relative_humidity","longitude","latitude"
"urn:ngsi-ld:WeatherObserved:station-01","2026-09-01T00:10:00.000Z","14.2","81","8.353","50"
"urn:ngsi-ld:WeatherObserved:station-01","2026-09-01T00:20:00.000Z","14.1","82","8.353","50"
| Element | Meaning |
|---|---|
| Header row | All columns of the table (SELECT t.*) in table order without excluded columns, plus longitude and latitude when a point column is set; headers according to the renames. |
| Rows | Sorted ascending by the timestamp column. Every value is enclosed in double quotes, embedded quotes are doubled. NULL is output empty, dates as ISO 8601, objects and arrays as JSON. The line separator is CRLF. |
| Empty period | Returns a CSV containing only the header row; the columns then come from information_schema.columns. |
| HTTP 200 | Successful export. |
| HTTP 400 | Unknown or inactive model (Unsupported OpenData model.), invalid time parameter (Invalid month. Use YYYY-MM., Invalid year. Use YYYY.) or future period (Future months are not available., Future years are not available.) as JSON {"error": "…"}. |
| HTTP 500 | Database or configuration error, for example a missing renamed source column or duplicate headers. The response always reads The OpenData export is currently unavailable.; details appear only in the Node-RED log. |
Example flow
The package ships with an example flow. Import it in Node-RED via Menu → Import → Examples → @htag/node-red-contrib-hypertegrity-endpoints → postgresql-csv-api. Alternatively download postgresql-csv-api.json and import it via Menu → Import. After the import, create the connection, the registry and at least one model in the edit dialog.
Create connection
In the registry choose 'Add new' under PostgreSQL and enter host, database and credentials, or rely on the DB_* environment variables.
Define model
On the Models tab set model name, schema, table, timestamp column and frequency and exclude columns or entities.
Deploy and fetch
After deploy, GET /api/opendata/v1/<model name>.csv delivers the file, for example via browser or curl.
Show example flow as JSON
[
{"id": "tab1", "type": "tab", "label": "Endpoints", "disabled": false, "info": ""},
{"id": "api1", "type": "postgresql-csv-api", "z": "tab1", "name": "", "x": 200, "y": 100, "wires": []},
{"id": "c1", "type": "comment", "z": "tab1", "name": "PostgreSQL CSV API: Creates an API Endpoint for PostgreSQL in Nodered", "info": "", "x": 320, "y": 40, "wires": []}
]
Status and errors
| Indicator | Meaning |
|---|---|
| Green dot "GET /api/opendata/v1/:model.csv" (API node) | Route registered, connection pool ready. |
| Red ring "missing registry or httpNode" (API node) | No registry selected or Node-RED provides no httpNode server. |
| Red ring "duplicate route" (API node) | A second PostgreSQL CSV API node is already active. |
| Red ring "database unavailable" (API node) | The selected connection is not deployed yet (Could not create PostgreSQL pool: …). |
| Green dot "2 model(s)" (registry) | Models loaded and validated successfully. |
| Red ring "invalid configuration" (registry) | A model violates the identifier rules or a model name is duplicated; details in the log (Invalid schema for …, Duplicate public model name: …). |
| Error "PostgreSQL pool error: …" (connection) | Error in the connection pool, for example a dropped connection. |
| Error in the log on HTTP 500 | The cause (database error, Configured renamed column does not exist: …, Configured output column names are not unique) is only logged, never sent to the client. |
Notes
- Protection against SQL injection: Schema, table, timestamp column, point column and column names are checked against the pattern
^[a-z_][a-z0-9_]*$(allow-list) before use and only then inserted into the query. The model name from the URL is never used in SQL, only looked up in the registry. Period boundaries and excluded entity IDs are passed to PostgreSQL as query parameters ($1,$2, …). There are no free filter parameters. - Access control: The node does not authenticate requests. Node-RED must not be directly reachable from the public network. An API gateway (APISIX) is intended that rewrites the public prefix
/opendata/v1/*to/api/opendata/v1/*, checks an API key (key-auth, headerX-API-Key) and exposes only this route. The Node-RED editor must not be reachable through the gateway. - Admin endpoints: Load columns, load entity IDs and test connection run on
httpAdminand require theflows.readpermission of the Node-RED editor. Their error messages contain no database details. - Table requirements: Excluding entities and Load entity IDs require the table to have a column
entity_id. The point column requires the PostGIS extension (ST_X,ST_Y). Excluded columns are read from the database but not output. - Periods: Month and year boundaries are built with
make_timestamptz(…, 'Europe/Berlin'); timestamps in the CSV appear in UTC (ISO 8601). - Load: Every request triggers a full query of the period; there is no cache (
Cache-Control: no-store). For large tables an index on the timestamp column and, if needed, caching in the gateway are recommended. - Personal data: Exported tables can contain personal data, such as devices assigned to individual households or locations of individual persons. The node removes nothing automatically. Review every model before publication, exclude affected columns and
entity_idvalues and document the open data release. - Units: Values are taken from the database unchanged; rename columns to include units, for example
Temperature (°C).