Connecting over HTTPS

Connecting over HTTPS

New Developer Edition databases include an HTTPS API that allows you to securely query your database from any HTTP client. This functionality is available via an embedded pREST (opens in a new tab) server that runs alongside the database.

For Enterprise Edition accounts, reach out to your pgEdge solutions engineer to enable this feature.

Why HTTPS?

PostgreSQL is a fantastic database choice for almost any application, including many where there are limitations or restrictions on the compute and networking resources available. This HTTPS API supports querying from clients where raw TCP connections are not possible or where a PostgreSQL client library is unavailable. Most notably, some edge compute and serverless platforms are in this situation today.

Authentication (AuthN) and Authorization (AuthZ)

Connecting and querying over HTTPS builds on the same authn and authz mechanisms as standard PostgreSQL connections. This means that the same database roles and passwords are used when querying over HTTPS.

Currently, the HTTPS API only allows querying using the built-in app user. We recommend using the built-in admin user, or a separate administrator user that you create, to run migrations over a standard PostgreSQL TCP connection. Then, with the database schema in place, use the app user to query over HTTPS. As a result, all queries will be subject to the permissions assigned to the app user, which you can customize as needed.

Credentials may currently be passed via standard basic authentication headers. Support for additional authentication methods such as JWTs is planned. See below for specific authentication examples.

Database URLs

Each database in pgEdge has a unique URL that is used when connecting from any client, whether it be over a standard PostgreSQL connection or over HTTPS. Copy the URL from the database details page in the pgEdge UI to use with your client.

For example, connect to a database with a URL of random-name.a1.pgedge.io as follows.

Using PSQL:

PGPASSWORD=MYSECRETPASSWORD psql -U app -h random-name.a1.pgedge.io -d defaultdb

Using an HTTP client:

curl -u app:MYSECRETPASSWORD https://random-name.a1.pgedge.io/defaultdb/public

API Endpoints

The HTTPS API supports the following endpoints:

  • GET /show/:database/:schema/:table: Lists table structure.
  • GET /:database/:schema: Lists tables in a schema.
  • GET /:database/:schema/:table: Queries rows in a table.
  • POST /:database/:schema/:table: Insert a row into a table.
  • PUT /:database/:schema/:table?{field}={value}: Update matching row(s) in a table.
  • PATCH /:database/:schema/:table?{field}={value}: Update matching row(s) in a table.
  • DELETE /:database/:schema/:table?{field}={value}: Delete matching row(s) from a table.

For example, to query a public.users table in the defaultdb database, the request would look like this:

GET https://random-name.a1.pgedge.io/defaultdb/public/users

Query Parameters

Query parameters may be used to filter, paginate, and sort results.

Query StringDescription
{field}={value}Filter by a field name. Can be repeated.
_page={number}Paginate results.
_page_size={number}Set the number of results per page.
_select={field1},{field2}Select specific fields.
_count={field}Count per field. Can provide *.
_count_first=trueReturn count results as non-list.
_distinct=trueReturn distinct results.
_order={field1},{field2}Order by a field. Prefix the field with - for DESC order.
_groupby={field}Group by a field.

For more information on the supported query parameters, see the pREST documentation (opens in a new tab).

SQL Functions

Functions may be used to aggregate and group results.

NameUse in Request
SUMsum:field
AVGavg:field
MAXmax:field
MINmin:field
STDDEVstddev:field
VARIANCEvariance:field

Operators

Operators may be used to customize result filtering.

NameDescription
$eqMatches values exactly equal to the specified value.
$gtMatches values greater than the specified value.
$gteMatches values greater than or equal to the specified value.
$ltMatches values less than the specified value.
$lteMatches values less than or equal to the specified value.
$neMatches values not equal to the specified value.
$inMatches any values listed in the specified array.
$ninExcludes values listed in the specified array.
$nullMatches fields that are null.
$notnullMatches fields that are not null.
$trueMatches fields that are true.
$nottrueMatches fields that are not true (includes false and null).
$falseMatches fields that are false.
$notfalseMatches fields that are not false (includes true and null).
$likeMatches strings fully containing the specified pattern.
$ilikeCase-insensitively matches strings containing the specified pattern.
$nlikeExcludes strings fully containing the specified pattern.
$nilikeCase-insensitively excludes strings containing the specified pattern.
$ltreelancMatches if the left argument is an ancestor or the same as the right.
$ltreerdescMatches if the left argument is a descendant or the same as the right.
$ltreematchMatches ltree paths that meet the specified lquery conditions.
$ltreematchtxtMatches ltree paths against the specified textual query conditions.

Example Requests

In these examples, assume the Northwind Traders dataset is loaded onto a database defaultdb with the URL:

https://random-name.a1.pgedge.io

Query a Table

Query the first three rows from the categories table:

curl -u app:MYSECRETPASSWORD \
    "https://random-name.a1.pgedge.io/defaultdb/public/categories?_page=1&_page_size=3"
[
  {
    "picture": "\\x",
    "category_id": 1,
    "description": "Soft drinks, coffees, teas, beers, and ales",
    "category_name": "Beverages"
  },
  {
    "picture": "\\x",
    "category_id": 2,
    "description": "Sweet and savory sauces, relishes, spreads, and seasonings",
    "category_name": "Condiments"
  },
  {
    "picture": "\\x",
    "category_id": 3,
    "description": "Desserts, candies, and sweet breads",
    "category_name": "Confections"
  }
]

Count Rows

Count the number of rows in the categories table:

curl -u app:MYSECRETPASSWORD \
    "https://random-name.a1.pgedge.io/defaultdb/public/categories?_count=category_id&_count_first=true"
{
  "count": 8
}

Insert a Row

Insert a new row into the categories table:

curl -u app:MYSECRETPASSWORD \
    -X POST \
    "https://random-name.a1.pgedge.io/defaultdb/public/categories" \
    -d '{"category_id": 9, "category_name": "New Category"}'
{
  "category_id": 9,
  "category_name": "New Category",
  "description": null,
  "picture": null
}

Update a Row

Update the row with category_id equal to 1:

curl -u app:MYSECRETPASSWORD \
    -X PATCH \
    "https://random-name.a1.pgedge.io/defaultdb/public/categories?category_id=1" \
    -d '{"category_name": "New Beverages"}'
{
  "rows_affected": 1
}

Delete a Row

Delete the row with category_id equal to 1:

curl -u app:MYSECRETPASSWORD \
    -X DELETE \
    "https://random-name.a1.pgedge.io/defaultdb/public/categories?category_id=1"
{
  "rows_affected": 1
}

Group By

This query counts the number of products in each category:

curl -u app:MYSECRETPASSWORD \
    "https://random-name.a1.pgedge.io/defaultdb/public/products?_select=category_id&_count=product_id&_groupby=category_id"
[
  {
    "category_id": 1,
    "count": 12
  },
  {
    "category_id": 2,
    "count": 12
  },
  {
    "category_id": 3,
    "count": 13
  },
  {
    "category_id": 4,
    "count": 10
  },
  {
    "category_id": 5,
    "count": 7
  },
  {
    "category_id": 6,
    "count": 6
  },
  {
    "category_id": 7,
    "count": 5
  },
  {
    "category_id": 8,
    "count": 12
  }
]

Show Columns

Show the columns in the categories table:

curl -u app:MYSECRETPASSWORD \
    "https://random-name.a1.pgedge.io/show/defaultdb/public/categories"

Output:

[
  {
    "position": 1,
    "data_type": "smallint",
    "max_length": 16,
    "table_name": "categories",
    "column_name": "category_id",
    "is_nullable": "NO",
    "is_generated": "NEVER",
    "is_updatable": "YES",
    "table_schema": "public",
    "default_value": null
  },
  {
    "position": 2,
    "data_type": "character varying",
    "max_length": 15,
    "table_name": "categories",
    "column_name": "category_name",
    "is_nullable": "NO",
    "is_generated": "NEVER",
    "is_updatable": "YES",
    "table_schema": "public",
    "default_value": null
  },
  {
    "position": 3,
    "data_type": "text",
    "max_length": null,
    "table_name": "categories",
    "column_name": "description",
    "is_nullable": "YES",
    "is_generated": "NEVER",
    "is_updatable": "YES",
    "table_schema": "public",
    "default_value": null
  },
  {
    "position": 4,
    "data_type": "bytea",
    "max_length": null,
    "table_name": "categories",
    "column_name": "picture",
    "is_nullable": "YES",
    "is_generated": "NEVER",
    "is_updatable": "YES",
    "table_schema": "public",
    "default_value": null
  }
]

More Information

You can find more information about the pREST API in its documentation (opens in a new tab).