ORMs & Frameworks

Prisma + QuickBooks Desktop

Use Prisma ORM to query QuickBooks Desktop data with full type safety. Introspect the schema automatically and use Prisma Client for clean, typed queries.

Quick Start

  1. Install Syntra ODBC and ensure the server is running on localhost:5433.
  2. Initialize Prisma. Run npx prisma init in your project.
  3. Configure the datasource in schema.prisma to point to Syntra.
  4. Introspect the schema. Run npx prisma db pull to generate models from QuickBooks tables.
  5. Generate Prisma Client. Run npx prisma generate and start querying.

Datasource Configuration

Update your schema.prisma to connect to Syntra ODBC:

// schema.prisma

datasource db {
  provider = "postgresql"
  url      = "postgresql://qbconnect:your_config_toml_password@localhost:5433/quickbooks"
}

generator client {
  provider = "prisma-client-js"
}

For production, use an environment variable: url = env("DATABASE_URL") and set DATABASE_URL in your .env file.

Schema Introspection

Run npx prisma db pull and Prisma will read the QuickBooks schema from Syntra and generate models automatically:

// Auto-generated by `prisma db pull`

model Customer {
  listId    String  @id @map("list_id")
  fullName  String  @map("full_name")
  email     String? @map("email")
  phone     String? @map("phone")
  balance   Decimal @map("balance")
  isActive  Boolean @map("is_active")

  @@map("customers")
}

model Invoice {
  txnId              String   @id @map("txn_id")
  txnDate            DateTime @map("txn_date")
  refNumber          String?  @map("ref_number")
  customerRefListId  String   @map("customer_ref_list_id")
  balanceRemaining   Decimal  @map("balance_remaining")
  isPaid             Boolean  @map("is_paid")

  @@map("invoices")
}

Prisma Client Queries

After running npx prisma generate, use Prisma Client with full type safety and autocompletion:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Find top customers by balance
const topCustomers = await prisma.customer.findMany({
  where: { isActive: true },
  orderBy: { balance: "desc" },
  take: 10,
});

console.log("Top 10 customers by balance:");
topCustomers.forEach((c) => {
  console.log(`  ${c.fullName}: $${c.balance}`);
});

// Find unpaid invoices from this year
const unpaidInvoices = await prisma.invoice.findMany({
  where: {
    isPaid: false,
    txnDate: { gte: new Date("2025-01-01") },
  },
  orderBy: { balanceRemaining: "desc" },
});

console.log(`\n${unpaidInvoices.length} unpaid invoices found`);

await prisma.$disconnect();

Tips

  • ✓ Run prisma db pull periodically to pick up schema changes as you add QuickBooks modules.
  • ✓ On the Standard plan, Syntra ODBC is read-only. The Pro plan enables prisma.customer.create() and other write operations.
  • ✓ Use prisma.$queryRaw for complex JOINs that go beyond Prisma's query builder.

Full reference and more examples: Prisma integration docs →

Type-safe QuickBooks queries with Prisma

Download Syntra ODBC and introspect your QuickBooks schema in seconds.

Download Free Trial