11 ms·
PostgREST – Serve a RESTful API from any Postgres database
- anonu 4y agoThis is getting close to a feature I've enjoyed in KDB for some time. The open port in KDB is overloaded to handle different protocols, like HTTP.
- AnEro 4y agoBig fan of Supabase and bigger fan of PostgRest, I have so many weird side projects I wouldn't have done if it wasn't quick and easy to set up this stuff.
- turbobooster 4y agoI have an old Django web app that needs to go REST since it's coupled withe the front end I now want to use Vue. The problem is the dang views/controllers. I would have to rewrite those?
- melony 4y agoHave young'uns these days not heard of ajax?
- clintonb 4y agohttps://www.django-rest-framework.org/ https://www.django-rest-framework.org/
- sidmitra 4y agoInstead of Vue, have you considered htmx with django-rest-framework? See https://htmx.org/essays/spa-alternative/ https://htmx.org/essays/spa-alternative/
- umangsh 4y agoDRF (https://www.django-rest-framework.org/ https://www.django-rest-framework.org/) provides a lot of functionality out of the box for REST views. In addition, DRF braces (https://django-rest-framework-braces.readthedocs.io/en/latest/ https://django-rest-framework-braces.readthedocs.io/en/lates...) helps share form and serializer objects reducing a lot of duplicate effort.
- AnEro 4y agoI'd say you could either just use vue + django and have use AJAX to fetch data from your Rest API of a database. Theres ways to handle auth aswell but its annoying. Or just not use vue with this project, personally I think we've overestimated how much interactivity/real time updates are needed in an MVP. I personally prefer to rewrite stuff entirely, cause there is always newer shinier solutions, but I also try to keep my projects small or scrappy because of this* *when it comes to side projects and fun projects that I am the primary user
- PreInternet01 4y agoPreviously discussed: 7 years ago: https://news.ycombinator.com/item?id=9927771 https://news.ycombinator.com/item?id=9927771 3 years ago: https://news.ycombinator.com/item?id=21435195 https://news.ycombinator.com/item?id=21435195 2 years ago: https://news.ycombinator.com/item?id=25159097 https://news.ycombinator.com/item?id=25159097 1 year ago: https://news.ycombinator.com/item?id=29389576 https://news.ycombinator.com/item?id=29389576 It's an insanely cool project, but I've yet to find a truly fitting use case for it. In theory, PostgREST combined with something like https://marmelab.com/react-admin/ https://marmelab.com/react-admin/ should give you a free back-end and admin panel for most projects, but in practice, I've always found that all kinds of 'small details' won't be quite right out of the box, and that customization is really hard...
- tomberek 4y agoI’ve used Prest (the Go reimplementation of PostgREST) because I could more easily customize or bring it in as a library in a larger application.
- gregwebs 4y agohow do you customize it and use it as a library? Using it as a framework is deprecated now? I didn't see docs talking about this.
- tomberek 4y agoPretty sure I started with this: https://github.com/prest/prest/blob/main/cmd/root.go https://github.com/prest/prest/blob/main/cmd/root.go And from there you can execute your own command and add handlers or other things as you wish.
- euroderf 4y agoI wonder if there's an SQLite version somewhere.
- __oh_es 4y ago
- valstu 4y agoAre there any ”api from postgres db” projects that are written in Node.js?
- smt88 4y agoYes, but like most large FOSS Node projects, they're dead or under-maintained (huge number of GitHub issues). There are none I'd use today.
- AprilArcus 4y agoGraphile (https://www.graphile.org/ https://www.graphile.org/) and Prisma (https://www.prisma.io/ https://www.prisma.io/)
- Sujan 4y agoPrisma has pivoted to be “just” a Node.JS ORM a few years ago (I work there). You can still use it to build an API of course, but you will need some additional tool or libraries.
- RedShift1 4y agoWhy did Prisma pivot to this? It sounded to me that offering a ready-to-go GraphQL API ala Firebase was a great idea and could certainly turn good profits.
- Sujan 4y agoMaybe. We observed that people migrated off as soon as the applications went into production or were serious, as the database<->api approach did not actually give them all the flexibility they wanted. (This was 3 years ago, the players that stayed in that market or entered later, of course learned from that and adapted. So I hear they are making their users happy. As are we as an ORM.)
- aobdev 4y agoI was about to say “but this one is!” and realized I had confused PostgREST with PostGraphile. If you’re interested in GraphQL, you can check out PostGraphile here: https://github.com/graphile/postgraphile https://github.com/graphile/postgraphile It’s interesting to me that PostgREST and Hasura are written in Haskell, but PostGraphile is not. Given the complexity in parsing arbitrary GQL queries I think I assumed that Haskell was a better fit for the job, hence the mixup!
- twistedcheeslet 4y agoDoes anyone have experience integrating 3rd party auth systems (ex Keycloak) with Postgrest? How did that experience go?
- liamconnell 4y agoI think you can integrate it with nginx, which would work well with postgrest.
- fulafel 4y agoNot exactly the personal experience you asked for but https://postgrest.org/en/stable/ecosystem.html https://postgrest.org/en/stable/ecosystem.html has receipes for oauth2 including Keycloak.
- awalias 4y agowe integrated Gotrue with PostgREST at Supabase and it works beautifully together https://github.com/supabase/gotrue https://github.com/supabase/gotrue (forked from Netlify)
- nick__m 4y agoI did it with Azure AD application roles in oauth tokens, it was a non event.
- college_physics 4y agohas any django fan used this project as an alternative to the django rest framework? (using postgres as the backend to django and serving the REST API directly). any thoughts about advantages / disadvantages?
- gregwebs 4y agoThese projects look great for reading data (although there are times where I would prefer to transform the data server side, that's not a deal breaker). However, they always seem lacking to me for writing data if I need to add business logic (I don't want to do this with triggers). Then if I am going to write an API for writing data, just adding some for reading data doesn't seem like much effort, although I see the value proposition as frontend development getting a very consistent API without having to ask the backend to develop APIs.
- agentultra 4y agoYou can use a library like https://github.com/agentultra/postgresql-replicant https://github.com/agentultra/postgresql-replicant or similar to hook in a control plane and use PostgREST as a data plane. Your business logic works on the event stream that comes from the WAL.
- gregwebs 4y agoI want synchronous validation with business logic. After the synchronous validation event streaming could be useful.
- Twisell 4y agoYou should be able to implement business logic sever side with stored procedures in plain PL/pgSQL OR with various supported procedural languages depending on your need (included with base distribution or through extensions). Same for transformation server side, unless I'm wrong, you are not limited to ORM like construct. You can call a stored procedures and only the result of server side computation is sent back.
- dventimihasura 4y agoWhy don't you want to write triggers?
- majkinetor 4y agoThey are considered GOTO of db. Good luck debuging complex trigger hierarchy.
- kiwicopple 4y agoPostgREST 11 is in pre-release too - Steve (the maintainer) wrote about some of the upcoming features here: https://supabase.com/blog/postgrest-11-prerelease https://supabase.com/blog/postgrest-11-prerelease (Disclosure: this points to the supabase blog and I work at supabase (as does Steve)) Some of the features include - Spreading related tables - similar to a JS "..." spread operator to flatten a response - Order by on related tables - "Anti-Joins" - e.g. filter the rows where the related table is null.
- MuffinFlavored 4y agoI'm kind of confused why GROUP BY and "DISTINCT" aren't prioritized as features. I know the workaround (create a view) but I'm a little curious why it isn't seen as a "I expected this to already be there" shortcoming for these rather "basic" features. I'm well aware the open source attitude means "go and fork it + PR it yourself if you want it so bad". Easier said than done as an outsider into a massive Haskell codebase. Transactions (2015): https://github.com/PostgREST/postgrest/issues/286 https://github.com/PostgREST/postgrest/issues/286 GROUP BY/DISTINCT (2017): https://github.com/PostgREST/postgrest/issues/915 https://github.com/PostgREST/postgrest/issues/915
- SOLAR_FIELDS 4y agoI agree 100%, I have been using supabase a lot over the last few months and the #1 glaring wart on an otherwise pretty good system is the lack of aggregates. Feels like a table stakes feature to at least support the very basic stuff like max, average, sum since any app that isn’t the most basic of basic apps is going to need those. And yes, I’m aware you can use an RPC or a view to work around that, but doing so very much black boxes your application logic into SQL and should be heavily avoided for several reasons and using those things come with their own limitations as well (can’t use Supabase Realtime on RPC or view, as one example).
- jpdb 4y agoI don't really understand the value of a project like PostgREST. It feels like you're coupling your application schema to your database schema, which is something you generally want to avoid. Is this only for niches where you are ok with the db schema being tightly coupled? Do you use specific views to decouple the two schemas? In that scenario it seems like you might eventually get to a point where your view is more complicated than setting up a more traditional application.
- dventimihasura 4y agoI don't want to avoid that.
- vore 4y agoThen I think you are in for a world of pain when you need to e.g. change how the underlying storage of your data looks but don't want to change the end user API. A lot of the time, the access patterns of an end user talking to your backend really don't match up to the access patterns of your backend talking to your database.
- dventimihasura 4y agoI have a layer of indirection between my end user API and my underlying storage, so that I can change the storage without changing the API. There's no pain involved.
- vore 4y agoThen... why not just use Postgres directly from your end user API's backend? You might as well use an ORM and cut out a layer of overhead from having to marshal data in an out of PostgREST and point of failure from having to run it.
- dventimihasura 4y agoPostgreSQL + PostgREST IS my "API backend." They're one and the same. There are no other layers. Using an ORM would ADD a layer, not subtract one. Perhaps what you're asking is, "Why not just have your user interface connect directly to PostgreSQL and issue SQL statements?"
- davidjfelix 4y agoI love PostgREST and have deployed it several times alongside existing postgres databases to improve developers lives. That being said, I think it's a little funny because it epitomizes APIs that I see people build where ACID compliance is a huge "must" for their database choice and then they expose resources which can only be independently updated in a manner that fails to expose ACID beyond the scope of individual resources. I think this is funny because it really makes you question if they ever actually needed full mutli-table transactions at all or if they could have used a different database all together. Anyways, it's a really cool tool.
- remram 4y agoHow hard would it be to do the opposite? Expose a postgres API that people can use by sending "SELECT" queries from psql or a postgres connector? Postgres has access control and an extension mechanism. Would the easiest be to use a real postgres server?
- mLuby 4y agoThat's just GraphQL with fewer steps.
- awb 4y agoI thought about this too, but there are a few things to consider: 1) you need to separate any non-queryable data into it’s own table. So you’d need _public and _private tables for most data types 2) any type of filter query will need a JOIN, which leaves you vulnerable to malicious long running queries 3) some tables you don’t want people to run SELECT * on, otherwise they can get your entire site data 4) you need to be really careful with DELETE, UPDATE and INSERT queries to prevent malicious behavior Maybe one way would be to write a bunch of whitelisted regex patterns for allowed queries, but you’re definitely playing with fire allowing someone to send SQL queries to your DB.
- remram 4y agoI'm not sure why you would put data you don't intend to be queried in the same database. As for DoS, I know postgres can impose limits (from having used the crt.sh database, which is available publicly), though I am not sure why you think so many JOINs would be required.
- awb 4y ago> I'm not sure why you would put data you don't intend to be queried in the same database Things like personal user / marketing info, internal data / metrics, etc.
- sigstoat 4y agomaybe foreign data wrappers would be relevant to your interests? i believe there are already a number of them which expose non-databasey things.
- jmull 4y agoThis is great in a lot of ways... A robust and far more comprehensive version of something I do in db-based apps. But... I'm leery of basing the auth on postgres' auth. It just doesn't seem up to expressing auth rules based on dynamic, app-specific business rules. (Maybe I just don't know postgres' auth mechanism well enough, though.) Also, it's perhaps overly complicated in pointless ways. A lot of this is mapping HTTP requests to SQL... why not just accept SQL and cut out all the unnecessary mapping? E.g. (real encoding not show for clarity) GET /theapi?sql=SELECT name, age FROM Person WHERE age >= :1&p1=21 It seems crazy to me that it defines an entire HTTP-based query language when SQL is right there to be used directly. Going to SQL directly makes the API far simpler and more powerful.
- deleted 4y ago[deleted]
- jeff-davis 4y agoIf someone can write their own SQL, it would be trivial to DoS the system. My guess is that the sublanguage is meant to prevent that (but it's just a guess).
- vbezhenar 4y agoThis sublanguage will either be useless or will not prevent DoS. The proper way to prevent DoS is to implement some kind of resource constraints for database queries. AFAIK for postgres every connection launches a separate database process. So in theory you can craft some kind of ulimits or cgroup limits which would restrict a process to a limited amount of RAM, CPU or IOPS. So if a given process will eat more RAM, it'll be killed, if it wants to mine bitcoins, it'll be throttled and killed by timeout eventually.
- taffer 4y ago> This sublanguage will either be useless or will not prevent DoS. Thinking in absolutes is not very helpful. PostgREST's API is designed to prevent DoS and it is flexible enough for most of your queries and when it isn't you can write a custom function and PostgREST will expose it for you as '/rpc/<my_function>'.
- defanor 4y agoAt work, we've finally replaced a large part of a custom (mostly-)web backend with PostgREST recently, and that's quite a relief: considerably less code to maintain in that project now, and that was a rather awkward code. Something akin to PostgREST's "Embedding with Top-level Filtering" [1] had to be provided for all the tables, with OpenAPI schema and a typed API (Haskell + Servant); I avoided manually writing it all down, but at the cost of poking framework internals, and maintainability suffered. It was particularly annoying that the code doesn't really do anything useful, except for standing between a database and an HTTP client, and simply mimics the database anyway. Whenever a change had to be introduced, it was introduced into the database, the backend, and the frontend simultaneously, so it wasn't even useful for some kind of compatibility. Now PostgREST handles all that, and only a few less trivial endpoints are handled by a custom backend (including streaming, which I'm considering replacing with postgrest-websocket [2] at some point). During the switch to PostgREST, the encountered minor issues were those with inherited tables (had to set a bunch of computed/virtual columns [3] in order to "embed" those), and with a bug on filtering using such relations (turned out it was an already-fixed regression [4], so an update helped). Also a couple of helper stored procedures (to use via /rpc/) for updates in multiple tables at once (many-to-many relationships, to edit entities along with their relationships, using fewer requests) were added (though the old custom backend didn't have that), the security policies were set from the beginning, the frontend was rewritten (which allowed to finally switch without adding more work), so it was only left to cleanup the backend. Not using views, since as mentioned above, database changes usually correspond to frontend changes, and the API doesn't have to be that stable yet. Happy with it so far. [1] https://postgrest.org/en/stable/api.html#embedding-with-top-level-filtering https://postgrest.org/en/stable/api.html#embedding-with-top-... [2] https://github.com/diogob/postgres-websockets https://github.com/diogob/postgres-websockets [3] https://postgrest.org/en/stable/api.html#computed-virtual-columns https://postgrest.org/en/stable/api.html#computed-virtual-co... [4] https://github.com/PostgREST/postgrest/issues/2530 https://github.com/PostgREST/postgrest/issues/2530
- sigmonsays 4y agoWhen does this hurt your scaling ability? From my experience, scaling a typical site has always been bottlenecked in the database. http workers are generally stateless and easily scalable. Where it gets tricky is optimizing the database reads, caching, and invalidation. Is the intended use case just simple CRUD of data that it not under extreme read or write loads? It seems like you could quickly build CRUD for tables in _language of choice_ and still have the ability to optimize reads with caching later..
- cpursley 4y agoYou can cache the API responses.
- majkinetor 4y agoI highly recommend this. Its so fast that it is crazy. I got 2k req/s on basic tests. Its filtering DSL is awesome, and should probably get supported by commune: https://postgrest.org/en/stable/api.html#operators https://postgrest.org/en/stable/api.html#operators Note that I didn't use it yet in production settings, but intent to. Auth do seem a bit complex. For anybody wanting a quick test on Windows: https://github.com/majkinetor/postgrest-test https://github.com/majkinetor/postgrest-test
- FlyingSnake 4y agoShameless plug: I once went down the PostgREST rabbit hole and loved it. I wrote a series of articles on how to use it to create a full fledged mobile app. I hope this helps someone trying to wet their feet in Postgrest. https://samkhawase.com/blog/postgrest/ https://samkhawase.com/blog/postgrest/
- MuffinFlavored 4y agoFor how often Nix is mentioned here on HackerNews, has anybody looked at the Nix config files for this repo to build it locally? https://github.com/PostgREST/postgrest/blob/main/cabal.project.non-nix https://github.com/PostgREST/postgrest/blob/main/cabal.proje... https://github.com/PostgREST/postgrest/blob/main/default.nix https://github.com/PostgREST/postgrest/blob/main/default.nix https://github.com/PostgREST/postgrest/blob/main/shell.nix https://github.com/PostgREST/postgrest/blob/main/shell.nix https://github.com/PostgREST/postgrest/tree/main/nix https://github.com/PostgREST/postgrest/tree/main/nix I couldn't for the life of me figure out how to play with this. I kind of don't believe "docker build ." is worse but I might be missing something. After I ejected out of playing with it on Mac OS, I found: https://github.com/NixOS/nix/issues/458#issuecomment-1019743603 https://github.com/NixOS/nix/issues/458#issuecomment-1019743... Over 13 parts to remove NixOS from Mac OS, involving reboots, /etc/fstab, OS level users, daemons, etc.
- password4321 4y agoPrevious mentions of / requests for something similar for SQLite: https://news.ycombinator.com/item?id=33894995#33897716 https://news.ycombinator.com/item?id=33894995#33897716 https://news.ycombinator.com/item?id=33484693#33485145 https://news.ycombinator.com/item?id=33484693#33485145 https://news.ycombinator.com/item?id=33078798#33119873 https://news.ycombinator.com/item?id=33078798#33119873 https://news.ycombinator.com/item?id=30636796#30637326 https://news.ycombinator.com/item?id=30636796#30637326 https://news.ycombinator.com/item?id=24442294#24442876 https://news.ycombinator.com/item?id=24442294#24442876 Any recommendations or anecdotes today?
- xputer 4y agoNot exactly the same but pocketbase is pretty neat. It's like firebase/supabase but then using SQLite. You can try it out here: https://pockethost.io/ https://pockethost.io/
- nesarkvechnep 4y agoNot RESTful at all. Not a mention of hypermedia.
- shinycode 4y agoThe French government has a open data GitHub with open source projects. One of the project which list all the data for entreprise addresses uses Postgrest (open data SIRENE de l'INSEE). Full fledge project implemented, interesting use of PostgREST https://github.com/etalab/annuaire-entreprises-sirene-api https://github.com/etalab/annuaire-entreprises-sirene-api
- BerislavLopac 4y agoI'm a big fan of Sandman 2 [0], which supports a much wider set of databases - basically everything supported by SQLAlchemy [1]. [0] https://sandman2.readthedocs.io https://sandman2.readthedocs.io [1] https://www.sqlalchemy.org/ https://www.sqlalchemy.org/
- reactspa 4y agoIs there anything like this for MySQL/MariaDB?
- ruslan_talpa 4y agoSoon (in the works now, it will be particularly interesting in the context of cloudflare workers talking to planetscale) https://github.com/subzerocloud/showcase https://github.com/subzerocloud/showcase
- jinmingjian 4y agoShameless insertion: We release our new HTTP interface with our free AIoT database - JoinBase: HN discuss: https://news.ycombinator.com/item?id=34181591 https://news.ycombinator.com/item?id=34181591 or the blog link: https://joinbase.io/blog/http-interface/ https://joinbase.io/blog/http-interface/ Different to PostgREST, the HTTP interface of JoinBase is integrated in the database. So, no verbose setup, one binary rules them all! Our HTTP interface is inspired from the ClickHouse, but we provide 100x message throughput than that of ClickHouse in the HTTP interface. JoinBase's HTTP interface is so fast that you can use it to provide unlimited production-level REST services without any worry. If someone are interesting for JoinBase, just request the free distribution here: https://joinbase.io/request/ https://joinbase.io/request/
- jackhalford 4y agoI love postgrest, for a read only API it is saving me entirely from writing any backend code. Today PRQL is on the frontpage, I wonder if postgrest could support it? edit: PRQL compiles down to SQL, so it should be possible to use it for postgrest, it just adds an extra step