Skip to content

Instantly share code, notes, and snippets.

@steve-chavez
Created July 12, 2018 19:35
Show Gist options
  • Save steve-chavez/38118d6bb6e80d5edcade8867957d877 to your computer and use it in GitHub Desktop.
Save steve-chavez/38118d6bb6e80d5edcade8867957d877 to your computer and use it in GitHub Desktop.
Quickstart for PostgREST with TimescaleDB
version: '3'
services:
db:
image: timescale/timescaledb:latest-pg10
ports:
- "5432:5432"
environment:
POSTGRES_DB: devices_small
POSTGRES_USER: app_user
pgrest:
image: postgrest/postgrest
ports:
- "3000:3000"
links:
- db:db
environment:
PGRST_DB_URI: postgres://app_user@db:5432/devices_small
PGRST_DB_SCHEMA: public
PGRST_DB_ANON_ROLE: app_user #In production this role should not be the same as the one used for the connection
depends_on:
- db
## Using the devices data set from
## https://docs.timescale.com/v0.10/tutorials/other-sample-datasets
# Starting containers
docker-compose up -d
# Downloading and loading data and schema
wget -qO- https://timescaledata.blob.core.windows.net/datasets/devices_small.tar.gz | tar xvz
psql -U app_user -d devices_small -h localhost <<EOF
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
\i devices.sql
\COPY readings FROM devices_small_readings.csv CSV
\COPY device_info FROM devices_small_device_info.csv CSV
EOF
## Reload PostgREST whenever the exposed schema changes to avoid issues
docker kill --signal=HUP timescaledb_pgrest_1
## Running some example queries:
# SELECT
# time,
# device_id,
# battery_temperature
# FROM readings
# WHERE battery_status = 'charging'
# ORDER BY time DESC LIMIT 10;
#
# This can be expressed in PostgREST as:
curl -s "localhost:3000/readings?select=time,device_id,battery_temperature&battery_status=eq.charging&order=time.desc&limit=10"
# Result:
[{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000959","battery_temperature":101.9},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000943","battery_temperature":101.9},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000935","battery_temperature":97.9},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000926","battery_temperature":96.3},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000903","battery_temperature":96.7},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000848","battery_temperature":101.2},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000846","battery_temperature":100},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000838","battery_temperature":95.4},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000797","battery_temperature":100.5},
{"time":"2016-11-15T20:19:30+00:00","device_id":"demo000795","battery_temperature":96.8}]
## SELECT
## device_id,
## histogram(battery_level, 20, 60, 5)
## FROM readings
## GROUP BY device_id
## LIMIT 10;
##
## For using more expensive operations such as GROUP BY(see https://postgrest.org/en/v5.0/api.html#custom-queries)
## you can create a VIEW or a stored procedure(https://postgrest.org/en/v5.0/api.html#stored-procedures).
## We'll use a view here:
psql -U app_user -d devices_small -h localhost <<\EOF
CREATE VIEW readings_hist AS
SELECT
device_id,
histogram(battery_level, 20, 60, 5)
FROM readings
GROUP BY device_id;
EOF
docker kill --signal=HUP timescaledb_pgrest_1
curl -s "localhost:3000/readings_hist?limit=10"
# Result:
[{"device_id":"demo000000","histogram":[0,0,0,7,215,206,572]},
{"device_id":"demo000001","histogram":[0,12,173,112,99,145,459]},
{"device_id":"demo000002","histogram":[0,0,187,167,68,229,349]},
{"device_id":"demo000003","histogram":[197,209,127,221,106,112,28]},
{"device_id":"demo000004","histogram":[0,0,0,0,0,39,961]},
{"device_id":"demo000005","histogram":[12,225,171,122,233,80,157]},
{"device_id":"demo000006","histogram":[0,78,176,170,8,40,528]},
{"device_id":"demo000007","histogram":[0,0,0,126,239,245,390]},
{"device_id":"demo000008","histogram":[0,0,311,345,116,228,0]},
{"device_id":"demo000009","histogram":[295,92,105,50,8,8,442]}]
@yshashix
Copy link

@steve-chavez Is there any updated PostgREST with TimescaleDB implementation or methods?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment