What is PgRest
[Warring] PgRest is still in a very early stage, anything described here could be changed.
To enable rest in PostgreSql.
- is a JSON document store.
- runing inside PostgreSql
- working with existing relational data
- capable of loading Node.js modules
- compatible with MongoLab’s REST API
- = LiveScript + Plv8 + Plv8x + Express
1 2 3 4 5 6 7
Supported features are:
- Functions can receive any arguments, and return a value or set of records.
- Execute any SQL commands with built-in plv8.execute() function.
- Records are converted to JSON which keys are column names.
- Other types are converted to strings.
- Partial support to write TRIGGER handlers.
- Inline commands with DO statement for PG 9.0 or newer versions.
- EXTENSION support for PG 9.1 or newer versions.
- Importing nodejs modules and creating user functions
- Calling conventions for user functions
Setup in debian/ubuntu
Add postgresql apt repository. You need to change
squeeze to your distribution codename. You can get your system codename by
Add apt repository key
Update and install packages
Update postgresql configuration
Edit /etc/postgresql/9.2/main/pg_hba.conf. Remove the line
local all postgres peer, and add the following lines.
1 2 3
Restart postgresql service
make sure you could access psql console.
Create test database
Create plv8 extension in the database
if there is no error in the response of curl, then your pgrest is all set.
How does PgRest work?
PgRest exposed all tables in schema
restful endpoints with a
For example, a table named
users will be mapped to
http://localhost:3000/collections/users endpoint and maps HTTP
Request methods to pgrest CURD(create, update, read, delete) functions
as the following.
- HTTP Get to
- HTTP POST to
- HTTP PUT to
- HTTP DELETE to
- pgrest_select - get an entity or entities.
- pgrest_insert - add an entity
- pgrest_upsert - update an entity or entities
- pgrest_delete - delete an entity
Example: insert a json to a existent table.
1 2 3 4 5 6 7 8 9 10 11
Thus, we could almost move all business logic to database level,
Yap, until now, I did not explain authorization and authentication in PgRest. Those are not current supported in PgRest tunk now, but I am going to explain how to implement it in next chapter.