Database Access

Postico browsing the transactions table — Postico, connected to a company's books.

A psql session in Terminal — A `psql` session in Terminal.

A Jupyter notebook charting account activity

Access your company's books as SQL tables: transactions, lines, the chart of accounts, classes, departments, customers, and vendors. They stay in sync with QuickBooks Online the same way the rest of Instabooks does.

We support the PostgreSQL wire protocol: no plugin, no connector to install beyond your client's own Postgres driver.

Things to know before you start:

It is read-only. No writes and no schema changes. Nothing you run here can alter your books.
It is a focused subset of PostgreSQL, aimed at analytical queries: joins, filters, grouping, aggregates, and date bucketing. See Tables & SQL for exactly what is supported. Anything unsupported comes back as a SQL error.

Generate a password

In Instabooks, click the gear next to your email, top right, to open Settings, then Database Access. Open the company you want to query and click Generate password. Give it a note describing what it is for, such as "monthly revenue script" or "Excel on my laptop", so you can tell your passwords apart later.

The password is shown once. Copy it when it appears. If you lose it, revoke it and generate another.

Your connection details

Every Postgres client asks for the same five things. The exact values for your company are on the Settings → Database Access page. This is what they mean:

Field	Value
Host	`db.instabooks.io`
Port	`5432`
Database	Your company's numeric id. One company, one database. If you have several, each has its own.
User	Your Intuit email address. Visible in the top right corner when signed in to Instabooks.
Password	The password you generated above.

TLS is required. The connection is always encrypted; a client that asks for an unencrypted connection is refused. Most clients do the right thing on their own. Where one needs to be told, set its SSL mode to require.

Pick your client

Terminal (psql)

Connect in one command, and use psql's schema commands to explore the tables.

GUI clients

Browse the tables and run queries in an editor with Postico, DBeaver, TablePlus, or pgAdmin.

Excel

Pull the books into a worksheet with Power Query, and refresh them on demand.

Scripts & notebooks

Python, Java, Go, Ruby, or anything else with a Postgres driver.

What you can query

We've distilled the complex Quickbooks Online data model into a simple, powerful, and consistent double-entry SQL schema.

SQL

select date_trunc('month', l.date) as month,
       sum(l.amount) as revenue
  from transaction_lines l
  join accounts a on l.account_oid = a.oid
 where a.type = 'Income'
 group by date_trunc('month', l.date)
 order by month;

The full column-by-column reference, the supported SQL, and the gotchas worth knowing before you sum anything are in Tables & SQL.

Security

Passwords are stored securely. Nobody, including us, can read yours back.
A password is scoped to one company, and is checked against your access to that company on every connection.
The connection is encrypted end to end.
Revoke a password any time from Settings → Database Access. Any client using it loses access immediately.

Next: Connect from the Terminal, or jump to the Tables and SQL reference.

Insta-query your books with SQL