CCPEDIAby Unity Nodes
Documentation/Canton Network Docs/Module 4: Full-Stack Application DevelopmentView on Canton Network Docs ↗

Connect and Query PQS

This tutorial demonstrates how to query contracts and transactions using SQL in the Participant Query Store (PQS).

​
Overview

This tutorial shows how to:
  • connect a PQS instance to a Daml ledger
  • connect a PQS instance to a PostgreSQL database
  • query contracts that get exported with SQL API

​
Prerequisites

Before running this tutorial, ensure you meet the following prerequisites:

​
Existing canton

We assume that the referenced tutorial has been completed successfully and that you still have a running Canton instance with a Daml ledger and any created contracts.

​
Starting PostgreSQL

We will use official PostgreSQL Docker image to run a PostgreSQL instance. The following command will start a PostgreSQL instance with the default user postgres and password postgres: 
docker run --name postgres-pqs -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:latest
This command will run PostgreSQL in a detached mode, mapping the container’s port 5432 to the host’s port 5432.

​
Starting PQS

Now we can start the PQS instance. The following command will run PQS, connecting it to the ledger and the database we just started: 
./scribe.jar pipeline ledger postgres-document \
     --pipeline-ledger-start Oldest \
     --source-ledger-host localhost \
     --source-ledger-port 6865 \
     --target-postgres-host localhost \
     --target-postgres-port 5432 \
     --target-postgres-database postgres
We should observe similar lines in the logs output if all goes well: 
14:07:44.206 I [zio-fiber-1449251953] com.digitalasset.zio.daml.ledgerapi.UpdateService:176 Converting transaction 12209d1ef62499b808f9f4f346d562087017eda89e2d09e3b38fa1a721706cd18bf4 (offset: 20, events: 1, remote trace: 9642bc089b80e5bd933acedbae8bbae9)  trace_id=00000000000000000000000000000000 correlation_id=9642bc089b80e5bd933acedbae8bbae9 application=scribe
14:07:44.389 I [zio-fiber-120135998] com.digitalasset.scribe.postgres.document.DocumentPostgres.Service:370 Advanced watermark: ix = 1, offset = 20  trace_id=00000000000000000000000000000000 application=scribe
This indicates that the PQS instance is successfully connected to the ledger and the database.

​
Exploring data with SQL

Now that we have all components connected and operational, we can start querying the data using SQL. We will use the psql command-line tool (supplied by the official PostgreSQL Docker image) to connect to the PostgreSQL instance and run SQL queries. In a new terminal, run the following command to connect to the PostgreSQL instance: 
docker exec -it postgres-pqs psql -U postgres -d postgres
Let’s explore the summary of current Active Contract Set (ACS): 
select * from summary_active();

     template_fqn      | payload_type | count
-----------------------+--------------+-------
 json-tests:Main:Asset | template     |     1
We can turn psql into the extended mode to improve readability of the output: 
postgres=# \x
Expanded display is on.
Now we can query the active contracts to see all the details along with the payload: 
select * from active();

-[ RECORD 1 ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
template_fqn          | json-tests:Main:Asset
payload_type          | template
create_event_pk       | 2
create_event_id       | (20,0)
created_at_ix         | 1
created_at_offset     | 20
archive_event_pk      |
archive_event_id      |
archived_at_ix        |
archived_at_offset    |
life_ix               | [1,)
contract_id           | 007062361c67bfd1dc91765d53eb1295a184f666e55d1ba095138e02106dadb0a6ca111220440aed4933428c643da953dd6d4dcd40aca058ea8f718d40066e0eb8b459d2fe
payload               | {"name": "Example Asset Name", "owner": "Alice::1220a6e4a07775b13cdb65a1d05c9a3b5885ca44f11eb2f1b354e5fbd6457cf21bfe", "issuer": "Alice::1220a6e4a07775b13cdb65a1d05c9a3b5885ca44f11eb2f1b354e5fbd6457cf21bfe"}
contract_key          |
metadata              |
created_effective_at  | 2025-05-30 03:54:54.2+00
archived_effective_at |
redaction_id          |
package_name          | json-tests
package_version       | 0.0.1
package_id            | 77efba997825f9e0c091d97d0d9fbed83fc9107a8fa33f57c2448314556147e3
signatories           | {Alice::1220a6e4a07775b13cdb65a1d05c9a3b5885ca44f11eb2f1b354e5fbd6457cf21bfe}
observers             | {}
witnesses             | {}
divulged_only         | false
creation_package_id   | 77efba997825f9e0c091d97d0d9fbed83fc9107a8fa33f57c2448314556147e3
contract_key_hash     |
We can turn off the extended mode by re-running the command: 
postgres=# \x
Expanded display is off.
Let’s now query the data with a more specific filter: 
select payload->>'name' as name
  from active('json-tests:Main:Asset')
 where payload->>'owner' like 'Alice::%';

        name
--------------------
 Example Asset Name

​
Next steps

In this tutorial, we have successfully connected a PQS instance to a Daml ledger and a PostgreSQL database, and we have explored the ledger data using SQL. You can refer to the following resources for more information: