SAP B1 Bridge

Securely query and manage SAP Business One and SQL Server databases behind firewalls. No ports to open, no VPN required.

SAP B1 Bridge connects your cloud application to on-premise SAP Business One systems and SQL Server databases through lightweight Windows agents that communicate outbound via WebSocket. Your internal systems stay protected behind the firewall while your app gets full access to data and SAP operations.

Your App (Cloud)           Firewall           Your Network
+------------------+          ||          +-------------------+
| Express + Bridge | <========||========= | Bridge Agent      |
|  - WebSocket     |   outbound only      |  - SQL Server     |
|  - REST API      |          ||          |  - SAP B1 SL      |
|  - Programmatic  |          ||          |  - SAP B1 DI API  |
+------------------+          ||          +-------------------+

How It Works

SAP B1 Bridge uses a synchronous direct-push communication model:

1. Agent connects outbound — The Windows agent initiates a WebSocket connection from inside your network to your cloud server. No inbound firewall ports needed.
2. Server pushes commands directly — When you execute a query or SAP request (via the programmatic API, REST endpoint, or test console), the server sends the command directly to the connected agent over the open WebSocket.
3. Agent executes and responds — The agent runs the SQL query or SAP operation locally and sends the result back over the same WebSocket connection.
4. Server resolves the result — The server matches the response to the original request and returns it to your code as a resolved Promise, or broadcasts it to connected UI clients in real time.

There is no polling. The server does not queue commands for agents to fetch later. Commands are dispatched immediately to the target agent, and the programmatic API methods (executeQuery, callServiceLayer, callDiApi) return a Promise that resolves when the agent responds or times out.

Installation

npm install @bestoneconsulting/sap-b1-bridge

Peer dependencies (install if not already in your project):

npm install express ws zod

Quick Start

import express from "express";
import { createServer } from "http";
import { createSAPB1Bridge } from "@bestoneconsulting/sap-b1-bridge";

const app = express();
app.use(express.json());
const server = createServer(app);

const bridge = await createSAPB1Bridge({
  app,
  server,
  appName: "My Company App",
  wsPath: "/ws",
  apiPrefix: "/api",
});

bridge.mountUI("/bridge");

server.listen(3000, () => {
  console.log("Bridge running on http://localhost:3000");
  console.log("Test console at http://localhost:3000/bridge");
});

That's it. Your app now has:

• A WebSocket endpoint at /ws for agents to connect to
• REST API endpoints under /api for managing agents and submitting queries
• A programmatic API for executing queries and SAP requests directly from your code
• A test console at /bridge for managing agents and running queries from your browser

Registering an Agent

Before the bridge can talk to your on-premise systems, you need to register an agent and install the agent software on your Windows server.

const agent = await bridge.registerAgent({
  name: "Main Office",
  serverName: "Production ERP",  // A friendly label — not a hostname or connection string
  capabilities: ["sql_server", "sap_service_layer", "sap_di_api"],
});

console.log("Agent API Key:", agent.apiKey);
// Give this API key to the agent software running on your Windows server

The serverName field is a descriptive label to help you identify which server or environment this agent represents (e.g. "Production ERP", "US Warehouse", "Dev Server"). It is not used as a connection string.

Contact Best One Consulting to obtain the Windows agent installer.

Built-in Test Console

The package includes a built-in web-based test console for managing agents, running queries, and viewing history. No separate build step or static files required.

bridge.mountUI("/bridge");
// Now accessible at http://localhost:3000/bridge

The test console includes:

• Query Console — Execute SQL queries, SAP Service Layer requests, and DI API operations with a form-based interface. Select your target type and agent, enter your query or request details, and see results pushed back in real time over WebSocket.
• Agents — Register new agents, view live connection status, copy API keys, and delete agents. Displays the WebSocket URL that agents need to connect to, with a one-click copy button.
• History — View recent query history with status, execution time, and row counts.
• Live WebSocket — Real-time connection status indicator and instant result updates. When a query completes, the result is pushed to all connected UI clients immediately.

The test console adapts to your bridge configuration (app name, API prefix, WebSocket path) automatically.

If you have a custom full-featured UI (such as the React-based interface from the source repository), you can serve it instead by providing a staticDir:

bridge.mountUI("/bridge", "./path/to/custom-ui-dist");

Programmatic API

All programmatic methods are synchronous — they return a Promise that resolves when the agent responds with the result or the request times out (default: 5 minutes). The server sends the command directly to the agent over WebSocket and waits for the response.

Execute SQL Queries

Run queries against SQL Server databases on your internal network:

const result = await bridge.executeQuery(agentId, "SELECT TOP 10 * FROM OITM");

if (result.status === "completed") {
  console.log("Rows:", result.rowCount);
  console.log("Data:", result.result);
  console.log("Duration:", result.executionTime, "ms");
} else {
  console.log("Error:", result.error);
}

Call SAP Service Layer

Send REST API requests to SAP Business One Service Layer:

// GET request
const partners = await bridge.callServiceLayer(agentId, {
  method: "GET",
  endpoint: "/BusinessPartners",
  queryParams: {
    $top: "5",
    $select: "CardCode,CardName,CardType",
    $filter: "CardType eq 'C'",
  },
});

// POST request
const newOrder = await bridge.callServiceLayer(agentId, {
  method: "POST",
  endpoint: "/Orders",
  body: {
    CardCode: "C20000",
    DocumentLines: [
      { ItemCode: "A00001", Quantity: 10 },
    ],
  },
});

// PATCH request
await bridge.callServiceLayer(agentId, {
  method: "PATCH",
  endpoint: "/BusinessPartners('C20000')",
  body: { Phone1: "555-0100" },
});

Call SAP DI API

Invoke SAP Business One DI API methods using the XML-first contract:

// Get an item
const item = await bridge.callDiApi(agentId, {
  operation: "di_get",
  object: "Items",
  key: { ItemCode: "A00001" },
});

// Add a business partner with XML
const result = await bridge.callDiApi(agentId, {
  operation: "di_add",
  object: "BusinessPartners",
  sapXml: `<BOM>
    <BO>
      <AdmInfo><Object>2</Object></AdmInfo>
      <BusinessPartners>
        <row>
          <CardCode>C99999</CardCode>
          <CardName>New Customer</CardName>
          <CardType>cCustomer</CardType>
        </row>
      </BusinessPartners>
    </BO>
  </BOM>`,
  options: { dryRun: false },
});

// Run a DI query
const queryResult = await bridge.callDiApi(agentId, {
  operation: "di_query",
  object: "Items",
  sapXml: `<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
    <env:Body>
      <dis:DoQuery xmlns:dis="http://www.sap.com/SBO/DIS">
        <QueryParams>SELECT ItemCode, ItemName FROM OITM WHERE ItemCode LIKE 'A%'</QueryParams>
      </dis:DoQuery>
    </env:Body>
  </env:Envelope>`,
});

DI API Operations:

| Operation | Description | Required Fields | |-----------|-------------|-----------------| | di_get | Retrieve an object | object, key | | di_add | Create a new object | object, sapXml | | di_update | Update an existing object | object, key, sapXml | | di_action | Perform an action (cancel, close, remove) | object, key, action | | di_query | Execute a DI query | object, sapXml |

DI API Options:

| Option | Type | Description | |--------|------|-------------| | dryRun | boolean | Validate without committing | | transaction | boolean | Wrap in a transaction | | returnXml | boolean | Return raw XML response | | allowDiQuery | boolean | Allow DI query execution |

Agent Management

// List all agents
const agents = await bridge.getAgents();

// Get a specific agent
const agent = await bridge.getAgent(agentId);

// Check if an agent is currently connected
const online = bridge.isAgentConnected(agentId);

// Get live capability status (from in-memory cache, reported by agent)
const caps = bridge.getAgentCapabilities(agentId);
// Returns: { sql: { enabled, available, reason }, serviceLayer: {...}, diApi: {...} }

// Request an agent to refresh its capabilities
await bridge.refreshAgentCapabilities(agentId);

// Get all currently connected agent IDs
const connectedIds = bridge.getConnectedAgentIds();

// Dispatch a previously created query to an agent for execution
await bridge.dispatchQueryToAgent(agentId, queryId);

// Delete an agent
await bridge.deleteAgent(agentId);

Company Management

Organize agents by company for multi-tenant setups:

const company = await bridge.createCompany("Acme Corp", "Production environment");
const companies = await bridge.getCompanies();
await bridge.deleteCompany(company.id);

REST API Endpoints

The bridge automatically registers these REST endpoints on your Express app:

| Endpoint | Method | Description | |----------|--------|-------------| | {prefix}/agents | GET | List all registered agents | | {prefix}/agents | POST | Register a new agent | | {prefix}/agents/:id | PATCH | Update agent details | | {prefix}/agents/:id | DELETE | Remove an agent | | {prefix}/agents/capabilities | GET | Get live capability status for all agents | | {prefix}/agents/:id/refresh-capabilities | POST | Request capability refresh from agent | | {prefix}/agents/:id/test | POST | Test agent connection (sends ping, waits for pong) | | {prefix}/queries | POST | Submit a query for execution | | {prefix}/queries/active | GET | Get most recent query | | {prefix}/queries/recent | GET | Get recent query history | | {prefix}/queries | GET | Get all queries | | {prefix}/companies | GET | List companies | | {prefix}/companies | POST | Create a company | | {prefix}/companies/:id | PATCH | Update a company | | {prefix}/companies/:id | DELETE | Delete a company | | {prefix}/config | GET | Get bridge configuration |

Query Submission (POST {prefix}/queries)

When you submit a query via the REST endpoint, the server:

1. Creates the query record in storage with status pending
2. If the target agent is connected, immediately sends the command (execute_query for SQL or execute_request for SAP targets) directly to the agent and updates status to executing
3. If the agent is not connected, marks the query as error with the message "Agent is not connected"
4. Returns the query record (with its current status) in the response

Results arrive asynchronously over WebSocket and are broadcast to connected UI clients as query_completed events. If you need to await the result in server code, use the programmatic API (executeQuery, callServiceLayer, callDiApi) instead.

WebSocket Protocol

Agents connect to {wsPath} and the bridge handles all communication over a single persistent WebSocket connection per agent.

Agent Authentication Flow

1. Agent opens WebSocket to {wsPath}
2. Agent sends { type: "auth", apiKey: "agent_xxx" }
3. Bridge validates the API key and responds with { type: "auth_success", agentId, connectionName, appName, appUrl } or { type: "auth_failed" } (and closes the connection)
4. Agent is now connected and ready to receive commands

Server-to-Agent Messages (Direct Push)

The server pushes commands directly to the agent — the agent never polls for work:

| Message Type | Direction | Description | |---|---|---| | execute_query | Server → Agent | Execute a SQL query. Fields: queryId, sqlQuery | | execute_request | Server → Agent | Execute a SAP request. Fields: queryId, targetType, requestPayload | | ping | Server → Agent | Connection health check. Fields: pingId | | heartbeat_ack | Server → Agent | Acknowledgment of agent heartbeat |

Agent-to-Server Messages

| Message Type | Direction | Description | |---|---|---| | auth | Agent → Server | Authenticate with API key | | heartbeat | Agent → Server | Periodic keepalive (every 30s) | | capabilities | Agent → Server | Report available capabilities | | query_result | Agent → Server | Return query/request results. Fields: queryId, result, rowCount, executionTime, error | | pong | Agent → Server | Response to ping. Fields: pingId |

UI Client Messages

Browser clients connect to the same WebSocket endpoint with { type: "ui_connect" } to receive real-time updates:

| Message Type | Direction | Description | |---|---|---| | ui_connect | Client → Server | Register as a UI client | | ui_connected | Server → Client | Confirmation of UI registration | | agent_status_update | Server → Client | Agent went online/offline. Fields: agentId, status | | agent_capabilities_update | Server → Client | Agent capabilities changed. Fields: agentId, capabilities | | query_submitted | Server → Client | New query created. Fields: queryId, agentId | | query_executing | Server → Client | Query sent to agent for execution. Fields: queryId | | query_completed | Server → Client | Query finished. Fields: queryId, status, result, rowCount, executionTime, error |

const ws = new WebSocket("ws://localhost:3000/ws");

ws.onopen = () => {
  ws.send(JSON.stringify({ type: "ui_connect" }));
};

ws.onmessage = (event) => {
  const message = JSON.parse(event.data);
  switch (message.type) {
    case "ui_connected":
      console.log("Connected to bridge");
      break;
    case "agent_status_update":
      console.log(`Agent ${message.agentId} is now ${message.status}`);
      break;
    case "query_completed":
      console.log(`Query ${message.queryId}: ${message.status}`, message.result);
      break;
  }
};

Custom Storage

By default, the bridge uses in-memory storage (data is lost on restart). For persistence, provide your own storage implementation:

import { createSAPB1Bridge } from "@bestoneconsulting/sap-b1-bridge";
import type { IBridgeStorage } from "@bestoneconsulting/sap-b1-bridge";

class MyDatabaseStorage implements IBridgeStorage {
  async getAgents() { /* query your database */ }
  async getAgent(id) { /* ... */ }
  async getAgentByApiKey(apiKey) { /* ... */ }
  async createAgent(data) { /* ... */ }
  async updateAgent(id, updates) { /* ... */ }
  async updateAgentStatus(id, status, lastHeartbeat) { /* ... */ }
  async deleteAgent(id) { /* ... */ }

  async getQueries() { /* ... */ }
  async getQuery(id) { /* ... */ }
  async getRecentQueries(limit) { /* ... */ }
  async getActiveQuery() { /* ... */ }
  async createQuery(data) { /* ... */ }
  async updateQueryStatus(id, status, updates?) { /* ... */ }

  async getCompanies() { /* ... */ }
  async getCompany(id) { /* ... */ }
  async createCompany(name, description?) { /* ... */ }
  async updateCompany(id, updates) { /* ... */ }
  async deleteCompany(id) { /* ... */ }
}

const bridge = await createSAPB1Bridge(options, new MyDatabaseStorage());

See the full IBridgeStorage interface for method signatures and return types.

Built-in PostgreSQL Storage (Drizzle ORM)

The package includes a ready-to-use PostgreSQL storage adapter built on Drizzle ORM. This gives you persistent storage without writing any database code.

1. Install Drizzle dependencies:

npm install drizzle-orm pg drizzle-kit
npm install -D @types/pg

Or if you use Neon serverless PostgreSQL:

npm install drizzle-orm @neondatabase/serverless drizzle-kit

2. Set up the database connection and push the schema:

Create a drizzle.config.ts in your project root:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Create src/db/schema.ts that re-exports the bridge schema (and any of your own tables):

export { bridgeCompanies, bridgeAgents, bridgeQueries } from "@bestoneconsulting/sap-b1-bridge";

// Add your own application tables here if needed

Push the schema to your database:

npx drizzle-kit push

This creates three tables: bridge_companies, bridge_agents, and bridge_queries. The bridge_ prefix avoids conflicts with your own application tables.

3. Create the Drizzle client and pass it to the bridge:

For standard PostgreSQL (using pg):

import express from "express";
import { createServer } from "http";
import { Pool } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { createSAPB1Bridge, createDrizzleBridgeStorage } from "@bestoneconsulting/sap-b1-bridge";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

const app = express();
app.use(express.json());
const server = createServer(app);

const storage = createDrizzleBridgeStorage(db);

const bridge = await createSAPB1Bridge(
  { app, server, appName: "My SAP App" },
  storage
);

bridge.mountUI("/bridge");
server.listen(3000);

For Neon serverless PostgreSQL:

import express from "express";
import { createServer } from "http";
import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import { createSAPB1Bridge, createDrizzleBridgeStorage } from "@bestoneconsulting/sap-b1-bridge";

const sql = neon(process.env.DATABASE_URL!);
const db = drizzle(sql);

const app = express();
app.use(express.json());
const server = createServer(app);

const storage = createDrizzleBridgeStorage(db);

const bridge = await createSAPB1Bridge(
  { app, server, appName: "My SAP App" },
  storage
);

bridge.mountUI("/bridge");
server.listen(3000);

Schema details:

| Table | Purpose | |-------|---------| | bridge_companies | Organization grouping for agents | | bridge_agents | Registered agents with API keys and connection status | | bridge_queries | Query history with status, results, and execution metrics |

The Drizzle schema objects are exported for advanced use cases (custom queries, migrations, extending):

import { bridgeCompanies, bridgeAgents, bridgeQueries } from "@bestoneconsulting/sap-b1-bridge";

Configuration Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | app | Express | required | Your Express application instance | | server | Server | required | Node.js HTTP server instance | | appName | string | "SAP B1 Bridge" | Application name sent to agents on connection | | wsPath | string | "/ws" | WebSocket endpoint path | | apiPrefix | string | "/api" | Prefix for all REST API routes |

Timeouts

• Query timeout: 300 seconds (5 minutes). If an agent doesn't respond within this window, the Promise rejects and the query is marked as error with message "Query timed out".
• Agent test timeout: 5 seconds. The POST {prefix}/agents/:id/test endpoint sends a ping and waits up to 5 seconds for a pong response.
• Heartbeat interval: Agents send heartbeats every 30 seconds. An agent is marked offline if no heartbeat is received within 90 seconds.

TypeScript Support

Full TypeScript support with exported types:

import type {
  SAPB1BridgeOptions,
  SAPB1BridgeAPI,
  AgentInfo,
  QueryResult,
  ServiceLayerRequest,
  DiApiRequest,
  AgentCapabilities,
  CapabilityStatus,
  CompanyInfo,
  RegisterAgentOptions,
  IBridgeStorage,
  BridgeQuery,
} from "@bestoneconsulting/sap-b1-bridge";

Target Types

| Target | Capability Key | Description | |--------|---------------|-------------| | SQL Server | sql_server | Execute T-SQL queries against Microsoft SQL Server | | SAP Service Layer | sap_service_layer | REST API calls to SAP Business One Service Layer | | SAP DI API | sap_di_api | XML-based SAP Business One DI API operations |

Example: Express App with SAP Integration

import express from "express";
import { createServer } from "http";
import { createSAPB1Bridge } from "@bestoneconsulting/sap-b1-bridge";

const app = express();
app.use(express.json());
const server = createServer(app);

const bridge = await createSAPB1Bridge({
  app,
  server,
  appName: "Inventory Dashboard",
  apiPrefix: "/api/bridge",
});

// Your own route that uses the bridge programmatic API
app.get("/api/inventory", async (req, res) => {
  const agents = await bridge.getAgents();
  const onlineAgent = agents.find((a) => bridge.isAgentConnected(a.id));

  if (!onlineAgent) {
    return res.status(503).json({ error: "No agents online" });
  }

  try {
    // This awaits the result — the server sends the command to the agent
    // and waits for the response over WebSocket
    const result = await bridge.executeQuery(
      onlineAgent.id,
      "SELECT ItemCode, ItemName, OnHand FROM OITM WHERE OnHand > 0"
    );

    if (result.status === "completed") {
      res.json({ items: result.result, count: result.rowCount });
    } else {
      res.status(500).json({ error: result.error });
    }
  } catch (err) {
    res.status(500).json({ error: "Query failed" });
  }
});

server.listen(3000);

License

MIT License - Copyright (c) 2025 Best One Consulting

Support

For questions, agent software, or implementation help:

Best One Consulting https://www.bestoneconsulting.com/