Querying db-sync for ADA Balance

Question

I want to write the ADA balance of my wallet to a console window using sql queries against the db-sync database.

How can I use sql statements to obtain the ADA balance of a given address by querying db-sync via postgresql? I tried querying the sum of the [value] of the utxo's of my wallet but seem to get inaccurate results acording to what my Eternl wallet says my balance is. Somehow I am misunderstanding what I should be summing etc.

There is no straightforward query for "wallet balance" in the interesting queries in the db-sync git repo.

Answer 1

The tx_out table contains both spent and unspent tx outputs. In order to calculate your balance, you need to sum only the unspent outputs.

We can check which outputs are unspent by joining on the tx_in table. If there is no associated entry for a particular tx_out row in the tx_in table, this output has not been spent.

Here are the associated queries for calculating both address and stake address balances:

-- address balance
select sum(tx_out.value)
from tx_out
left join tx_in on tx_in.tx_out_id = tx_out.tx_id and tx_in.tx_out_index = tx_out.index
where address = '<your-address-here>'
  and tx_in.id is null

-- stake address balance
select sum(tx_out.value)
from stake_address
inner join tx_out on tx_out.stake_address_id = stake_address.id
left join tx_in on tx_in.tx_out_id = tx_out.tx_id and tx_in.tx_out_index = tx_out.index
where stake_address.view = '<your-stake-address-here>'
  and tx_in.id is null

Note that these balances will not include your staking reward balance.