PostgREST: REST API for any Postgres database
github.com
github.com
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
nb: i'm a supabase cofounder
Clickable links to the Supabase client libraries, for those interested:
- JS: https://github.com/supabase/postgrest-js/
- Dart: https://github.com/supabase/postgrest-dart
- Rust: https://github.com/supabase/postgrest-rs
- Python: https://github.com/supabase/postgrest-py
Also, you can see how they're used together on https://pro.tzkt.io.
Did you run any loadtests that stressed the system enough to start dropping/failing requests? I'm wondering where that threshold is.
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.
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?
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.
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.
I cannot think of a situation where an API only handles CRUD data and lacks any behaviour.
But, If your API really only is pushing data around, ' such tooling is usefull and probably saves a lot of time.
You put an item in table queue using SELECT FOR UPDATE ... SKIP LOCKED and some out of band consumer sends your email.
PostgREST isn't about doing everything in the database it's about doing all the same patterns you already do but with less boilerplate.
I'd add that you also have hard to spec couplings and difficult to manage microservices setup.
Tools like MQTT or paradigms like eventsourcing might help. But those all presume your database is a datastore. And not the heart of your businesslogic.
Those third parties can still talk to the same database. We use this pattern all the time, PostgREST to serve the API and a whole bag of tools in various languages that work behind the scenes with their native postgres client tooling.
I guess you could front it with a reverse proxy if not, but would be nice to have auth built in.
Do you tightly orchestrate releases? Or do you simply never change the schema?
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
[1]: https://github.com/okbob/plpgsql_check
[2]: https://github.com/steve-chavez/socnet/blob/master/tests/ano...
The solution I came with is to have a git repository in which each schema is represented by a directory and each table, view, function, etc… by a .sql file containing its DDL. Every time I make a change in the database I make the change in the repository. It doesn't automate anything, it doesn't save me time in the process of modifying the database, it's actually a lot of extra work, but I think it's worth it. If I want to know when, who, why and how a table has been modified over the last 2 years, I just check the logs, commits dates, authors and messages, and display the diffs If I want to see exactly what changed.
It could not only be great for documentation purposes but also actually help maintenance by making sure that all statements are executed in all environments
This way whenever I make a change I call this one liner, overwrite previous sql dump in git repo, then stage all and commit.
This way il also enjoy diff over time for my schemas.
Specifically, testing the sql side with sql (pgtap) and having your sql code in files that get autoloaded to your dev db when saved. Migrations are autogenerated* with apgdiff and managed with sqitch. The process has it’s rough edges but it makes developing with this kind of stack much easier
* you still have to review the generated migration file and make small adjustments but it allows you to work with many entities at a time in your schema without having to remember to reflect the change in a specific migration file
I’m sure you could argue either way. Just adding this as an option to consider.
SQL has been the biggest flaw in this stack for me. I love using PostgREST/Postgraphile et al, but actually writing the SQL is just... eh. Maybe (lets hope) EdgeDB's EdgeQL or something similar could rectify this. The same Postgres core database and introspection for Postg{REST,raphile} but with a much improved devx
Another thing is it's Node, and can be used as a plug-in to your express app. This can make it easier to customize and extend over time. Eg; have parts of your schema be postgraphile and parts be custom JS.
Regardless, thank you for the correction!
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"]}]
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-query...
[2] https://postgrest.org/en/v7.0.0/api.html#resource-embedding
That is a pretty awesome feature to be mentioning as an "oh yeah, also..."! :) Bookmarked.
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.
That would be game breaking for me, lot of software can be skipped with such a thing
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.
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.
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.
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.
I've done it in read-only internal business apps though, it's great.
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.
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.
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.
Also
- written in Haskell
- a major building block for YC funded startup supabase.io (https://news.ycombinator.com/item?id=23319901)
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.
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).
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.
Perhaps you'd be surprised in knowing that a resource can be a stored procedure, quoting Roy Fielding[1]:
"a single resource can be the equivalent of a database stored procedure, with the power to abstract state changes over any number of storage items"
In general I think PostgREST's REST implementation is evolving. Also, we've had Roy Fielding giving feedback[2] on an issue before. Once we fix that issue, we'll ask him if he thinks if PostgREST is REST. I have a feeling that he might reply positively :-]. We'll see.
[1]:https://roy.gbiv.com/untangled/2008/rest-apis-must-be-hypert...
[2]: https://github.com/PostgREST/postgrest/issues/1089#issuecomm...
I'll direct you to the title of the post to which this comment is associated.
>> REST APIs must be hypertext-driven
> I am getting frustrated by the number of people calling any HTTP-based interface a REST API.
Now maybe I missed all the hypertext in postgrest, but given all of its documentation obviously fails the criteria of
> A REST API must not define fixed resource names or hierarchies (an obvious coupling of client and server).
I don't see how it could be in any way REST.
> Also, we've had Roy Fielding giving feedback[2] on an issue before.
That is feedback on an issue of HTTP implementation and compliance, it has nothing to do with REST.
Now look I really don't mind APIs being good HTTP citizens and having nothing to do with hypermedia, and that REST is an interesting idea doesn't mean it's a good idea (at least for programmatic APIs).
But it's like people saw a picture of a baby in a bath, went "well I don't need the pink thing in the middle but I'd sure like to wash up a bit", and when others point out they're carrying around a jug of soapy water which they insist is a baby called Fred those others get called "Baby purists"[-1].
Postgrest, returning/modifying predefined resource(s) based on standard HTTP verbs and whatnot, is at least as RESTful as most other things that use the term. Despite the imprecision, this is still a somewhat useful descriptor beyond "oh it's arbitrary RPC". And it's not at all like GraphQL
It definitely proves that postgrest is not rest in any way.
> REST as initially defined has become some kind of hyperlinked / referenced platonic ideal that very few do or even attempt.
Sure? At no point have I been arguing for doing REST. Just for not calling things REST when they obviously are not?
> Postgrest, returning/modifying predefined resource(s) based on standard HTTP verbs and whatnot, is at least as RESTful as most other things that use the term.
While I completely agree that PostgREST's qualifier is entirely as worthless as every other thing calling itself REST, I don't think that's praiseworthy.
> Despite the imprecision
It's not imprecision, it's actively lying. It doesn't do what it says on the tin, and the tin doesn't describe what it actually do.
REST is an actual acronym, not an arbitrary trademark, it stands for words which have meaning.
[1]: http://postgrest.org/en/v7.0.0/api.html#resource-embedding
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).
- MySQL / MariaDB
- SQL Server / MSSQL
- SQLite
- and Postgres
https://github.com/xgenecloud/xgenecloud/
We do support instant GraphQL as well!
(Full disclosure : Im the creator)
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.
You can see it on this page: http://postgrest.org/en/v7.0.0/
It may be good for small-medium projects but when you process millions of heavy computing requests - its not for that.
I assume you were using it with high throughput? We are benchmarking it at around 2000 request/s now [1], and finding it's better to scale it horizontally rather than vertically.
> process millions of heavy computing requests
Was this reads from the database? Was the compute happening inside a Postgres function/view?
[1] Benchmarks https://github.com/supabase/benchmarks/issues/2
The project (postgrest) has too many disadventages at that scale for us.
https://github.com/majkinetor/postgrest-test
Also on nix by steve-chavez
This is what it takes to implement user auth.
Syntax highlighting would help understanding as well.
Edit: Forgot the URL: https://postgrest.org/en/v7.0.0/auth.html#sql-user-managemen...
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)
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/post...
Tabular Data Stream https://en.wikipedia.org/wiki/Tabular_Data_Stream
[1] https://www.postgresql.eu/events/pgconfeu2019/sessions/sessi...
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?
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.
Can you elaborate this part? Does PostgREST does that? If not, any example of it
> It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning.
Which is, you'll notice, the same best practice used for decades by DBAs who need to serve multiple applications connecting to the same database.
(Also, it's not exactly uncommon that a data store API would have a single client which you control - eg. a single webapp, an internal application... in which case you can go nuts with breaking changes.)
Clearly, the Database's job contrary to what the name suggests is to not only stick data into it but also a bunch of business logic that's essentially untestable. No one needs domain models when you've got database tightly coupled with behavior and logic of your business.
What could go wrong?
The whole point of the API is that it is a repository for accessing the data from one or more databases, and packaging it up for the user to consume. It imports domain models which are totally isolated from the rest of the dependencies. The data doesn't even have to be in the database and can come from multiple sources such as S3 or whatever. It can consume data from the user and kick off various processes or insert into the database. It is a generic interface that does more than just CRUD in the DB.
There is a risk of mixing storage/business logic, and you might end up being tied to postgres, but it's manageable using functions/views/schemas. And its also a really convenient place to do some business logic, as all your data is right there and available via a very expressive/extensible query language.
It is, however, a pretty common design to have all your data flowing into a database, and being served from there. Indeed, it's probably the simplest and most common design for a basic web application. PostgREST or Hasura can do excellent work there.
In the end they are projects with semi-same goals but different approaches. I went with Postgraphile and I haven't regretted it one bit, but no reason you can't try both, they are easy to set up and get a lay of the land.
Regardless, that's no reason to say it's "not a good idea at all." having a schema change ripple through the stack is just a trade-off, which is often even desirable. If engineers are shipping code to production with postgrest, and attributing it in part to their success, maybe reconsider the rigidity of your architectural thinking. https://paul.copplest.one/blog/nimbus-tech-2019-04.html#api-...
It's an excellent idea.
> One of the main point of a REST API is to abstract away your internal data structures for the clients that makes sense.
One of the points of database views, which are much older than REST, is to abstract away your internal data structures behind facades that make sense for the clients.
> Also refactoring and database migrations should NOT change the public API of any software, especially not a REST API.
That's...inaccurate. Refactoring, including of the base storage layer shouldn't. The exposed schema of a database is an API, which is logically distinct from the base storage layer. Some designs may just expose base tables and handle mapping for the client outside of the database, but there's no fundamental reason it has to be that way.
> Now, when using something like this, even the most basic change will have a ripple effect on your whole infrastructure and break every client.
No, it won't. PostgREST exposes a single schema. There's no reason that schema should contain low-level implementation, just the views defining the public API.
It makes sense to put all the logic into DB views and ditch the domain models. Forget unit tests and functional tests against company's business logic - those are pretty obsolete concepts. We don't need robust software, we need quick and dirty ducktaped APIs. Who needs data validation?
DB views are a mechanism for representing domain models just as much as classes in an OOP language are.
> Forget unit tests and functional tests against company's business logic
Implementing functionality in the DB changes how you implement and execute tests, but it doesn't prevent testing.
If you are using a DB at all and don't understand how to test functionality implemented there, that's a problem, sure, but the correction to that problem isn't to just minimize the functionality in the database and continuing to fail to test it.
Views can be writable (views with non-trivial relations to base tables require explicit specification of what behavior to execute on writes through triggers, but that's obviously true of classical domain models, too.)
Let me raise a few questions for you:
- Let's say we want to change the database from postgres to Oracle in the future. How do I go about doing it?
- How about complex logic that needs to be done in a declarative language such as SQL? SQL was not developed to write logic. You could do a lot of things in it. It is turing complete but doesn't mean you should.
- How do you debug SQL views?
- How about version control and updating views, with traceability?
- Do you think SQL is more readable for logic code than say Python? Surely, basic logic can be represented in SQL. But IMO it suffers readability.
- How about CPU/memory consumption and how do you manage to vertically scale?
You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.
If by “database” you mean “storage backend” (either in whole or in part), then the answer is oracle_fdw.
If you mean the API implementation, then, just as if you wanted to use a different language/platform when it wasn't implemented via Postgres originally, it's a complete reimplementation of that layer.
But, really, changing DBs isn't a root need, it's a solution, and unless we know the actual problem, we can't determine a solution (and “switch DBs to Oracle” likely isn't the best solution.
> How about complex logic that needs to be done in a declarative language such as SQL?
Complex logic is often more clearly expressed in a declarative language.
OTOH, to the extent there is a need for procedural/imperative logic, Postgres supports a variety of procedural languages, including Python.
> How about version control and updating views, with traceability?
There are a number of variations on approaches for this with database schemas in devops pipelines. Whether your schema is just base tables or includes views/triggers/etc. doesn't really make any difference here.
> Do you think SQL is more readable for logic code than say Python?
I think most appropriate of SQL, pl/pgSQL, and Python for each component is more readable than just-Python
> You're trying to use a tool (views), not for its intented purpose. It was not meant for sticking your company's entire domain model.
That’s...exactly what views were designed for. For a long time the implementations in most RDBMSs weren't fairly limited, but that's not really the case now.
> This is a completely wrong approach especially in enterprise environment. Might be ok with a small project.
Honestly, LOB apps in an enterprise environment is probably where this approach is most valuable. It might not be right for your core application in a startup where you are aiming for hockey stick growth. At least, from the complaints I've heard about horizontally scaling Postgres, I'd assume that.
I love this book and it explicitly addresses a lot of painpoints in designing complex systems. For example, business counterparts might request writing to CSV instead of database. You want to decouple domain models from RDBMS.
Views / stored procedures are recommended to provide APIs.
- Reads(simple, filtering by primary key): 2k req/s
- Writes: 1.5k req/s
Is security enabled or not ? Whats the load on the database, how it behaves during load?
Thats a really vague statement you did there.
> 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.
I worked on a CRUD service for three months before replacing it with Hasura.
So you get worse performance and harder to maintain code.
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.
It is disfavored by some people for good reasons. I don't think it's generally disfavored even if it should be, and it's definitely not deprecated.
I don’t see the benefit of Heroku anymore when the self hosting is this simple
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?
What RDBMS did you use in on those times? PostgreSQL is pretty powerful these days(RLS, Extensions, FDWs, json functions, etc). I'm lead to believe that older RDBMS's didn't offer all the rich functionality we have today.