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 and add its command-line tools.
• Windows: run the EnterpriseDB installer 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:

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.

Look around

Basic psql's backslash commands work against your books:

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:

select guide from readme;

Some first queries

-- 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;