- Setup a tutorial PostgreSQL database with docker:
sudo docker run --name pgdb -p 5433:5432 -e POSTGRES_PASSWORD=p -d postgresInstall PostgREST
- Download
tar.xzfile from: https://github.com/PostgREST/postgrest/releases/latest
tar xJf postgrest-<version>-<platform>.tar.xzLinux:
# un-compress into working directory
tar xJf bin/postgrest-v7.0.1-linux-x64-static.tar.xz
# run
./postgrest
Missing: FILENAME
Usage: postgrest FILENAME
PostgREST 7.0.1 (UNKNOWN) / create a REST API to an existing Postgres database
Available options:
-h,--help Show this help text
FILENAME Path to configuration file
Example Config File:
db-uri = "postgres://user:pass@localhost:5432/dbname"
db-schema = "public" # this schema gets added to the search_path of every request
db-anon-role = "postgres"
db-pool = 10
db-pool-timeout = 10
server-host = "!4"
server-port = 3000
## unix socket location
## if specified it takes precedence over server-port
# server-unix-socket = "/tmp/pgrst.sock"
## unix socket file mode
## when none is provided, 660 is applied by default
# server-unix-socket-mode = "660"
## base url for swagger output
# openapi-server-proxy-uri = ""
## choose a secret, JSON Web Key (or set) to enable JWT auth
## (use "@filename" to load from separate file)
# jwt-secret = "secret_with_at_least_32_characters"
# secret-is-base64 = false
# jwt-aud = "your_audience_claim"
## limit rows in response
# max-rows = 1000
## stored proc to exec immediately after auth
# pre-request = "stored_proc_name"
## jspath to the role claim key
# role-claim-key = ".role"
## extra schemas to add to the search_path of every request
# db-extra-search-path = "extensions, util"
## stored proc that overrides the root "/" spec
## it must be inside the db-schema
# root-spec = "stored_proc_name"
## content types to produce raw output
# raw-media-types="image/png, image/jpg"- libpq
- PostgrSQL C Library
# execute psql within docker
sudo docker exec -it pgdb psql -U postgres
# create api schema
postgres=# create schema api;
# create table for 'orders' endpoint:
postgres=# create table api.orders (
postgres(# id serial primary key,
postgres(# order_type text not null default 'new',
postgres(# order_status text not null default 'pending',
postgres(# price real not null default 0,
postgres(# datetime timestamptz not null default now()
postgres(# );
# add some values
insert into api.orders (order_type) values ('new'), ('swap');
# create nologin role
create role web_anon nologin;
grant usage on schema api to web_anon;
grant select on api.orders to web_anon;
The web_anon role has permission to access things in the api schema, and to read rows in the orders table.
It’s a good practice to create a dedicated role for connecting to the database, instead of using the highly privileged postgres role.
Name the role authenticator and also grant him the ability to switch to the web_anon role :
create role authenticator noinherit login password 'p';
grant web_anon to authenticator;
Now quit out of psql; it’s time to start the API!
\q
PostgREST uses a configuration file to tell it how to connect to the database. Create a file tutorial.conf with this inside:
db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schema = "api"
db-anon-role = "web_anon"
The configuration file has other options, but this is all we need. Now run the server:
./postgrest tutorial.conf
You should see
Listening on port 3000
Attempting to connect to the database...
Connection successful
It’s now ready to serve web requests. There are many nice graphical API exploration tools you can use, but for this tutorial we’ll use curl because it’s likely to be installed on your system already. Open a new terminal (leaving the one open that PostgREST is running inside). Try doing an HTTP request for the todos.
curl http://localhost:3000/orders
The API replies:
[
{
"id": 1,
"order_type": "new",
"order_status": "pending",
"price": 0,
"datetime": null
},
{
"id": 2,
"order_type": "swap",
"order_status": "pending",
"price": 0,
"datetime": null
}
]