lightning-sql -- Command to do complex queries on list commands
SYNOPSIS
sql query
DESCRIPTION
Command added in v23.02.
The sql RPC command runs the given query across a sqlite3 database created from various list commands.
When tables are accessed, it calls the below commands, so it's no faster than any other local access (though it goes to great length to cache listnodes
and listchannels
) which then processes the results.
It is, however faster for remote access if the result of the query is much smaller than the list commands would be.
Note that you may need to use -o
if you use queries which contain =
(which make lightning-cli(1) default to keyword style)
- query (string): The standard sqlite3 query to run.
Note that queries like "SELECT *" are fragile, as columns will change across releases; see lightning-listsqlschemas(7).
PERMITTED SQLITE3 FUNCTIONS
Writing to the database is not permitted, and limits are placed on various other query parameters.
Additionally, only the following functions are allowed:
- abs
- avg
- coalesce
- count
- date
- datetime
- julianday
- hex
- quote
- length
- like
- lower
- upper
- min
- max
- strftime
- sum
- time
- timediff
- total
- unixepoch
TREATMENT OF TYPES
The following types are supported in schemas, and this shows how they are presented in the database. This matters: a JSON boolean is represented as an integer in the database, so a query will return 0 or 1, not true or false.
-
hex. A hex string.
- JSON: a string
- sqlite3: BLOB
-
hash/secret/pubkey/txid: just like hex.
-
msat/integer/u64/u32/u16/u8. Normal numbers.
- JSON: an unsigned integer
- sqlite3: INTEGER
-
boolean. True or false.
- JSON: literal true or false
- sqlite3: INTEGER
-
number. A floating point number (used for times in some places).
- JSON: number
- sqlite3: REAL
-
string. Text.
- JSON: string
- sqlite3: TEXT
-
short_channel_id. A short-channel-id of form 1x2x3.
- JSON: string
- sqlite3: TEXT
TABLES
Note that the first column of every table is a unique integer called rowid
: this is used for related tables to refer to specific rows in their parent. sqlite3 usually has this as an implicit column, but we make it explicit as the implicit version is not allowed to be used as a foreign key.
The following tables are currently supported:
-
bkpr_accountevents
(see lightning-bkpr-listaccountevents(7))account
(typestring
, sqltypeTEXT
)type
(typestring
, sqltypeTEXT
)tag
(typestring
, sqltypeTEXT
)credit_msat
(typemsat
, sqltypeINTEGER
)debit_msat
(typemsat
, sqltypeINTEGER
)currency
(typestring
, sqltypeTEXT
)timestamp
(typeu32
, sqltypeINTEGER
)description
(typestring
, sqltypeTEXT
)outpoint
(typestring
, sqltypeTEXT
)blockheight
(typeu32
, sqltypeINTEGER
)origin
(typestring
, sqltypeTEXT
)payment_id
(typehex
, sqltypeBLOB
)txid
(typetxid
, sqltypeBLOB
)fees_msat
(typemsat
, sqltypeINTEGER
)is_rebalance
(typeboolean
, sqltypeINTEGER
)part_id
(typeu32
, sqltypeINTEGER
)
-
bkpr_income
(see lightning-bkpr-listincome(7))account
(typestring
, sqltypeTEXT
)tag
(typestring
, sqltypeTEXT
)credit_msat
(typemsat
, sqltypeINTEGER
)debit_msat
(typemsat
, sqltypeINTEGER
)currency
(typestring
, sqltypeTEXT
)timestamp
(typeu32
, sqltypeINTEGER
)description
(typestring
, sqltypeTEXT
)outpoint
(typestring
, sqltypeTEXT
)txid
(typetxid
, sqltypeBLOB
)payment_id
(typehex
, sqltypeBLOB
)
-
channels
indexed byshort_channel_id
(see lightning-listchannels(7))source
(typepubkey
, sqltypeBLOB
)destination
(typepubkey
, sqltypeBLOB
)short_channel_id
(typeshort_channel_id
, sqltypeTEXT
)direction
(typeu32
, sqltypeINTEGER
)public
(typeboolean
, sqltypeINTEGER
)amount_msat
(typemsat
, sqltypeINTEGER
)message_flags
(typeu8
, sqltypeINTEGER
)channel_flags
(typeu8
, sqltypeINTEGER
)active
(typeboolean
, sqltypeINTEGER
)last_update
(typeu32
, sqltypeINTEGER
)base_fee_millisatoshi
(typeu32
, sqltypeINTEGER
)fee_per_millionth
(typeu32
, sqltypeINTEGER
)delay
(typeu32
, sqltypeINTEGER
)htlc_minimum_msat
(typemsat
, sqltypeINTEGER
)htlc_maximum_msat
(typemsat
, sqltypeINTEGER
)features
(typehex
, sqltypeBLOB
)
-
closedchannels
(see lightning-listclosedchannels(7))peer_id
(typepubkey
, sqltypeBLOB
)channel_id
(typehash
, sqltypeBLOB
)short_channel_id
(typeshort_channel_id
, sqltypeTEXT
)alias_local
(typeshort_channel_id
, sqltypeTEXT
, from JSON objectalias
)alias_remote
(typeshort_channel_id
, sqltypeTEXT
, from JSON objectalias
)opener
(typestring
, sqltypeTEXT
)closer
(typestring
, sqltypeTEXT
)private
(typeboolean
, sqltypeINTEGER
)- related table
closedchannels_channel_type_bits
, from JSON objectchannel_type
row
(reference toclosedchannels_channel_type.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)bits
(typeu32
, sqltypeINTEGER
)
- related table
closedchannels_channel_type_names
, from JSON objectchannel_type
row
(reference toclosedchannels_channel_type.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)names
(typestring
, sqltypeTEXT
)
total_local_commitments
(typeu64
, sqltypeINTEGER
)total_remote_commitments
(typeu64
, sqltypeINTEGER
)total_htlcs_sent
(typeu64
, sqltypeINTEGER
)funding_txid
(typetxid
, sqltypeBLOB
)funding_outnum
(typeu32
, sqltypeINTEGER
)leased
(typeboolean
, sqltypeINTEGER
)funding_fee_paid_msat
(typemsat
, sqltypeINTEGER
)funding_fee_rcvd_msat
(typemsat
, sqltypeINTEGER
)funding_pushed_msat
(typemsat
, sqltypeINTEGER
)total_msat
(typemsat
, sqltypeINTEGER
)final_to_us_msat
(typemsat
, sqltypeINTEGER
)min_to_us_msat
(typemsat
, sqltypeINTEGER
)max_to_us_msat
(typemsat
, sqltypeINTEGER
)last_commitment_txid
(typehash
, sqltypeBLOB
)last_commitment_fee_msat
(typemsat
, sqltypeINTEGER
)close_cause
(typestring
, sqltypeTEXT
)last_stable_connection
(typeu64
, sqltypeINTEGER
)
-
forwards
indexed byin_channel and in_htlc_id
(see lightning-listforwards(7))created_index
(typeu64
, sqltypeINTEGER
)in_channel
(typeshort_channel_id
, sqltypeTEXT
)in_htlc_id
(typeu64
, sqltypeINTEGER
)in_msat
(typemsat
, sqltypeINTEGER
)status
(typestring
, sqltypeTEXT
)received_time
(typenumber
, sqltypeREAL
)out_channel
(typeshort_channel_id
, sqltypeTEXT
)out_htlc_id
(typeu64
, sqltypeINTEGER
)updated_index
(typeu64
, sqltypeINTEGER
)style
(typestring
, sqltypeTEXT
)fee_msat
(typemsat
, sqltypeINTEGER
)out_msat
(typemsat
, sqltypeINTEGER
)resolved_time
(typenumber
, sqltypeREAL
)failcode
(typeu32
, sqltypeINTEGER
)failreason
(typestring
, sqltypeTEXT
)
-
htlcs
indexed byshort_channel_id and id
(see lightning-listhtlcs(7))short_channel_id
(typeshort_channel_id
, sqltypeTEXT
)id
(typeu64
, sqltypeINTEGER
)expiry
(typeu32
, sqltypeINTEGER
)amount_msat
(typemsat
, sqltypeINTEGER
)direction
(typestring
, sqltypeTEXT
)payment_hash
(typehash
, sqltypeBLOB
)state
(typestring
, sqltypeTEXT
)
-
invoices
indexed bypayment_hash
(see lightning-listinvoices(7))label
(typestring
, sqltypeTEXT
)description
(typestring
, sqltypeTEXT
)payment_hash
(typehash
, sqltypeBLOB
)status
(typestring
, sqltypeTEXT
)expires_at
(typeu64
, sqltypeINTEGER
)amount_msat
(typemsat
, sqltypeINTEGER
)bolt11
(typestring
, sqltypeTEXT
)bolt12
(typestring
, sqltypeTEXT
)local_offer_id
(typehash
, sqltypeBLOB
)invreq_payer_note
(typestring
, sqltypeTEXT
)created_index
(typeu64
, sqltypeINTEGER
)updated_index
(typeu64
, sqltypeINTEGER
)pay_index
(typeu64
, sqltypeINTEGER
)amount_received_msat
(typemsat
, sqltypeINTEGER
)paid_at
(typeu64
, sqltypeINTEGER
)paid_outpoint_txid
(typetxid
, sqltypeBLOB
, from JSON objectpaid_outpoint
)paid_outpoint_outnum
(typeu32
, sqltypeINTEGER
, from JSON objectpaid_outpoint
)payment_preimage
(typesecret
, sqltypeBLOB
)
-
nodes
indexed bynodeid
(see lightning-listnodes(7))nodeid
(typepubkey
, sqltypeBLOB
)last_timestamp
(typeu32
, sqltypeINTEGER
)alias
(typestring
, sqltypeTEXT
)color
(typehex
, sqltypeBLOB
)features
(typehex
, sqltypeBLOB
)- related table
nodes_addresses
row
(reference tonodes.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)type
(typestring
, sqltypeTEXT
)port
(typeu16
, sqltypeINTEGER
)address
(typestring
, sqltypeTEXT
)
option_will_fund_lease_fee_base_msat
(typemsat
, sqltypeINTEGER
, from JSON objectoption_will_fund
)option_will_fund_lease_fee_basis
(typeu32
, sqltypeINTEGER
, from JSON objectoption_will_fund
)option_will_fund_funding_weight
(typeu32
, sqltypeINTEGER
, from JSON objectoption_will_fund
)option_will_fund_channel_fee_max_base_msat
(typemsat
, sqltypeINTEGER
, from JSON objectoption_will_fund
)option_will_fund_channel_fee_max_proportional_thousandths
(typeu32
, sqltypeINTEGER
, from JSON objectoption_will_fund
)option_will_fund_compact_lease
(typehex
, sqltypeBLOB
, from JSON objectoption_will_fund
)
-
offers
indexed byoffer_id
(see lightning-listoffers(7))offer_id
(typehash
, sqltypeBLOB
)active
(typeboolean
, sqltypeINTEGER
)single_use
(typeboolean
, sqltypeINTEGER
)bolt12
(typestring
, sqltypeTEXT
)used
(typeboolean
, sqltypeINTEGER
)label
(typestring
, sqltypeTEXT
)
-
peerchannels
indexed bypeer_id
(see lightning-listpeerchannels(7))peer_id
(typepubkey
, sqltypeBLOB
)peer_connected
(typeboolean
, sqltypeINTEGER
)reestablished
(typeboolean
, sqltypeINTEGER
)state
(typestring
, sqltypeTEXT
)scratch_txid
(typetxid
, sqltypeBLOB
)- related table
peerchannels_channel_type_bits
, from JSON objectchannel_type
row
(reference topeerchannels_channel_type.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)bits
(typeu32
, sqltypeINTEGER
)
- related table
peerchannels_channel_type_names
, from JSON objectchannel_type
row
(reference topeerchannels_channel_type.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)names
(typestring
, sqltypeTEXT
)
local_htlc_minimum_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.local
)local_htlc_maximum_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.local
)local_cltv_expiry_delta
(typeu32
, sqltypeINTEGER
, from JSON objectupdates.local
)local_fee_base_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.local
)local_fee_proportional_millionths
(typeu32
, sqltypeINTEGER
, from JSON objectupdates.local
)remote_htlc_minimum_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.remote
)remote_htlc_maximum_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.remote
)remote_cltv_expiry_delta
(typeu32
, sqltypeINTEGER
, from JSON objectupdates.remote
)remote_fee_base_msat
(typemsat
, sqltypeINTEGER
, from JSON objectupdates.remote
)remote_fee_proportional_millionths
(typeu32
, sqltypeINTEGER
, from JSON objectupdates.remote
)ignore_fee_limits
(typeboolean
, sqltypeINTEGER
)lost_state
(typeboolean
, sqltypeINTEGER
)feerate_perkw
(typeu32
, sqltypeINTEGER
, from JSON objectfeerate
)feerate_perkb
(typeu32
, sqltypeINTEGER
, from JSON objectfeerate
)owner
(typestring
, sqltypeTEXT
)short_channel_id
(typeshort_channel_id
, sqltypeTEXT
)channel_id
(typehash
, sqltypeBLOB
)funding_txid
(typetxid
, sqltypeBLOB
)funding_outnum
(typeu32
, sqltypeINTEGER
)initial_feerate
(typestring
, sqltypeTEXT
)last_feerate
(typestring
, sqltypeTEXT
)next_feerate
(typestring
, sqltypeTEXT
)next_fee_step
(typeu32
, sqltypeINTEGER
)- related table
peerchannels_inflight
row
(reference topeerchannels.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)funding_txid
(typetxid
, sqltypeBLOB
)funding_outnum
(typeu32
, sqltypeINTEGER
)feerate
(typestring
, sqltypeTEXT
)total_funding_msat
(typemsat
, sqltypeINTEGER
)splice_amount
(typeinteger
, sqltypeINTEGER
)our_funding_msat
(typemsat
, sqltypeINTEGER
)scratch_txid
(typetxid
, sqltypeBLOB
)
close_to
(typehex
, sqltypeBLOB
)private
(typeboolean
, sqltypeINTEGER
)opener
(typestring
, sqltypeTEXT
)closer
(typestring
, sqltypeTEXT
)- related table
peerchannels_features
row
(reference topeerchannels.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)features
(typestring
, sqltypeTEXT
)
funding_pushed_msat
(typemsat
, sqltypeINTEGER
, from JSON objectfunding
)funding_local_funds_msat
(typemsat
, sqltypeINTEGER
, from JSON objectfunding
)funding_remote_funds_msat
(typemsat
, sqltypeINTEGER
, from JSON objectfunding
)funding_fee_paid_msat
(typemsat
, sqltypeINTEGER
, from JSON objectfunding
)funding_fee_rcvd_msat
(typemsat
, sqltypeINTEGER
, from JSON objectfunding
)to_us_msat
(typemsat
, sqltypeINTEGER
)min_to_us_msat
(typemsat
, sqltypeINTEGER
)max_to_us_msat
(typemsat
, sqltypeINTEGER
)total_msat
(typemsat
, sqltypeINTEGER
)fee_base_msat
(typemsat
, sqltypeINTEGER
)fee_proportional_millionths
(typeu32
, sqltypeINTEGER
)dust_limit_msat
(typemsat
, sqltypeINTEGER
)max_total_htlc_in_msat
(typemsat
, sqltypeINTEGER
)their_reserve_msat
(typemsat
, sqltypeINTEGER
)our_reserve_msat
(typemsat
, sqltypeINTEGER
)spendable_msat
(typemsat
, sqltypeINTEGER
)receivable_msat
(typemsat
, sqltypeINTEGER
)minimum_htlc_in_msat
(typemsat
, sqltypeINTEGER
)minimum_htlc_out_msat
(typemsat
, sqltypeINTEGER
)maximum_htlc_out_msat
(typemsat
, sqltypeINTEGER
)their_to_self_delay
(typeu32
, sqltypeINTEGER
)our_to_self_delay
(typeu32
, sqltypeINTEGER
)max_accepted_htlcs
(typeu32
, sqltypeINTEGER
)alias_local
(typeshort_channel_id
, sqltypeTEXT
, from JSON objectalias
)alias_remote
(typeshort_channel_id
, sqltypeTEXT
, from JSON objectalias
)- related table
peerchannels_state_changes
row
(reference topeerchannels.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)timestamp
(typestring
, sqltypeTEXT
)old_state
(typestring
, sqltypeTEXT
)new_state
(typestring
, sqltypeTEXT
)cause
(typestring
, sqltypeTEXT
)message
(typestring
, sqltypeTEXT
)
- related table
peerchannels_status
row
(reference topeerchannels.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)status
(typestring
, sqltypeTEXT
)
in_payments_offered
(typeu64
, sqltypeINTEGER
)in_offered_msat
(typemsat
, sqltypeINTEGER
)in_payments_fulfilled
(typeu64
, sqltypeINTEGER
)in_fulfilled_msat
(typemsat
, sqltypeINTEGER
)out_payments_offered
(typeu64
, sqltypeINTEGER
)out_offered_msat
(typemsat
, sqltypeINTEGER
)out_payments_fulfilled
(typeu64
, sqltypeINTEGER
)out_fulfilled_msat
(typemsat
, sqltypeINTEGER
)last_stable_connection
(typeu64
, sqltypeINTEGER
)- related table
peerchannels_htlcs
row
(reference topeerchannels.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)direction
(typestring
, sqltypeTEXT
)id
(typeu64
, sqltypeINTEGER
)amount_msat
(typemsat
, sqltypeINTEGER
)expiry
(typeu32
, sqltypeINTEGER
)payment_hash
(typehash
, sqltypeBLOB
)local_trimmed
(typeboolean
, sqltypeINTEGER
)status
(typestring
, sqltypeTEXT
)state
(typestring
, sqltypeTEXT
)
close_to_addr
(typestring
, sqltypeTEXT
)last_tx_fee_msat
(typemsat
, sqltypeINTEGER
)direction
(typeu32
, sqltypeINTEGER
)
-
peers
indexed byid
(see lightning-listpeers(7))id
(typepubkey
, sqltypeBLOB
)connected
(typeboolean
, sqltypeINTEGER
)num_channels
(typeu32
, sqltypeINTEGER
)- related table
peers_netaddr
row
(reference topeers.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)netaddr
(typestring
, sqltypeTEXT
)
remote_addr
(typestring
, sqltypeTEXT
)features
(typehex
, sqltypeBLOB
)
-
sendpays
indexed bypayment_hash
(see lightning-listsendpays(7))created_index
(typeu64
, sqltypeINTEGER
)id
(typeu64
, sqltypeINTEGER
)groupid
(typeu64
, sqltypeINTEGER
)partid
(typeu64
, sqltypeINTEGER
)payment_hash
(typehash
, sqltypeBLOB
)updated_index
(typeu64
, sqltypeINTEGER
)status
(typestring
, sqltypeTEXT
)amount_msat
(typemsat
, sqltypeINTEGER
)destination
(typepubkey
, sqltypeBLOB
)created_at
(typeu64
, sqltypeINTEGER
)amount_sent_msat
(typemsat
, sqltypeINTEGER
)label
(typestring
, sqltypeTEXT
)bolt11
(typestring
, sqltypeTEXT
)description
(typestring
, sqltypeTEXT
)bolt12
(typestring
, sqltypeTEXT
)completed_at
(typeu64
, sqltypeINTEGER
)payment_preimage
(typesecret
, sqltypeBLOB
)erroronion
(typehex
, sqltypeBLOB
)
-
transactions
indexed byhash
(see lightning-listtransactions(7))hash
(typetxid
, sqltypeBLOB
)rawtx
(typehex
, sqltypeBLOB
)blockheight
(typeu32
, sqltypeINTEGER
)txindex
(typeu32
, sqltypeINTEGER
)locktime
(typeu32
, sqltypeINTEGER
)version
(typeu32
, sqltypeINTEGER
)- related table
transactions_inputs
row
(reference totransactions.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)txid
(typetxid
, sqltypeBLOB
)idx
(typeu32
, sqltypeINTEGER
, from JSON fieldindex
)sequence
(typeu32
, sqltypeINTEGER
)
- related table
transactions_outputs
row
(reference totransactions.rowid
, sqltypeINTEGER
)arrindex
(index within array, sqltypeINTEGER
)idx
(typeu32
, sqltypeINTEGER
, from JSON fieldindex
)amount_msat
(typemsat
, sqltypeINTEGER
)scriptPubKey
(typehex
, sqltypeBLOB
)
RETURN VALUE
On success, an object containing rows is returned. It is an array. Each array entry contains an array of values, each an integer, real number, string or null, depending on the sqlite3 type.
The object may contain warning_db_failure if the database fails partway through its operation.
On success, an object is returned, containing:
- rows (array of arrays):
- (array)
The following warnings may also be returned:
- warning_db_failure: A message if the database encounters an error partway through.
ERRORS
On failure, an error is returned.
AUTHOR
Rusty Russell <[email protected]> is mainly responsible.
SEE ALSO
lightning-listtransactions(7), lightning-listchannels(7), lightning-listpeers(7), lightning-listnodes(7), lightning-listforwards(7)
RESOURCES
Main web site: https://github.com/ElementsProject/lightning
EXAMPLES
Example 1: A simple peers selection query:
Request:
lightning-cli sql -k "query"="SELECT id FROM peers"
{
"id": "example:sql#1",
"method": "sql",
"params": {
"query": "SELECT id FROM peers"
}
}
Response:
{
"rows": [
[
"nodeid020202020202020202020202020202020202020202020202020202020202"
]
]
}
Example 2: A statement containing =
needs -o
in shell:
Request:
lightning-cli sql -o "SELECT label, description, status FROM invoices WHERE label='label inv_l12'"
{
"id": "example:sql#2",
"method": "sql",
"params": [
"SELECT label, description, status FROM invoices WHERE label='label inv_l12'"
]
}
Response:
{
"rows": [
[
"label inv_l12",
"description inv_l12",
"unpaid"
]
]
}
Example 3: If you want to get specific nodeid values from the nodes table:
Request:
lightning-cli sql -o "SELECT nodeid FROM nodes WHERE nodeid != x'nodeid030303030303030303030303030303030303030303030303030303030303'"
{
"id": "example:sql#3",
"method": "sql",
"params": [
"SELECT nodeid FROM nodes WHERE nodeid != x'nodeid030303030303030303030303030303030303030303030303030303030303'"
]
}
Response:
{
"rows": [
[
"nodeid020202020202020202020202020202020202020202020202020202020202"
],
[
"nodeid010101010101010101010101010101010101010101010101010101010101"
],
[
"nodeid040404040404040404040404040404040404040404040404040404040404"
]
]
}
Example 4: If you want to compare a BLOB column, x'hex'
or X'hex'
are needed:
Request:
lightning-cli sql "SELECT nodeid FROM nodes WHERE nodeid IN (x'nodeid010101010101010101010101010101010101010101010101010101010101', x'nodeid030303030303030303030303030303030303030303030303030303030303')"
{
"id": "example:sql#4",
"method": "sql",
"params": [
"SELECT nodeid FROM nodes WHERE nodeid IN (x'nodeid010101010101010101010101010101010101010101010101010101010101', x'nodeid030303030303030303030303030303030303030303030303030303030303')"
]
}
Response:
{
"rows": [
[
"nodeid010101010101010101010101010101010101010101010101010101010101"
],
[
"nodeid030303030303030303030303030303030303030303030303030303030303"
]
]
}
Example 5: Related tables are usually referenced by JOIN:
Request:
lightning-cli sql -o 'SELECT peer_id, to_us_msat, total_msat, peerchannels_status.status FROM peerchannels INNER JOIN peerchannels_status ON peerchannels_status.row = peerchannels.rowid'
{
"id": "example:sql#5",
"method": "sql",
"params": [
"SELECT peer_id, to_us_msat, total_msat, peerchannels_status.status FROM peerchannels INNER JOIN peerchannels_status ON peerchannels_status.row = peerchannels.rowid"
]
}
Response:
{
"rows": [
[
"nodeid020202020202020202020202020202020202020202020202020202020202",
490493792,
1000000000,
"CHANNELD_NORMAL:Channel ready for use."
]
]
}
Example 6: Simple function usage, in this case COUNT. Strings inside arrays need ", and ' to protect them from the shell:
Request:
lightning-cli sql "SELECT COUNT(*) FROM forwards"
{
"id": "example:sql#6",
"method": "sql",
"params": [
"SELECT COUNT(*) FROM forwards"
]
}
Response:
{
"rows": [
[
9
]
]
}
Example 7:
Request:
lightning-cli sql "SELECT * from peerchannels_features"
{
"id": "example:sql#7",
"method": "sql",
"params": [
"SELECT * from peerchannels_features"
]
}
Response:
{
"rows": [
[
28,
23,
0,
"option_static_remotekey"
],
[
29,
23,
1,
"option_anchors_zero_fee_htlc_tx"
],
[
30,
23,
2,
"option_anchors"
]
]
}