REST API

Powered by PostgREST

Having a HTTP interface to your Postgres database significantly improves the rate at which you can develop applications. Sometimes developers build CRUD web servers from scratch, but for many use cases, you can simply use PostgREST which is also available on Tembo Cloud.

PostgREST is a well-designed and supported Postgres project built by an awesome group of engineers, and arguably is the fastest way to get a REST service in place for your Postgres database.

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The immediacy with which PostgREST enables the interaction with the database eliminates the need for a middle-layer application server.

By leveraging the underlying capabilities of PostgreSQL, PostgREST offers automatic API generation, allowing you to define the API’s structure using the database schema and immediately query it via the API endpoint.

Main Features:

Automatic API Generation: With just a database schema, PostgREST generates a comprehensive and secure REST API without requiring additional layers of code.
Role-Based Authentication: It integrates seamlessly with PostgreSQL’s role-based access control, ensuring security and allowing granular permissions for API consumers.
Real-time updates with WebSockets: PostgREST can push real-time updates to clients through WebSockets, enhancing responsiveness and interactivity.
Support for Stored Procedures: Custom business logic can be incorporated directly through PostgreSQL stored procedures, making it accessible via the API.

Enabling PostgREST on Tembo Cloud

Via UI

You can enable the PostgREST app on your Tembo Cloud instance by navigating to “Apps”, then “PostgREST”. Click “Activate” to enable PostgREST on your instance.

Via API

First, you will need to generate an API token so that you can communicate with your Tembo instance. Navigate to cloud.tembo.io/generate-jwt and follow the instructions to generate a token. Alternatively, you can follow the instructions here.

Set your Tembo token as an environment variable, along with your organization id and the Tembo instance id. Fetch the TEMBO_DATA_DOMAIN from the “Host” parameter of your Tembo instance.

export TEMBO_TOKEN=<your token>
export TEMBO_ORG=<your organization id>
export TEMBO_INST=<your instance id>
export TEMBO_DATA_DOMAIN=<you Tembo domain>

PostgREST comes pre-configured to run in Tembo Cloud. However, you can optionally configure PostgREST by setting any of the environment variables configurations supported by PostgREST.

Patch your existing Tembo instance using the Tembo Cloud Platform API to enable PostgREST. We’ll set the the configurations to None so that the defaults are assigned.

import requests

TEMBO_ORG = os.environ["TEMBO_ORG"]
TEMBO_INST = os.environ["TEMBO_INST"]
TEMBO_TOKEN = os.environ["TEMBO_TOKEN"]

resp = requests.patch(
    url=f"https://api.tembo.io/api/v1/orgs/{TEMBO_ORG}/instances/{TEMBO_INST}",
    headers={"Authorization": f"Bearer {TEMBO_TOKEN}"},
    json={
        "app_services": [
            {"restapi": None},  // default configuration
        ]
    }
)

curl -X PATCH \
  -H "Authorization: Bearer ${TEMBO_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"app_services": [{"restapi": null}]}' \
  "https://api.tembo.io/api/v1/orgs/${TEMBO_ORG}/instances/${TEMBO_INST}"

Using PostgREST

PostgREST is served within your data plane domain. This value is the same value as the host on your Tembo Postgres instance and can be found at cloud.tembo.io.

First, create a table in the database:

psql postgres://$yourUser:$yourPassword@${TEMBO_DATA_DOMAIN}:5432/postgres

CREATE TABLE products (
  uid int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  category text NOT NULL,
  unit_price numeric NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Read the OpenAPI schema

PostgREST auto-generates the OpenAPI schema for your tables. You can read this with simple GET requests to your database. We will filter the response to only show the schema for the products table in the examples below.

TEMBO_DATA_DOMAIN = os.environ["TEMBO_DATA_DOMAIN"]

resp = requests.get(
    url=f"https://{TEMBO_DATA_DOMAIN}/restapi/v1",
    headers={"Authorization": f"Bearer {TEMBO_TOKEN}"},
)
resp.json()['definitions']['products']

curl -X GET \
  -H "Authorization: Bearer ${TEMBO_TOKEN}" \
  -H "Content-Type: application/json" \
  https://${TEMBO_DATA_DOMAIN}/restapi/v1 | jq .definitions.products

The API response gives us the schema for our products table.

{
	"required": ["uid", "name", "category", "unit_price", "created_at"],
	"properties": {
		"uid": {
			"description": "Note:\nThis is a Primary Key.<pk/>",
			"format": "integer",
			"type": "integer"
		},
		"name": {
			"format": "text",
			"type": "string"
		},
		"category": {
			"format": "text",
			"type": "string"
		},
		"unit_price": {
			"format": "numeric",
			"type": "number"
		},
		"created_at": {
			"default": "now()",
			"format": "timestamp with time zone",
			"type": "string"
		}
	},
	"type": "object"
}

Insert two records into the products table

There are no products in the table since we just created it. Let’s use the REST API to insert two products into the table.

resp = requests.post(
    url=f"https://{TEMBO_DATA_DOMAIN}/restapi/v1/products",
    headers={"Authorization": f"Bearer {TEMBO_TOKEN}"},
    json=[
      {
        "name": "ballpoint-pen",
        "category": "office-supplies",
        "unit_price": 0.75,
      },
      {
        "name": "stapler",
        "category": "office-supplies",
        "unit_price": 2.5,
      },
    ]
)

curl -X POST \
  -H "Authorization: Bearer ${TEMBO_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '[{"name": "ballpoint-pen", "category": "office-supplies", "unit_price": 0.75}, {"name": "stapler", "category": "office-supplies", "unit_price": 2.5}]' \
  "https://${TEMBO_DATA_DOMAIN}/restapi/v1/products"

Read all records from the products

Now that we added some data, let’s use the REST API to select all records from the table.

resp = requests.get(
    url=f"https://{TEMBO_DATA_DOMAIN}/restapi/v1/products",
    headers={"Authorization": f"Bearer {TEMBO_TOKEN}"},
)
resp.json()

curl -X GET \
  -H "Authorization: Bearer ${TEMBO_TOKEN}" \
  -H "Content-Type: application/json" \
  https://${TEMBO_DATA_DOMAIN}/restapi/v1/products

We inserted two products and as expected, the API response gave us two records.

[
	{
		"uid": 1,
		"name": "ballpoint-pen",
		"category": "office-supplies",
		"unit_price": 0.75,
		"created_at": "2023-10-12T14:30:41.170505+00:00"
	},
	{
		"uid": 2,
		"name": "stapler",
		"category": "office-supplies",
		"unit_price": 2.5,
		"created_at": "2023-10-12T14:37:55.188744+00:00"
	}
]

Read all records where price is less than 2

We can also filter the records by adding query parameters to the URL. Let’s filter the records to only show products where the unit price is less than 2.

resp = requests.get(
    url=f"https://{TEMBO_DATA_DOMAIN}/restapi/v1/products",
    headers={"Authorization": f"Bearer {TEMBO_TOKEN}"},
    params={
        "unit_price": "lt.2.00"
    }
)
resp.json()

curl -X GET \
  -H "Authorization: Bearer ${TEMBO_TOKEN}" \
  -H "Content-Type: application/json" \
  https://${TEMBO_DATA_DOMAIN}/restapi/v1/products?unit_price=lt.2.00

As expected, there is just one product with a price less than 2.

[
	{
		"uid": 1,
		"name": "ballpoint-pen",
		"category": "office-supplies",
		"unit_price": 0.75,
		"created_at": "2023-10-12T14:30:41.170505+00:00"
	}
]

Advanced

PostgREST has many advanced features. Check out the PostgREST project documentation for more information, and give them a star!