How to query contracts and transactions using SQL

Query

How to query contracts that are in Active Contract Set (ACS)

To fetch contracts in the ACS use the active() table function [1] which is part of Participant Query Store’s (PQS) schema and can be used as any other PostgreSQL function.

select * from active();

----------------------+----------------------------------------------------------------------------------------------
template_fqn          | register:DA.Register:IssuerApproval
payload_type          | template
create_event_pk       | 124095
create_event_id       | #12202a593e23a993b63ac897d5e87b4a1ee087bb62cc32d6f5b32ea5001f3d2d40a1:5
created_at_ix         | 37524
created_at_offset     | 000000000000009343
archive_event_pk      |
archive_event_id      |
archived_at_ix        |
archived_at_offset    |
life_ix               | [37524,)
contract_id           | 0074486cb0469f59b0b5bda4b5794a68cd9350a677ad5e4363d0e4e69b0b925d77ca0212202e62fe6fd912dc18...
payload               | {"issue": {...}}
contract_key          |
metadata              |
created_effective_at  | 2025-05-19 03:34:10.929+00
archived_effective_at |
redaction_id          |
package_name          | register
package_version       | 0.0.0
package_id            | c9238339098524de2923b702aaf1ea3d832250f05b5930d2fa66c1308590505a
signatories           | {issuer-12::12209223396a4c57103512bcc3ff188549d14ecebe084b21f6508989c9caf403998b}
observers             | {}

How to query contracts that are in ACS by type

The previous example is very resource-consuming and unadvisable to use except on tiny datasets. A more typical (and efficient at the same time) use case is to limit the result set to a specific Daml template type. The reason for improved efficiency is PostgreSQL’s ability to prune unused partitions when planning the query execution. As long as there is no ambiguity in resolving the type, the following forms are equivalent.

select * from active('register:DA.Register:IssuerApproval');
select * from active('DA.Register:IssuerApproval');
select * from active('IssuerApproval');

How to query contracts that are in ACS by information in payload

To query contracts by business information in the payload, use JSONB operators [2] in the WHERE clause.

select *
from active('register:DA.Register:IssuerApproval')
where payload->'issue'->>'issuer' = 'foo';

select payload->'token'->'wallet'->>'label'
from active('TokenOpen')
where payload->'token'->'issue'->>'issuer' = 'foo'
  and (payload->'token'->>'quantity')::decimal > 42;

Note

See also How to add an index on expression over JSONB payload.

How to join contracts that are in ACS

To join contracts from multiple Daml template types use standard JOIN syntax.

select ia.contract_id, ip.contract_id
from active('IssuerApproval') ia
inner join active('IssuerProposal') ip
  on ia.payload->>'transferId' = ip.payload->>'transferId';

How to aggregate data from contract values

Since PQS is backed by PostgreSQL database, one can use aggregate functions available as part of Structured Query Language (SQL) syntax.

select payload->'token'->'issue'->>'issuer'          as issuer,
       sum((payload->'token'->>'quantity')::decimal) as total_quantity
from active('TokenOpen')
group by payload->'token'->'issue'->>'issuer';

How to query state in the past

PQS uses Event Sourcing [3] architecture and therefore users can peek into the state at an arbitrary point in ledger history by providing an offset explicitly.

select ia.contract_id, ip.contract_id
from active('IssuerApproval', 'some_offset') ia
inner join active('IssuerProposal', 'some_offset') ip
  on ia.payload->>'transferId' = ip.payload->>'transferId';

How to fetch a contract by its contract ID

Contracts may be fetched by their contract ID (regardless of their activeness state). Keep in mind, that interface views and contracts share the same contract ID so the function will return interface views as well.

select * from lookup_contract('contract_id');

How to get a list of contracts created in an offset range

To get a sorted list of “contract created” events within a specific offset range.

select *
from creates(from_offset := 'from', to_offset := 'to')
order by created_at_offset, create_event_pk;

Caution

This query is potentially resource-heavy in the presence of multiple template types and is a poor substitute for Ledger API transaction stream.

How to get a list of contracts created in an open offset range

By default, offset edges in creates() and archives() SQL functions represent closed ranges. To make either or both open, use WHERE clauses with desired refinements.

select *
from creates(from_offset := 'from', to_offset := 'to')
where created_at_offset > 'from';

How to emulate a stream of “contract created” events with SQL

Although PQS is a poor substitute for Ledger API stream of events, it is sometimes justifiable to get a stream of “contract created” events directly from a PQS database.

To emulate such a stream, one needs to implement client-side busy polling of the database using a similar query.

select *
from creates(from_offset := 'last_memoized_offset')
where created_at_offset > 'last_memoized_offset'
order by created_at_offset;

The client driver should execute this query in a loop, memoizing [4] the latest observed offset and passing it into the next iteration. It might be a good idea to add sleeping intervals in the loop when no results are returned to minimize resource wasting.

However, if there is a hard requirement on latency and scalability, the best approach is to source ledger events directly from the Ledger API. PQS is designed to be used best as a complementary source to Ledger API’s transaction stream to query ledger states at particular offsets, rather than provide event streams.

Optimize

PQS is backed by PostgreSQL database. Any query optimization is essentially a database/SQL tuning concern. The usual SQL development best practices apply here:

  • Apply indexes for frequently used clauses

  • Limit data fetching (remove unnecessary columns in final projection, avoid using SELECT *)

  • Design Daml models to be read-friendly

  • Avoid paginating with the help of OFFSET SQL clause

  • Avoid using queries similar to SELECT COUNT(*) if possible

For additional information concerning performance optimization, refer to Optimize.

How to add an index on expression over JSONB payload

To optimize read performance of queries that use WHERE clauses with information from payload add indexes on expression [5].

call create_index_for_contract(
  'token_wallet_holder_idx',
  'register:DA.Register:Token',
  '(payload->''wallet''->>''holder'')',
  'hash'
);

call create_index_for_contract(
  'token_open_wallet_qty_idx',
  'register:DA.Register:TokenOpen',
  '((payload->''wallet''->>''quantity'')::decimal)',
  'btree'
);

Provide full expressions used in WHERE clauses (including operators and casts).

How to limit data fetching

List only columns with data required and avoid using *.

Most Read API functions return an abundance of data in the result set, including contract’s liveness, divulgence and disclosure metadata. Fetching this data involves joining several internal tables. However, if metadata is not required, the query will cause unnecessary burden on PostgreSQL resources. On the other hand, the query planner in PostgreSQL is smart to prune automatically unnecessary joins [6].

Bad:

select * from active('Token');

Good:

select payload->'wallet'->>'holder' as holder,
       (payload->'wallet'->>'quantity')::decimal as quantity
from active('Token');

How to paginate efficiently

Make sure your paginated access [7] to data is efficient and does not cause performance degradation.

Bad:

select *
from the_source
order by the_key
limit page_size
offset (page_num * page_size);

Good:

select *
from the_source
where the_key > prev_page_last_key
order by the_key
limit page_size;