We talk about a tool that allows you to configure the API to work with PostgreSQL queries. We are talking about the capabilities, advantages and disadvantages of the utility, as well as alternative solutions.
Photos - Campaign Creators - UnsplashWhat is PostgREST
This is an
open source web server for working with the PostgreSQL database by API. According to the authors, the solution is an alternative to manual
CRUD programming. With its help, you do not need to write business logic that complicates the structure of the database and engage in object-relational mapping (ORM mapping), which leads to the appearance of
imperative code .
PostgREST is written in Haskell, distributed
under a free license and has an extensive community - product support can be obtained in the
gitter chat . Today, the web server is actively
used in production for projects such as an analytical tool from Oracle - Moat, the e-commerce platform iAdvize and a system for creating charts using URL Image-charts.
Extensions for
OAuth ,
websocket and
nginx are also developed for PostgREST.
A bit about the possibilities
The system is capable of processing queries to all views and tables of the database schema. For example, to obtain data from the people table, it is enough to form the following command:
GET /people HTTP/1.1
In addition to
GET , among the keywords can be identified:
OPTIONS, POST, PATCH and
DELETE . All of them take into account access rights when working with data. A request to a table for which the user has insufficient privileges will be rejected. The official documentation
has a corresponding example . The database receives an API request to add a new task to the todo table:
curl http://localhost:3000/todos -X POST \ -H "Content-Type: application/json" \ -d '{"task": "do bad thing"}'
In response, PostgREST generates a failure:
{ "hint": null, "details": null, "code": "42501", "message": "permission denied for relation todos" }
The web server also supports stored procedures. They are written in
PL / pgSQL ,
PL / Python, or PLV8. Ready-made procedures can be called using
POST / rpc / procedure_name (when passing named parameters) or
GET / rpc / procedure_name (when working with
GET parameters).
What the community thinks about the tool
It is
believed that the PostgREST system is lightweight and undemanding to resources (with six instances, memory consumption rarely exceeds 70 MB, while for the Node.js / Waterline bundle this figure grows to one gigabyte). A similar point of view was expressed by a resident of Hacker News and
noted that PostgREST is the first tool on Haskell that he deployed in production.
Photo - Neringa Šidlauskaitė - UnsplashBut there are those in the community who criticize PostgREST. One user
noted that the Haskell code is
difficult to read , so debugging such libraries in a production environment can be difficult. At the same time, the tool,
in fact, transfers tasks for processing business logic from the backend to the database - this was done more than ten years ago. In this regard, there is an opinion that instead of PostgREST it is better to write constraints in plain SQL.
Another user with HN
says the web server is only suitable for deploying temporary and high-performance applications. In the long run, difficulties may arise with the database structure - the database schema becomes an API schema, and for it you need to build separate views.
What are the analogues
PostgREST is not the only web server that allows you to work with databases via API. For example, there is
Hasura , a lightweight GraphQL server that acts as an intermediary between a web application and PostgreSQL. The system will generate a GraphQL schema based on an existing database or create a new one. She is also friends with
GraphQL Subscriptions , provides dynamic control of access rights and automatic generation of queries for joining tables.
Today Hasura is used by many companies -
including those from the Fortune 500 list. And the system has integrations with the cloud platforms of the largest Western IaaS providers.
You can also mention
Prisma - the service also aims to replace the traditional ORM. But unlike PostgREST and Hasura, the solution works with several databases at once - PostgreSQL, MySQL and MongoDB. As a result, users get a flexible tool, but this
affects its functionality. For example, Prisma has fewer ways to manage business logic. But given that the tool is actively supported (it has
more than 16 thousand stars on GitHub), it’s worth waiting for improvements.
At 1cloud, we offer the
Cloud Object Storage service. It is suitable for backups, archival data, as well as the exchange of corporate documents.
The rental price
consists of two components: we take into account the amount of stored data and outgoing traffic.
Additional reading from the corporate blog 1cloud.ru:
How to save money using the application programming interface (API) How IaaS helps 1C franchisees: 1cloud experience How do application developers use a virtual container?