The PostgREST recommended in this issue is a standalone Web server that converts your PostgreSQL database directly to RESTful apis. Structural constraints and permissions in the database determine API endpoints and operations.

PostgREST performance

Three factors affecting speed. First, the server is written in Haskell, using a Warp HTTP server (aka a compiled language with lightweight threads). Next, it delegates as much computation as possible to the database, including

.

• Serialize JSON responses directly in SQL
• data verification
• Authorization
• Combine row counting and retrieval
• returning *

Finally, it makes efficient use of database

through the Hasql library

• Keep the database connection pool
• Using the PostgreSQL binary protocol
• Stateless to allow horizontal scaling

Table and view

All views and tables in the exposed mode can be accessed by the requested active database role. They are exposed to a deep layer of routes. For example, the entire contents of the table people are returned to

GET  HTTP / 1.1 / people < / span > < / code > < / pre >

No depth/nesting/route. Each route provides OPTIONS, GET, HEAD, POST, PATCH, and DELETE verbs, depending entirely on database permissions.

Horizontal filtering (line)

You can filter the resulting rows by adding criteria to the columns. For example, to return a person under 13:

GET /people? age=lt.13 HTTP/1.1

You can evaluate multiple conditions on a column by adding more query string parameters. For example, return students who are 18 or older :

GET /people? age=gte.18& student=is.true HTTP/1.1

For more complex filters, you must create a new view in the database, or use a stored procedure. For example, here’s a view that shows “Stories of the Day,” including possibly older fixed stories:

CREATE VIEW fresh_stories AS
SELECT *
FROM stories
WHERE pinned = true
OR published >  now() - interval '1  day'
ORDER BY pinned DESC, published DESC; 

This view will provide a new endpoint:

GET /fresh_stories HTTP/1.1

Logical operator

AND evaluates with multiple conditions on columns by default, but you can use them OR in combination with the or operator. For example, to return a person under 18 or over 21:

GET /people? Or = (age. Lt. 18, the age. The gt. 21) HTTP / 1.1 < / span > < / code > < / pre >

To negate any operator, you can precede it with notlike? a=not.eq.2 or? not.and=(a.gete.0, a.lete.100).

You can also apply complex logic to conditions:

GET /people? grade=gte.90& student=is.true& Or = age. Eq. (14, not. And (age. Gte. 11, the age, lte. 17)) < / span > HTTP / 1.1 < / code > < / pre >

< Full text search

The fts filter mentioned above has a number of options to support flexible text queries, that is, select common search versus phrase search and the language used for stem extraction. Suppose that tsearch is a table with columns of type tsvectormy_tsv. The following example illustrates these possibilities.

GET /tsearch? my_tsv=fts(french).amusant HTTP/1.1

GET /tsearch? my_tsv=plfts.The%20Fat%20Cats HTTP/1.1

GET /tsearch? my_tsv=not.phfts(english).The%20Fat%20Cats HTTP/1.1

GET /tsearch? my_tsv=not.wfts(french).amusant HTTP/1.1

Using websearch_to_tsquery requires at least version 11.0 of PostgreSQL and raises errors in earlier versions of the database.

Architecture structure

Mode isolation

A PostgREST instance exposes all tables, views, and stored procedures for a single PostgreSQL schema (namespace of database objects). This means that private data or implementation details can go into different private modes and not be visible to the HTTP client.

It is recommended that you do not expose tables on the API schema. Rather, it exposes views and stored procedures that isolate internal details from the outside world. This allows you to change the internal structure of the schema and maintain backward compatibility. It also makes your code easier to refactor and provides a natural way to do API versioning.

< Function

By default, when a function is created, the permission to execute it is not restricted by the role. Function access is PUBLIC – it can be performed by all roles (see the PostgreSQL Permissions page for more details). This is not ideal for API patterns. To disable this behavior, you can run the following SQL statement:

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON  FUNCTIONS FROM PUBLIC; 

This will change the permissions of all functions created in all modes in the future. There is currently no way to limit it to a single model. In our opinion, this is a good practice anyway.

After

, you need to explicitly grant EXECUTE permissions to the function:

GRANT EXECUTE ON FUNCTION  login TO anonymous;
GRANT EXECUTE ON  FUNCTION signup TO anonymous; 

You can also grant execution permission for all functions in the schema to a higher privileged role:

Security definer

function executes with the permissions of the user who called it. This means that the user must have all permissions to perform the actions performed by the procedure. If the function accesses a private database object, your API role will not be able to successfully execute the function.

Another option is to use options to define functions. The permissions are then checked only once, calling the permissions of the function, and the operations in the function will have the permissions of the user of the function itself.

-- login as a user wich has privileges on the  private schemas

-- create a sample function
create or replace  function login(email text,  pass text) returns jwt_token as $$
begin
-- access to a private schema called 'auth'
select auth.user_role(email, pass) into _role;
-- other operations
-- ... 
end;
$$ language plpgsql security definer; 

Installation

You can install PostgREST from the official Homebrew repo.

brew install postgrest

You can install PostgREST from nixpkgs.

nix-env -i haskellPackages.postgrest

Run PostgREST

If you download PostgREST from the publishing page, extract the zip file first to get the executable file.

# For UNIX platforms
tar Jxf postgrest-[version]-[platform].tar.xz

# On Windows you should unzip the file

Now you can run PostgREST–help using the flag to see the instructions:

# Running postgrest binary
./postgrest --help

# Running postgrest installed from a package manager
postgrest --help

# You should see a usage help message

The PostgREST server reads the configuration file as its only parameter:

postgrest /path/to/postgrest.conf

# You can also generate a sample config file with
# postgrest -e >  postgrest.conf
# You'll need to edit this file and remove the usage parts for postgrest to read  it

—END—

Open Source license: MIT license