# Connecting with psql

`psql` is PostgreSQL's own command-line client. It's a great place to work out a query before putting it in a script or a spreadsheet.

## Install psql

`psql` ships with PostgreSQL. You do not need to run the PostgreSQL *server* — the client alone is enough.

- **macOS:** `brew install libpq`, then follow the `PATH` line Homebrew prints. Or install [Postgres.app](https://postgresapp.com/) and add its command-line tools.
- **Windows:** run the [EnterpriseDB installer](https://www.postgresql.org/download/windows/) and select only *Command Line Tools*.
- **Linux:** `apt install postgresql-client` or `dnf install postgresql`.

## Connect

Copy the ready-made command from **Settings → Database Access → Terminal (psql)**. It already has your host, company id, and email filled in:

```bash
psql "host=db.instabooks.io dbname=9341452924740861 user=you@yourcompany.com"
```

psql prompts for the password. Paste the one you generated in **Settings** — it will not echo as you type. Leaving the password off the command line like this keeps it out of your shell history.

> **Don't build a `postgresql://` URL by hand.** Your username is an email address, and the `@` in it collides with the `@` that separates credentials from the host. Use the `key=value` form above, or copy the pre-encoded URL from the **URL** tab in **Settings**.

## Look around

Basic psql's backslash commands work against your books:

| Command | What it does |
| --- | --- |
| `\dt` | List the tables. |
| `\dt+` | The same, with a description of what each table holds. |
| `\d transactions` | The columns of one table, and their types. |
| `\d+ transactions` | The same, with a line per column explaining what it means. Worth reading before writing a query. |
| `\df *` | Every function that works here, with its signature. (Bare `\df` shows nothing — the pattern is what makes it list built-ins.) |
| `\q` | Quit. |

There is also a `readme` table holding a one-page description of exactly the SQL this endpoint supports. It is meant to be read by whatever is writing your queries — including an LLM:

```sql
select guide from readme;
```

## Some first queries

```sql
-- The ten most recent transactions.
select date, type, doc_num, memo
  from transactions
 order by date desc
 limit 10;

-- What the chart of accounts looks like.
select type, count(*)
  from accounts
 group by type
 order by type;

-- Monthly income for one year.
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'
   and l.date >= '2024-01-01'
   and l.date <= '2024-12-31'
 group by date_trunc('month', l.date)
 order by month;

-- Top vendors by spend.
select v.display_name, sum(l.amount) as spend
  from transaction_lines l
  join vendors v on l.vendor_oid = v.oid
  join accounts a on l.account_oid = a.oid
 where a.type = 'Expense'
 group by v.display_name
 order by sum(l.amount) desc
 limit 10;
```

> **These are balanced books.** Both sides of every entry are rows in `transaction_lines`, so a `sum(amount)` over lines you have not constrained comes to zero. See [Tables & SQL](/docs/db/sql.md).

Next: the [Tables and SQL reference](/docs/db/sql.md), or connect [GUI Clients](/docs/db/gui.md).