instabooks Try instantly

Insta-query your books with SQL

Instabooks delivers fast read-only PostgreSQL access to QuickBooks Online data. Unlock ledger-backed analytics, agents and applications.

Troubleshooting

View as Markdown

Below we cover some common problems encountered with Instabooks Database Access and how to solve them.

Authentication failed

There a few reasons authentication can fail, here are some likely causes to evaluate.

  • Is the password for this company? A password is issued for one company. Pointing a client at a different company id with the same password fails.
  • Is the user your Instabooks email? You must use the full email address that you associated with your Instabooks account. This is displayed in the upper right corner when you are logged into Instabooks.
  • Is the password still active? Check Settings → Database Access. If it was revoked, generate a new one.
  • Did the whole password get pasted? A truncated paste, or a line break picked up from a copy, is a common cause. Generate a fresh one and copy it with the copy button.
  • Do you still have access to the company? If you've lost access to a company, your credenitals will no longer work.

If you are hand-writing a postgresql://user:pass@host/db URL, the @ in your email breaks it. Use the key=value form, or copy the pre-encoded URL from the URL tab in Settings.

The client cannot reach the server

If you are seeing refused connections, network timeouts, or "could not translate host name", consider the following suggestions.

  • Check the host is db.instabooks.io and the port is 5432. Note this is not the same host you use in a browser.
  • Corporate networks and some VPNs block outbound port 5432. If a connection from home works and one from the office does not. You will need to check with your network adminstrator.

SSL / TLS errors

Instabooks Database Access connections are always encrypted. Most PostgreSQL clients negotiate this on their own; if for some reason this is failing, check your client documentation on how to set the SSL mode to require (sslmode=require) in a connection string.

Query errors

ErrorWhat it means
relation … does not exist
42P01		 No such table. Names are snake_case and plural. See Tables & SQL.
column … does not exist
42703		 No such column. See Tables & SQL.
column must appear in the GROUP BY clause
42803		 A projected column is neither grouped by nor inside an aggregate. Add it to GROUP BY, or wrap it. See Tables & SQL.
… is not supported
0A000		 A construct outside the supported subset. The error's detail and hint name what to write instead. See Tables & SQL.
syntax error at or near …
42601		 The statement could not be parsed. Check for a typo, more than one statement in the box, or a psql meta-command sent to the wrong client. See Tables & SQL.

The numbers look wrong

A total comes out at zero

Both sides of every QuickBooks transaction are stored as rows in transaction_lines, so an unconstrained sum(amount) should net to zero by construction. Join accounts on account_oid and filter it — see Tables & SQL.

A query returns no rows

  • LIKE is case-sensitive here, as in PostgreSQL. 'expenses:%' matches nothing; 'Expenses:%' matches the subtree.
  • Check the value really exists — select distinct type from accounts before filtering on a type, and mind the exact spelling QuickBooks uses.

Excel and ODBC

  • "We couldn't authenticate with the credentials provided." The credentials must be entered on the Database tab of Power Query's prompt, not Default/Windows. Clear the stored ones under Data → Get Data → Data Source Settings → Edit Permissions and enter them again.
  • The driver is not found. Install psql ODBC, then restart Excel. The driver name in the connection string must match what is installed — PostgreSQL Unicode(x64) for 64-bit Excel. See Connecting Excel.
  • A load takes forever. You are probably pulling a whole table. Put a grouped query in the SQL statement box of the From ODBC dialog's Advanced options instead — see Connecting Excel.

Performance

There is no query timeout, so an expensive query runs to completion. Two habits keep things quick:

  • Avoid table scans transaction_lines whole. Lean on SQL filtering and aggregation.
  • Prefer one grouped query to many small ones. date_trunc exists so that a monthly trend is a single query.

Still stuck?

Write to support with the client you are using, the exact error text, and the company you were connecting to. Never send us your password.

Back to the Overview.