18 ms·
PostgREST: REST API for any Postgres database
- efojs 6y agoWhat a time to live!
- sopooneo 6y agoI am clearly missing something, but at that point, why not just allow the client (presumably a web app running in a browser in most cases) to just make requests via SQL? You would of course want to add authentication/authorization layers. You’re want record and field level restriction. But if the entirety of you data is in an RDMS, why put this “REST” paradigm in the middle? Why not just admit you’re querying a database, and query the database? If the only reason is access control, it seems a simpler solution must be possible.
- piaste 6y agoIt's difficult to grant an untrusted client a level of raw SQL access that won't let them do harm. For example, even without access to any specific table or function, even with rate limits, I can denial-of-service the server by asking it to make a massive recursive pure computation.
- tehlike 6y agographql kind of does that. SQL is very complex, T-SQL is turing complete, meaning you can do lots of damage. you can bring servers to a halt if unchecked. It's pretty hard to restrict what can be done keeping flexibility.
- ruslan_talpa 6y agoThis is the “corect” response to the question above. :)
- xwdv 6y agoMost web applications are basically just interfaces to a database. Why even have “REST” at all?
- jaikant77 6y agoYou got me thinking. GET/PUT/POST is not really needed for a completely server rendered app.
- ntr-- 6y agoI think we will see more of a push towards SSR technologies like LiveView for exactly this reason: https://hexdocs.pm/phoenix_live_view/Phoenix.LiveView.html https://hexdocs.pm/phoenix_live_view/Phoenix.LiveView.html
- tiew9Vii 6y agoI often thought the same with the use of GraphQL. Instead of building a highly curated API with business logic, optimisations and other general stuff you are building a direct interface around how your data looks in the db, a 1:1 mapping and crud interface. In software we are taught to prefer loose coupling vs tight coupling yet here people are preferring the tight coupling. I’m not a fan of this as the user interface (API) has a tight coupling with how you store your data. Then like you say, why not just speak SQL as you have all the same issues, essentially multiple clients writing to/owning the same tables.
- taffer 6y agoThe PostgREST documentation makes it pretty clear that for decoupling and security reasons the data schema should be separated from the API schema. You should only provide the client with views and functions and keep all data in a separate schema. This also solves the versioning problem, because if your API schema is separate from your data, you can simply drop and re-create the entire schema for deployment without needing migrations.
- dragonwriter 6y ago> I am clearly missing something, but at that point, why not just allow the client (presumably a web app running in a browser in most cases) to just make requests via SQL? Because REST-over-HTTP is low impedance with browser-based web apps, whereas SQL is...not. Plus, with REST, you abstract whether all, some, or none of your data is an RDBMS; the fact that you've implemented something with PostgREST doesn't mean everything related to it and linked from it is implemented the same way.
- CuriouslyC 6y agoPostgres isn't 100% set up to let fully untrusted clients do stuff. For example, right now there is no access control around listen/notify. You'd have to do some baseline query pre-processing to be completely secure, which could be a non-trivial task depending on just what you want to let clients do. I've done it in read-only internal business apps though, it's great.
- nothera 6y agoThe biggest problem it has great entry difficulty. Few years ago i stuck at building custom social auth with postgres, even tho postgrest explained basic principles of authorization using roles, it required a lot of sql code to work, a lot like a hundred lines of sql code, this 100 lines is a paradise for mistakes for someone writing sql functions first day.
- qualitylandlor 6y agoSo I guess people use this cause they don't want to spend some time learning SQL...
- piaste 6y agoThat's exactly backwards. People who don't want to learn SQL tend to write CRUD API and rely on code-first ORMs to manage the database. When you instead use PostgREST, or Hasura, you're usually going to write a ton of SQL - tables instead of classes, views and stored procedures instead of interfaces, row-level security rules instead of authorization code.
- ruslan_talpa 6y agoA “ton” is very relative. Those tables still have to be written even with ORM. And as for views/rls/triggers the total LOC i would say is orders of magnitude smaller then in traditional stacks and a lot more readable (it’s static definitions vs imperative code)
- janci 6y agoI see how it can replace 80% of glue in my typical project. However I am not sure how to handle the remaining difficult 20%. Particulary where to put the business logic. I don't want to have all my business logic in database. I don't want to write all business logic in SQL. SQL is not good language for this and the tooling is suboptimal: editors, version control, libraries, testing. Is there a way to define stored procedures and triggers in some host languge (as with SQLite)? Or is the recommended way to add extra API handled by language of choice? But I don't want to do the same things in two different ways (i.e. querying by PostgREST and querying the DB directly)
- ptrwis 6y agoHow much effort of human mind is being lost to solve these ORM or JS frameworks problems. Programmers community is fighting with problems which at first should not even exist. With good tools we shouldn't even bother about them
- christophilus 6y agoSomething I don’t understand about these sorts of tools is how you’d handle user signups. Is that something you just side load as a second service?
- CuriouslyC 6y agoBig thumbs up for PostgREST. While it hasn't been completely issue free for me, it's a great project making a ton of progress and the community is very helpful and responsive. I found myself butting heads with the limitations of the API quite a bit, but since it has a wonderful RPC feature, you can always drop a custom endpoint to do what you need to do without completely ejecting. You can also surface other systems with PostgREST, using foreign data wrappers. This is great because you can use Postgres's rock solid role system to manage access to them. FDWs are surprisingly easy to write using Multicorn, and you can get pretty crazy with them if you're fronting a read replica (which you should be doing anyway once past the proof of concept stage).
- JyB 6y agoCan someone explain to me what problem this solves? I'm having trouble understanding.
- gidan 6y agoMoving business logic lower at the database level. Mainly for better performance at the cost of code more difficult to deal with.
- eska 6y agoThe bigger reason seems to be avoiding to write boiler plate code.
- pojzon 6y ago*worse performance, because you drop more things that often happen before accessing db -> on the level of database - Simple as that its not going to spend 100% computing power on things you would expect it to.. So you get worse performance and harder to maintain code.
- punnerud 6y agoIn stead of Heroku, try hosting yourself using Dokku: http://dokku.viewdocs.io/dokku/ http://dokku.viewdocs.io/dokku/ I don’t see the benefit of Heroku anymore when the self hosting is this simple
- corytheboyd 6y agoHeroku is still around and doing fine, so what? It’s definitely far more expensive than it’s worth if you know what you are doing, but if you just want to host a dumb rails app without thinking about anything Heroku is a great option. If you want to ease into hosting your own code, Dokku is a great option. No need for the put down
- derefr 6y agoAnyone here using this in production? I’ve always thought it was a neat idea, but haven’t heard of anyone building their API-oriented-SaaS company around it.
- lhenk 6y agoWe're using it at CYPEX to automatically make customer data consumable for our frontend. https://www.cybertec-postgresql.com/en/products/cypex/ https://www.cybertec-postgresql.com/en/products/cypex/
- np_tedious 6y agoI've used it for admin tools at real jobs. And once powered a read only "consumer" (writes came from offline webscraping) hobby project with it to make a no-backend-code react app. I see no reason it couldn't work in prod, especially if you had a CDN or HTTP cache in front. What I found most striking is that it relies on postgres for just about everything. Content obviously (sometimes straight from tables, sometimes via db views), but also users and permissions. I'd first assumed there would be a config file a mile long but it really is all Postgres.
- danielbln 6y agoWe've used this in production for geospatial data delivery via GKE cloud run. Worked quite well.
- theshetty 6y agoIt looks like, there are a few already using this in production You can see it on this page: http://postgrest.org/en/v7.0.0/ http://postgrest.org/en/v7.0.0/
- floatingsmoke 6y agoI recently read that supabase.io is using this. https://supabase.io/docs/postgrest/server/about https://supabase.io/docs/postgrest/server/about
- pojzon 6y agoTried at scale, but it was simply not performant enough. All the additional things that you can extract before getting to db have made it too slow and overloaded databases. It may be good for small-medium projects but when you process millions of heavy computing requests - its not for that.
- jensneuse 6y agoPostgraphile is a better alternative. Then there's also Hasura, 8Base and XGeneCloud. Did I miss anyone?
- np_tedious 6y agoMost (or all?) of those are GraphQL rather than REST.
- masklinn 6y agoGiven that, despite the project name, the tool described by TFA is pretty obviously RPC over HTTP rather than anything resembling Fielding's description, graphql would would just as well.
- michelpp 6y agoSorry but I disagree, PostgREST is REST. For example URLs map to resources and it uses HTTP verbs. GraphQL does not.
- masklinn 6y ago> Sorry but I disagree Feel free to. But maybe consider that "taking advantage of HTTP" != "REST". > PostgREST is REST. The primary if not sole distinction of a REST system according to the creator of the concept is hyperlinking. From what I understand of postgrest, hyperlinking is nonexistent. > For example URLs map to resources Which doesn't really matter when these URLs are magic strings. It's also not really true, URLs are a mix of procedures (/tsearch, literally everything below /rpc) and function names, really, to be passed a bundle of parameters through query strings. And the project itself recommends using stored procedures (and views) when exposing the system to any sort of untrusted environments. > it uses HTTP verbs Not actually relevant to REST, and serving as little more than a form of namespacing.
- steve-chavez 6y ago
- xyproto 6y agoI gave this a shot but found it more cumbersome than just writing an API server in Go.
- CameronNemo 6y agoHow many tables are you working with? Do you solve the over/under fetching problem or just ignore it? Do you support all of the filters that postgrest does? How about security? How do you authorize requests I worked on a CRUD service for three months before replacing it with Hasura.
- xyproto 6y agoSimple tables. I understand that complex problems sometimes require complex tools, though.
- vagrantJin 6y agoThis looks neat. Anything similar for mysql?
- lazyant 6y agoYes: http://restsql.org/ http://restsql.org/
- vagrantJin 6y agoThanks.
- deleted 6y ago[deleted]
- Pi-Calculus 6y agoWe (www.gaswork.com) use PostgREST in production to serve up job posts and collect anonymous usage analytics for our mobile app and single page app.
- specialist 6y agoNeat. I'd rather have MIME type(s) for result sets. So we can tunnel over HTTPS. Postgres Wire Protocol https://crate.io/docs/crate/reference/en/4.3/interfaces/postgres.html https://crate.io/docs/crate/reference/en/4.3/interfaces/post... Tabular Data Stream https://en.wikipedia.org/wiki/Tabular_Data_Stream https://en.wikipedia.org/wiki/Tabular_Data_Stream
- michelpp 6y agoYou can request different mime/types and postgrest will send you, for example CSV of the results instead of json.
- CameronNemo 6y agoArrow would be a god send.
- jpalomaki 6y agoZedstore[1] is columnar storage layer for Postgres. It's not yet there, but I understood the goals is to get it in Postgres. Arrow as a format should make more sense with something like that in the background. I don't think you can efficiently pull the results in column oriented fashion right now. [1] https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2738/slides/233/ZedStore-PGConfeu2019-Milan.pdf https://www.postgresql.eu/events/pgconfeu2019/sessions/sessi...
- fulafel 6y agoPostgREST is great. Also - written in Haskell - a major building block for YC funded startup supabase.io (https://news.ycombinator.com/item?id=23319901 https://news.ycombinator.com/item?id=23319901)
- kissgyorgy 6y agoThis is not a good idea at all. One of the main point of a REST API is to abstract away your internal data structures for the clients that makes sense. Also refactoring and database migrations should NOT change the public API of any software, especially not a REST API. Now, when using something like this, even the most basic change will have a ripple effect on your whole infrastructure and break every client.
- fulafel 6y agoDepends on your use case. REST is about putting resources on the web. Sometimes it can be eg datasets backes by SQL tables. Views / stored procedures are recommended to provide APIs.
- bausano_michael 6y agoI can see how an API like this would be a great fit for some no code solution integration. Certainly bad idea in some situations, but let's not be too quick to judge the usefulness of the project.
- kissgyorgy 6y agoI'm not saying it cannot be useful at all, but most projects should not even consider using it.
- kowlo 6y agoAfter reading your comment I expected to see low to mediocre activity on their repository... however, 15.1k stars seems to indicate that many do think it's a good idea. I know GitHub stars are not a measure of whether something is a good idea or not - but why do you think so many have positively engaged with it?
- kissgyorgy 6y agoLots of developers in general doesn't seem to understand the concept of a REST API. What are the benefits? Why are we doing this at all? How to do it properly? Most APIs are not REST, just some RPC-like APIs over HTTP. When you have a tool like this, it's just easier to use it and call it a day - without thinking too much or learning the concepts properly.
- diveanon 6y agoHasura provides a similar service for graphql along with many many other features along with a enterprise tier. Worth a look if you are considering tools like this.
- Thaxll 6y agoHow do you add logs, tracing monitoring to this since everything is baked into the DB? I still think this is a terrible idea.
- cdaringe 6y agoYour application server layer doesn't go away, it just thins up. Add your middleware and tracing provisions as needed around the DB IO boundary per usual.
- mildbyte 6y agoPostgREST is great and really reduces the boilerplate around building REST APIs. They recommend implementing access rules and extra business logic using PostgreSQL-native features (row level security, functions etc) but once you get your head around that, it really speeds things up! If you're interested in playing around with a PostgREST-backed API, we run a fork of PostgREST internally at Splitgraph to generate read-only APIs for every dataset on the platform. It's OpenAPI-compatible too, so you get code and UI generators out of the box (example [0]): $ curl -s "https://data.splitgraph.com/splitgraph/oxcovid19/latest/-/rest/epidemiology?and=countrycode.eq.GBR,adm_area_3.eq.Oxford)&limit=1&order=date.desc" [{"source":"GBR_PHE","date":"2020-11-20", "country":"United Kingdom", "countrycode":"GBR", "adm_area_1":"England", "adm_area_2":"Oxfordshire", "adm_area_3":"Oxford", "tested":null, "confirmed":3079, "recovered":null, "dead":41, "hospitalised":null, "hospitalised_icu":null, "quarantined":null, "gid":["GBR.1.69.2_1"]}] [0] https://www.splitgraph.com/splitgraph/oxcovid19/latest/-/api-schema https://www.splitgraph.com/splitgraph/oxcovid19/latest/-/api...
- uhoh-itsmaciek 6y agoWhy a fork?
- mildbyte 6y agoCouple reasons: We don't actually have a massive PostgreSQL instance with all the datasets: we store them in object storage using cstore_fdw. In addition, we can have multiple versions of the same dataset. Basically, when a REST query comes in, we build a schema made out of "shim" tables powered by our FDW [1] that dynamically loads table regions from object storage and point the PostgREST instance to that schema at runtime. When we were writing this, PostgREST didn't support working against multiple schemas (I think it does now but it still only does introspection once at startup), so we made a change to PostgREST code to treat the first part of the HTTP route as the schema and make it lazily crawl the new schema on demand. Also, at startup, PostgREST introspects the whole database to find, besides tables and their schemas, also FK relations between tables. This is so that you can grab an entity and other entities related to it by FK with a single query [2]. In our case, we might have thousands of these "shim" tables in a database, pointing to actual datasets, so this introspection takes a lot of time (IIRC it does a giant join involving pg_class, pg_attribute and pg_constraint?). We don't support FK constraints between different Splitgraph datasets anyway, so we removed that code in our fork for now. [1] https://www.splitgraph.com/docs/large-datasets/layered-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [2] https://postgrest.org/en/v7.0.0/api.html#resource-embedding https://postgrest.org/en/v7.0.0/api.html#resource-embedding
- xgenecloud 6y agoCheckout XgeneCloud for instant REST APIs on - MySQL / MariaDB - SQL Server / MSSQL - SQLite - and Postgres https://github.com/xgenecloud/xgenecloud/ https://github.com/xgenecloud/xgenecloud/ We do support instant GraphQL as well! (Full disclosure : Im the creator)
- ErunamoJAZZ 6y agoI have been using Postgraphile (similar to this one but for GraphQL) for many months. The great thing about this way to create systems is that you don't expend time doing glue code, just in the real business logic. But a big pain is to maintain your database code, by example the version control of your functions. There are not suitable linters, and testing can't be done over postgres functions but must be done over GraphQL instead. Using things like this will save you months of development time!, Even if I agree there are some systems that will not be good idea to implement in this way.
- snuxoll 6y agoLack of linters can be a pain, but testing is easily handled with pgTAP.
- awb 6y ago> Using things like this will save you months of development time! When you need to integrate with 3rd parties though you're right back to writing traditional backend code. So now you have extra dependencies and a splintered code base. Yes, this automates boilerplate which is awesome for small, standalone apps, but in my experience I haven't seen months of development time saved with these tools.
- CuriouslyC 6y agoThis isn't really true, in most cases you can write FDWs over foreign resources that let you query them like any other table, use them in joins, etc. Postgres is really more of an application platform than a database at this point. Just don't try to have the same PG instance be your master and your platform.
- lumberjack24 6y agoYou might want to check Forest Admin in that case. It creates an extensible admin REST API on top of your Postgres. Comes with native CRUD routes for all your tables but you can also declare your own to refund a transaction using Stripe's API, send an email using Twilio's API... Here's an explanatory video - https://bit.ly/ForestAdminIntro5min https://bit.ly/ForestAdminIntro5min
- talolard 6y agoI really love postgrest! I did a lot of django before and found myself constantly wrestling with the orm to express what I wanted, then writing endless pages of serializers and view sets and had to debug why they were always slow. Postgrest has a learning curve, but the performance boost vs django is huge, and I can use more advanced db features like triggers more easily and in a way that’s native to the overall project.
- mtnGoat 6y agoSeems like a cool idea, has anyone tried this in production, at scale? How is the performance?
- steve-chavez 6y agoDocs on performance are going to be updated soon. But on a low end t3a.nano, with PostgREST master version, you'll get: - Reads(simple, filtering by primary key): 2k req/s - Writes: 1.5k req/s
- pojzon 6y agoWhat kind of reads and writes? Are those geospatial operations or simple insert/read operations on a table that has a single column no foreign keys ? Is security enabled or not ? Whats the load on the database, how it behaves during load? Thats a really vague statement you did there.
- steve-chavez 6y agoThe load tests were done on the chinook database[1]. > Thats a really vague statement you did there. Yeah, it's lacking details, sorry about that. We'll publish a more detailed write up soon. [1]: https://github.com/lerocha/chinook-database https://github.com/lerocha/chinook-database
- pojzon 6y agoWill there also be a reference for comparison in there ?
- codenesium 6y agoI've learned you're better off building software that matches exactly what you need right now and change it over time as requirements change. These tools are great for a prototype but you pay the debt in the future. This is coming from someone who built a system system just like postgrest for a different tech stack.
- ruslan_talpa 6y agoI’ve heard the same type of argument a long time ago against choosing wordpress when it was still relatively new (usually from custom webdev shops when talking to clients)
- adevx 6y agoEven with WordPress the assumption that you pay for it in the future is valid. Albeit anecdotal, I have always needed to rewrite from scratch after first launching on WordPress. But that doesn't have to be a bad thing. Often with tools that help you bootstrap quickly, say ORM's, GraphQL, and in this case PostgREST there comes a time that optimizing or extending is more work than writing a custom build solution. You have to decide if time to market or validating your business model is more important than a potential premature optimization.
- codenesium 6y agoThere are definitely the right tools for the right job. I work in enterprise on applications that will run for decades. It makes no sense for us to sacrifice long term maintainability to ship something fast.
- rvr_ 6y agoAnd that is how you end up with massive data leaks.
- Tarq0n 6y agoCan you provide any examples of postgREST being involved in a data leak?
- rvr_ 6y agoWhy should I? the whole idea is error prone. I have seen, many times, in the wild, excessive data exposure thru REST APIs. Lowering the bar does not help.
- michelpp 6y agoPostgREST is far more secure than most API servers. Any API server can leak data. Most API servers define their own, native, completely one-off security system that does all checks in-app and logs into the database as a superuser. The framework-du-jour is basically an insecure rooted processes by definition. PostgREST logs in with zero privileges. It then uses Postgres' native role based row-level security system to control access. Are you saying that is insecure?
- adevx 6y agoPostgREST is not inherently more secure, it is as secure as your RLS policies and session mapping. It comes at a price of tight database coupling. When using a cache layer you might need to write an api level security layer anyway.
- rvr_ 6y agoDo you remember the old days, when desktop apps (many written in Delphi) connected directly to the SGDB, relying on views and stored procedures? This kind of "server-client" software is still very common inside corporate LANs. Since the browser speaks a very limited set of protocols, projects like postgREST are just a bridge between frontend and storage, thus moving all needed business logic inside into the SGDB. This kind of model is hell. It's history repeating itself all over again.
- otar 6y agoI've been witness of a fully functional http client wrapper written in PL/SQL on Oracle. It's been working very well on REST and SOAP APIs. Codebase was a mission critical system for a large financial organization. Development soeed was advantage, but the trade-off was that the good database developer skill is still rare and you had to grow and teach other [junior] devs for years. They used to stick with the team much longer time than the average developer, but still I believe it is a disadvantage. What about PostgREST, the biggest issue I have with it is a DB server being available publicly in the net, I usually try my best to either place DB servers in the private network or "hide" them. Other than this argument, it's a pleasure to develop on that low level. SQL is an important skill and it's strange why so many devs know it superficially.
- deleted 6y ago[deleted]
- somurzakov 6y agoyou can probably hide postgREST behind combination of API Gateway/Load Balancer/WAF/IDS+IPS that could solve most of headahches with security
- FlyingSnake 6y agoThis is the correct way to go. My PostgREST is behind nginx for example.
- robertlagrant 6y agoIs this the ApEx? The gift that keeps on giving, for Oracle.
- SahAssar 6y ago> What about PostgREST, the biggest issue I have with it is a DB server being available publicly in the net, I usually try my best to either place DB servers in the private network or "hide" them. I've heard this argument many times (and thought it myself), but when dealing with postgrest it seems that if you have a proper JWT setup (which is how postgrest handles AuthN) and use postgres' security features (like row level security) perhaps it should not be thought as a rule anymore. IMO it seems like having the api layer only assume a role and having the DB handle AuthZ would mean better security since you can implement more fine grained rules that are actually verified by the part of the stack that knows the data structure already. It's also not allowing arbitrary SQL, it's translating from HTTP to SQL, so nobody can do "SET ROLE 'admin';" unless you write a specific SQL function that does that.
- deleted 6y ago[deleted]
- ivanceras 6y agoI made something[1] similar in rust inspired by this project. I made modification to the syntax to make it more ergonomic in the case of grouping filters with parenthesis for complex and nested filters. I also added joining of tables, renaming columns and more. [1]: https://github.com/ivanceras/restq https://github.com/ivanceras/restq
- xav0989 6y agoSurpringly enough, crt.sh is pretty much all built in PLSQL and PLpgSQL.[1] I'm sure there are advantages to running it all from the database engine itself, but finding devs that can work on it must not be easy. [1]: https://github.com/crtsh/certwatch_db https://github.com/crtsh/certwatch_db
- michelpp 6y agoThat's surprising, SQL is one of the most well known languages in the world. plpgsql has its quirks, but as languages go, it's pretty straightforward, most things do what you expect them to do.
- anthony_doan 6y agoIt's not the language but more about how the data is store (data structures/indexes) and what you're going to do with the data. At least during my time as a developer, I've come across many people that didn't understand this. When asked why they want to use elasticsearch over RMDB is it because they wanted Trie over B+tree? They didn't understand. Also the use cases almost always relational. Postgres have good enough FTS actually if anything elasticsearch is almost always a complement database not a replacement to RMDB.
- IggleSniggle 6y agoHonestly folks choose elasticsearch only in part for Trie over B+tree. It provides a fair amount of magic wrt to appropriate defaults for dealing with large datasets in a distributed store. I think a lot of folks would be better off with RMDB, but if you barely know SQL and spend most of your time making UIs, you’re lucky to have the breadth of know-how to configure Postgres the right way (no offense intended to frontend developers). Of course, Elasticsearch’s magic defaults expectations may come back to bite you later on when you’re using it OOTB this way, but it’s hard to argue with throwing a ton of data in it and then -POOF- you have highly performant replicated queries, with views that are written in your “home” programming language, without even really necessarily understanding what your schema was to start with (yikes, but also, I get it).
- tutfbhuf 6y agoDoes this work for YugabyteDB?
- michelpp 6y agoThere's always this confusion that comes up whenever PostgREST is discussed on HN, which is many times at this point. There is the misconception that arises that you use PostgREST to remove your existing stack entirely and you do everything in SQL. This is not true, you're not going to send emails from SQL or run entire multi-GB ETL reductions during an http request. If you want an API call to kick off some external processing, then insert that job into a queue table and do the same thing you always did before, consume the queue out of band and run whatever process you want. Another one that comes up is that somehow postgrest is "insecure". Of course, if you invert the problem, you see that postgrest is actually the most secure because it uses postgres' native security system to enforce access. That access is enforced onto your API, and you know what, it's enforced on every other client to your DB as well. That's a security unification right there. That's more secure. What PostgREST does is let you stop spending months of time shuttling little spoonfuls of data back and forth from your tables to your frontend. It's all boilerplate, install it in a day, get used to it, and move onto the all those other interesting, possibly-out-of-band, tasks that you can't get to because the API work is always such a boring lift.
- deleted 6y ago[deleted]
- Fire-Dragon-DoL 6y agoIs there an admin UI for postgrest, along the lines of ActiveAdmin? That would be game breaking for me, lot of software can be skipped with such a thing
- majkinetor 6y agoHere is my Windows test if somebody want to experiment with this: https://github.com/majkinetor/postgrest-test https://github.com/majkinetor/postgrest-test Also on nix by steve-chavez https://github.com/steve-chavez/postgrest-benchmark https://github.com/steve-chavez/postgrest-benchmark
- nickthemagicman 6y agoI love this idea but had to come to terms with the fact that I don't understand SQL enough to use it. This is what it takes to implement user auth. https://postgrest.org/en/v3.2/examples_users.html https://postgrest.org/en/v3.2/examples_users.html
- mixmastamyk 6y agoLooks doable, but I’d like most of that automated. Syntax highlighting would help understanding as well.
- gurjeet 6y agoHere you go. GP linked to a very old version. This one is the latest version, and examples are syntax highlighted. Edit: Forgot the URL: https://postgrest.org/en/v7.0.0/auth.html#sql-user-management https://postgrest.org/en/v7.0.0/auth.html#sql-user-managemen...
- mixmastamyk 6y agoSaw JWT in the auth docs. Isn’t it disfavored aka deprecated these days?
- rishav_sharan 6y agoI thought jwts are all the rage these days, with all the jamstack, api first approaches. Who deprecated jwt and why?
- RedShift1 6y agoJWT is not deprecated at all, where did you get that from?
- mixmastamyk 6y agoSecurity experts say not to use it. Search this site for multiple references.
- RedShift1 6y agoList me any points you have and I'll gladly address them.
- mixmastamyk 6y agoThey’re not my points and I don’t use it based on their recommendations.
- RedShift1 6y agoHalf of the time when arguments against JWT are made is because they are not using it correctly, think it is supposed to do something when they are not meant to do that thing, or are quick to dismiss JWT without being able to point to an alternative that solves their stated deficiencies. Your journey for evaluating if JWT fits your use case should start here: https://tools.ietf.org/html/rfc7519 https://tools.ietf.org/html/rfc7519
- EamonnMR 6y agoI'd love something like this for MySQL. Or really anything to remove the headache of installing MySQL client libraries in python and ruby.
- hu3 6y agoI haven't tried but you might want to check https://github.com/xgenecloud/xgenecloud/ https://github.com/xgenecloud/xgenecloud/
- dang 6y agoIf curious see also 2019 https://news.ycombinator.com/item?id=21435195 https://news.ycombinator.com/item?id=21435195 2017 https://news.ycombinator.com/item?id=13959156 https://news.ycombinator.com/item?id=13959156 2015 https://news.ycombinator.com/item?id=9927771 https://news.ycombinator.com/item?id=9927771 2015 https://news.ycombinator.com/item?id=8831960 https://news.ycombinator.com/item?id=8831960
- kiwicopple 6y agoEvery project on https://supabase.io https://supabase.io uses PostgREST. It's an amazing tool, especially when combined with Row Level Security. We have built client libraries for Javascript, Rust, Python, Dart, and a few more on the way thanks to all the community maintainers. Supabase employs the PostgREST maintainer (Steve), who is an amazing guy. He works full-time on PostgREST, and there are a few cool things coming including better JSONB and PostGIS support. We recently benchmarked PostgREST for those interested: https://github.com/supabase/benchmarks/issues/2 https://github.com/supabase/benchmarks/issues/2 nb: i'm a supabase cofounder
- hrishi 6y agoGreat work guys, love what you're building at Supabase! Did you run any loadtests that stressed the system enough to start dropping/failing requests? I'm wondering where that threshold is.
- kiwicopple 6y agoYes, we are benchmarking all of the components we use in Supabase, including the end-to-end system here: https://github.com/supabase/benchmarks/issues https://github.com/supabase/benchmarks/issues We still have a few benchmarks to complete, but PostgREST has been thoroughly tested now. Steve just spent the past month improving throughput on PostgREST, with amazing results (50% increase). tldr: for simple GET requests on a t3a.nano you can get up to ~2200 requests/s with db-pooling.
- hrishi 6y agoThanks! It's amazing what you can get done with optimization vs horizontal scaling. What would you say are the current failure modes? Say for a t3a.nano, what combination of payload size/queue length/rps/other parameters would absolutely mandate an upgrade in capability?
- kiwicopple 6y agoI couldn't tell you with a reasonable degree of unfortunately. We will do some stress testing at some point, but we haven't started them yet. We see few 500 errors across all of the PostgREST requests at Supabase. The errors that I can remember are from users doing `select=*` on tables which would return hundreds of MB of data. If you're thinking of adopting it then we'd be happy to help figure out if it's the right tool for your use-case.