LIGHTNING-SQL(7)
💅 NAME 💅
okay so welcome to the LIGHTNING-SQL(7) page, bestie — this is the manual for the sql RPC command, and it's giving “complex queries on list commands” energy. 💅
sql — Command to do complex queries on list commands.
✨ SYNOPSIS ✨
---
# 🦋 DESCRIPTION 🦋
Command added in `v23.02`.
The <cyan>`sql` RPC command</cyan> 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. lowkey important, ngl.
It is, however faster for remote access if the result of the query is much smaller than the list commands would be. slay for bandwidth. 🌸
> 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)`. fr fr, don't get too attached to `SELECT *`.
---
# 🌈 PERMITTED SQLITE3 FUNCTIONS 🌈
Writing to the database is not permitted, and limits are placed on various other query parameters. so no sneaky writes, bestie. 💀
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`
- `json_object`
- `json_group_array`
---
# 🎀 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`. <purple>this is the part where the type lore gets real</purple>.
- **`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 tables which have a `created_index` field use that as the primary key (and `rowid` is an alias to this), otherwise an explicit `rowid` integer primary key is generated, whose value changes on each refresh. This field 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` (type `string`, sqltype `TEXT`)
- `type` (type `string`, sqltype `TEXT`)
- `tag` (type `string`, sqltype `TEXT`)
- `credit_msat` (type `msat`, sqltype `INTEGER`)
- `debit_msat` (type `msat`, sqltype `INTEGER`)
- `currency` (type `string`, sqltype `TEXT`)
- `timestamp` (type `u32`, sqltype `INTEGER`)
- `currencyrate` (type `number`, sqltype `REAL`)
- `description` (type `string`, sqltype `TEXT`)
- `outpoint` (type `string`, sqltype `TEXT`)
- `blockheight` (type `u32`, sqltype `INTEGER`)
- `origin` (type `string`, sqltype `TEXT`)
- `payment_id` (type `hex`, sqltype `BLOB`)
- `txid` (type `txid`, sqltype `BLOB`)
- `fees_msat` (type `msat`, sqltype `INTEGER`)
- `is_rebalance` (type `boolean`, sqltype `INTEGER`)
- `part_id` (type `u32`, sqltype `INTEGER`)
---
- **`bkpr_income`** — the income table, with its own whole `lightning-bkpr-listincome(7)` moment 💸✨
- `account` — type `string`, sqltype `TEXT` 🎀
- `tag` — type `string`, sqltype `TEXT` 🏷️
- `credit_msat` — type `msat`, sqltype `INTEGER` 💖
- `debit_msat` — type `msat`, sqltype `INTEGER` 💸
- `currency` — type `string`, sqltype `TEXT` 💱
- `timestamp` — type `u32`, sqltype `INTEGER` ⏰
- `description` — type `string`, sqltype `TEXT` 📝
- `outpoint` — type `string`, sqltype `TEXT` 🔗
- `txid` — type `txid`, sqltype `BLOB` 🧾
- `payment_id` — type `hex`, sqltype `BLOB` 🆔
- **`chainmoves`** — indexed by `account_id` (see `lightning-listchainmoves(7)`) ⛓️
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `account_id` — type `string`, sqltype `TEXT` 🎀
- `credit_msat` — type `msat`, sqltype `INTEGER` 💖
- `debit_msat` — type `msat`, sqltype `INTEGER` 💸
- `timestamp` — type `u64`, sqltype `INTEGER` ⏰
- `primary_tag` — type `string`, sqltype `TEXT` 🏷️
- related table `chainmoves_extra_tags` — the extra-tags side quest 🌈
- `row` — reference to `chainmoves.created_index`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `extra_tags` — type `string`, sqltype `TEXT` 🏷️
- `peer_id` — type `pubkey`, sqltype `BLOB` 🫶
- `originating_account` — type `string`, sqltype `TEXT` 🎀
- `spending_txid` — type `txid`, sqltype `BLOB` 🧾
- `utxo` — type `outpoint`, sqltype `TEXT` 🔗
- `payment_hash` — type `hash`, sqltype `BLOB` #️⃣
- `output_msat` — type `msat`, sqltype `INTEGER` 💖
- `output_count` — type `u32`, sqltype `INTEGER` 🔢
- `blockheight` — type `u32`, sqltype `INTEGER` 🧱
- **`channelmoves`** — indexed by `account_id`, also indexed by `payment_hash` (see `lightning-listchannelmoves(7)`) 🔀
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `account_id` — type `string`, sqltype `TEXT` 🎀
- `credit_msat` — type `msat`, sqltype `INTEGER` 💖
- `debit_msat` — type `msat`, sqltype `INTEGER` 💸
- `timestamp` — type `u64`, sqltype `INTEGER` ⏰
- `primary_tag` — type `string`, sqltype `TEXT` 🏷️
- `payment_hash` — type `hash`, sqltype `BLOB` #️⃣
- `part_id` — type `u64`, sqltype `INTEGER` 🧩
- `group_id` — type `u64`, sqltype `INTEGER` 👯
- `fees_msat` — type `msat`, sqltype `INTEGER` 💸
- **`channels`** — indexed by `short_channel_id` (see `lightning-listchannels(7)`) 📡
- `source` — type `pubkey`, sqltype `BLOB` 🫶
- `destination` — type `pubkey`, sqltype `BLOB` 🎯
- `short_channel_id` — type `short_channel_id`, sqltype `TEXT` 🆔
- `direction` — type `u32`, sqltype `INTEGER` 🧭
- `public` — type `boolean`, sqltype `INTEGER` 🌍
- `amount_msat` — type `msat`, sqltype `INTEGER` 💰
- `message_flags` — type `u8`, sqltype `INTEGER` 🚩
- `channel_flags` — type `u8`, sqltype `INTEGER` 🏁
- `active` — type `boolean`, sqltype `INTEGER` ✅
- `last_update` — type `u32`, sqltype `INTEGER` 🕰️
- `base_fee_millisatoshi` — type `u32`, sqltype `INTEGER` 💵
- `fee_per_millionth` — type `u32`, sqltype `INTEGER` 📈
- `delay` — type `u32`, sqltype `INTEGER` ⏳
- `htlc_minimum_msat` — type `msat`, sqltype `INTEGER` 🔽
- `htlc_maximum_msat` — type `msat`, sqltype `INTEGER` 🔼
- `features` — type `hex`, sqltype `BLOB` 🧬
- **`closedchannels`** — (see `lightning-listclosedchannels(7)`) 🚪
- `peer_id` — type `pubkey`, sqltype `BLOB` 🫶
- `channel_id` — type `hash`, sqltype `BLOB` #️⃣
- `short_channel_id` — type `short_channel_id`, sqltype `TEXT` 🆔
- `alias_local` — type `short_channel_id`, sqltype `TEXT`, from JSON object `alias` 🏷️
- `alias_remote` — type `short_channel_id`, sqltype `TEXT`, from JSON object `alias` 🏷️
- `opener` — type `string`, sqltype `TEXT` 🔓
- `closer` — type `string`, sqltype `TEXT` 🔒
- `private` — type `boolean`, sqltype `INTEGER` 🙈
- related table `closedchannels_channel_type_bits`, from JSON object `channel_type` — the bitsy channel-type table 🧩
- `row` — reference to `closedchannels_channel_type.rowid`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `bits` — type `u32`, sqltype `INTEGER` 🧮
- related table `closedchannels_channel_type_names`, from JSON object `channel_type` — the names version, because of course 💅
- `row` — reference to `closedchannels_channel_type.rowid`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `names` — type `string`, sqltype `TEXT` 🏷️
- `total_local_commitments` — type `u64`, sqltype `INTEGER` 🧾
- `total_remote_commitments` — type `u64`, sqltype `INTEGER` 🧾
- `total_htlcs_sent` — type `u64`, sqltype `INTEGER` 📤
- `funding_txid` — type `txid`, sqltype `BLOB` 🧾
- `funding_outnum` — type `u32`, sqltype `INTEGER` 🔢
- `funding_psbt` — type `string`, sqltype `TEXT` 📜
- `funding_withheld` — type `boolean`, sqltype `INTEGER` 🙊
- `leased` — type `boolean`, sqltype `INTEGER` 📝
- `funding_fee_paid_msat` — type `msat`, sqltype `INTEGER` 💸
- `funding_fee_rcvd_msat` — type `msat`, sqltype `INTEGER` 💰
- `funding_pushed_msat` — type `msat`, sqltype `INTEGER` 🫸
- `total_msat` — type `msat`, sqltype `INTEGER` 💰
- `final_to_us_msat` — type `msat`, sqltype `INTEGER` 🫶
- `min_to_us_msat` — type `msat`, sqltype `INTEGER` 🔽
- `max_to_us_msat` — type `msat`, sqltype `INTEGER` 🔼
- `last_commitment_txid` — type `hash`, sqltype `BLOB` #️⃣
- `last_commitment_fee_msat` — type `msat`, sqltype `INTEGER` 💸
- `close_cause` — type `string`, sqltype `TEXT` 💔
- `last_stable_connection` — type `u64`, sqltype `INTEGER` 🧘
- **`forwards`** — indexed by `in_channel` and `in_htlc_id` (see `lightning-listforwards(7)`) ➡️
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `in_channel` — type `short_channel_id`, sqltype `TEXT` 📥
- `in_htlc_id` — type `u64`, sqltype `INTEGER` 🆔
- `in_msat` — type `msat`, sqltype `INTEGER` 💖
- `status` — type `string`, sqltype `TEXT` 📊
- `received_time` — type `number`, sqltype `REAL` ⏰
- `out_channel` — type `short_channel_id`, sqltype `TEXT` 📤
- `out_htlc_id` — type `u64`, sqltype `INTEGER` 🆔
- `updated_index` — type `u64`, sqltype `INTEGER` 🔄
- `style` — type `string`, sqltype `TEXT` 💅
- `fee_msat` — type `msat`, sqltype `INTEGER` 💸
- `out_msat` — type `msat`, sqltype `INTEGER` 💰
- `resolved_time` — type `number`, sqltype `REAL` ⏱️
- `failcode` — type `u32`, sqltype `INTEGER` 💀
- `failreason` — type `string`, sqltype `TEXT` 😭
- **`htlcs`** — indexed by `short_channel_id` and `id` (see `lightning-listhtlcs(7)`) 🧾
- `short_channel_id` — type `short_channel_id`, sqltype `TEXT` 🆔
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `updated_index` — type `u64`, sqltype `INTEGER` 🔄
- `id` — type `u64`, sqltype `INTEGER` 🆔
- `expiry` — type `u32`, sqltype `INTEGER` ⏳
- `amount_msat` — type `msat`, sqltype `INTEGER` 💰
- `direction` — type `string`, sqltype `TEXT` 🧭
- `payment_hash` — type `hash`, sqltype `BLOB` #️⃣
- `state` — type `string`, sqltype `TEXT` 📍
- **`invoices`** — indexed by `payment_hash` (see `lightning-listinvoices(7)`) 🧾💖
- `label` — type `string`, sqltype `TEXT` 🏷️
- `description` — type `string`, sqltype `TEXT` 📝
- `payment_hash` — type `hash`, sqltype `BLOB` #️⃣
- `status` — type `string`, sqltype `TEXT` 📊
- `expires_at` — type `u64`, sqltype `INTEGER` ⏳
- `amount_msat` — type `msat`, sqltype `INTEGER` 💰
- `bolt11` — type `string`, sqltype `TEXT` 🧾
- `bolt12` — type `string`, sqltype `TEXT` 🧾
- `local_offer_id` — type `hash`, sqltype `BLOB` #️⃣
- `invreq_payer_note` — type `string`, sqltype `TEXT` 📝
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `updated_index` — type `u64`, sqltype `INTEGER` 🔄
- `pay_index` — type `u64`, sqltype `INTEGER` 💳
- `amount_received_msat` — type `msat`, sqltype `INTEGER` 💰
- `paid_at` — type `u64`, sqltype `INTEGER` ⏰
- `paid_outpoint_txid` — type `txid`, sqltype `BLOB`, from JSON object `paid_outpoint` 🧾
- `paid_outpoint_outnum` — type `u32`, sqltype `INTEGER`, from JSON object `paid_outpoint` 🔢
- `payment_preimage` — type `secret`, sqltype `BLOB` 🤫
- **`networkevents`** — (see `lightning-listnetworkevents(7)`) 📡
- `created_index` — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑
- `timestamp` — type `u64`, sqltype `INTEGER` ⏰
- `peer_id` — type `pubkey`, sqltype `BLOB` 🫶
- `type` — type `string`, sqltype `TEXT` 🏷️
- `reason` — type `string`, sqltype `TEXT` 📝
- `duration_nsec` — type `u64`, sqltype `INTEGER` ⏱️
- `connect_attempted` — type `boolean`, sqltype `INTEGER` 🔌
- **`nodes`** — indexed by `nodeid` (see `lightning-listnodes(7)`) 🌐
- `nodeid` — type `pubkey`, sqltype `BLOB` 🫶
- `last_timestamp` — type `u32`, sqltype `INTEGER` ⏰
- `alias` — type `string`, sqltype `TEXT` 🏷️
- `color` — type `hex`, sqltype `BLOB` 🎨
- `features` — type `hex`, sqltype `BLOB` 🧬
- related table `nodes_addresses` — the node’s address list 🏠
- `row` — reference to `nodes.rowid`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `type` — type `string`, sqltype `TEXT` 🏷️
- `port` — type `u16`, sqltype `INTEGER` 🔌
- `address` — type `string`, sqltype `TEXT` 📍
- `option_will_fund_lease_fee_base_msat` — type `msat`, sqltype `INTEGER`, from JSON object `option_will_fund` 💸
- `option_will_fund_lease_fee_basis` — type `u32`, sqltype `INTEGER`, from JSON object `option_will_fund` 📊
- `option_will_fund_funding_weight` — type `u32`, sqltype `INTEGER`, from JSON object `option_will_fund` ⚖️
- `option_will_fund_channel_fee_max_base_msat` — type `msat`, sqltype `INTEGER`, from JSON object `option_will_fund` 💵
- `option_will_fund_channel_fee_max_proportional_thousandths` — type `u32`, sqltype `INTEGER`, from JSON object `option_will_fund` 📈
- `option_will_fund_compact_lease` — type `hex`, sqltype `BLOB`, from JSON object `option_will_fund` 📦
- **`offers`** — indexed by `offer_id` (see `lightning-listoffers(7)`) 🎁
- `offer_id` — type `hash`, sqltype `BLOB` #️⃣
- `active` — type `boolean`, sqltype `INTEGER` ✅
- `single_use` — type `boolean`, sqltype `INTEGER` 1️⃣
- `force_paths` — type `boolean`, sqltype `INTEGER` 🛣️
- `bolt12` — type `string`, sqltype `TEXT` 🧾
- `description` — type `string`, sqltype `TEXT` 📝
- `used` — type `boolean`, sqltype `INTEGER` ✔️
- `label` — type `string`, sqltype `TEXT` 🏷️
- **`peerchannels`** — indexed by `peer_id` (see `lightning-listpeerchannels(7)`) 🫶📡
- `peer_id` — type `pubkey`, sqltype `BLOB` 🫶
- `peer_connected` — type `boolean`, sqltype `INTEGER` 🔌
- `reestablished` — type `boolean`, sqltype `INTEGER` 🔄
- `state` — type `string`, sqltype `TEXT` 📍
- `scratch_txid` — type `txid`, sqltype `BLOB` 🧾
- related table `peerchannels_channel_type_bits`, from JSON object `channel_type` — channel-type bits, slay 🧩
- `row` — reference to `peerchannels_channel_type.rowid`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `bits` — type `u32`, sqltype `INTEGER` 🧮
- related table `peerchannels_channel_type_names`, from JSON object `channel_type` — and the matching names 💅
- `row` — reference to `peerchannels_channel_type.rowid`, sqltype `INTEGER` 🔗
- `arrindex` — index within array, sqltype `INTEGER` 🔢
- `names` — type `string`, sqltype `TEXT` 🏷️
- `local_htlc_minimum_msat` — type `msat`, sqltype `INTEGER`, from JSON object `updates.local` 🔽
- `local_htlc_maximum_msat` — type `msat`, sqltype `INTEGER`, from JSON object `updates.local` 🔼
- **`local_cltv_expiry_delta`** — type `u32`, sqltype `INTEGER`, pulled straight outta the JSON object `updates.local` ✨
- **`local_fee_base_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `updates.local`
- **`local_fee_proportional_millionths`** — type `u32`, sqltype `INTEGER`, from JSON object `updates.local`
- **`remote_htlc_minimum_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `updates.remote` 💫
- **`remote_htlc_maximum_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `updates.remote`
- **`remote_cltv_expiry_delta`** — type `u32`, sqltype `INTEGER`, from JSON object `updates.remote`
- **`remote_fee_base_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `updates.remote`
- **`remote_fee_proportional_millionths`** — type `u32`, sqltype `INTEGER`, from JSON object `updates.remote`
- **`ignore_fee_limits`** — type `boolean`, sqltype `INTEGER`
- **`lost_state`** — type `boolean`, sqltype `INTEGER`
- **`feerate_perkw`** — type `u32`, sqltype `INTEGER`, from JSON object `feerate`
- **`feerate_perkb`** — type `u32`, sqltype `INTEGER`, from JSON object `feerate`
- **`owner`** — type `string`, sqltype `TEXT`
- **`short_channel_id`** — type `short_channel_id`, sqltype `TEXT`
- **`direction`** — type `u32`, sqltype `INTEGER`
- **`channel_id`** — type `hash`, sqltype `BLOB`
- **`funding_txid`** — type `txid`, sqltype `BLOB`
- **`funding_outnum`** — type `u32`, sqltype `INTEGER`
- **`initial_feerate`** — type `string`, sqltype `TEXT`
- **`last_feerate`** — type `string`, sqltype `TEXT`
- **`next_feerate`** — type `string`, sqltype `TEXT`
- **`next_fee_step`** — type `u32`, sqltype `INTEGER`
- 🗂️ related table **`peerchannels_inflight`**
- **`row`** — reference to `peerchannels.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`funding_txid`** — type `txid`, sqltype `BLOB`
- **`funding_outnum`** — type `u32`, sqltype `INTEGER`
- **`feerate`** — type `string`, sqltype `TEXT`
- **`total_funding_msat`** — type `msat`, sqltype `INTEGER`
- **`splice_amount`** — type `integer`, sqltype `INTEGER`
- **`our_funding_msat`** — type `msat`, sqltype `INTEGER`
- **`scratch_txid`** — type `txid`, sqltype `BLOB`
- **`close_to`** — type `hex`, sqltype `BLOB`
- **`private`** — type `boolean`, sqltype `INTEGER`
- **`opener`** — type `string`, sqltype `TEXT`
- **`closer`** — type `string`, sqltype `TEXT`
- 🗂️ related table **`peerchannels_features`**
- **`row`** — reference to `peerchannels.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`features`** — type `string`, sqltype `TEXT`
- **`funding_pushed_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `funding` 💸
- **`funding_local_funds_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `funding`
- **`funding_remote_funds_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `funding`
- **`funding_fee_paid_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `funding`
- **`funding_fee_rcvd_msat`** — type `msat`, sqltype `INTEGER`, from JSON object `funding`
- **`funding_psbt`** — type `string`, sqltype `TEXT`, from JSON object `funding`
- **`funding_withheld`** — type `boolean`, sqltype `INTEGER`, from JSON object `funding`
- **`to_us_msat`** — type `msat`, sqltype `INTEGER`
- **`min_to_us_msat`** — type `msat`, sqltype `INTEGER`
- **`max_to_us_msat`** — type `msat`, sqltype `INTEGER`
- **`total_msat`** — type `msat`, sqltype `INTEGER`
- **`fee_base_msat`** — type `msat`, sqltype `INTEGER`
- **`fee_proportional_millionths`** — type `u32`, sqltype `INTEGER`
- **`dust_limit_msat`** — type `msat`, sqltype `INTEGER`
- **`their_max_htlc_value_in_flight_msat`** — type `msat`, sqltype `INTEGER`
- **`our_max_htlc_value_in_flight_msat`** — type `msat`, sqltype `INTEGER`
- **`their_reserve_msat`** — type `msat`, sqltype `INTEGER`
- **`our_reserve_msat`** — type `msat`, sqltype `INTEGER`
- **`spendable_msat`** — type `msat`, sqltype `INTEGER`
- **`receivable_msat`** — type `msat`, sqltype `INTEGER`
- **`minimum_htlc_in_msat`** — type `msat`, sqltype `INTEGER`
- **`minimum_htlc_out_msat`** — type `msat`, sqltype `INTEGER`
- **`maximum_htlc_out_msat`** — type `msat`, sqltype `INTEGER`
- **`their_to_self_delay`** — type `u32`, sqltype `INTEGER`
- **`our_to_self_delay`** — type `u32`, sqltype `INTEGER`
- **`max_accepted_htlcs`** — type `u32`, sqltype `INTEGER`
- **`alias_local`** — type `short_channel_id`, sqltype `TEXT`, from JSON object `alias`
- **`alias_remote`** — type `short_channel_id`, sqltype `TEXT`, from JSON object `alias` ✨
- 🗂️ related table **`peerchannels_state_changes`**
- **`row`** — reference to `peerchannels.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`timestamp`** — type `string`, sqltype `TEXT`
- **`old_state`** — type `string`, sqltype `TEXT`
- **`new_state`** — type `string`, sqltype `TEXT`
- **`cause`** — type `string`, sqltype `TEXT`
- **`message`** — type `string`, sqltype `TEXT`
- 🗂️ related table **`peerchannels_status`**
- **`row`** — reference to `peerchannels.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`status`** — type `string`, sqltype `TEXT`
- **`in_payments_offered`** — type `u64`, sqltype `INTEGER`
- **`in_offered_msat`** — type `msat`, sqltype `INTEGER`
- **`in_payments_fulfilled`** — type `u64`, sqltype `INTEGER`
- **`in_fulfilled_msat`** — type `msat`, sqltype `INTEGER`
- **`out_payments_offered`** — type `u64`, sqltype `INTEGER`
- **`out_offered_msat`** — type `msat`, sqltype `INTEGER`
- **`out_payments_fulfilled`** — type `u64`, sqltype `INTEGER`
- **`out_fulfilled_msat`** — type `msat`, sqltype `INTEGER`
- **`last_stable_connection`** — type `u64`, sqltype `INTEGER`
- 🗂️ related table **`peerchannels_htlcs`**
- **`row`** — reference to `peerchannels.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`direction`** — type `string`, sqltype `TEXT`
- **`id`** — type `u64`, sqltype `INTEGER`
- **`amount_msat`** — type `msat`, sqltype `INTEGER`
- **`expiry`** — type `u32`, sqltype `INTEGER`
- **`payment_hash`** — type `hash`, sqltype `BLOB`
- **`local_trimmed`** — type `boolean`, sqltype `INTEGER`
- **`status`** — type `string`, sqltype `TEXT`
- **`state`** — type `string`, sqltype `TEXT`
- **`close_to_addr`** — type `string`, sqltype `TEXT`
- **`last_tx_fee_msat`** — type `msat`, sqltype `INTEGER`
And that's a wrap on `peerchannels` 🎀 next table bestie:
- 🫶 **`peers`** indexed by `id` (see `lightning-listpeers(7)`)
- **`id`** — type `pubkey`, sqltype `BLOB`
- **`connected`** — type `boolean`, sqltype `INTEGER`
- **`num_channels`** — type `u32`, sqltype `INTEGER`
- 🗂️ related table **`peers_netaddr`**
- **`row`** — reference to `peers.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`netaddr`** — type `string`, sqltype `TEXT`
- **`remote_addr`** — type `string`, sqltype `TEXT`
- **`features`** — type `hex`, sqltype `BLOB`
- 💅 **`sendpays`** indexed by `payment_hash` (see `lightning-listsendpays(7)`)
- **`created_index`** — type `u64`, sqltype `INTEGER PRIMARY KEY` 👑 (yes, the whole `INTEGER PRIMARY KEY` is the sqltype, that's not a typo)
- **`id`** — type `u64`, sqltype `INTEGER`
- **`groupid`** — type `u64`, sqltype `INTEGER`
- **`partid`** — type `u64`, sqltype `INTEGER`
- **`payment_hash`** — type `hash`, sqltype `BLOB`
- **`updated_index`** — type `u64`, sqltype `INTEGER`
- **`status`** — type `string`, sqltype `TEXT`
- **`amount_msat`** — type `msat`, sqltype `INTEGER`
- **`destination`** — type `pubkey`, sqltype `BLOB`
- **`created_at`** — type `u64`, sqltype `INTEGER`
- **`amount_sent_msat`** — type `msat`, sqltype `INTEGER`
- **`label`** — type `string`, sqltype `TEXT`
- **`bolt11`** — type `string`, sqltype `TEXT`
- **`description`** — type `string`, sqltype `TEXT`
- **`bolt12`** — type `string`, sqltype `TEXT`
- **`completed_at`** — type `u64`, sqltype `INTEGER`
- **`payment_preimage`** — type `secret`, sqltype `BLOB`
- **`erroronion`** — type `hex`, sqltype `BLOB`
- 🦋 **`transactions`** indexed by `hash` (see `lightning-listtransactions(7)`)
- **`hash`** — type `txid`, sqltype `BLOB`
- **`rawtx`** — type `hex`, sqltype `BLOB`
- **`blockheight`** — type `u32`, sqltype `INTEGER`
- **`txindex`** — type `u32`, sqltype `INTEGER`
- **`locktime`** — type `u32`, sqltype `INTEGER`
- **`version`** — type `u32`, sqltype `INTEGER`
- 🗂️ related table **`transactions_inputs`**
- **`row`** — reference to `transactions.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`txid`** — type `txid`, sqltype `BLOB`
- **`idx`** — type `u32`, sqltype `INTEGER`, from JSON field `index`
- **`sequence`** — type `u32`, sqltype `INTEGER`
- 🗂️ related table **`transactions_outputs`**
- **`row`** — reference to `transactions.rowid`, sqltype `INTEGER`
- **`arrindex`** — index within array, sqltype `INTEGER`
- **`idx`** — type `u32`, sqltype `INTEGER`, from JSON field `index`
- **`amount_msat`** — type `msat`, sqltype `INTEGER`
- **`scriptPubKey`** — type `hex`, sqltype `BLOB`
---
# 💖 RETURN VALUE 💖
When it all goes right, you get an object with `rows` in it, and `rows` is an array. Every entry inside that array is itself an array of values — and each one of those values is an integer, real number, string or null, depending on the sqlite3 type. Simple as that 🫶
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. Like, it got most of the way there and then tripped 😭 at least it tells you.
---
# 😭 ERRORS 😭
On failure, an error is returned. That's the whole vibe. No extra lore, no secret sauce — if it breaks, you get an error.
---
# 🫶 AUTHOR 🫶
`Rusty Russell` `rusty@rustcorp.com.au` is mainly responsible. Iconic behavior honestly 👑
---
# 👀 SEE ALSO 👀
- `lightning-listtransactions(7)`
- `lightning-listhtlcs(7)`
- `lightning-listinvoices(7)`
- `lightning-listoffers(7)`
- `lightning-listchannels(7)`
- `lightning-listpeers(7)`
- `lightning-listpeerchannels(7)`
- `lightning-listsendpays(7)`
- `lightning-listchainmoves(7)`
- `lightning-listchannelmoves(7)`
- `lightning-bkpr-listaccountevents(7)`
- `lightning-bkpr-listincome(7)`
- `lightning-listnodes(7)`
- `lightning-listforwards(7)`
That's a whole reading list bestie, go click through them 💅
---
# 🌈 RESOURCES 🌈
Main web site: `https://github.com/ElementsProject/lightning`
# ✨ EXAMPLES ✨
Okay bestie, these are the little real-world moments where the `sql` command absolutely eats. 💅 All the commands and JSON are copied exactly, so you can run them and see the magic yourself. ✨
### 🎀 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 🦋
Lowkey important: if your SQL has `=` in it, the shell is gonna be nosy, so use `-o`. 😤
**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: Getting specific `nodeid` values from the `nodes` table 🌸
You want certain nodeids? Say less. 👑
**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: Comparing a `BLOB` column — use `x'hex'` or `X'hex'` 🧚
If you're comparing a `BLOB` column, you literally need `x'hex'` or `X'hex'`. No cap. 💀
**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` 💖
This is giving “let’s bring the whole friend group together.” 🫶
**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 — `COUNT` says hi 🍓
Strings inside arrays need `"`, and `'` to protect them from the shell. Shell-proofing is self-care. ✨
**Request:**
$ lightning-cli sql "SELECT COUNT(*) FROM forwards"
{ "id": "example:sql#6", "method": "sql", "params": [ "SELECT COUNT(*) FROM forwards" ] }
**Response:**
{ "rows": [ [ 9 ] ] }
---
### 🌈 Example 7: `SELECT *` from `peerchannels_features` 🌈
Main character query tbh. 👑
**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" ], [ 30, 23, 2, "option_anchors" ] ] }
---
That’s the whole page, bestie. Cute little footer moment: source/version is `Core Lightning v26.06.9`, and the page tag is `LIGHTNING-SQL(7)`. Slay, bye. 💅🎀
📦 where this page comes from
- nixpkgs unstable: clightning 26.06.9
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash-1017 on : a language model's rewrite of the original, under its terms