RestQ

The simplest way to express data operations in a rest API.

Implemented using the combination of appropriate HTTP methods, url and csv for the data format.

Example: Querying a simple table person with filtering, grouping and paging.

GET /person?age=lt.42&(student=eq.true|gender=eq.'M')&group_by=sum(age),grade,gender&having=min(age)=gt.42&order_by=age.desc,height.asc&page=20&page_size=100

This can then be converted into a SQL query.

SELECT * FROM person
WHERE age < 42
    AND (student = true OR gender = 'M')
GROUP BY sum(age), grade, gender
HAVING min(age) > 42
ORDER BY age DESC, height ASC
LIMIT 100 OFFSET 1900 ROWS

The response body will contain a csv formatted data of the results from the query.

RestQ Syntax/Grammar:

create  = ["CREATE" | "PUT"], "/",  table, column_def_list, "\n", csv

select = "GET", "/", table, [join_table], column_list, [ "?", condition]

delete = "DELETE", table, [ "?", condition ]

update = ["UPDATE | "PATCH"] table, set_expr_list, [ "?", condition]

drop = ["DROP" | "DELETE"] "-", table

alter = ["ALTER" | "PATCH"] table, { drop_column | add_column | alter_column }

drop_column = "-", column

add_column = "+", column_def

alter_column = column, "=", column_def


column_def_list =  "{", { column_def }, "}"
        | "(", { column_def }, ")"

column_def = [ { column_attributes } ], column, [ "(" foreign ")" ], ":", data_type, [ "(" default_value ")" ]

column_attributes = primary | index | unique

primary = "*"

index = "@"

unique = "&"

data_type = "bool" | "s8" | "s16" | "s32" | "s64" | "u8" | "u16", etc

default_value  = value

value = number | string | bool ,..etc

column = string, ".", string
        | string

table = string

foreign = table

insert = table, column_list ,"\n", csv

column_list = "{", { column }, "}"


join_table = table, join_type, table

join_type = right_join | left_join | inner_join | full_join

right_join = "->"

left_join = "<-"

inner_join = "-><-"

full_join = "<-->"

condition = expr

expr =  column | value | binary_operation

binary_operation = expr, operator, expr

operator = "and" | "or" | "eq" | "gte" | "lte" ,..etc

Data types

• bool : boolean
• s8 : u8 that autoincrements
• s16 : u16 that autoincrements
• s32 : u32 that autoincrements, serial
• s64 : u64 that autoincrements, bigserial
• f32 : float 4 bytes
• f64 : float 8 bytes
• i8,i16,i32,i64 : signed integer
• u8,u16,u32,u64 : unsigned intergers
• text : utf8 string
• uuid : plain uuid, randomly generated when null
• uuid_rand : randomly generated uuid
• uuid_slug : create a new uuid and generate a url friend base64 string out of it.
• utc : timestamp with time zone in utc,
• url : url types
• json : json
• bytes : binary data

Creating a table and inserting records in one request.

PUT /+product{*product_id:s32,name:text,created_by(users):u32,created:utc,is_active:bool}
Content-Type: text/csv; charset=UTF-8

1,go pro,1,2019-10-31 11:59:59.872,,true
2,shovel,1,2019-11-01 07:30:00.462,,false

• http method is PUT
• url is a restq syntax.
• body is csv

The equivalent SQL:

CREATE TABLE product (
 product_id serial NOT NULL PRIMARY,
 name character varying NOT NULL,
 created_by integer NOT NULL REFERENCES users(user_id),
 created timestamp with time zone NOT NULL DEFAULT now(),
 is_active boolean NOT NULL

INSERT INTO product(product_id, name, created_by, is_active)
VALUES(
    (1,'go pro',1,2019-10-31 11:59:59.872,DEFAULT,true)
    (2,'shovel',1,2019-11-01 07:30:00.462,DEFAULT,false)
);

Show the table definition

HEAD /product

Show all tables

HEAD /

Querying the records

GET /product{product_id,name}?is_active=eq.true&order_by=created.desc

SELECT product_id,name FROM product WHERE is_active = true ORDER BY created DESC

Inserting records

POST /product{product_id,name,created_by,created,is_active}
1,go pro,1,2019-10-31 11:59:59.872,,true
2,shovel,1,2019-11-01 07:30:00.462,,false

INSERT INTO product(product_id, name, created_by, is_active)
VALUES(
    (1,'go pro',1,2019-10-31 11:59:59.872,true)
    (2,'shovel',1,2019-11-01 07:30:00.462,false)
);

Insert with query

POST /user{user_id,name,person_id(GET/person{id}?person.name=name)}
1,TOM JONES,,

INSERT INTO user(user_id, name, person_id)
VALUES(1, 'TOM JONES', (SELECT person.id FROM person WHERE person.name='TOM JONES'));

Updating records

PATCH /product{description="I'm the new description now"}?product_id=1

UPDATE product SET description = 'I\'m the new description now' WHERE product_id = 1;

Bulk updating records

2 versions of the same record is passed, first is the original, the next is the updated one

PATCH /product{*product_id,name}
1,go pro,1,go pro hero4
2,shovel,2,slightly used shovel

UPDATE product SET name = 'go pro hero4' WHERE id = 1;
UPDATE product SET name = 'slightly used shovel' WHERE id = 2'

Delete

DELETE /product?product_id=1

DELETE FROM product WHERE product_id = '1'

Delete multiple

DELETE /product{product_id}
1
2
3

DELETE FROM product WHERE product_id IN ('1','2','3')

Delete multiple, by name(no primary keys).

DELETE /product{name,is_active}
Go Pro,true
Shovel,true
Chair,true

DELETE FROM product WHERE name = 'Go Pro' AND is_active = 'true';
DELETE FROM product WHERE name = 'Shovel' AND is_active = 'true';
DELETE FROM product WHERE name  = 'Chair' AND is_active = 'true';

Delete all records of a table

DELETE /product

TRUNCATE product;

Complex select (with joins)

GET /product<-users{product.*,users.user_name}?product_id=1&is_active=true&created=gt.2019-11-05T08:45:03.432

SELECT product.*, users.user_name
    FROM product
        LEFT JOIN users ON product.created_by = users.user_id
    WHERE product_id = 1
        AND is_active = true
        AND created > '2019-11-05T08:45:03.432'

Join tables

Supported join types

• INNER JOIN table1-><-table2
• OUTER JOIN
  • LEFT JOIN table1<-table2
  • RIGHT JOIN table1->table2
  • FULL JOIN table1<-->table2

Prior crate and inspiration

• inquerest, in the works of porting to call this library.
• postgrest, restq differs syntax to postgrest, with focus on intuitive filter clause

Please support this project: