SQL Overview

In Propel, you can query your data using SQL. This can be done either through the GraphQL SQL API or the PostgreSQL-compatible SQL interface. Both allow you to benefit from Propel's fast response times, high availability, and access controls, all while using familiar SQL syntax.

Propel's SQL supports PostgreSQL syntax, including joins, unions, and common table expressions for more complex queries. This enables any application or business intelligence (BI) tool that relies on PostgreSQL compatibility to connect seamlessly to Propel.

SQL API

The SQL API lets you execute SQL queries from your application via the GraphQL API. The SQL API is particularly useful for querying data from web and mobile apps to build dashboards and reports.

The example below demonstrates a simple SQL query to select ten records from a Data Pool using the SQL API. You can query Data Pools by unique name or ID.

GraphQL Query

query {
  sqlV1(input: { query: "SELECT * FROM \"TacoSoft Demo Data\" LIMIT 3" }) {
    columns {
      columnName
    }
    rows
  }
}

JSON Response

{
  "data": {
    "sqlV1": {
      "columns": [
        {
          "columnName": "order_item_id"
        },
        {
          "columnName": "quantity"
        },
        {
          "columnName": "taco_name"
        },
        {
          "columnName": "sauce_name"
        },
        {
          "columnName": "restaurant_id"
        },
        {
          "columnName": "restaurant_name"
        },
        {
          "columnName": "taco_total_price"
        },
        {
          "columnName": "tortilla_id"
        },
        {
          "columnName": "toppings"
        },
        {
          "columnName": "sauce_id"
        },
        {
          "columnName": "taco_unit_price"
        },
        {
          "columnName": "order_id"
        },
        {
          "columnName": "order_item_generated_at"
        },
        {
          "columnName": "taco_id"
        },
        {
          "columnName": "timestamp"
        },
        {
          "columnName": "tortilla_name"
        }
      ],
      "rows": [
        [
          "76f49551-86c8-43d0-8e47-592ba302c1f8",
          "2",
          "Veggie",
          "Habanero Hot Sauce",
          "605cf627-794e-47f6-9f23-19c8dda7c24a",
          "Los Compadres",
          "5.5",
          "fc7a6d71-2e74-4f2a-9d5d-b9c5390ada12",
          "[\"Guacamole\"]",
          "a37d3001-f953-47e3-b1ed-149f8897d094",
          "2.75",
          "097e58a1-bccc-4ffe-b2b5-5e81508243ce",
          "2024-01-01T00:01:08.537Z",
          "6e27b959-b7dc-4b70-89bc-0f4562028604",
          "2023-12-31T23:01:08.537Z",
          "Flour"
        ],
        [
          "d98e7e92-1d92-4516-a805-ac02a4ac27eb",
          "3",
          "Chorizo",
          "Queso Blanco",
          "605cf627-794e-47f6-9f23-19c8dda7c24a",
          "Los Compadres",
          "10.5",
          "c4f74e55-9162-487a-9825-19bf389f7b9e",
          "[\"Lime\",\"Guacamole\"]",
          "54ba79c8-786e-46d4-a583-8b51df6a06e2",
          "3.5",
          "097e58a1-bccc-4ffe-b2b5-5e81508243ce",
          "2024-01-01T00:01:08.537Z",
          "a7e7f311-018a-4393-9a65-90adfe851cc5",
          "2023-12-31T23:01:08.537Z",
          "Spinach"
        ],
        [
          "1f4dfa25-5c01-4075-9186-28c3fc627ca2",
          "3",
          "Carnitas",
          "Salsa Verde",
          "605cf627-794e-47f6-9f23-19c8dda7c24a",
          "Los Compadres",
          "9.75",
          "fc7a6d71-2e74-4f2a-9d5d-b9c5390ada12",
          "[\"Lime\",\"Guacamole\"]",
          "56b4b6f6-0c1d-4949-927a-8a0057943c6b",
          "3.25",
          "4f60a170-7d10-44b4-bec8-126b41bcc915",
          "2024-01-01T00:01:08.537Z",
          "33b36059-6428-4f30-88e8-d920ec4a4ea4",
          "2023-12-31T23:31:08.537Z",
          "Flour"
        ]
      ]
    }
  }
}

Usage

Arguments

The table below describes the input parameters for the SQL API. These parameters allow you to specify the SQL query.

Field	Type	Required	Description
query	String	Yes	The SQL query.

Read more about SqlV1Input.

Response

The SQL API responds with a SqlResponse object that includes a values array of headers and an array of arrays with the rows.

Field	Type	Nullable	Description
columns	array of SqlColumnResponse	No	The column names in the same order as present in the data field.
rows	array of String	No	The data gathered by the SQL query. The data is returned in an N x M matrix format, where the first dimension are the rows retrieved, and the second dimension are the columns. Each cell can be either a string or null, and the string can represent a number, text, date or boolean value.
info	QueryInfo	No	The Query statistics and metadata.

Read more about SqlResponse.

PostgreSQL-compatible SQL interface

Propel supports the PostgreSQL wire protocol, which allows you to use PostgreSQL clients to connect to Propel. Any business intelligence (BI) tool or application that supports PostgreSQL can also query Propel.

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

Connection details

To connect a client to the SQL Interface, you must create a Propel Application and give it the DATA_POOL_READ and DATAPOOL_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.