Joynare Nexus: Database Adapters

Joynare Nexus implements a decoupled database adapter system inspired by the WebMethods Flow architecture. It separates Infrastructure (Connections) from Business Logic (Adapter Services).

Core Concepts

• Connections: Defined centrally in config/connections.yaml. They manage the physical connection pools and credentials.
• Adapter Services: Defined in .adapter files. they perform specific operations (Select, Insert, etc.) using a named connection.
• Unified Pipeline: Adapters are "First-Class" services. They can be executed directly via CLI or called from any Flow Service.

---

1. Connection Configuration

Connections are managed in config/connections.yaml. This file should be treated as administrative configuration.

Schema

connections:
  - name: "ConnectionName"   # Referenced by adapters
    type: "sql"              # Currently only "sql" is supported
    driver: "sqlite3"        # sqlite3, postgres, mysql, etc.
    dsn: "connection_string" # Data Source Name

Example (SQLite)

connections:
  - name: "CustomerDB"
    type: "sql"
    driver: "sqlite3"
    dsn: "./data/customers.db"

---

2. Adapter Service Definition

Adapter services are YAML files with a .adapter extension. They define the SQL operation and how data maps to the Pipeline.

Anatomy of an .adapter file

name: "GetCustomerById"
namespace: "customers"
connection: "CustomerDB" # Reference to name in connections.yaml
type: "sql"
operation: "select"      # select, insert, update, delete
sql: "SELECT * FROM users WHERE id = ${userId}"

pipeline:
  input:
    userId: "int"
  output:
    results: "array"     # The engine automatically populates this

# Optional: Map DB columns to specific pipeline field names
outputMap:
  username: "customerName"
  email: "contactEmail"

---

3. SQL Operations

Select

Queries return a results array (all rows) and a row object (the first row) in the pipeline.

• If outputMap is provided, only mapped columns are renamed; others are included as-is.
• If no outputMap is provided, all columns are merged into the pipeline using their DB column names.

Insert / Update / Delete

These operations perform modifications and return:

• rowsAffected: (int) The number of rows modified by the operation.

---

4. Variable Binding (Dynamic SQL)

Joynare Nexus supports safe parameter binding using the ${variableName} syntax.

• Variables are extracted from the Pipeline at runtime.
• The engine uses prepared statements to prevent SQL injection.

Example:

UPDATE orders SET status = ${newStatus} WHERE id = ${orderId}

---

5. Usage

Direct Execution (Testing)

You can run an adapter directly from the CLI to verify it:

go run ./cmd/esb run customers:GetCustomerById --input '{"userId": 1}'

Invocation in Flows

Call an adapter from a Flow Service just like a standard service:

steps:
  - name: "FetchData"
    type: "invoke"
    service: "customers:GetCustomerById"
    input:
      userId: "${cid}"
    output:
      row: "customerRecord"

---

6. Supported Drivers (Enterprise Bundle)

Joynare Nexus includes the most common enterprise database drivers out-of-the-box. You do not need to install anything to use these:

• SQLite3: driver: "sqlite3"
• PostgreSQL: driver: "postgres"
• MySQL / MariaDB: driver: "mysql"
• SQL Server: driver: "sqlserver"

7. Independent Driver Support (ODBC Bridge)

If you need to connect to a database not listed above (like Oracle, IBM DB2, or custom legacy systems) without rebuilding Joynare Nexus, you can use the ODBC Bridge.

How to use an Independent Driver:

1. Install ODBC Driver: Install the standard ODBC driver for your database on the host machine (e.g., "Oracle ODBC Driver").
2. Configure Connection: Use driver: "odbc" and provide the full ODBC connection string as the DSN.

Example (Oracle via ODBC):

connections:
  - name: "LegacyOracle"
    type: "sql"
    driver: "odbc"
    dsn: "Driver={Oracle in OraClient11g_home1};Dbq=myTNSService;Uid=myUser;Pwd=myPassword;"