Zum Hauptinhalt springen

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​

NameTypeRequiredDescription
querytextNoSQL query to execute (overrides config query)
parametersjsonNoArray of query parameters for parameterized queries
batch_datajsonNoArray of parameter arrays for batch operations
tablesjsonNoload_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​

NameTypeRequiredDescription
resultjsonNoDatabase 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
successbooleanNoWhether the database operation succeeded

Settings​

Basic​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Database DriverdriverenumYespostgrespostgres, mysql, sqlite3Database driver to use
OperationoperationenumYesqueryquery, execute, transaction, batch, load_tablesDatabase operation to perform
SQL QueryquerytextNoSQL query or command to execute
Bind Parameters Fromparameters_fromstringNoComma-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​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Connection Stringconnection_stringsecretYesDatabase connection string

Advanced​

SettingNameTypeRequiredDefaultAllowed valuesDescription
Transaction QueriesqueriesjsonNoArray of SQL queries for transaction operation
Table Prefixtable_prefixstringNoload_tables op: prefix prepended to each table name (e.g. pax_)
Drop Existingdrop_existingbooleanNofalseload_tables op: DROP each target table before recreating it
Replace By Keyreplace_keysjsonNoload_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​

CodeDescription
CONNECTION_FAILEDFailed to connect to the database
QUERY_ERRORSQL query execution failed
TRANSACTION_FAILEDTransaction failed and was rolled back
UNSUPPORTED_DRIVERThe 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.

Etwas unklar? Senden Sie Feedback zur Dokumentation per E-Mail..