PostgREST Cheatsheet: JOIN Table Queries

PostgREST allows you to interact with PostgreSQL via a REST API. By using it, you can handle your database in a RESTful manner, and in some languages, you can interact with the database using built-in functions. Supabase, a serverless database platform that has been gaining attention recently, also utilizes PostgREST.

However, I couldn't find the syntax for JOINs when looking through the documentation, and since there were few Japanese articles available, I've compiled this reference guide.

Read

SELECT statements can be written as follows:

SELECT name, score FROM table_name WHERE id > 10 ORDER BY score DESC
// GET /table_name?select=name,score&id=gt.10&order=score.desc

Important Notes

• Regarding the SELECT part
  • SELECT * from table_name can be written as table_name?select=*.
  • Note that /table_name?id=gt.10 returns the same result as select=*.
• The following operators are available:
  • PostgREST Operators
  • AND/OR conditions can be written as and=(filter1,filter2,...) / or=(filter1, filter2,...). (Place conditional expressions like target=gt.10 inside the filter part)
• Other supported features (omitted here):
  • limit clause
  • array columns
  • virtual columns

How to use JOIN

In short, JOINs can be achieved using another_table_name(column). Below are the query and tables I created when I tested this.

GET /task?select=*,status_name(name),tag_name(name),task_deadline(dead_line)

This is a visualization of the tables. Please note that these were generated from MySQL using dbdiagram and have been slightly modified.

The exact definitions are as follows. (It's long, so I've put it in a toggle)

Table Definition

CREATE TABLE "tag_name" (
  "id" SERIAL PRIMARY KEY,
  "tag_name" varchar(64) UNIQUE NOT NULL
);

CREATE TABLE "status_name" (
  "id" SERIAL PRIMARY KEY,
  "name" varchar(64) UNIQUE NOT NULL
);

CREATE TABLE "task" (
  "id" SERIAL PRIMARY KEY,
  "name" varchar(256) UNIQUE NOT NULL,
  "description" varchar(1024) NOT NULL,
  "created_at" timestamp DEFAULT current_timestamp,
  "updated_at" timestamp DEFAULT current_timestamp,
  "status_id" int NOT NULL
);

CREATE TABLE "task_tag" (
  "task_id" int NOT NULL,
  "tag_id" int NOT NULL,
  PRIMARY KEY ("task_id", "tag_id")
);

CREATE TABLE "task_deadline" (
  "task_id" int PRIMARY KEY NOT NULL,
  "dead_line" date NOT NULL
);

CREATE INDEX "status_id_index" ON "task" ("status_id");

CREATE INDEX "task_id_index_on_task_tag" ON "task_tag" ("task_id");

CREATE INDEX "tag_id_index" ON "task_tag" ("tag_id");

CREATE INDEX "task_id_index_on_task_deadline" ON "task_deadline" ("task_id");

ALTER TABLE "task" ADD FOREIGN KEY ("status_id") REFERENCES "status_name" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "task_tag" ADD FOREIGN KEY ("task_id") REFERENCES "task" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "task_tag" ADD FOREIGN KEY ("tag_id") REFERENCES "tag_name" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "task_deadline" ADD FOREIGN KEY ("task_id") REFERENCES "task" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

Write

Write operations are described in JSON object format within the POST data.

POST /table_name HTTP/1.1

{ "id": "aiueo", "name": "akstn", ...}

Important Notes

• Regarding PUT
  • PUT is basically the same as POST.
  • When using PUT, please be careful to specify the PRIMARY KEY using the =eq. operator.
• Regarding DELETE
  • DELETE is also basically the same.
  • You can use Horizontal Filtering (Rows), such as WHERE clauses.
  • By adding Prefer: return=representation to the header, you can view the deleted data.

Bulk Insert

Bulk Insert can be performed using CSV in addition to handling arrays in JSON.

POST /people HTTP/1.1
Content-Type: text/csv

name,score
Taro,45
Jiro,30
Saburo,92

Finally

Did anyone notice why the title of this article included 🈴? That's right. Because it's a JOIN! (lol)

References

• Document
• GitHub
• Supabase