PgREST

student started in the summer the grafting everyone and welcome to the picturesque talk so CL

from Taiwan and there are usually speak to very different crowd come from a like a pro background and and quite new to pose questions but in doing this sort of stuff about 10 years and we can see when I was hospitalized still software on and that's when I had a lot of free time is no more of the activities I start looking at stressed 0 this is really need lots of crazy so much it's like a database you put things in and thinking that we can do a lot more than that so i'd among 1 from the future time and the last slide have lost for and is about the future of was rest days and stuff right so key dress kind of like a futurist experiment that kind of things so what featuresets is essentially like this inductive store like the particular that basically being bashing about in this conference and they runs in cycles graphs and the interesting bit is that works with your existing data taken and turn your existing relational data into more a department store and like try to onto the joining in the things transparently and what was chosen later and you can actually knows no Jess modules like India models into it and turned the the young whatever is on Indian or whatever your existing are just we call into sort procedure inside up was present use those procedures as part cause or transmission need for producing the output and Mongol lab is is a among the be hosting service and have a rest API for access via by them and so we can go and steal the idea from there and then have a very similar and incompatible with the idea that and lastly a couple months ago on someone contributed by a plugin for the dress allows you to to like real-time subscription to the data that are similar to 1 of our business offering is my note are based their hosted like a Kansas service thing and then you have recently I and also what's up his support has a nice JavaScript from client API that you can do like subscribing changes to the data so you can kind to that a binding on in browsers side and without that can so yet there's some some something similar to that so to

start using was present some it kind of most of the logic is inside PostgreSQL as a store procedure but you can have still need a layer or 2 to to launch it and right now that layers in our in no OJS so you installed futurists from Indian soldiers and install globally and then you can also start up the with 2 existing databases and rounds of like turning populating all your existing tables or relations were reduced into armrests point so here a

sample for what we actually use in production is a Taiwanese Parliament website there's a lot of time size of work of economists I talked this morning right so that this is not the official Taiwanese parliament kind of the thing which we recall from the on the on structural government side and turned into a structured and team power by interest so it's like this is the appeal document and then the Bill has a relation to motions where this bill was being discussed on and in several occasions right so that kind of his being flattened by our picturesque when you define a view of what the on the B you want to expose an API is where where the the relation from motions of enjoying there but what he doesn't like later anyway so that that is

the people been talking about its come from some of those ecological type of postmodern database and this it it's got a very popular for the past few years this easy for people to just start using it and I think is a very destructive thing to to like stealing from ideas from then because spin out like not only seek of this improvement in post-crisis well and so

much as quickly as I go through some of the posts rest of features that we are exploiting and then turn it into useful components will be addressed here on the 1st thing of course is the states and they don't have not only to describe it as everyone knows by its very exciting and very promising and very fast in 4 and so on and so it's not like like examined when you to post Grasso I'm kind of some of the features are like I'm not probably not very interesting to this crowd by usually go from them to other people that offer them a schema in post-crisis like space so we can put on the search on then also have views being defined for a predefined queries and triggers for our actions you can fetch true operation or in certain things like that so schema

is like well you can put a prefix like you collect as a set of and our relations were views or functions into a group right like what we do use force tensions and put things into schemas and and these

are like predefined queries we can put complex complicated queries and resource joins or whatever interview which is turned into like socially acquired like a like how you as as a table so in . 3 there is also a materialist view and so in future as the idea is that on we mapped tables or relations into rest point and because the book for the like for the past 10 years the the web development people are selling is like turning relational high into a sum of effective representation by that's what you get from recent point and that's where you intend to decide if you want to join something or or or not and so by providing the joint resulting views then that we can just map the relation into the rest of book

and on so the nexus the traders and this is that the the pop components for our based access control in PostScript I've addressed and so basically you can you can put just hooks onto us updates in and out it and insert and delete and things like that so I so that's

probably all the things you already know and the exciting thing but for the past few years is that is the days in support after diseases score is the POV a extension we can start using JavaScript to right so procedure in that's right so but that's the way you do it you you can create the function like that was the adjusted body and then you can just use it as a as a store procedure by calling it is of no more on function involves graphs while but the thing is that when you have lots of this kind of store procedure written jobless really hard to maintain and so on so I I I created extension co POV acts which is some essentially away for packaging on and you modules and put it into your boss with database and young man on there is an additional operator being provided by his ascension is that the pipes are O'Connor pipe invoke extension which take the left hand side is adjacent and evaluate that's to the right hand side JavaScript text and put in the left hand side as the these are variable so so you see that the discussion here are we actually get the flu element in the 1st element which refers to and you can do arbitrary expression like the posterior well and like in the last talk there's the worst we're talking about and like there's no way to manipulate Jason efficiently solvable I mean this this is efficient either anyway but is now making it possible you can set up they act and set fool with was the adjacent columns and make a fool than you fool being the full being process with like the bar element deleted and then returned itself think so the pipe so the pipe invoke operator is like applying your adjacent columns or or our document with the task of text on the right hand side but is also tells court invoke operator which runs the right hand side in CoffeeScript script so for those of you don't encompasses more on modern demonstrated was which has composite published good and that has a lot of Hindi short show costs like the at operator is this and so so you can do that and and also has the implicit return for the expression functions so that kind of time kind of tennis into a more readable form for applying the John Austria on prime adjacent of 11 sites with this expression in encompassed could then return OK so that's operator the more importantly what you'll be is capable of this is like loading and models into all possible so for example there's a in model called Qs which handles the query parameters like the usual I'll are something equals something and something that was something in the form of of of the string and this model provides the passing of that and then to load it into some Samples grass is that you state OK POV X import x US which is the model man and minus F of this data being the function imposed so it's a function call pass on the school Qs which takes a taxi and returns a taste and there's a prefix for POV expertise and because we wanna supported at 4 and I . 0 as well so from that will create demand co-op your VAX is over just like the the part that is the text was a validation rules and on the right-hand side you say you want that the pass on the scope Qs to be to be to be that the just the function that it's in the 2 Qs package other than name parts so you has like lifted it from the on from QS package and find the pause function which is supported by the marginal and turn it into a PostgreSQL are stored procedure and you can use it as well the just a normal function in impulse grows parts on the scope US with effect and it turns it into evidence and documents yeah I'm not sure why you want to pass that kind of thing PostgreSQL on but actually from the use case for that is that once titles BG Q that RQ extension are from the sky people that you store that kind of thing in imports rose so if you need the process that in was grassland you need that the OK so so we that we can know how to process the information and impose restless was and if there isn't just a more easily that's been bundles on by by in the end and then with that you know you

can do functional index and then now you can have like complicated populations like but but the written in properly packaged adjusted page rather than most string of create function daughter of crazy things so procedures so now now and you can do a lot more of that

so again back to the US and so running could jurist but which is you're on the existing database and the relations in their into press important and in this set something like this I want to start running the fall of up as configurable is in your local host and his collection fails and it is still oresent . 4 and just get and could impose and delete and things like that and it has this and some paging people are paging metadata which you can sort of say you want to do like I'm skipping to the 2nd page of something that's similar to the Mongol lab on recipient and I described earlier but so that's example for the output from the to get point but essentially on the interesting bit is that you might think that on this site and cycle or am I turning database thing into Jason and doing joining bits and there no not really so is actually inside the G. so when we have this request is being i just processed very and was very minimal processing getting like to know what you're trying to do what you get in the collection appeals and then there was turned into a store procedure called which states the dress slacks and selecting the calendar of search it was built a strong bond here and and you can do this Crary's things like that and this is from the arm the arm angle that currently as well so they have a very similar our grammars trained to to Mongol and so that was also turned into part of the futurist select our current parameters here so this is like everything is done as in part of the store procedures from the so right now only the query you can use is is with is with the the the the the query string similar to Mongolia tuned to model the but seeing the OJ XQuery talk yesterday I think that it it makes more sense to to have it like something like that here and then our but this is the only I apply the borders a component for turning on arbitrary structure into what is actually generating the query and OK so for

relational they not like all other thing actually in the beginning is like turning like that of individual tables into resin point is like trivial righteous serialized so but what about like something relational you want to join something far the places and so the way to do it is actually creative view and then we have a DSL for generating a view collection so this is like for example there's a calendar table table and then we want to join the traditional Colin Cole sitting which is constructed from a function call which is like know that from the POV X on so you know you have this that the bulge and here is the underscore calendar city I and that's turning some structure into like a a separate ideas of that a more

comprehensive assembled here is like from another table which is these sitting here is joining from is joining with the calendar was not really joining because still subselect and there was the condition of 4 for matching the on the relation related data there so this is actually pulling out different of 3 tables and then turning that into like of the nested structure you need for your essay and points OK so so on so this this pretty much covers the theory only beef that you can all turn your existing relational it into something of structural and nested tend to flatten out of the newly defined by view and the way we do is that of you you defined those endpoint in those views in in a particular schema and was picturesque you can say OK the these API and is sharing every relational every views on the schema so you have you know more data which is the normal relational way on the public and then what we do is we create on additional layer of of views which are like on the picturesque as you can see here that the rest of thing which is constructed from the public sitting table so the view is more on of a decorated points that you get from adjoining under they have you want to be represented in interest and point so this covers the that the real part and then for the on the

ACL which is more interesting part is that on late for example in in Mongolia be hosted mobility the services I mentioned on you can the because good part of that is that it is a rest point already so you can have your like where are your web client I just talk to that then points but promises that the ACL is is all known when you have access keep right of all the data otherwise it's read-only so it's not very useful for not being a full-featured API and point pesetas you can have like you have no access control for other that in it too so the way that we we we see that this will work use of for example because we have this on update rules or features and you can say to expect something right so when we create a view which is actually being served as the re only a cat point you can optionally create rules when this being updated on or inserted or deleted on and to additionally his checks here so some of the more practical example here is it is somewhat when you when you have the again 1 accessed with this excess token are like that nowadays not given everyone does that also there and then so it's in your own so this is the the layer of the server way when that is also extracted when you have all the residue header turned it into part of the arm commands are part of the parameter into the futurist select work dressed up there are calls so from there you get those additional metadata into the the around all of these your of field and then and then in in your rules that's doing that instead as check because you can use the futurists on goalkeeper ran away to to extract that an hour as you can and probably you to be doing the token validation or populating the user from the token populating the group ownership from the token into the actual check there and if it doesn't work I mean it's like forbidden you just throw exception here otherwise you do the original up there so that's how the best infrastructure but there and then there should be a library for making all these kind of things like easier and does so by solving but of infrastructure there is there and then are OK so that's states of the DSS control bit and this is how how we mentioned that the work and that they are actually of working example for for that already but it's in in something not not in the public so next up is the real-time API for it it is it is part of the WebSocket plugging and was solid and it will create a treatise on the tables that you're interested in and now you can this is these are similar to the fibers in the eye and say OK I want to start listening to this important and so this is from the links collection and every time is updated the Corbeil be called so you have to kind of like a transparent way of processing the updates from from the quasi OK so I that's what we've been doing for of euro so and diseases like go hobby project and and several people are working on that and so it's not like X is will faster practically the developed but there are actual production use of it and the re only part is quite robust but the the part requiring a still control and the stuff is on this a lot more work and the number of pitch appreciate your your your comments and how you feel it it's it's it's usable or crazy or something that so what's next will be lots more documents and this simplified wasn't authentication authorization integration mentioned this already a plugin for integrating other the party of an occasion like based Facebook Twitter is just the the normal or something and without logging you get the access token are as I described it that should be used along with the ACL and on but there should be a lot more of a simplified libraries around that's to to do more fine-grained control and and stuff like that and also like the way we envision this destroyed sites of now there's only a very tiny seeing layer of server for translating your of requests into the futurist underscore select all optical right so that can actually go away with the gentleman yesterday mentioned in the legend talk there's a gets to the oracle and he has a broad have of so made that when when we who back into the store procedure there we have a surrealist environment is that but speaking which the or there's a sum uh engine next plugging which is going to next post west all which also does the translation from which to view quest into PostgreSQL our on granaries right so we can map that into the store procedure call which is a simple mapping because you have to you you have to actually be very masses is Internet into the corresponding on cost for procedure call PostgreSQL and then you know we get like surrealists thing right no no server-side logic and they will be just 1 thing to scale and so did real time on the real time in a support that I mentioned last slice is is very exciting to a lot of people and I know some people are trying to do on like regular libraries around that so you can get up to support up to dress and point running and then you have of just that they are updated from point size so you don't really see anything service but that's already mentioned the outcome the engine PostgreSQL uploading is already there and then I should try that already with Mr. of binding and there's some we're problem with like we have to queries or something and then but a but it's basically working it's got quite amazing concerned with with that actually have the ijX talking to a picturesque directly so there's no more store recorders normal no chairs or pies and layer of that the thing that connects to pose grants style in index so and so I think this is quite knew a thing and then plus a crazy ideas in exploiting on some of the greatest post press features that are made out of like from my big rights like application developer a lot of the Crazy Horse prestige as I did not know about and and so on was another OK I can combine and try to do something with that and and and this is the experiment and that this is the stuff we've been building so as the much my talk thank

and . 4 of the Congress website redundant high 1

so we have so by the way

I had I had a rather logical got 0 that so it's like the got in government how it got that to get the right so we feel something coke of 0 so like with website change when it is only because of their website was publicly announced that but it so that's the Congress website for it

I 1 up down right so this is the actual what Congress field and we have a represented nice performance and so that's kind only application in angular so the role the highest from Jason which is qualified

rest and this is actually

joined from several tables and think so on so we have sort of you probably can't read anything

but this this this and yeah we we have like this bill is being like gone through several processing of different communities or or a whole whole Congress sitting was on the and that and the mn many contents this regarding our true so chunks of the laws and things like that so so this is actually of the thing joint from 5 different tables because we have to the Congress website I would have the Congress library website and the order of things and then kind of cross-referencing altogether created a huge you on that's and internet view into a the and pointers with that so there's no actual so recall that is basically what the view describes is the again point