Query Postgres database with RESTful API

Have you been in a situation that needs to access your Postgres data using API or automating your queries? Posting data to your database requires scripting friendly and robust method. Performing your query using only REST API URL and no SQL language involvement or experience needed, or building a workflow that quickly and professionally queries and posts to the database.

in summary the main reasons that make you use API to perform operations on the DB are:

• Query database to extract data in your browser, not sql client is needed.
• Automate or scripting your data using API RESTful requests.
• Isolate DB from direct queries. the DB connection is not exposed anywhere.

It is a good practive as well to isolate DB in private network or subnet and only respond to DB query using APIs and not expose your Database.

A PostgREST (or known as postgrest) is the answer, an undependant standalone web server that runs your PostgreSQL database directely into RESTful API. Using PostgREST is an alternative to manual CRUD programming. Custom API servers suffer problems. Writing business logic often duplicates, ignores or hobbles database structure. Object-relational mapping is a leaky abstraction leading to slow imperative code. The PostgREST philosophy establishes a single declarative source of truth: the data itself.

The disadvantages from using REST API are secuirty, You have to secure your api access and DB password inside docker compose YML file, which is beyond the scope of this post.

the following step is Optional, for pupose of this lab only, create a new postgres database and import dvdrentals data.

• Creation of Postgres using Docker

docker run -dp 5432:5432 -v Postgres-vol:/var/lib/pstgresql/data -e POSTGRES_PASSWORD=<password> postgres

OR

• Creation of Postgres docker Container using docker-compose.yml file

postgres:
    container_name: postgres
    build: ./postgres
    ports:
      - "5432:5432"
    volumes:
      - type: volume
        source: postgres-vol
        target: /var/lib/postgresql/data/
    environment:
      - POSTGRES_USER=<postgres_user>
      - POSTGRES_PASSWORD=<postgres_password>
      - POSTGRES_DB=dvdrental

volumes:
  postgres-vol: {}

bring up the postgres database

docker-compose up -d --build postgres

• Get Sample database data for DVD rental for data query demonstration

wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip

#transfer tar file to container
docker cp ./dvdrental postgres:/var/lib/postgresql/data

• Import dvdrental database and verify database and tables.

# Restore Sample dvdrental data to dvdrebtak database
docker exec -it postgres /bin/bash
root@05e318029806:/# pg_restore -U <Postgres_user> -d dvdrental /var/lib/postgresql/data/dvdrental.tar

# Verify Database
docker exec -it postgres psql -d sensordb -U <Postgres_user> -c "\l"

# Verify Database imported Tables
docker exec -it postgres psql -d dvdrental -U <Postgres_user> -c "\dt"

Installing PostgREST (Postgrest) using Docker:

sudo docker run --name postgrest -dp 5433:5432 \
                -e POSTGRES_PASSWORD=mypassword \
                Postgrest

Installation using Docker-Compose:

install docker compose and run docker-compose.yml file as below

postgrest:
    container_name: postgrest
    image: postgrest/postgrest
    ports:
      - "4000:3000"
    environment:
      # PGRST_DB_URI=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgrest-db:5432/${POSTGRES_DB}
      - PGRST_DB_URI=postgres://<user>:<pass>@postgrest:5432/dvdrental
      - PGRST_DB_ANON_ROLE=<user_role>

Start Postgrest container using

docker-compose -d --build up postgrest

-d for De-attach from container session (run in background)

Hint: After any modification in docker-compose file, restart postgrest container to take effect

docker-compose restart postgrest

Verify Installation:

• Point your browser to public/private IP where postgret is installed and use the same port

http://<Postgrest_IP_Address>:4000

Query Postgrest for actor table

#Browser
http://<public_ip>:4000/actor

#curl
curl localhost:4000/actor

• Few Examples on the complexity of query that can be executed

# Select Only first_name and Actor_id coloums
/actor?select=first_name,actor_id

# Search for Actor first_name (fts Full Text Search) is Bela
/actor?first_name=fts.Bela

# display first 10 actors
/actor?limit=10

# Search for Film that is greater than 100 min
/film?length=gt.100

# Search using multiple parameters using length and Rating of "R"
/film?length=gt.100&rating=eq.R

# Search for film with "Finding Anaconda" (Contain Space use %20)
/film?title=eq.Finding%20Anaconda

for more advanced queries check resources section

POST using Postgrest

in Posting or insert data to the database (adding new row) just point your api to table URL e.g. http://<Postgres_ip>:4000/actor and insert data into the payload of the POST request.

    {
		    "actor_id": 201,
		    "first_name": "Test_First_Name",
		    "last_name": "Test_Last_Name"
		}

the same for DELETE http method

Resources:

https://www.postgresqltutorial.com/postgresql-sample-database/

https://postgrest.org/en/v8.0/api.html

Tags: DB automationpostgresRESTfulTechnology