ORMs & Frameworks

Drizzle ORM + QuickBooks Desktop

Use Drizzle ORM for lightweight, type-safe access to QuickBooks Desktop data. Connect via the postgres-js driver with full TypeScript support and schema inference.

New: skip the hand-written schema entirely. Install @syntraodbc/drizzle-schema for pre-built, typed Drizzle tables for every QuickBooks entity.
See the SDK

Quick Start

  1. Install Syntra ODBC and ensure the server is running on localhost:5433.
  2. Install dependencies. Run npm install drizzle-orm postgres and npm install -D drizzle-kit.
  3. Create a connection with the postgres-js driver.
  4. Define or infer your schema and run type-safe queries.

Connection Setup

Create a Drizzle instance using the postgres driver:

// db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres({
  host: "localhost",
  port: 5433,
  database: "quickbooks",
  username: "qbconnect",
  password: process.env.SYNTRA_PASSWORD, // from config.toml
});

export const db = drizzle(client);

Schema Definition

Define your QuickBooks tables with Drizzle's schema builder:

// schema.ts
import { pgTable, text, numeric, boolean, date } from "drizzle-orm/pg-core";

export const customer = pgTable("customers", {
  listId: text("list_id").primaryKey(),
  fullName: text("full_name").notNull(),
  email: text("email"),
  phone: text("phone"),
  balance: numeric("balance").notNull(),
  isActive: boolean("is_active").notNull(),
});

export const invoice = pgTable("invoices", {
  txnId: text("txn_id").primaryKey(),
  txnDate: date("txn_date").notNull(),
  refNumber: text("ref_number"),
  customerRefListId: text("customer_ref_list_id").notNull(),
  balanceRemaining: numeric("balance_remaining").notNull(),
  isPaid: boolean("is_paid").notNull(),
});

export const invoiceLine = pgTable("invoice_lines", {
  txnId: text("txn_id").notNull(),
  lineId: text("line_id").primaryKey(),
  itemRefFullName: text("item_ref_full_name"),
  quantity: numeric("quantity"),
  rate: numeric("rate"),
  amount: numeric("amount"),
});

Schema Introspection with Drizzle Kit

Instead of writing schemas by hand, pull them from Syntra automatically:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    host: "localhost",
    port: 5433,
    database: "quickbooks",
    user: "qbconnect",
    password: process.env.SYNTRA_PASSWORD!,
  },
  out: "./drizzle",
});

// Then run:
// npx drizzle-kit introspect

Type-Safe Queries

Query QuickBooks data with full TypeScript autocompletion:

import { db } from "./db";
import { customer, invoice, invoiceLine } from "./schema";
import { eq, desc, gte, and, sql } from "drizzle-orm";

// Top customers by balance
const topCustomers = await db
  .select()
  .from(customer)
  .where(eq(customer.isActive, true))
  .orderBy(desc(customer.balance))
  .limit(10);

// Unpaid invoices with customer details
const unpaid = await db
  .select({
    invoiceNumber: invoice.refNumber,
    date: invoice.txnDate,
    customerName: customer.fullName,
    balance: invoice.balanceRemaining,
  })
  .from(invoice)
  .innerJoin(customer, eq(invoice.customerRefListId, customer.listId))
  .where(
    and(
      eq(invoice.isPaid, false),
      gte(invoice.txnDate, "2025-01-01")
    )
  )
  .orderBy(desc(invoice.balanceRemaining));

console.log(`${unpaid.length} unpaid invoices`);

// Revenue by item using raw SQL
const revenueByItem = await db
  .select({
    item: invoiceLine.itemRefFullName,
    totalRevenue: sql`SUM(${invoiceLine.amount})`.as("total"),
    lineCount: sql`COUNT(*)`.as("count"),
  })
  .from(invoiceLine)
  .groupBy(invoiceLine.itemRefFullName)
  .orderBy(sql`SUM(${invoiceLine.amount}) DESC`)
  .limit(20);

console.log("Top items by revenue:", revenueByItem);

Tips

  • ✓ Use drizzle-kit introspect to auto-generate schemas.
  • ✓ Standard plan is read-only. Pro plan enables write operations.
  • ✓ The postgres driver supports connection pooling out of the box.

Full reference: Node.js / Drizzle docs →

Type-safe QuickBooks queries with Drizzle

Download Syntra ODBC and start querying with Drizzle ORM in minutes.

Download Free Trial