PostgREST – Serve a RESTful API from any Postgres database | Hacker News Reader

PostgREST – Serve a RESTful API from any Postgres database

postgrest.org

304 pointsthunderbong138 comments

Previously discussed: 7 years ago: https://news.ycombinator.com/item?id=9927771 3 years ago: https://news.ycombinator.com/item?id=21435195 2 years ago: https://news.ycombinator.com/item?id=25159097 1 year ago: 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/ 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...

I’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.

how 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.

Pretty sure I started with this: 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.

I wonder if there's an SQLite version somewhere.

More feature rich but perhaps this? Been dying to toy with this and fslite

https://github.com/pocketbase/pocketbase

Absolutely love pocketbase.

And, interesting to note that using triggers within pocketbase isn't well supported because they do a dry run insert to check against constraints and then delete it if the constraint fails.

https://github.com/pocketbase/pocketbase/discussions/650#dis...

I mention this because the OP inquired about stored procedures, somewhat related.

Still, pocketbase is so amazing.

https://datasette.io/?

Thanks to these comments I've taken a deeper look at both Pocketbase and Datasette. I wonder, could I use replication to keep the DB of Datasette (operating in read-only mode) in sync with the DB of Pocketbase.

Is supabase what you're describing?

I could be wrong but I think they use postgREST underneath.

Yes, it definitely does.

I've always wondered about this without trying it myself. Isn't this be something that looks ideal to start with, but as the project progresses and the edge cases keep stacking, you end up writing a complete middle layer, which effectively turns out to be your own framework?

Most of the edge cases can be handled using stored procedures on the database, which can be called in PostgREST using a generic '/rpc/<name>' endpoint. You can use foreign data wrappers to handle almost all the remaining edge cases, though that has diminishing returns in terms of time savings vs having a separate service as cases increase in complexity.

Complexity is often unavoidable. The question is where do you put it?

> Most of the edge cases can be handled using stored procedures on the database,

This is what actually turned my off about it when I used it for a small hobby project: the tooling around programming in SQL is absolute crap.

Expressing business logic in stored procedures is great in theory, but in practice only the very simplest business logic will be written as stored procedures, because anything non-trivial is impossible to debug, impossible to log properly and there is never a call-stack available when the inevitable runtime error/exception occurs.

If the tooling for writing, debugging, deploying and testing stored procedures were up to the level of what Turbo Pascal was in the 80s, I'd do it in a heartbeat.

As things stand, the only way to figure out bugs in the system is to visually inspect the stored procedure source code.

I've seen a very complex payroll written in stored procedures. You're right about debugging, it's horrible. Once you get good at it there is an elegance to the solution but refactoring sux. Then there is performance tuning. You can get things so fast because you don't have to fetch from the db, you're already at the data source. You can also get things so slow as your loaded up on complex queries.

The tooling support definitely needs to improve. Postgres has support for emitting notices in stored procedures, but the lack of debugging is big a minus.

If you write code in PLV8 you can mock Postgres specific global variables to debug code using Node, and testing is pretty smooth with PGTAP. The deployment story is still something every shop has to reinvent at this point though.

> looks ideal

It's the opposite, this is a stupid idea by the sound of it, as it goes against what we've learned for decades in this industry. You yourself speculated what could be wrong with it, not exactly hard to see.

Who knows, it might be great once you actually do it, but honestly I dont think so.

Thanks! Macroexpanded:

Building single-page-apps with PostgREST - https://news.ycombinator.com/item?id=30132947 - Jan 2022 (84 comments)

PostgREST 9.0 - https://news.ycombinator.com/item?id=29389576 - Nov 2021 (121 comments)

PostgREST: REST API for any Postgres database - https://news.ycombinator.com/item?id=25159097 - Nov 2020 (205 comments)

PostgREST - https://news.ycombinator.com/item?id=21435195 - Nov 2019 (237 comments)

PostgREST – A fully RESTful API from any existing PostgreSQL database - https://news.ycombinator.com/item?id=13959156 - March 2017 (87 comments)

PostgREST – REST API from any PostgreSQL database - https://news.ycombinator.com/item?id=9927771 - July 2015 (204 comments)

Automatic REST API for Any Postgres Database - https://news.ycombinator.com/item?id=8831960 - Jan 2015 (68 comments)

This 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.

If 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).

This 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.

> 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>'.

I think that's separate from the language though. I'm not saying it would process any SQL... it can be restricted to whatever function and SQL patterns that are deemed acceptable. Realistically, though, your language is going to be seriously inflexible or allow DoS... In fact I think you're typically more likely to hit seriously inflexible before excluding the possibility of DoS. So I think you're going to need something besides the query language to prevent DoS anyway.

PostgreSQL row-level security (which PostgREST defers to) is very flexible, more or less anything expressible as a SQL query can be used to filter what the user can see.

You can see an example here: https://postgrest.org/en/stable/auth.html#roles-for-each-web...

The main downside of RLS is that complex queries may require hand-optimizing.

Because queries like this will kill any db, and you don’t need any privileges

SELECT crypt( encode(digest(gen_random_bytes(1024), 'sha512'), 'base64'), gen_salt('bf', 20) )FROM generate_series(1, 1000000)

More explanations here https://www.freecodecamp.org/news/stop-calling-postgrest-mag...

Wouldn't it be much easier to disable/restrict certain sql functions than do all of the http->sql mapping?

No, as there are countless ways to do the same. Whitelisting is more effective if your code is running on untrusted devices (frontend)

Postgres can limit queries to only given number of milliseconds.

Give me access to your PG with a user that has no privileges, can not run queries longer then X ms and see how easy it would be to DOS the db :)

Postgres's row level security is absolutely sophisticated enough to model arbitrary business security rules, probably with more precision than almost any other auth system in widespread use.

The issue with using it is more around its complexity, and difficulty verifying the constraints without building custom tooling for that. You almost need something like formal methods to model your security model.

But it's an extremely powerful security tool that if you're using postgres at all, is probably worth the effort spent learning and maintaining. And if you're going that far, you already have the skills and tools to run all your auth on it so why not.

I started using RLS in the Supabase offering and am now a total fan of it. The supabase implementation, in particular, allows you to expose the API to javascript which is cool! When you use RLS that way, users get their own tokens to pass in the session which limits the records in the db they have access to. So, if you had a token stolen - the db would limit access to just that account.

I'll second this; Supabase obviously popularized this whole method of doing things, but once you've worked in it it genuinely feels like the first step forward this industry has had in five years.

One of my related pocket theories for the next ten years: many people keep saying AI is going to eat jobs. Probably not in engineering, at least for a while. But, we're starting to see a new generation of software companies being built on these kinds of truly new-way-of-doing-things technologies, whether that's Postgrest/RLS, functions as a service, or no-code tools like Retool. The productivity and efficiency advantage that these companies will have over the incumbents is incomprehensible.

I think a lot of legacy programmers won't adjust. Its hard to admit to yourself that, honestly, we're spending most of our days doing really freakin similar things as other engineers as other companies; and we as an industry are starting to solve these problems in more and more off the shelf ways.

Which is all to say, the three-fold theory/prediction is: "backend engineering" will experience the least growth, or even highest decline, in job openings of any software engineering specialty over the next ten to fifteen years. The effort involved in it will move left-and-right on the spectrum: higher demand for frontend engineers, and higher demand for DevOps/Cloud/etc. We will see billion dollar software companies selling software as a service with the vast majority of their "backend stack" having no code (potentially some number of serverless functions to plug gaps in no-code capabilities).

I totally agree that it feels like the right way, at least for my values. However, I struggle to apply modern software practices. Am currently struggling with writing a function that is triggered by an insert, said function needs to join the new row to another table. Without a good debugging/iteration environment, it's going to be another example of a great product that fails b/c of bad dev experience.

We do it exactly this way in ClickHouse, and it works perfectly.

You can even expose the database server to the internet and get away with it if you configure the user access control, quotas, and limitations on query complexity.

For example, these services:

https://play.clickhouse.com/play?user=play

https://ghe.clickhouse.tech/

https://pastila.nl/

https://aretestsgreenyet.com/

work this way by querying ClickHouse directly.

ClickHouse has REST API as a first-class feature.

How do you determine query complexity? Do you go off of the query plan or something like looking at the columns, joins, etc.?

There are only a few ways to determine it before running the query - by the amount of rows/bytes to scan. But most of the logic happens when query is already started - by the possibility to stop it and throw exception if some metrics became too large.

For example, this query will not start, because the amount of rows to scan is greater than the limit: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT...

This query will fail with the error: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT...

  Code: 160. DB::Exception: Estimated query execution time (580.1882010677873 seconds) is too long. Maximum: 60. Estimated rows to process: 9946243959: While executing MergeTreeThread. (TOO_SLOW) (version 22.13.1.294 (official build))

I see. How do you determine when to kill the query before running it then? Of course you could do “if the process is running longer than X seconds kill and returns NNN status code” but that feels somewhat wasteful if you can be smarter when calculating the possible cost.

The cost can be calculated, but not always.

Example 1:

  SELECT ... FROM table WHERE key BETWEEN c1 AND c2

- here we can get the number of records and bytes to scan before even starting the query.

Example 2:

  SELECT ... FROM table WHERE key = (SELECT ...)

- here we cannot know how many records will be scanned until we will finish the subquery.

Maybe some estimations can be applied... but we don't do that.

> why not just accept SQL and cut out all the unnecessary mapping?

You might be interested in what we're building: Seafowl, a database designed for running analytical SQL queries straight from the user's browser, with HTTP CDN-friendly caching [0]. It's a second iteration of the Splitgraph DDN [1] which we built on top of PostgreSQL (Seafowl is much faster for this use case, since it's based on Apache DataFusion + Parquet).

The tradeoff for allowing the client to run any SQL vs a limited API is that PostgREST-style queries have a fairly predictable and low overhead, but aren't as powerful as fully-fledged SQL with aggregations, joins, window functions and CTEs, which have their uses in interactive dashboards to reduce the amount of data that has to be processed on the client.

There's also ROAPI [2] which is a read-only SQL API that you can deploy in front of a database / other data source (though in case of using databases as a data source, it's only for tables that fit in memory).

[0] https://seafowl.io/

[1] https://www.splitgraph.com/connect

[2] https://github.com/roapi/roapi

(Replying to own post)... thanks for the info about Postgres RLS/row-level security. I guess I should not be so leery...

Looking at the last project I did, the app-level db-middleware basically consists of two things: (1) plumbing boilerplate; (2) enforces app-level auth rules, either by preventing unauthorized operations or adding required filters. It looks like I should probably look at RLE and can likely move anything app-specific out of the middleware... which might let it collapse to something entirely generic, and therefore entirely reusable, and quite possibly something I don't have to write myself.

Why not just accept SQL? It's a matter of contract. With REST APIs you specify a contract, it gets robust with time, and other software can expect only what's in the contract. SQL allows you to run arbitrary functions over data in the database, so the whole schema and parts of the content in a way become your contract - and your options to modify the schema (or how you use the database) get very constrained.

You can limit the visible schema, and thus the contract, per role with SQL.

That's a very good point!

>Why not just accept SQL?

Sanitize your inputs. I'll let little bobby tables link to the XKCD

Who says don't sanitize inputs?

I'm just suggesting to not invent another query language, especially where a rather obvious and natural one already exists. You can and should sanitize untrusted input regardless.

Yeah, but this particular contract/API is pretty much "run arbitrary select/insert/update/delete statements" anyway!

If you're using the postgrest language your options to stabilise the interface are minimal anyway, so why not just go with SQL and a whitelist?

Yes, but the REST vs SQL forces a specific API for changes. REST - update/delete a specific record. SQL - update/delete based on a filter, or a query result. I can do `set a = b` for all rows in the table, which may be needed internally, but it's a pretty bad external interface.

I did the oppisite, I needed auth in a toy application I was writing and was dreading writing and getting correct the whole auth layer, so I thought to myself "self, postgres has a very rich and through auth system, why not just use that" So I did just that, I just pass the user credentials straight through to to the database connection and let the database decide what access the user gets to the data.

I am not sure if I would do this on something serious, but it was a lot of fun to set up and saved me a lot of code by reusing the comprehensive auth system that lives with the actual data in question.

The nice thing about this approach is that if you ever develop other applications or front-ends to this database, even in some other framework, the user auth and access rules are already there.

It's been a while but does postgres let you re-auth on an existing connection? I was able to do that in Oracle and that allowed the use of connection pools rather than one connection per user, which can become a bottleneck.

You can use SET ROLE to become another user you have permission to become (I think you need to be in the same group or something) and RESET ROLE to go back to your original user. If the initial connection was made with a superuser or whatever, you could change to the user in the connection manager and reset when the connection goes back in the pool.

> why not just accept SQL

I wish. Unfortunately, sometimes a stateless HTTP REST API is simply a non-negotiable business requirement.

SQL `SELECT`-only API is a stateless API.

I tried to do it but couldn't find open source MIT licensed SQL parser which is needed to make it secure against SQL injection.

Metabase works well is such scenarios.

Haskell repositories have SQL parsers aplenty, some BSD3, at least one MIT-licensed: https://hackage.haskell.org/package/queryparser

Maybe pick a language that has better libraries for language parsing and analysis?

I 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.

I don't want to avoid that.

Then 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.

I 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.

Then... 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.

PostgreSQL + 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?"

How does that square with:

> I don't want to avoid [coupling my application schema to my database schema]

It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up. (I think your indirection layer is a good idea; I’m curious what your previous post meant in light of that.)

It doesn't. I wasn't addressing "coupling your application schema to your database schema." I was addressing "change how the underlying storage of your data looks but don't want to change the end user API" in the parent comment.

> It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up

No, because my layer of indirection is in the database in the form of views and procedures. I could be wrong, but I took "coupling my application schema to my database schema" to be something like "having your HTTP API depend on objects in the database", which it does because of the way that postgREST works. If that's the kind of coupling we're talking about, then that's the kind of coupling I would rather embrace than avoid.

We’re considering it for a use case at work. In our case, it’s to allow a team of analysts to be more or less self sufficient in publishing some data for external consumption without needing to deal with deployments and the like. This way, changing requirements can be handled by the analysts themselves by updating the tables or views published by PostgRest, without needing to think about changing a REST service and such.

Sounds like a security nightmare, highly recommend pairing them with a dedicated security minded person to ensure correct configurations of access control (networklayer/hosts, row level, resource denial of service etc)

at the very least have a read of https://postgrest.org/en/stable/admin.html

The idea is to have the IT folks managing the configuration of both Postgres and PostgRest, the analysis folks will obviously be working in SQL only.

> SQL only.

I assume you mean readonly SQL then... (perhaps creating views too)

In the past, I’ve used a specific ‘api’ schema that contains the views and functions that modifies a ‘data’ schema. You can then have multiple versions of the ‘api’ schema for versioning (with a different postgrest instance pointing at each versioned api schema). It’s possible…

Speed. There might be other advantages, but it will be much faster than any non-DB framework.

I’d want to limit it to simple schemes.

But if you’re only exposing one table- it would be pretty darn fast.

The recommended way to use Postgrest is to put a layer of views and optionally stored functions on top of your schema to decouple it from your API. Take a look at this Postgrest starter kit[1] which uses a separate API schema for this purpose.

[1] https://github.com/subzerocloud/postgrest-starter-kit

If you aren't writing a web app then you can potentially scrap the web server tier entirely, which can yield security and simplicity benefits in some cases. For example, any app where the userbase size is well known and stable e.g. internal apps, apps for medical, military, industrial use cases. In such a two-tier architecture you implement your business logic using either SQL or server extensions like PL/Java (https://tada.github.io/pljava/) and then provide users with a desktop or mobile app to access the database directly. PostgREST is useful for languages without good DB drivers, or where you need to traverse HTTP only firewalls/proxies.

Advantages:

• Get back all the time spent boilerplating and bikeshedding ad-hoc app specific REST protocols.

• Eliminates the (near) superuser privileged web servers that pose a security risk if compromised. Eliminate SQL injection, XSS, XSRF as bug classes.

• Allows smart users like business analysts to bypass the UI partially or completely and go straight to a SQL console, because end users = db users 1:1 and ACLs are understood by the RDBMS directly.

• Use UI frameworks and languages that aren't JavaScript. Use context menus, menu bars, hotkeys OS services or whatever else makes your users productive.

• Use multi-threading, files, special hardware as part of your core app architecture if you need it.

• If you can afford the server side resources: align DB transaction length with UI "transaction" length.

Obviously there are also downsides. I wouldn't write Instagram this way. Postgres doesn't scale very well to lots of connections. Oracle/MSSQL scale a lot better and have other advantages like much better blob support, but you'd have to get comfortable with the idea of building new apps on them.

You can mix and match, it doesn't have to be purist. Retain a thin, simple and rarely updated web server that just handles the requests you don't send directly to the DB e.g. for things like ElasticSearch. Or if you can (i.e. not on Supabase) write custom Postgres extensions that let you use SQL stored procedures as your RPC protocol. It has some advantages over HTTP.

Lately I've been experimenting with this design a bit. The traditional hassle has been non-web distribution to desktops. https://conveyor.hydraulic.dev/ fixes that. If you're using something like Electron or the JVM you can do a build+release cycle for Win/Mac/Linux in about 60 seconds all from your dev laptop, and you can make installed clients do a fast update check on each launch just like a web app would. There are some open questions about the best way to handle user authentication when connecting direct to a DB if you don't want passwords. The nice thing is you can e.g. bind the results of SQL query or an ORM directly into your UI toolkit. JSON, REST, custom paging code and all the other goop CRUD apps end up with just boil away.

The database schema is much harder to change than anything on the application layer. That's unarguable.

From there people mostly decide on two philosophies: "I'll write an adapter layer so that it's easy to change my data" and "I'll take those robust, fixed facts and write my application around handling them".

Honestly, I have no idea if one of those is any better than the other. I can't even say with confidence that one will lead to problems that another won't; they look equivalent to me. The choice seems to be always made based on worldview, and it's not even one of those "fast and loose" vs. "methodical" choices. All the differences I see people pointing are false ones.

I've actually worked on a large complex postgrest-based backend and the cons are all based on practical considerations imo:

  - the dev workflow on a db-as-codebase system is less familiar, less well understood, with tooling general several years behind "normal" code work.
  - branching and deployments similarly are just different in ways it's hard to prepare for, leading to low confidence in the deployed system.
  - testing and debugging: pgtap has different constraints than normal unit testing, debugging sql functions is tricky and awkward. again the tooling is missing or far behind.
  - in most profitable applications I've seen, the DB is the single largest cost and the most likely to become a bottleneck you can't loosen by throwing money at servers. having all your logic in there won't *necessarily* make this worse but it certainly won't make it better.

DBAs have dealt with all of these things for decades and they have skills and tools and mental models for them. But devs and DBAs practice different disciplines with different goals, and not everything crosses over easily. Engineers working on a system like this from either side will end up acquiring a degree of competence even expertise in the other one. Making them desirable for other employers and difficult to replace.

Overall I don't strictly prefer this approach, but it definitely has under appreciated strengths and should probably be used more. It's hard to say how it could end up if more resources were put into actually developing the tooling necessary to back it up.

> having all your logic in there won't necessarily make this worse but it certainly won't make it better.

Logic is a very broad term, and as long as you're talking about number crunching / machine learning, I'd agree. But most web or LOB applications have pretty simple logic. According to Michael Stonbraker[1], a typical OLTP DBMS spends only 4% of its processing time doing useful work, which includes any kind of business logic, among other things. The rest is spent on housekeeping tasks such as context switching and transaction management.

The more business logic you move out of the database, i.e. to the middle tier, the more roundtrips you need per transaction. During roundtrips, transactions can't do any meaningful work, which means more idle transactions, larger connection pool, more locking, and context switches.

In other words, for typical OLTP workloads, each transaction should ideally occur in a single roundtrip, which requires the logic to reside within the DBMS.

[1] https://blog.jooq.org/mit-prof-michael-stonebraker-the-tradi...

So, I'm in the middle of building a backend for the first time and I was evaluating PostgREST just yesterday.

Here's the value prop: Your database is the "source of truth" but can be accessed in many, MANY different ways. Usually via some kind of ORM system.

This can give you a head start on building a more carefully considered REST API - where it gives you the base CRUD routes for every table in an acceptable format and you can build on top of it. Or if you're accessing your DB through some other interface for your web app but need something quick to build a new face for the service like a mobile app.

I recently ditched building a traditional REST API in favor of just using what my ORM provides to interact with my DB. Something like this will come in handy if I ever need one.

There are niches where db schema IS the app schema.

Some time ago I wrote a REST wrapper for the .NET SQL connectors which allowed me to post and query data from the database. It was more than enough for my usage and I could interact with that 'service' from anywhere in the network without bothering on installing and configuring the SQL connector on the endpoints.

I could see this or something similar to it being beneficial for generating a REST API initially when the schema just happens to pretty closely fit what the REST API should be.

But after that initial step, I wouldn't want my API dependent on the schema (directly), nor would I want my database schema dependent on the API code. As a side note, that's one reason I didn't take a liking to Django.

Honestly, I'd rather have an RPC based API in the year 2023 than a REST API. REST, I think, was a bit of a mistake in terms of a source for data that would be sent to a stateful frontend as JSON. REST makes sense for webpages, but nothing about data is inherently page-like. I've run into enough quirks dealing with RESTful APIs and the libraries that claim to handle them that I think we should be looking for a better fit.

> It feels like you're coupling your application schema to your database schema, which is something you generally want to avoid.

This is an interesting statement that probably should be expanded more upon!

I agree with it, because it can be nice to be able to change how certain data is returned to any consumers of your API, for convenience or maybe some business rules. For example, you might want to aggregate data from multiple tables into a single list of JSON objects for filling out a table in some application downstream. Furthermore, you might be interested in being able to change the underlying DB schema without affecting how your API returns data, since its consumers don't necessarily care about how you name your tables or what references what internally.

At the same time, I do disagree with my own point somewhat, because you can just use a DB view for pretty much the same outcome. There's no reason why MyAppUserListViewEntity couldn't match my_app.user_list_view in your database 1:1, I'd actually argue that such a mapping for reading data would be really easy to reason about and the discoverability would be pretty good, while at the same time still letting you introduce changes as necessary.

Furthermore, there's something really nice about codegen: being able to tell some generator where your local development instance of your database is running and generating application entities with all of the mappings (for example, JPA) with a single command, or doing the opposite and creating the schema from your entities. Sadly in most cases such technologies are underutilized and for whatever reason many out there still write their ORM mappings manually for something like Hibernate (or write dynamic SQL manually, with something like myBatis).

In the end, I'm not sure. Coupling might mean issues down the road, but decoupling now might mean introducing a level of abstraction/indirection that might just be needless cruft, like the tendency that you sometimes see in Java projects, along the lines of: MyBusinessObject/Dto <--> SomeMapper <--> MyEntity <--> MyEntityDao <--> MyEntityMapper/Repository; Not saying that that's necessary OR that it's a bad approach, Java just has lots of codebases out there that end up with many abstractions, hence the example.

The value is that you can get ultra peformant CRUD app supporint bunch of filtering operations OTB within an hour or so. If you developed one youreself, it would probably be slower TBH. Depending on what you do, this can be lifesaver or thing to avoid.

I'll answer in theory, because I haven't used it. And I assume there are lots of ways this theory breaks down in practice.

The first (theoretical) benefit is that it removes a lot of redundancy. Databases already offer a lot of things applications do for themselves, and typically it's a best practice to do those things in the database anyway to guard against application bugs. For instance, defining CHECK constraints is a best practice regardless of application validation. (There's a lot of disagreement over where the DB/app boundary is and how much overlap there should be.)

Second, databases can be declarative because they are managing the data itself. The presence of a constraint makes a guarantee about the data regardless of history (versions, changes, bugs, etc.). Similarly for declarative authorization (GRANT, RLS, etc.).

Third, these benefits compound when dealing with many smaller, hastily-written applications.

PostgREST 11 is in pre-release too - Steve (the maintainer) wrote about some of the upcoming features here:

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.

I'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

GROUP BY/DISTINCT (2017): https://github.com/PostgREST/postgrest/issues/915

I 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).

Big 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.

I 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?

Have young'uns these days not heard of ajax?

https://www.django-rest-framework.org/

Instead of Vue, have you considered htmx with django-rest-framework? See https://htmx.org/essays/spa-alternative/

DRF (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/lates...) helps share form and serializer objects reducing a lot of duplicate effort.

I'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

I 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

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

I 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.

These 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.

You can use a library like 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.

I want synchronous validation with business logic. After the synchronous validation event streaming could be useful.

You 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.

Why don't you want to write triggers?

They are considered GOTO of db. Good luck debuging complex trigger hierarchy.

They are considered that by whom? Not by me, they're not. Personally, I've never encountered a complex trigger hierarchy and I see no good reason why I ever would, and I write lots of triggers. But, that's just me. Your results may vary.

Experts: https://www.red-gate.com/simple-talk/databases/sql-server/t-...

"When I work with TRIGGERs, which is rare"

Evidently, even some experts do use triggers.

"I normally limit them to at most one per operation per TRIGGER"

Great! So do I! So far so good. I guess I'm still in the good graces of some experts.

"in full ANSI/ISO SQL, we do have schema level constraints, the CREATE ASSERTION statement, but that is another topic"

Not in PostgreSQL, we don't. Evidently some experts may not be perfect experts.

Shameless 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/

For 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.proje...

https://github.com/PostgREST/postgrest/blob/main/default.nix

https://github.com/PostgREST/postgrest/blob/main/shell.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-1019743...

Over 13 parts to remove NixOS from Mac OS, involving reboots, /etc/fstab, OS level users, daemons, etc.

Previous mentions of / requests for something similar for SQLite:

https://news.ycombinator.com/item?id=33894995#33897716

https://news.ycombinator.com/item?id=33484693#33485145

https://news.ycombinator.com/item?id=33078798#33119873

https://news.ycombinator.com/item?id=30636796#30637326

https://news.ycombinator.com/item?id=24442294#24442876

Any recommendations or anecdotes today?

Not 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/

The 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

Shameless insertion:

We release our new HTTP interface with our free AIoT database - JoinBase:

HN discuss: https://news.ycombinator.com/item?id=34181591

or the blog link: 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/

How 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?

I 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.

I'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.

> 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.

maybe foreign data wrappers would be relevant to your interests? i believe there are already a number of them which expose non-databasey things.

That's just GraphQL with fewer steps.

When 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..

You can cache the API responses.

I 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

At 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-...

[2] https://github.com/diogob/postgres-websockets

[3] https://postgrest.org/en/stable/api.html#computed-virtual-co...

[4] https://github.com/PostgREST/postgrest/issues/2530

has 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?

This 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.

Does anyone have experience integrating 3rd party auth systems (ex Keycloak) with Postgrest? How did that experience go?

I think you can integrate it with nginx, which would work well with postgrest.

Not exactly the personal experience you asked for but https://postgrest.org/en/stable/ecosystem.html has receipes for oauth2 including Keycloak.

we integrated Gotrue with PostgREST at Supabase and it works beautifully together https://github.com/supabase/gotrue (forked from Netlify)

I did it with Azure AD application roles in oauth tokens, it was a non event.

I'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

[1] https://www.sqlalchemy.org/

Is there anything like this for MySQL/MariaDB?

Soon (in the works now, it will be particularly interesting in the context of cloudflare workers talking to planetscale)

https://github.com/subzerocloud/showcase

Are there any ”api from postgres db” projects that are written in Node.js?

Yes, 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.

Graphile (https://www.graphile.org/) and Prisma (https://www.prisma.io/)

Prisma 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.

Why 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.

Maybe.

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.)

I 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

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!

Directus is actually one. You get the front end which is basically an admin UI, but you also get a full blown fully featured REST API

https://directus.io/

Are you aware of supabase? It's rather nice.

Out of curiosity why would you prefer a DB foundation implemented in Node over a mature project in Haskell?

Not RESTful at all. Not a mention of hypermedia.

Reply on news.ycombinator.com