# Troubleshooting

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

| Error | What it means |
| --- | --- |
| **relation … does not exist** `42P01` | No such table. Names are `snake_case` and plural. See [Tables & SQL](/docs/db/sql.md). |
| **column … does not exist** `42703` | No such column. See [Tables & SQL](/docs/db/sql.md). |
| **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](/docs/db/sql.md). |
| **… is not supported** `0A000` | A construct outside the supported subset. The error's detail and hint name what to write instead. See [Tables & SQL](/docs/db/sql.md). |
| **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](/docs/db/sql.md). |

## 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](/docs/db/sql.md).

### 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](/docs/db/excel.md).
- **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](/docs/db/excel.md).

## 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](/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](/docs/db.md).