Database
Category: Data
Node id: database
Executes SQL queries and commands against PostgreSQL, MySQL, or SQLite databases
What it does
- Multi-database support: PostgreSQL, MySQL, SQLite
- Parameterized queries to prevent SQL injection
- Transaction support with automatic rollback on failure
- Batch operations for bulk inserts and updates
- Connection pooling with reuse across executions
Use cases
- Store processed FHIR resources in a relational database
- Query patient demographics for pipeline enrichment
- Batch-insert clinical observations from HL7 feeds
- Run analytics queries on healthcare data warehouses
Runtime
- Performance tier:
variable
Input ports
| Name | Type | Required | Description |
|---|---|---|---|
query | text | No | SQL query to execute (overrides config query) |
parameters | json | No | Array of query parameters for parameterized queries |
batch_data | json | No | Array of parameter arrays for batch operations |
tables | json | No | load_tables op: a {table: [rows]} payload (create-if-missing, all-TEXT, batch INSERT). Wire e.g. dicom_deid:crosswalk_tables here to keep the re-id key in the LOCAL DB. |
Output ports
| Name | Type | Required | Description |
|---|---|---|---|
result | json | No | Database operation result: query returns rows/row_count/columns; execute returns rows_affected/last_insert_id; transaction returns results/query_count; batch returns batch_count/rows_affected |
success | boolean | No | Whether the database operation succeeded |
Settings
Basic
| Setting | Name | Type | Required | Default | Allowed values | Description |
|---|---|---|---|---|---|---|
| Database Driver | driver | enum | Yes | postgres | postgres, mysql, sqlite3 | Database driver to use |
| Operation | operation | enum | Yes | query | query, execute, transaction, batch, load_tables | Database operation to perform |
| SQL Query | query | text | No | SQL query or command to execute | ||
| Bind Parameters From | parameters_from | string | No | Comma-separated input ports to bind as $1, $2 ... in order, written as "port" or "port.column". Pulls a single scalar out of whatever an upstream node produced -- a bare value, a rows array, or a {rows:[...]} envelope -- so a query result wires straight in. Use it when a statement must act on the row THIS run handled: without it there is no way to name that row, because the generic {{placeholder}} resolver skips maps and slices. A port with no value binds NULL rather than shifting later positions. |
Auth
| Setting | Name | Type | Required | Default | Allowed values | Description |
|---|---|---|---|---|---|---|
| Connection String | connection_string | secret | Yes | Database connection string |
Advanced
| Setting | Name | Type | Required | Default | Allowed values | Description |
|---|---|---|---|---|---|---|
| Transaction Queries | queries | json | No | Array of SQL queries for transaction operation | ||
| Table Prefix | table_prefix | string | No | load_tables op: prefix prepended to each table name (e.g. pax_) | ||
| Drop Existing | drop_existing | boolean | No | false | load_tables op: DROP each target table before recreating it | |
| Replace By Key | replace_keys | json | No | load_tables op: {table: column} naming the column that identifies a row across runs. Rows carrying those key values are DELETEd before the batch is inserted, so re-running a study replaces its rows instead of adding a second copy. Absent = append, which is what leaves duplicates behind when a study is retried. |
Errors
| Code | Description |
|---|---|
CONNECTION_FAILED | Failed to connect to the database |
QUERY_ERROR | SQL query execution failed |
TRANSACTION_FAILED | Transaction failed and was rolled back |
UNSUPPORTED_DRIVER | The specified database driver is not supported |
Runtime notes
- Output shape under 'result' varies by operation: query has rows/row_count/columns, execute has rows_affected/last_insert_id, transaction has results/query_count, batch has batch_count/rows_affected
- The 'query' input overrides the config query — useful for dynamic SQL from upstream nodes
- Connections are pooled and reused across executions; max 25 open, 5 idle, 5-minute lifetime
- Transaction and batch operations auto-rollback on any query failure
See Build any workflow for how to put nodes together and Ports, types and data flow for how port types connect.
Something unclear? Send documentation feedback by email.