Query contracts using SQL

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 Dicker 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             | {}

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: