Node.js

Node.js applications can connect to Syntra ODBC using the pg package, the standard PostgreSQL client for Node.js.

Prerequisites

• Syntra ODBC installed and running
• Node.js 16 or later

Install

npm install pg

Basic Connection

const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  port: 5433,
  database: 'quickbooks',
  user: 'syntra',
  password: 'yourpassword',
});

async function main() {
  await client.connect();

  const res = await client.query(
    'SELECT name, balance, email FROM customers WHERE balance > $1 ORDER BY balance DESC',
    [0]
  );

  console.table(res.rows);
  await client.end();
}

main().catch(console.error);

Using a Connection Pool

For production applications, use a connection pool to manage multiple concurrent queries efficiently:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5433,
  database: 'quickbooks',
  user: 'syntra',
  password: 'yourpassword',
  max: 10,
});

async function getOpenInvoices() {
  const { rows } = await pool.query(`
    SELECT ref_number, customer_ref_full_name, txn_date, balance_remaining
    FROM invoices
    WHERE is_paid = false
    ORDER BY txn_date DESC
    LIMIT 100
  `);
  return rows;
}

Parameterized Queries

Always use parameterized queries to prevent SQL injection:

const { rows } = await pool.query(
  'SELECT * FROM customers WHERE name ILIKE $1',
  ['%acme%']
);

Forcing a Live Query

To bypass the cache and query QuickBooks directly:

await client.query('SET QB_MAX_STALENESS = 0');
const { rows } = await client.query('SELECT * FROM customers');

Writing Data

Syntra supports write operations that go directly to QuickBooks:

await client.query(
  `INSERT INTO customers (name, phone, email)
   VALUES ($1, $2, $3)`,
  ['Acme Corp', '555-0100', 'billing@acme.com']
);

See INSERT / UPDATE / DELETE for supported entities and syntax.

Using with Express

const express = require('express');
const { Pool } = require('pg');

const app = express();
const pool = new Pool({
  host: 'localhost',
  port: 5433,
  database: 'quickbooks',
  user: 'syntra',
  password: 'yourpassword',
});

app.get('/api/customers', async (req, res) => {
  try {
    const { rows } = await pool.query(
      'SELECT name, balance, phone, email FROM customers ORDER BY name'
    );
    res.json(rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Database query failed' });
  }
});

app.listen(3000, () => console.log('Server running on port 3000'));

Tips

• Connection pooling: Always use Pool instead of Client in production to avoid connection overhead.
• Error handling: Wrap queries in try/catch blocks and handle connection errors gracefully.
• TypeScript: The pg package includes TypeScript definitions. Use import { Pool } from 'pg' with typed row results for type safety.
• Streaming: For large result sets, use client.query(new Cursor(...)) with the pg-cursor package to process rows in batches.