# Database Access

![Postico browsing the transactions table](/img/docs/db/hero-postico.png)
*Postico, connected to a company's books.*

![A psql session in Terminal](/img/docs/db/hero-psql.png)
*A `psql` session in Terminal.*

![A Jupyter notebook charting account activity](/img/docs/db/hero-notebook.png)
*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](/docs/db/sql.md) 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)](/docs/db/psql.md) — Connect in one command, and use psql's schema commands to explore the tables.
- [GUI clients](/docs/db/gui.md) — Browse the tables and run queries in an editor with Postico, DBeaver, TablePlus, or pgAdmin.
- [Excel](/docs/db/excel.md) — Pull the books into a worksheet with Power Query, and refresh them on demand.
- [Scripts & notebooks](/docs/db/clients.md) — 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](/docs/db/sql.md).

## 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](/docs/db/psql.md), or jump to the [Tables and SQL reference](/docs/db/sql.md).