> ## Documentation Index
> Fetch the complete documentation index at: https://cantonfoundation-version-dashboard-update.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect and Query PQS

> Connect a PQS instance to a Canton ledger and a PostgreSQL database, then explore ledger data with 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](/appdev/reference/pqs-sql-reference)

## Prerequisites

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

* successful completion of the [Canton and the JSON Ledger API](/appdev/modules/m4-json-api-tutorial) tutorial

* a running Daml ledger with non-empty ACS

* `Docker` - installation instructions: [https://docs.docker.com/engine/install/](https://docs.docker.com/engine/install/)

* `Java 17` or later - installation instructions: [https://docs.oracle.com/en/java/javase/17/install/overview-jdk-installation.html](https://docs.oracle.com/en/java/javase/17/install/overview-jdk-installation.html)

* `scribe.jar` - download instructions: [Install and download](/sdks-tools/development-tools/pqs#install-and-download)

## 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`:

```bash theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```bash theme={"theme":{"light":"github-light","dark":"github-dark"}}
./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:

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```bash theme={"theme":{"light":"github-light","dark":"github-dark"}}
docker exec -it postgres-pqs psql -U postgres -d postgres
```

Let's explore the summary of current Active Contract Set (ACS):

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select * from summary_active();
```

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
     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:

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
postgres=# \x
Expanded display is on.
```

Now we can query the active contracts to see all the details along with the payload:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select * from active();
```

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
-[ 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:

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
postgres=# \x
Expanded display is off.
```

Let's now query the data with a more specific filter:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select payload->>'name' as name
  from active('json-tests:Main:Asset')
 where payload->>'owner' like 'Alice::%';
```

```text theme={"theme":{"light":"github-light","dark":"github-dark"}}
        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:

* [SQL API](/appdev/reference/pqs-sql-reference) - for more details on the SQL API provided by PQS

* [How to query contracts and transactions using SQL](/appdev/modules/m4-query-with-pqs) - for more examples of querying contracts and
  transactions using SQL