PostgREST Turns PostGRE RESTful
Written by Kay Ewbank   
Thursday, 21 November 2019

PostgREST,  a standalone web server that turns your PostgreSQL database directly into a RESTful API has been updated with improvements to full text search and support for embedding views.

PostgREST serves a fully RESTful API from any existing PostgreSQL database. The developers say it provides a cleaner, more standards-compliant, faster API than you are likely to write from scratch. and achieves sub-second response times for up to 2000 requests per second on Heroku free tier.

postgrest

One reason for the speed is that the server is written in Haskell using the Warp HTTP server, which is a compiled language with lightweight threads. PostgREST delegates as much calculation as possible to the database including serializing JSON responses directly in SQL, data validation, authorization, and combined row counting and retrieval.

It also uses the database efficiently with the Hasql library by keeping a pool of db connections, using the PostgreSQL binary protocol, and being stateless to allow horizontal scaling.

PostgREST handles authentication via JSON Web Tokens and delegates authorization to the role information defined in the database. This avoids problems of having multiple version of what roles can access what information. When dealing with the database the server assumes the identity of the currently authenticated user, and for the duration of the connection can't do anything the user themselves couldn't. Other forms of authentication can be built on top of the JWT primitive. Because PostgREST uses PostgreSQL you get row-level security. If you're running PostgREST against PostgreSQL 9.5, it supports true row-level security. Earlier versions simulate it with triggers and security-barrier views.

The developers say PostgREST offers a good alternative to manual CRUD programming, you can use PostgreSQL to join data and let its query planner figure out the details rather than to loop through rows yourself. They also say PostgREST offers leak-proof abstraction as there's no ORM involved. Creating new views happens in SQL with known performance implications.

postgrest

More Information 

PostgREST On GitHub

PostgREST Homepage

Related Articles

PostgreSQL 12 Released

The Enduring Influence Of Postgres

PostgreSQL Improves Declarative Partitioning 

PostgreSQL Adds Parallel Query Support

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Oracle SQL Developer for VS Code
26/02/2024

Oracle has recently introduced some goodies for developers working with VS Code. This is an extension that integrates  SQL Developer within VS Code.



Call For Code 2024 Focuses On Generative AI
01/03/2024

This year's Call for Code challenge has been launched with a theme of the use of generative AI technology for solutions that aim to improve equitable access to resources and opportunities for historic [ ... ]


More News

raspberry pi books

 

Comments




or email your comment to: comments@i-programmer.info

Last Updated ( Thursday, 21 November 2019 )