hledger and SQLite

With SQLite you can do full relational queries on your hledger data.

Here we export all transactions to a database and run some queries:

$ hledger -f examples/bcexample.hledger print -O sql | sqlite3 bcexample.db
$ sqlite3 bcexample.db -column -header
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .tables
postings
sqlite> .schema
CREATE TABLE postings(id serial,txnidx int,date1 date,date2 date,status text,code text,description text,comment text,account text,amount numeric,commodity text,credit numeric,debit numeric,posting_status text,posting_comment text);
sqlite> select count(*) from postings;
count(*)
--------
3203
sqlite> select distinct txnidx,description,abs(amount) from postings order by abs(amount) desc limit 5;
txnidx  description                                       abs(amount)
------  ------------------------------------------------  -----------
1031    Allowed contributions for one year                17500
1035    Allowed contributions for one year                17500
1027    Allowed contributions for one year                17000
142     Transfering accumulated savings to other account  5000
954     Hoogle | Payroll                                  4615.38
sqlite> sqlite> select * from postings where txnidx=954;
id  txnidx  date1       date2  status  code  description       comment  account                                     amount    commodity  credit   debit    posting_status  posting_comment
--  ------  ----------  -----  ------  ----  ----------------  -------  ------------------------------------------  --------  ---------  -------  -------  --------------  ---------------
    954     2012-01-05         *             Hoogle | Payroll           Assets:US:BofA:Checking                     1350.6    USD                 1350.6
    954     2012-01-05         *             Hoogle | Payroll           Assets:US:Vanguard:Cash                     1200      USD                 1200
    954     2012-01-05         *             Hoogle | Payroll           Assets:US:Federal:PreTax401k                -1200     IRAUSD     1200
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:Federal:PreTax401k  1200      IRAUSD              1200
    954     2012-01-05         *             Hoogle | Payroll           Income:US:Hoogle:Salary                     -4615.38  USD        4615.38
    954     2012-01-05         *             Hoogle | Payroll           Income:US:Hoogle:GroupTermLife              -24.32    USD        24.32
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Health:Life:GroupTermLife          24.32     USD                 24.32
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Health:Dental:Insurance            2.9       USD                 2.9
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Health:Medical:Insurance           27.38     USD                 27.38
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Health:Vision:Insurance            42.3      USD                 42.3
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:Medicare            106.62    USD                 106.62
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:Federal             1062.92   USD                 1062.92
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:State               365.08    USD                 365.08
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:CityNYC             174.92    USD                 174.92
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:SDI                 1.12      USD                 1.12
    954     2012-01-05         *             Hoogle | Payroll           Expenses:Taxes:Y2012:US:SocSec              281.54    USD                 281.54
    954     2012-01-05         *             Hoogle | Payroll           Assets:US:Hoogle:Vacation                   4.62      VACHR               4.62
    954     2012-01-05         *             Hoogle | Payroll           Income:US:Hoogle:Vacation                   -4.62     VACHR      4.62
sqlite>

You can avoid creating a .db file, always querying the latest journal file instead:

$ (hledger print -O sql; echo "select * from postings where account like 'liabilities%' and amount > 0") | sqlite3 -column -header

Here's a shell function to make that convenient:

$ hq() { (hledger print -O sql; echo "$1") | sqlite3 -column -header; }

Example queries:

$ hq "select distinct(account) from postings order by account"
$ hq "select * from postings where account like '%savings%' and amount > 0"

The txnidx field connects postings belonging to the same transaction. Using this, we can query transactions, and in more complex ways than hledger can. Here's an example where we want to see just the transfers from Checking to Liabilities. This is hard to do accurately with hledger's CLI(1):

$ echo; hq "select * from postings where txnidx in \
  (select txnidx from postings where account regexp 'Liabilities' and amount > 0 and txnidx in \
    (select txnidx from postings where account regexp 'Checking' and amount < 0))"
> > 
id  txnidx  date1       date2  status  code  description                           comment  account                      amount   commodity  credit  debit   posting_status  posting_comment
--  ------  ----------  -----  ------  ----  ------------------------------------  -------  ---------------------------  -------  ---------  ------  ------  --------------  ---------------
    147     2012-01-08         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   140.36   USD                140.36                                 
    147     2012-01-08         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -140.36  USD        140.36                                         
    163     2012-02-11         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   725.96   USD                725.96                                 
    163     2012-02-11         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -725.96  USD        725.96                                         
    177     2012-03-09         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   580      USD                580                                    
    177     2012-03-09         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -580     USD        580                                            
...

(1. Well.. it's not that hard to get a decent result given typical data patterns:

$ hledger -f examples/bcexample.hledger print Checking | hledger -f- areg -w80 Liabilities amt:'>0'
Transactions in Liabilities and subaccounts:
2012-01-08 Chase:Slate | Pay..  Li:US:Ch:Slate          140.36 USD    140.36 USD
2012-02-11 Chase:Slate | Pay..  Li:US:Ch:Slate          725.96 USD    866.32 USD
2012-03-09 Chase:Slate | Pay..  Li:US:Ch:Slate          580.00 USD   1446.32 USD
...

but in general, this is not as robust as the relational query.)

Only hledger's print command outputs SQL. But many hledger reports can produce CSV, and you can also run SQLite on that:

or on hledger's JSON output:

See also hledger and dsq / DataStation.