You can create a REST api using postgrest project.

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API.

I highly recommend reading their docs. Especially the first page where they explain the motivation behind this project.

Creating a postgres instance

sudo docker run --name pg_rest_tut -p 5433:5432 \
                -e POSTGRES_PASSWORD=notused \
                -d postgres

Install the postgrest

Follow this step

Create database for API

Access to database via psql

sudo docker exec -it pg_rest_tut psql -U postgres

Create a schema for the API. This is useful because we will have a strong distinction about which database objects that will be exposed to the API.

create schema api;

Create a table for todos under api schema.

create table api.todos (
  id int primary key generated by default as identity,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

Create a role for anonymous web requests. When a request comes in, PostgREST will switch into this role in the database to run queries.

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

The web_anon role has permission to access things in the api schema, and to read rows in the todos table.

It’s a good practice to create a dedicated role for connecting to the database, instead of using the highly privileged postgres role. So we’ll do that, name the role authenticator and also grant it the ability to switch to the web_anon role

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

Create postgrest config and run

Create a config file called myconfing.conf and put this inside:

db-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres"
db-schemas = "api"
db-anon-role = "web_anon"

Now you can run postgrest with the config

postgrest myconfig.conf

If you see the following line, you can hit the local todos server

# API server listening on 0.0.0.0:3000

As db-anon-role web_anon does only have read permissions, you shouldn't be able to post a new todo.

curl http://localhost:3000/todos -X POST \
     -H "Content-Type: application/json" \
     -d '{"task": "do bad thing"}'

# {"code":"42501","details":null,"hint":null,"message":"permission denied for table todos"}

End of day 7

Today we have learned how to create an API using postgrest over postgres database.

Tomorrow we will explore postgrest deeper.