Docs / Workflows / Actions / Database actions
Database actions
Read or change records in an OAIZ database. Query database runs a SELECT, and Write to database runs an INSERT, UPDATE or DELETE.
Query database
A query returns records without changing them. The action runs one SELECT in the selected database, using the job account’s access and row rules. Each returned row is an object keyed by column name, so joined columns need distinct names.
SELECT
id,
name
FROM customers
WHERE id = $1This example expects a customers table. Its first parameter is {{ properties.customer_id }}. The result includes rows, columns, rowCount and truncated, which reports whether the returned result was cut short.
Write to database
A write runs one INSERT, UPDATE or DELETE. RETURNING supplies the changed records as results. A data-modifying WITH statement can combine related changes into one statement.
UPDATE "customers"
SET status = $1
WHERE id = $2
RETURNING
id,
statusThe first parameter is {{ properties.status }} and the second is {{ properties.customer_id }}. The result includes command, table, rowCount and rows. An output event is optional when the stored change is the workflow’s final result.
SQL and values
- databaseUuid
- Identifies the OAIZ database. These actions use managed OAIZ databases rather than a database reached through a provider connection.
- sql
- The statement is sent as written. Table and schema changes belong to database management.
- params
- An ordered list of values bound to $1, $2 and subsequent placeholders. Event references belong here. A parameter containing one property reference keeps that property’s JSON type. A boolean or list binds only to a placeholder of its own type, such as a boolean column or an array, or to jsonb. Otherwise the action fails and names the placeholder to cast. Cast it to jsonb, for example $3::jsonb, and read it with ($3::jsonb)::boolean or jsonb_array_elements_text($3::jsonb). A missing referenced property fails the action.
Limits and test effects
Each statement has a 30-second execution limit. Reads and RETURNING return at most 1,000 rows. Writes have no row-count or change-data size cap.
Committed writes with row changes produce one row event. Check rowImagesOmitted before using its changes entries. When false, entries contain per-row images. When true, they contain counts and empty image objects. Both cases use the same entry fields. The Databases reference explains the fields and when images are omitted.
With the Write permission off, a test executes the write and rolls it back. With Write on, it commits real changes. The workflow’s Publish Events setting does not prevent committed database row events. PostgreSQL sequences can advance even when row changes roll back.