Database Destination

Insert or update records in a relational database using parameterized SQL statements.

Overview

The Database destination writes processed messages directly to a relational database using parameterized SQL statements. It supports PostgreSQL, MySQL, and Microsoft SQL Server. This destination is commonly used to persist normalized clinical data, store lab results, maintain audit logs, or populate analytics tables from healthcare message streams.

Configuration

Define a Database destination in your root intu.yaml under the destinations key:

yaml

# intu.yaml
destinations:
  lab-results-db:
    type: database
    database:
      driver: postgres
      dsn: "postgres://${DB_USER}:${DB_PASS}@db.hospital.internal:5432/clinical?sslmode=require"
      statement: |
        INSERT INTO lab_results (
          patient_id, order_id, test_code, test_name,
          result_value, units, reference_range, abnormal_flag,
          collection_date, received_at
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
        ON CONFLICT (order_id, test_code) DO UPDATE SET
          result_value = EXCLUDED.result_value,
          received_at = NOW()
      max_conns: 10

Properties

driver string required

Database driver. One of: postgres, mysql, mssql.

dsn string required

Data source name (connection string). Format varies by driver. Supports environment variable interpolation with ${VAR} syntax. Always use environment variables for credentials.

statement string required

Parameterized SQL statement to execute for each message. Use positional placeholders ($1, $2, ... for PostgreSQL; ? for MySQL/MSSQL). The transformer must return parameters in the matching order.

max_conns int optional

Maximum number of open database connections in the pool. Defaults to 5.

max_idle_conns int optional

Maximum number of idle connections retained in the pool. Defaults to 2.

conn_max_lifetime_ms int optional

Maximum lifetime of a connection in milliseconds before it is closed and replaced. Defaults to 0 (no limit).

DSN Format by Driver

Driver	DSN Format
`postgres`	`postgres://user:pass@host:5432/dbname?sslmode=require`
`mysql`	`user:pass@tcp(host:3306)/dbname?tls=true`
`mssql`	`sqlserver://user:pass@host:1433?database=dbname&encrypt=true`

Full Example

Receive HL7v2 ORU lab result messages via MLLP, extract individual test results, and store them in a PostgreSQL table.

Root Configuration

yaml

# intu.yaml
destinations:
  clinical-db:
    type: database
    database:
      driver: postgres
      dsn: "postgres://${DB_USER}:${DB_PASS}@db.hospital.internal:5432/clinical?sslmode=require"
      statement: |
        INSERT INTO lab_results (
          patient_id, order_id, test_code, test_name,
          result_value, units, reference_range, abnormal_flag,
          collection_date
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      max_conns: 10

Channel Configuration

yaml

# channels/lab-to-db/channel.yaml
name: lab-to-db
description: Persist HL7v2 lab results to PostgreSQL

source:
  type: tcp
  tcp:
    port: 6664
    mode: mllp

destinations:
  - clinical-db

Destination Transformer

typescript

// channels/lab-to-db/transformer.ts
import { Message, Context } from "@intu/sdk";

export default function transform(msg: Message, ctx: Context): Message[] {
  const hl7 = msg.body;
  const pid = hl7.segments?.find((s: any) => s.name === "PID");
  const obr = hl7.segments?.find((s: any) => s.name === "OBR");
  const obxSegments = hl7.segments?.filter((s: any) => s.name === "OBX") ?? [];

  const patientId = pid?.fields[3]?.components[0] ?? "";
  const orderId = obr?.fields[2]?.components[0] ?? "";
  const collectionDate = obr?.fields[7]?.components[0] ?? "";

  return obxSegments.map((obx: any) => ({
    ...msg,
    body: {
      params: [
        patientId,
        orderId,
        obx.fields[3]?.components[0] ?? "",
        obx.fields[3]?.components[1] ?? "",
        obx.fields[5]?.components[0] ?? "",
        obx.fields[6]?.components[0] ?? "",
        obx.fields[7]?.components[0] ?? "",
        obx.fields[8]?.components[0] ?? "",
        collectionDate,
      ],
    },
  }));
}

Security Never embed database credentials directly in YAML files. Always use environment variable references (${DB_USER}, ${DB_PASS}) and define the actual values in your .env file, which should be excluded from version control.

Note The transformer must return a body.params array whose elements correspond positionally to the placeholders in the SQL statement. For fan-out scenarios (e.g. one HL7 message producing multiple OBX rows), the transformer can return an array of messages.