PostgreSQL as a label data source: one query, thousands of labels

The article master data lives in PostgreSQL and the labels should come out of it. The usual route is a CSV export that is already stale the moment it finishes. The better route: the database stays the source, and label printing queries it directly.

Everything described here applies to MySQL and MariaDB as well - only the connection string looks different.

Two routes into the database

Through the agent, when the database sits inside your own network. The zplCloudCli agent opens an outbound connection to the platform, runs the query locally and sends back only the result rows. The connection string lives on your machine alone. No inbound ports are needed.

Straight from the cloud, when the database is reachable anyway - an Azure Database for PostgreSQL or Amazon RDS, for example. The platform then connects by itself; the connection string is stored AES-encrypted in the platform database and decrypted only to run the query.

Agent route: register the server

The agent learns about its databases through an option, an environment variable or a file. All three are equivalent.

# as a start-up option
zplcloud proxy --agent "Warehouse" \
  --postgres WMS="Host=pg.internal.lan;Database=wms;Username=zplcloud;Password=...;SSL Mode=Require"

# or as an environment variable
export ZPLCLOUD_PG_WMS_CONNECTION="Host=pg.internal.lan;Database=wms;Username=zplcloud;Password=..."
zplcloud proxy --agent "Warehouse"

# or as pgservers.json next to the binary or under ~/.zplcloud/
# { "postgresServers": { "WMS": "Host=pg.internal.lan;Database=wms;Username=zplcloud;Password=..." } }

The name behind the option (WMS) is what shows up in the picker later. For MySQL the option is --mysql, for MariaDB --mariadb, with matching variables and files.

One tip from practice: create a dedicated database user with read-only rights on exactly the tables labels need. The agent only ever runs SELECT, but an account that cannot do more is the stronger guarantee.

Creating the data source

Under Data hub → Data sources → New pick the type PostgreSQL (agent) or PostgreSQL (Cloud), then the agent and the server. The query field takes a single SELECT:

SELECT a.sku, a.description, a.ean, s.bin, a.best_before
FROM article a
JOIN stock s ON s.article_id = a.id
WHERE a.active = true

Fields reads the columns without loading any data - afterwards they are available in the designer as bindings. Test checks the connection.

What a query may contain

The rules are deliberately tight, because a data source is there to read and not to write:

• A single SELECT or WITH only, no semicolon, no comments, no multiple statements.
• Filter values always reach the database as parameters, never as assembled text. SQL injection through the filter row is therefore not possible.
• Filter, sort and LIMIT/OFFSET are applied by the database, not by the platform. Your indexes do their job.
• At most 1000 rows per fetch, 15 second timeout.

Those 1000 rows are not a ceiling for printing: for bulk runs the platform fetches one chunk after another server-side, up to 50,000 labels in one go. Only one chunk is ever held in memory.

Where the data ends up

A finished data source is available everywhere data is needed:

• Print views - a protected URL with a form where somebody searches by EAN and prints the label that comes back.
• Batch printing - every hit of a query at once, sent to the printer in chunks.
• Designer - as binding data, so the preview shows real values instead of placeholders.

Common pitfalls

• "The query must start with SELECT." A trailing semicolon is enough to trigger this. Drop it.
• No columns found. Computed columns need a name: SELECT price 1.19 AS gross instead of SELECT price 1.19.
• Connection fails. Reachable PostgreSQL servers usually insist on TLS: SSL Mode=Require belongs in the connection string.
• Agent not online. The agent maintains the connection itself. The agent overview in the data hub shows which ones are present and which servers they report.