PostgreSQL interface

Propel supports the PostgreSQL protocol, which allows you to connect to Propel using PostgreSQL clients. Any business intelligence (BI) tool or application that supports PostgreSQL can also query Propel’s Serverless ClickHouse.

In a sense, Propel pretends to be a PostgreSQL instance, allowing you to connect a client application seamlessly as if it were PostgreSQL.

Use cases

1. Connect your application using a PostgreSQL client.
2. Connect business intelligence tools like Looker, Tableau, or Metabase.
3. Connect observability tools Grafana.
4. Connect ELT / ETL platforms like Fivetran or Airbyte to move data.

Connection details

To connect a client to the PostgreSQL Interface, you must create a Propel Application and give it the DATA_POOL_QUERY scope.

Once you've created the Application, you can use the connection details below to connect your client to Propel.

Detail	Value
Host	postgresql.us-east-2.propeldata.com
Port	5432
Database	propel
user	Your Propel Application ID
password	Your Propel Aplication client secret

Clients and drivers

Propel supports various clients and drivers for connecting to its PostgreSQL interface. This section provides details on how to set up and use some of the most common clients and drivers to interact with Propel's Serverless ClickHouse.

psql

The official PostgreSQL CLI

Postgres.js

A full-featured client for Node.js

Connecting with psql (PostgreSQL CLI)

To connect to Propel using the PostgreSQL Command Line Interface (CLI) tool, psql, follow the steps below. This section will guide you through the process of establishing a connection and executing basic queries. The -W flag will prompt you for your password.

psql -h postgres.us-east-2.propeldata.com \
  -p 5432 \
  -d propel \
  -U $PROPEL_APPLICATION_ID \
  -W

After a successful connection, you can make the query below.

propel=> SELECT * FROM "TacoSoft Demo Data" LIMIT 3;

Connecting with Postgres.js

Postgres.js is a full-featured PostgreSQL client for Node.js and Deno.

Install Postgres.js and related dependencies.

npm i postgres

Create a db.js file with the connection details.

// db.js
import postgres from 'postgres'

const sql = postgres({
  host: 'postgresql.us-east-2.propeldata.com',
  port: 5432,
  database: 'propel',
  username: process.env.APPLICATION_ID,
  password: process.env.APPLICATION_SECRET
})

export default sql

Create environment variables APPLICATION_ID and APPLICATION_SECRET with your actual credentials.

Use the connection to execute commands.

import sql from './db.js'

async function getTacoSoftDemoData(limit) {
  try {
    const result = await sql`
      SELECT * FROM "TacoSoft Demo Data" LIMIT ${limit}
    `
    console.log(result)
    return result
  } catch (error) {
    console.error('Error executing query:', error)
  } finally {
    sql.end()
  }
}

Limitations

• The PostgreSQL interface only supports SELECT queries.