[HN Gopher] PostgREST - Serve a RESTful API from any Postgres da...
___________________________________________________________________
PostgREST - Serve a RESTful API from any Postgres database
Author : thunderbong
Score : 219 points
Date : 2022-12-29 13:04 UTC (9 hours ago)
(HTM) web link (postgrest.org)
(TXT) w3m dump (postgrest.org)
| jmull wrote:
| 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.
| ruslan_talpa wrote:
| 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...
| RhodesianHunter wrote:
| Wouldn't it be much easier to disable/restrict certain sql
| functions than do all of the http->sql mapping?
| 411111111111111 wrote:
| No, as there are countless ways to do the same.
| Whitelisting is more effective if your code is running on
| untrusted devices (frontend)
| jmull wrote:
| (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.
| aljarry wrote:
| 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.
| dragonwriter wrote:
| You can limit the visible schema, and thus the contract, per
| role with SQL.
| lelanthran wrote:
| 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?
| officialchicken wrote:
| >Why not just accept SQL?
|
| Sanitize your inputs. I'll let little bobby tables link to
| the XKCD
| jmull wrote:
| 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.
| somat wrote:
| 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.
| SoftTalker wrote:
| 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.
| timando wrote:
| 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.
| giraffe_lady wrote:
| 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.
| mildbyte wrote:
| > 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
| edmundsauto wrote:
| 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.
| 015a wrote:
| 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).
| zX41ZdbW wrote:
| 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.
| yladiz wrote:
| How do you determine query complexity? Do you go off of the
| query plan or something like looking at the columns, joins,
| etc.?
| zX41ZdbW wrote:
| 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.cli
| ckhouse.com/play?user=play#U0VMRUNUICogRlJPT...
|
| This query will fail with the error: https://play.clickhous
| e.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))
| yladiz wrote:
| 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.
| jeff-davis wrote:
| 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).
| vbezhenar wrote:
| 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.
| taffer wrote:
| > 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>'.
| jmull wrote:
| 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.
| [deleted]
| smallnamespace wrote:
| 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.
| remram wrote:
| 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?
| awb wrote:
| 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.
| sigstoat wrote:
| maybe foreign data wrappers would be relevant to your
| interests? i believe there are already a number of them which
| expose non-databasey things.
| gregwebs wrote:
| 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.
| Twisell wrote:
| 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.
| agentultra wrote:
| 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.
| gregwebs wrote:
| I want synchronous validation with business logic. After the
| synchronous validation event streaming could be useful.
| dventimihasura wrote:
| Why don't you want to write triggers?
| majkinetor wrote:
| They are considered GOTO of db. Good luck debuging complex
| trigger hierarchy.
| dventimihasura wrote:
| 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.
| majkinetor wrote:
| Experts: https://www.red-gate.com/simple-
| talk/databases/sql-server/t-...
| dventimihasura wrote:
| "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.
| kiwicopple wrote:
| 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.
| MuffinFlavored wrote:
| 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
| MuffinFlavored wrote:
| 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.
| majkinetor wrote:
| 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
| valstu wrote:
| Are there any "api from postgres db" projects that are written in
| Node.js?
| fulafel wrote:
| Out of curiosity why would you prefer a DB foundation
| implemented in Node over a mature project in Haskell?
| adamnemecek wrote:
| Are you aware of supabase? It's rather nice.
| smt88 wrote:
| 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.
| AprilArcus wrote:
| Graphile (https://www.graphile.org/) and Prisma
| (https://www.prisma.io/)
| Sujan wrote:
| 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.
| redmacaron wrote:
| 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/
| aobdev wrote:
| 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!
| defanor wrote:
| 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
| FlyingSnake wrote:
| 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/
| password4321 wrote:
| 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?
| AnEro wrote:
| 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.
| turbobooster wrote:
| 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?
| umangsh wrote:
| 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.
| AnEro wrote:
| 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
| melony wrote:
| Have young'uns these days not heard of _ajax_?
| clintonb wrote:
| https://www.django-rest-framework.org/
| sidmitra wrote:
| Instead of Vue, have you considered htmx with django-rest-
| framework? See https://htmx.org/essays/spa-alternative/
| shinycode wrote:
| 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
| jpdb wrote:
| 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.
| xemoka wrote:
| 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...
| justsomehnguy wrote:
| 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.
| pyuser583 wrote:
| 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.
| jeff-davis wrote:
| 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.
| taffer wrote:
| 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
| [deleted]
| ravenstine wrote:
| 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.
| majkinetor wrote:
| 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.
| mike_hearn wrote:
| 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.
| marcosdumay wrote:
| 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.
| giraffe_lady wrote:
| 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.
| taffer wrote:
| > 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...
| KronisLV wrote:
| > 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.
| dventimihasura wrote:
| I don't want to avoid that.
| vore wrote:
| 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.
| dventimihasura wrote:
| 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.
| sokoloff wrote:
| 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.)
| dventimihasura wrote:
| 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.
| vore wrote:
| 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.
| dventimihasura wrote:
| 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?"
| arnsholt wrote:
| 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.
| maerF0x0 wrote:
| 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
| arnsholt wrote:
| 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.
| maerF0x0 wrote:
| > SQL only.
|
| I assume you mean readonly SQL then... (perhaps creating
| views too)
| dimmke wrote:
| 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.
| PreInternet01 wrote:
| 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...
| tomberek wrote:
| 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.
| gregwebs wrote:
| 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.
| tomberek wrote:
| 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.
| euroderf wrote:
| I wonder if there's an SQLite version somewhere.
| __oh_es wrote:
| More feature rich but perhaps this? Been dying to toy with
| this and fslite
|
| https://github.com/pocketbase/pocketbase
| xrd wrote:
| Absolutely love pocketbase.
|
| And, interesting to note that using stored procedures
| 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.
|
| Still, pocketbase is so amazing.
| jhd3 wrote:
| https://datasette.io/?
| IceWreck wrote:
| Is supabase what you're describing?
|
| I could be wrong but I think they use postgREST underneath.
| the_duke wrote:
| Yes, it definitely does.
| esquire_900 wrote:
| 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?
| CuriouslyC wrote:
| 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.
| pvillano wrote:
| Complexity is often unavoidable. The question is where do
| you put it?
| lelanthran wrote:
| > 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.
| dang wrote:
| 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)
| twistedcheeslet wrote:
| Does anyone have experience integrating 3rd party auth systems
| (ex Keycloak) with Postgrest? How did that experience go?
| liamconnell wrote:
| I think you can integrate it with nginx, which would work well
| with postgrest.
| fulafel wrote:
| Not exactly the personal experience you asked for but
| https://postgrest.org/en/stable/ecosystem.html has receipes for
| oauth2 including Keycloak.
| awalias wrote:
| we integrated Gotrue with PostgREST at Supabase and it works
| beautifully together https://github.com/supabase/gotrue (forked
| from Netlify)
| nick__m wrote:
| I did it with Azure AD application roles in oauth tokens, it
| was a non event.
| anonu wrote:
| 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.
| nesarkvechnep wrote:
| Not RESTful at all. Not a mention of hypermedia.
| college_physics wrote:
| 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?
| davidjfelix wrote:
| 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.
| sigmonsays wrote:
| 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..
| cpursley wrote:
| You can cache the API responses.
___________________________________________________________________
(page generated 2022-12-29 23:00 UTC)