PgREST
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Kao, Chia-liang
PgREST turns PostgreSQL into a RESTful JSON document store, allows running JavaScript and npm modules as stored procedures for queries and triggers from within the database. It also provides Firebase-compatible API for developing real-time applications. PgREST is... a JSON document store running inside PostgreSQL working with existing relational data capable of loading Node.js modules compatible with MongoLab's REST API and Firebase's real-time API This talk will cover: the building blocks of PgREST: PostgreSQL, plv8js, plv8x examples for turning existing relational data into REST endpoints building real-time applications with PgREST
 Should we get started? Is this on? Hello. Good afternoon, everyone. Welcome to the Peachy Rest talk. So I'm CL from Taiwan, and I usually speak to very different crowd. Come from like a pro background, and I'm quite new to Postgres, and I've been doing open source stuff for about 15 years, and what we can see when I'm hospitalized, I still do software. And that's when I had a lot of free time, because no more outdoor activities. I start looking at Postgres, and I say, oh, this is really neat. Lots of crazy stuff. Not just like a database, you put things in and take it out. You can do a lot more than that. So right, I'm from Taiwan, so I'm from the future. And the last slide, the last talk from Andrew is about the future of Postgres days and stuff, right? So Peachy Rest is kind of like a futuristic experiment of that kind of things. So what Peachy Rest is essentially like a JSON document store, like the particular database that people have been bashing about in this conference. But it runs inside Postgres, and the interesting bit is that it works with your existing data. You can kind of turn your existing relational data into more like a document store. You kind of like try to do the joining and the things transparently. I'll show some examples later. And you can actually load Node.js modules, like the NPM modules, into it and turn whatever is on NPM or whatever your existing JavaScript code into store procedure inside Postgres and use those procedures as tools or transformation you need for producing your output. And MongoLab is a MongoDB hosting service. They have a REST API for assessing data that is hosted by them. And so we kind of steal the idea from there and then have a very similar and compatible API to that. And lastly, a couple of months ago, someone contributed a plugin for Peachy Rest that allows you to do like real-time subscription to the data update, similar to what Firebase is offering. Does anyone know Firebase? It's hosted back into the service thing and you have REST API and also WebSocket support has a nice JavaScript client API that you can do like subscribing changes to your data. So you can kind of do data binding in browser side and without a backend. So yeah, there's something similar to that. So to start using Postgres, it's kind of most of the logic is inside Postgres as a store procedure but you kind of still need a layer to launch it. And right now that layer is in Node.js. So you install the Peachy Rest from NPN. So just NPN install globally. And then you can now start Peachy Rest with your existing database and kind of run like turning, populating all your existing tables or relations or views into REST endpoint. So here's a sample for what we actually use in production is a Taiwanese parliament website. There's a Russian parliament site. Parliament site talk this morning. So this is not the official Taiwanese parliament. This is kind of the thing we recall from the unstructured government site and turning into a structured API and just being powered by Peachy Rest. So it's like this is a BIO document and then the BIO has a relation to motions where this BIO was being discussed on and in several occasions. So that kind of is being flattened by Peachy Rest when you define a view of what the BIO you want to expose as the API is where the relation from motions are being joined there but more details later. Anyway, so the database is people have been talking about in this conference about Node SQL or as you call post-modern database. And it's got very popular for the past few years because it's easy for people to just start using it. And I think it's a very constructive thing to like stealing from ideas from then because we are not like not only SQL, we got this all this improvement in Postgres as well. And so I'm going to just quickly go through some of the Postgres features that we are exploiting and then turn it into the useful components for Peachy Rest here. The first thing of course is the station data type. I don't need to describe it because everyone knows about it. It's very exciting and very promising and very fast in I4. And so like I say, I'm quite new to Postgres. So some of the features are like probably not very interesting to this crowd but I usually go through them to other people like, for example, schema in Postgres is like NAND space. So we can put view, sorry, I'm going to also have views being defined for predefined queries and then triggers for actions you can attach to operation or insert and things like that. So schema is like, well, you can put prefix like you collect a set of relations or views or functions into a group like what we use for extensions, you can put things into schemas. And views are like predefined queries. So we can put complicated queries and then with lots of joins or whatever into view which is turned into like essentially a query like how you access a table. So in 9.3 there is also materialized view. And so in Pgres the idea is that we map tables or relations into rest endpoint. And because for the past 10 years the web development people are solving is like turning relational data into some kind of a flattened representation. That's what you get from rest endpoint. And that's where you kind of need to decide if you want to join something or not. And so by providing the joined results in views then we can just map the relation into the rest endpoint. And so next is the triggers. And this is the building component for access control in PgRest. So basically you can put just hooks onto updates and insert and delete and things like that. So that's probably all the things you already know. And the exciting thing for the past few years is the JSON support after the JSON support is the POVA extension where you can start using JavaScript to write store procedure in Postgres. So the way you do it is you kind of create the function like that with a JavaScript body and then you can just use it as a store procedure by calling it as a normal function in Postgres. Well, but the thing is that when you have lots of this kind of store procedure written in JavaScript, it's really hard to maintain. So I created an extension called POVAX which is essentially a way for packaging MPM modules and then put it into your Postgres database. And beyond that, there's additional operator being provided by this extension is that the pipe, I'll call it pipe invoke extension, which takes the left hand side as a JSON document and evaluates that to the right hand side JavaScript text and put in the left hand side as this variable. So you can see that the expression here will actually get the full element and the first element which returns to. And you can do arbitrary expression like the plus here as well. And like in the last talk, there's a, we're talking about like there's no way to manipulate JSON efficiently. Well, I mean, this is not very efficient either anyway, but it's not making it possible. You can say update X and then set full where full is the JSON column and make the full, the new full being the full being processed with like the bar element deleted and then return itself. So the pipe, sorry, the pipe invoke operator is like applying your JSON column or document with the JavaScript text on the right hand side. But there's also a tilt invoke operator which runs the right hand side as in coffee script. So for those of you don't know coffee is more modern JavaScript which transcompiles with coffee script and it has a lot of handy shortcuts like the add operator is this. And so you can do that and also it has the implicit return for the expression functions. So that kind of turned it into a more readable form for applying the JavaScript on, applying the JSON document on left hand side with this expression in coffee script and return it. Okay. So that's the operator. But more importantly, what POVAX is capable of is like loading your MPM modules into Postgres. So for example, there's a MPM module called QS which handles the query per arm like the usual URL something equals something and something equals something form of the string. And this module provides the parsing bit of that. And then to load it into Postgres is that you say, okay, POVAX import QS which is the module name. And then you say minus F, this bit being the function in Postgres. So it's a function called parse underscore QS which takes a text and returns a JSON. There's a prefix for POVAX.JSON because we want to support it for 9.0 as well. So for 9.0, we create a domain called POVAX.JSON which is like the text with the validation rules. And then the right hand side you say you want that the parse underscore QS to be the JavaScript function that is in the QS package under the name parse. So you kind of like lift it from the QS package from MPM and find the parse function which is exported by the module and then turn that into a Postgres store procedure. And now you can use it as well, just a normal function in Postgres now parse underscore QS with a text and then it turns that into a JSON document. I'm not sure why you want to parse that kind of thing in Postgres though. But I actually found the use case for that. It's SkyTools PGQ, the Q extension from the Skype people. They actually store that kind of thing in Postgres. So if you need to process that in Postgres land, you will need that. So with that, we can now process the information in Postgres with anything written in JavaScript more easily. It's being bundled by MPM. And then with that, you know, we can do functional index and then now you can have like complicated calculation like written in properly packaged JavaScript package rather than long string of create function dollar dollar crazy things store procedures. So now you can do a lot more with that. So again, back to BGST. So running PGRES DB would just turn your existing database and the relations in there into Resend Point. And it looks something like this. Once you start running the default, but it's configurable is in your local host and then collection bills. And it gives you a Resend Point for just get and put and post and delete and things like that and it has this some paging default paging metadata which you can say you want to do like skipping to the second page or something like that. That's similar to the MongoLab recipe I described earlier. So that's example for the output from the get endpoint. But essentially the interesting bit is that you might think that this is like just like an ORM like turning database thing into JSON and then doing the joining bits in there. No, not really. So it is actually inside PG. So when we have this request, it's been just processed very with very minimal processing getting like to know what you're trying to do like you're getting the collection bills. And then that was turned into a store procedure call which says PG Res Select and just selecting the calendar. Oh, sorry. It was a bill. Sorry. That's wrong. And you can do this query things like this is from the MongoLab query API as well. So they have a very similar query string to Mongo. So that was also turned into part of the PG Res Select query parameters here. So this is like everything is done as in part of the store procedure is wrong. So right now the query you can use is with the query string similar to MongoDB. But seeing the JS query talk yesterday, I think maybe it makes more sense to have it like something like that here. But this is definitely like a pluggable, just a component for turning arbitrary structure into what is actually generating the query at the end. So for relational data, like the thing I showed you in the beginning is like turning like individual tables into rest endpoint is like trivial, right? Just serialize the season. But what about like something relational? You want to join something from other places? Sorry. And so the way to do it is actually create a view. And then we have a DSL for generating a view declaration. So this is like, for example, there's a calendar table. And then we want to join additional column called sitting, which is constructed from a function code, which is like loaded from the PLVAX. So you have this, that the function here is the underscore calendar sitting ID. And that's turning some structure into like a separate ID or something like that. A more comprehensive example here is like from another table, which is the sitting here is joining with the calendar. So it's not really joining because it's doing a subselect. And then with the condition for matching the related data there. So this is actually pulling out different free tables and then turning that into like the next structure you need for your rest endpoint. So this pretty much covers the read-only bits that you can turn your existing relational data into something structural and nested and flattened out thing. You define it by view. And the way we do is that you define those endpoints in those views in a particular schema. And with PGRS you can say, OK, this API is sharing every relation or every view under the schema. So you have your normal data, which is the normal relational one under public. And then what we do is we create additional layer of views which are like under PGRS. So as you can see here is a PGRS.sitting, which is constructed from the public sitting table. So the view is more of the decorated ones that you get from joining other data that you want to be represented in your rest endpoint. So this covers the read part. And then for the ACL, which is more interesting part, is that like for example in MongoLab, the hosted MongoDB services that I mentioned, you can, the good part of that is that it's a rest endpoint already. So you can have your web client just talk to the endpoint. But the problem is that the ACL is all or none. When you have an SS key, you can write to all the data. Otherwise, it's read only. So it's not very useful for that being a full-featured API endpoint per se, because you can have like, you have no SS control for the data in it. So the way that we see that this will work is, for example, because we have this update rules or triggers and you can say, do instead something. So when we create a view, which is actually being served as the read only API endpoint, you can optionally create rules when this being updated on or inserted on or deleted on and then do additional ACL checks here. So a more practical example here is when you have your API endpoint being accessed with an SS token like the nowadays NLP, but everyone does the OOS bearer. So this is the thin layer of the server. When that is also extracted, when you have an authorization header, you turn that into part of the command, part of the parameter into the PGRES select or PGRES update calls. So from there, you get those additional metadata into the PGRES param field. And then in your rules that's doing the instead ACL check, you can use the PGRES parameter to extract that. And as you can mention, probably you'll be doing the token validation or populating the user from the token, populating the group ownership from the token, and then do the actual check there. And then if it doesn't work, I mean, if it's forbidden, you just throw exception here, otherwise you do the original update. So that's the infrastructure. But there should be a library for making all these kind of things a lot easier. And so, but it's not there yet. But the infrastructure there is there. And then, okay, so that's the SS control bit and this is how we imagine it will work. There are actual working examples for that already, but it's in something not in the public code yet. So next up is the real-time API for it is part of the PGRES WebSocket plugin. And once you install it, it will create triggers on the tables that you're interested in. And now you can, this is similar to the Firebase API, but you can just say, okay, I want to start listening to this endpoint. And so this is from the links collection. And every time it's updated, the code will be called. So you have kind of like a transparent way of processing the updates from the client side. Okay. So that's what we've been doing for about a year or so. And because this is like a hobby project and then several people are working on that. And so it's not like, it is, well, it's not super actively being developed on, but there are actual production use of it. And the read-only part is quite robust. But the part requiring ACL control and the stuff is, needs a lot more work. And then appreciate your comments and how you feel it's usable or crazy or something like that. So what's next will be lots more documents. And the simplified authentication authorization integration I mentioned. There's already a plugin for integrating third party authentication like Facebook, Twitter, just normal OS thing. And with that plugin, you get the SS token as I described that should be used along with the ACL. But there should be a lot more simplified libraries around that to do more fine-grained control and stuff like that. And also, like the way we envision this to work is like, now there's only very tiny same layer of server for translating your request into the pgrest underscore select or update calls, right? So that can actually go away with the gentleman yesterday mentioned in the lightning talk. There's like HTTP protocol layer and he has a prototype of it. So maybe that, when we hook up that into the store procedure there, we'll have a serverless environment just a little bit speaking HTTP. Or there's a engine X plugin, which is called engine X Postgres, which also does the translation from HTTP request into Postgres queries. So we can map that into the store procedure call, which is a simple mapping because you have the URL, you have the HTTP verb methods, and you turn that into the corresponding store procedure call in Postgres. And then, yeah, we get like serverless thing, right? No server side logic. And there will be just one thing to scale. And so the real time support that I mentioned in the last slides is very exciting to a lot of people. And I know some people are trying to build like wrapper libraries around that so you can get just pgrest endpoint up and running and then you have just the data updated from the client side so you don't really see anything on the server side. That's already mentioned. So the engine X Postgres plugin is already there. And then I actually tried it already with the binding. And there's some weird problem with like when you have two queries or something, but it's basically working. It's quite amazing because with that, I actually have the engine X talking to pgrest directly. So there's no more server code. There's no more Node.js or Python layer of that. The thing that connects to Postgres, it's done in engines. So I think this is quite new a thing and then lots of crazy ideas in it exploiting some of the greatest Postgres features that I mean out of like from my background, it's like an application developer, a lot of the crazy Postgres features I did not know about. And then so once I know that, okay, I can combine and try to do something with that. And then this is the experiment and this is the stop we've been building. So that's pretty much my talk. End point for the Congress website read down in Taiwan. So we have, so by the way, I run a project called got zero dot TW. So it's like the government in Taiwan's got dot TW, right? So we built something called got zero. So like for the government website, you change one letter and suddenly becomes a better website with proper API and stuff like that. Okay, so that's the Congress website for Taiwan down right. So this is the actual Congress field and we have it represented in nice format. And so that's client only application in Angular. So the raw data is from Jason, which is powered by PGRest. And this is actually joined from several tables, I think. So we have that, sorry, you probably can't read anything. We have the, like this bill has been like gone through several process in different communities or whole Congress sitting or something like that. And the amendment it contains is regarding two chunks of the laws and things like that. So this is actually, I think, joined from five different tables because we have the Congress website, we have the Congress library website and other things. And then kind of cross-referencing all together created a huge view on that and then turn that view into API endpoint just with that. So there's no actual server code for that. Basically what the view describes is the API endpoint.
 Should we get started?  Is this on?  Hello.  Good afternoon, everyone.  Welcome to the Peachy Rest talk.  So I'm CL from Taiwan, and I usually speak to very different crowd.  Come from like a pro background, and I'm quite new to Postgres, and I've been doing open  source stuff for about 15 years, and what we can see when I'm hospitalized, I still  do software.  And that's when I had a lot of free time, because no more outdoor activities.  I start looking at Postgres, and I say, oh, this is really neat.  Lots of crazy stuff.  Not just like a database, you put things in and take it out.  You can do a lot more than that.  So right, I'm from Taiwan, so I'm from the future.  And the last slide, the last talk from Andrew is about the future of Postgres days and stuff,  right?  So Peachy Rest is kind of like a futuristic experiment of that kind of things.  So what Peachy Rest is essentially like a JSON document store, like the particular database  that people have been bashing about in this conference.  But it runs inside Postgres, and the interesting bit is that it works with your existing data.  You can kind of turn your existing relational data into more like a document store.  You kind of like try to do the joining and the things transparently.  I'll show some examples later.  And you can actually load Node.js modules, like the NPM modules, into it and turn whatever  is on NPM or whatever your existing JavaScript code into store procedure inside Postgres  and use those procedures as tools or transformation you need for producing your output.  And MongoLab is a MongoDB hosting service.  They have a REST API for assessing data that is hosted by them.  And so we kind of steal the idea from there and then have a very similar and compatible  API to that.  And lastly, a couple of months ago, someone contributed a plugin for Peachy Rest that  allows you to do like real-time subscription to the data update, similar to what Firebase  is offering.  Does anyone know Firebase?  It's hosted back into the service thing and you have REST API and also WebSocket support  has a nice JavaScript client API that you can do like subscribing changes to your data.  So you can kind of do data binding in browser side and without a backend.  So yeah, there's something similar to that.  So to start using Postgres, it's kind of most of the logic is inside Postgres as a store  procedure but you kind of still need a layer to launch it.  And right now that layer is in Node.js.  So you install the Peachy Rest from NPN.  So just NPN install globally.  And then you can now start Peachy Rest with your existing database and kind of run like  turning, populating all your existing tables or relations or views into REST endpoint.  So here's a sample for what we actually use in production is a Taiwanese parliament website.  There's a Russian parliament site.  Parliament site talk this morning.  So this is not the official Taiwanese parliament.  This is kind of the thing we recall from the unstructured government site and turning into  a structured API and just being powered by Peachy Rest.  So it's like this is a BIO document and then the BIO has a relation to motions where this  BIO was being discussed on and in several occasions.  So that kind of is being flattened by Peachy Rest when you define a view of what the BIO  you want to expose as the API is where the relation from motions are being joined there  but more details later.  Anyway, so the database is people have been talking about in this conference about Node  SQL or as you call post-modern database.  And it's got very popular for the past few years because it's easy for people to just  start using it.  And I think it's a very constructive thing to like stealing from ideas from then because  we are not like not only SQL, we got this all this improvement in Postgres as well.  And so I'm going to just quickly go through some of the Postgres features that we are  exploiting and then turn it into the useful components for Peachy Rest here.  The first thing of course is the station data type.  I don't need to describe it because everyone knows about it.  It's very exciting and very promising and very fast in I4.  And so like I say, I'm quite new to Postgres.  So some of the features are like probably not very interesting to this crowd but I usually  go through them to other people like, for example, schema in Postgres is like NAND space.  So we can put view, sorry, I'm going to also have views being defined for predefined queries  and then triggers for actions you can attach to operation or insert and things like that.  So schema is like, well, you can put prefix like you collect a set of relations or views  or functions into a group like what we use for extensions, you can put things into schemas.  And views are like predefined queries.  So we can put complicated queries and then with lots of joins or whatever into view which  is turned into like essentially a query like how you access a table.  So in 9.3 there is also materialized view.  And so in Pgres the idea is that we map tables or relations into rest endpoint.  And because for the past 10 years the web development people are solving is like turning  relational data into some kind of a flattened representation.  That's what you get from rest endpoint.  And that's where you kind of need to decide if you want to join something or not.  And so by providing the joined results in views then we can just map the relation into  the rest endpoint.  And so next is the triggers.  And this is the building component for access control in PgRest.  So basically you can put just hooks onto updates and insert and delete and things like that.  So that's probably all the things you already know.  And the exciting thing for the past few years is the JSON support after the JSON support  is the POVA extension where you can start using JavaScript to write store procedure in Postgres.  So the way you do it is you kind of create the function like that with a JavaScript body  and then you can just use it as a store procedure by calling it as a normal function in Postgres.  Well, but the thing is that when you have lots of this kind of store procedure written  in JavaScript, it's really hard to maintain.  So I created an extension called POVAX which is essentially a way for packaging MPM modules  and then put it into your Postgres database.  And beyond that, there's additional operator being provided by this extension is that the  pipe, I'll call it pipe invoke extension, which takes the left hand side as a JSON document  and evaluates that to the right hand side JavaScript text and put in the left hand side  as this variable.  So you can see that the expression here will actually get the full element and the first  element which returns to.  And you can do arbitrary expression like the plus here as well.  And like in the last talk, there's a, we're talking about like there's no way to manipulate  JSON efficiently.  Well, I mean, this is not very efficient either anyway, but it's not making it possible.  You can say update X and then set full where full is the JSON column and make the full,  the new full being the full being processed with like the bar element deleted and then  return itself.  So the pipe, sorry, the pipe invoke operator is like applying your JSON column or document  with the JavaScript text on the right hand side.  But there's also a tilt invoke operator which runs the right hand side as in coffee script.  So for those of you don't know coffee is more modern JavaScript which transcompiles  with coffee script and it has a lot of handy shortcuts like the add operator is this.  And so you can do that and also it has the implicit return for the expression functions.  So that kind of turned it into a more readable form for applying the JavaScript on, applying  the JSON document on left hand side with this expression in coffee script and return  it.  Okay.  So that's the operator.  But more importantly, what POVAX is capable of is like loading your MPM modules into Postgres.  So for example, there's a MPM module called QS which handles the query per arm like the  usual URL something equals something and something equals something form of the string.  And this module provides the parsing bit of that.  And then to load it into Postgres is that you say, okay, POVAX import QS which is the  module name.  And then you say minus F, this bit being the function in Postgres.  So it's a function called parse underscore QS which takes a text and returns a JSON.  There's a prefix for POVAX.JSON because we want to support it for 9.0 as well.  So for 9.0, we create a domain called POVAX.JSON which is like the text with the validation  rules.  And then the right hand side you say you want that the parse underscore QS to be the JavaScript  function that is in the QS package under the name parse.  So you kind of like lift it from the QS package from MPM and find the parse function which  is exported by the module and then turn that into a Postgres store procedure.  And now you can use it as well, just a normal function in Postgres now parse underscore QS  with a text and then it turns that into a JSON document.  I'm not sure why you want to parse that kind of thing in Postgres though.  But I actually found the use case for that.  It's SkyTools PGQ, the Q extension from the Skype people.  They actually store that kind of thing in Postgres.  So if you need to process that in Postgres land, you will need that.  So with that, we can now process the information in Postgres with anything written in JavaScript  more easily.  It's being bundled by MPM.  And then with that, you know, we can do functional index and then now you can have like complicated  calculation like written in properly packaged JavaScript package rather than long string  of create function dollar dollar crazy things store procedures.  So now you can do a lot more with that.  So again, back to BGST.  So running PGRES DB would just turn your existing database and the relations in there into Resend  Point.  And it looks something like this.  Once you start running the default, but it's configurable is in your local host and then  collection bills.  And it gives you a Resend Point for just get and put and post and delete and things like  that and it has this some paging default paging metadata which you can say you want to do  like skipping to the second page or something like that.  That's similar to the MongoLab recipe I described earlier.  So that's example for the output from the get endpoint.  But essentially the interesting bit is that you might think that this is like just like  an ORM like turning database thing into JSON and then doing the joining bits in there.  No, not really.  So it is actually inside PG.  So when we have this request, it's been just processed very with very minimal processing  getting like to know what you're trying to do like you're getting the collection bills.  And then that was turned into a store procedure call which says PG Res Select and just selecting  the calendar.  Oh, sorry.  It was a bill.  Sorry.  That's wrong.  And you can do this query things like this is from the MongoLab query API as well.  So they have a very similar query string to Mongo.  So that was also turned into part of the PG Res Select query parameters here.  So this is like everything is done as in part of the store procedure is wrong.  So right now the query you can use is with the query string similar to MongoDB.  But seeing the JS query talk yesterday, I think maybe it makes more sense to have it  like something like that here.  But this is definitely like a pluggable, just a component for turning arbitrary structure  into what is actually generating the query at the end.  So for relational data, like the thing I showed you in the beginning is like turning like  individual tables into rest endpoint is like trivial, right?  Just serialize the season.  But what about like something relational?  You want to join something from other places?  Sorry.  And so the way to do it is actually create a view.  And then we have a DSL for generating a view declaration.  So this is like, for example, there's a calendar table.  And then we want to join additional column called sitting, which is constructed from  a function code, which is like loaded from the PLVAX.  So you have this, that the function here is the underscore calendar sitting ID.  And that's turning some structure into like a separate ID or something like that.  A more comprehensive example here is like from another table, which is the sitting here  is joining with the calendar.  So it's not really joining because it's doing a subselect.  And then with the condition for matching the related data there.  So this is actually pulling out different free tables and then turning that into like  the next structure you need for your rest endpoint.  So this pretty much covers the read-only bits that you can turn your existing relational  data into something structural and nested and flattened out thing.  You define it by view.  And the way we do is that you define those endpoints in those views in a particular schema.  And with PGRS you can say, OK, this API is sharing every relation or every view under  the schema.  So you have your normal data, which is the normal relational one under public.  And then what we do is we create additional layer of views which are like under PGRS.  So as you can see here is a PGRS.sitting, which is constructed from the public sitting  table.  So the view is more of the decorated ones that you get from joining other data that  you want to be represented in your rest endpoint.  So this covers the read part.  And then for the ACL, which is more interesting part, is that like for example in MongoLab,  the hosted MongoDB services that I mentioned, you can, the good part of that is that it's  a rest endpoint already.  So you can have your web client just talk to the endpoint.  But the problem is that the ACL is all or none.  When you have an SS key, you can write to all the data.  Otherwise, it's read only.  So it's not very useful for that being a full-featured API endpoint per se, because you can have  like, you have no SS control for the data in it.  So the way that we see that this will work is, for example, because we have this update  rules or triggers and you can say, do instead something.  So when we create a view, which is actually being served as the read only API endpoint,  you can optionally create rules when this being updated on or inserted on or deleted  on and then do additional ACL checks here.  So a more practical example here is when you have your API endpoint being accessed with  an SS token like the nowadays NLP, but everyone does the OOS bearer.  So this is the thin layer of the server.  When that is also extracted, when you have an authorization header, you turn that into  part of the command, part of the parameter into the PGRES select or PGRES update calls.  So from there, you get those additional metadata into the PGRES param field.  And then in your rules that's doing the instead ACL check, you can use the PGRES parameter  to extract that.  And as you can mention, probably you'll be doing the token validation or populating the  user from the token, populating the group ownership from the token, and then do the actual check  there.  And then if it doesn't work, I mean, if it's forbidden, you just throw exception here,  otherwise you do the original update.  So that's the infrastructure.  But there should be a library for making all these kind of things a lot easier.  And so, but it's not there yet.  But the infrastructure there is there.  And then, okay, so that's the SS control bit and this is how we imagine it will work.  There are actual working examples for that already, but it's in something not in the  public code yet.  So next up is the real-time API for it is part of the PGRES WebSocket plugin.  And once you install it, it will create triggers on the tables that you're interested in.  And now you can, this is similar to the Firebase API, but you can just say, okay, I want to  start listening to this endpoint.  And so this is from the links collection.  And every time it's updated, the code will be called.  So you have kind of like a transparent way of processing the updates from the client  side.  Okay.  So that's what we've been doing for about a year or so.  And because this is like a hobby project and then several people are working on that.  And so it's not like, it is, well, it's not super actively being developed on, but there  are actual production use of it.  And the read-only part is quite robust.  But the part requiring ACL control and the stuff is, needs a lot more work.  And then appreciate your comments and how you feel it's usable or crazy or something  like that.  So what's next will be lots more documents.  And the simplified authentication authorization integration I mentioned.  There's already a plugin for integrating third party authentication like Facebook, Twitter,  just normal OS thing.  And with that plugin, you get the SS token as I described that should be used along with  the ACL.  But there should be a lot more simplified libraries around that to do more fine-grained  control and stuff like that.  And also, like the way we envision this to work is like, now there's only very tiny  same layer of server for translating your request into the pgrest underscore select  or update calls, right?  So that can actually go away with the gentleman yesterday mentioned in the lightning talk.  There's like HTTP protocol layer and he has a prototype of it.  So maybe that, when we hook up that into the store procedure there, we'll have a serverless  environment just a little bit speaking HTTP.  Or there's a engine X plugin, which is called engine X Postgres, which also does the translation  from HTTP request into Postgres queries.  So we can map that into the store procedure call, which is a simple mapping because you  have the URL, you have the HTTP verb methods, and you turn that into the corresponding  store procedure call in Postgres.  And then, yeah, we get like serverless thing, right?  No server side logic.  And there will be just one thing to scale.  And so the real time support that I mentioned in the last slides is very exciting to a lot  of people.  And I know some people are trying to build like wrapper libraries around that so you can  get just pgrest endpoint up and running and then you have just the data updated from the  client side so you don't really see anything on the server side.  That's already mentioned.  So the engine X Postgres plugin is already there.  And then I actually tried it already with the binding.  And there's some weird problem with like when you have two queries or something, but it's  basically working.  It's quite amazing because with that, I actually have the engine X talking to pgrest directly.  So there's no more server code.  There's no more Node.js or Python layer of that.  The thing that connects to Postgres, it's done in engines.  So I think this is quite new a thing and then lots of crazy ideas in it exploiting some  of the greatest Postgres features that I mean out of like from my background, it's like  an application developer, a lot of the crazy Postgres features I did not know about.  And then so once I know that, okay, I can combine and try to do something with that.  And then this is the experiment and this is the stop we've been building.  So that's pretty much my talk.  End point for the Congress website read down in Taiwan.  So we have, so by the way, I run a project called got zero dot TW.  So it's like the government in Taiwan's got dot TW, right?  So we built something called got zero.  So like for the government website, you change one letter and suddenly becomes a better website  with proper API and stuff like that.  Okay, so that's the Congress website for Taiwan down right.  So this is the actual Congress field and we have it represented in nice format.  And so that's client only application in Angular.  So the raw data is from Jason, which is powered by PGRest.  And this is actually joined from several tables, I think.  So we have that, sorry, you probably can't read anything.  We have the, like this bill has been like gone through several process in different  communities or whole Congress sitting or something like that.  And the amendment it contains is regarding two chunks of the laws and things like that.  So this is actually, I think, joined from five different tables because we have the  Congress website, we have the Congress library website and other things.  And then kind of cross-referencing all together created a huge view on that and then turn  that view into API endpoint just with that.  So there's no actual server code for that.  Basically what the view describes is the API endpoint.
PgREST_2_0_59.png PgREST_2_60_311.png PgREST_2_312_359.png PgREST_2_360_1595.png PgREST_2_1596_1691.png PgREST_2_1692_2111.png PgREST_2_2112_6599.png PgREST_2_6600_7427.png PgREST_2_7428_11411.png PgREST_2_11412_11639.png PgREST_2_11640_12155.png PgREST_2_12156_13703.png PgREST_2_13704_14495.png PgREST_2_14496_18647.png PgREST_2_18648_18863.png PgREST_2_18864_19775.png PgREST_2_19776_20867.png PgREST_2_20868_23807.png PgREST_2_23808_32387.png PgREST_2_32388_32459.png PgREST_2_32460_32507.png PgREST_2_32508_34787.png PgREST_2_34788_35087.png PgREST_2_35088_35135.png PgREST_2_35136_35195.png PgREST_2_35196_35315.png PgREST_2_35316_35423.png PgREST_2_35424_36215.png PgREST_2_36216_36755.png PgREST_2_36756_37979.png PgREST_2_37980_38831.png PgREST_2_38832_39227.png PgREST_2_39228_39527.png PgREST_2_39528_39755.png PgREST_2_39756_39971.png PgREST_2_39972_40895.png PgREST_2_40896_40900.png