13 ms·

PostgREST 9.0

284 points by kiwicopple 5y ago

revskill 5y ago
Wish PostgresQL has built-in version control for every DDL changes. For example, i want a history of changes when i run command like CREATE OR REPLACE FUNCTION... Else programming inside database is still a pain.
- manojlds 5y ago
  EdgeDB seems like a good way to approach this https://www.edgedb.com/ https://www.edgedb.com/
- grzm 5y ago
  One way I've approached this in the past is to dump the database schema after each schema modification and use version control on those schema. There's a python project out there (apologies: I can't recall the name of it right now) now that leverages pg_dump and its options to make this easier. Not perfect, but useable. And better than nothing! Found it: https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor
- jayd16 5y ago
  You can use DB versioning tools like FlywayDB or Liquibase. Makes it consistent at least.
- vincnetas 5y ago
  You dont write code directly in db as you dont write code directly in python web app. Even though you can edit production live files, you dont do this. Instead you do changes in version control and deploy. Same goes for DB. Change fluway scripts, version control them and do deployment.
  - peterth3 5y ago
    Come here to say this, here’s a link to flyway if anyone is curious - https://github.com/flyway/flyway https://github.com/flyway/flyway. Many ORMs come with migration systems. For example, SQLAlchemy’s migration tool is Alembic - https://github.com/sqlalchemy/alembic https://github.com/sqlalchemy/alembic.
  - laurent92 5y ago
    But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds of “schema upgraders”, all based on their flavor of XML, JSON, text files and their associated name comventions, precisely because the canonical SQL way of doing it is limping.
    - Cthulhu_ 5y ago
      Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.
    - necovek 5y ago
      I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).
      - mhitza 5y ago
        > If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
        necovek 5y ago
- thejosh 5y ago
  What is your application, and does it not handle migrations?
- guyromm 5y ago
  that's actually precisely what i implemented in my postgrest-svelte starter template (https://github.com/guyromm/svelte-postgrest-template https://github.com/guyromm/svelte-postgrest-template). ./pg_schema_dump.sh breaks down the schema into an entity-per-file structure at ./sql/schema, while. ./db_init.sh knows how to create a fresh database schema from this dump. the per-file breakdown allows to nicely version the schema in git.
- michelpp 5y ago
  I'm not sure why you're getting downvoted, this is a fine question. One approach is to use Point In Time Recovery. When you run your migration, you take note of the LSN (the point in the WAL stream) just before you make your changes. Then you can roll back to the point in time right before you applied the migration. Note that you will need some other mechanism for restoring any data you added after the migration point.
- Dystopian 5y ago
  While general best practice is that you version your changes in git and deploy migrations with something like FlyWay I've also run into the problem of needing some form of 'audit log' to make sure no DDL changes are being made in prod (and if they are, moving them over into version control). I have a migration which is something like this one: https://www.alibabacloud.com/help/doc-detail/169290.html https://www.alibabacloud.com/help/doc-detail/169290.html which uses a trigger on `ddl_command_end` in PG to copy over the query which made the DDL change from `pg_stat_activity` to a new audit schema to stash. Can definitely help with maintenance and finding out what happened when.
- cultofmetatron 5y ago
  I do this with migrations. its not perfect but it works well enough.
kiwicopple 5y ago
PostgREST v9.0.0 was released last night. A few neat features include better support for Partitioned Tables, better handling for Postgres functions with unnamed parameters, and a (much requested) addition for LEFT JOIN support. Big shout out to the PostgREST maintainers and contributors (https://postgrest.org/en/v9.0/releases/v9.0.0.html#thanks https://postgrest.org/en/v9.0/releases/v9.0.0.html#thanks). The last release was only a few months ago so it's great to see so much momentum.
gowthamgts12 5y ago
I'm using sandman2[0] as a wrapper for psql and mysql databases. how is it different or any feature/differentiating factor I should consider for my future wrappers? [0] - https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2
- SahAssar 5y ago
  Does sandman support joining, filtering to only fetch some columns, full-text-search, authentication to use different roles based on a token, defining callable functions in sql? Looking at the sandman docs those were some features that I've used a lot in postgrest but weren't mentioned in sandman.
  - gowthamgts12 5y ago
    ah i never needed those for my usecase. didn't knew postgrest can do these - would come in handy in future. thanks
dorianmariefr 5y ago
Using this is a code smell
- mnd999 5y ago
  Maybe, an app I inherited used this as it’s interaction with the database. The main issue was that because it wasn’t really sophisticated enough to do the queries they wanted so they wrote a bunch of stored procedures (in JavaScript?!) and used it to call them. This fairly rapidly led to the database getting overloaded. I guess it’s maybe a smell because you’re limiting the database capabilities down from SQL, which is very powerful, down to the postgrest set of abstractions. Maybe that’s okay for your app now and maybe it always will be. But in this case, it wasn’t.
  - claytonjy 5y ago
    what does PostgREST take away from you over SQL? Sure it has limitations in what it can API-ify, but couldn't you stick anything more exotic in a function or procedure?
- lvturner 5y ago
  why?
  - pulse7 5y ago
    Not the author of the post, but I guess the author meant that using JavaScript to communicate directly with PostgreSQL database over PostgREST is "architectural code smell": in this case database can easily be hacked and everybody could write to/remove anything from such an unprotected database...
    - afavour 5y ago
      But Postgres has row level security, so the OP would be wrong in that case.
    - TranquilMarmot 5y ago
praveen9920 5y ago
This reminds me of parse.com but more simplistic
- juangburgos 5y ago
  I do not find it more simple, parse has very simple to use, time-saving, client side libraries. PostgREST does not seem to have anything close to that. There seem to be 3rd party client side libraries, but mostly abandon-ware.
guyromm 5y ago
postgREST is the bomb and, together with svelte, changed my life, productivity wise - no less. here's a short video explaining how this project can allow a front-end developer unleash PostgreSQL's power without having to write backend code. https://drive.google.com/file/d/1ncp00Mb7L3TO9TIRgNEF6pRdt-bbpHdI/view https://drive.google.com/file/d/1ncp00Mb7L3TO9TIRgNEF6pRdt-b...
- Jasp3r 5y ago
  If you use this, also take a look at GraphQL, AWS has a hosted GraphQL service called Appsync, and if you look into self hosted you can also use Prisma. Imagine postgREST with a better query format.
  - xdanger 5y ago
    Checkout postgraphile at https://www.graphile.org/ https://www.graphile.org/ , they have a whole ecosystem build around postgresql, typescript, graphql.
    - axhl 5y ago
      Just to +1 this and add that maintainer responsiveness and its community is excellent.
    - rand0mdude 5y ago
      Just curious, how does it compare to Hasura? Looks pretty similar but without the ui
      - xdanger 5y ago
        Someone called it: "hasura for adults” It's highly customisable, works directly with postgresql row levels security and the performance is quite good. It has a custom GraphiQL gui to work on queries/mutations. To really see how it all works together checkout the starter project: https://github.com/graphile/starter https://github.com/graphile/starter it has migrations, job queue, graphql-codegen etc. Benjie (https://github.com/benjie https://github.com/benjie) is one of the greatest maintainers I've ever seen!
boomskats 5y ago
This is awesome progress. Shout out to @steve-chavez his new colleagues at Supabase. Hopefully you'll get another logo for your Sponsors section soon.
- steve-chavez 5y ago
  Many thanks! Also want to mention that I could not do the work without the help of the PostgREST Team[1] :) [1]: https://github.com/orgs/PostgREST/people https://github.com/orgs/PostgREST/people
strikeling1010 5y ago
Ah, great just what I needed, to query my database over http. Should only be a few orders of magnitude slower, who cares!
- strikeling1010 5y ago
  Maybe for my next project, I'll make sure to expose my database to the internet to make use of this. I want to make sure it's as easy as possible to write to my database. I've always found security groups to get in the way as well, so I can just add in a rule for 0.0.0.0/0. Then to avoid DDoS's, I can stick my database behind cloudflare, should only add another 100ms or so #yolo. Then what I'll have is a fully functioning database over HTTP connected to the internet behind cloudflare just to avoid connecting to my database normally in my application code!
  - afavour 5y ago
    Next time this instinct strikes you try doing 30 seconds of research. I’ve not used it but have briefly read about PostgREST and every single complaint you’ve listed here is inaccurate.
joss82 5y ago
PostgREST seems super cool. I'm curious so know if anyone here is using it in production, for a commercial project?
- kiwicopple 5y ago
  PostgREST is used inside every Supabase project. We have >50K projects now[0], some of them making (tens of) millions of API requests through PostgREST daily. It's a fantastic piece of tech, especially when coupled with PostgreSQL Row Level Security. We have some benchmarks here in case you're concerned about performance: https://github.com/supabase/benchmarks/issues/2 https://github.com/supabase/benchmarks/issues/2 [0] hosted platform. We don't add telemetry to self-hosting
  - joss82 5y ago
    Thanks, it's good to know! I was not concerned about performance at all. Actually, Postgresql's performance is one of its strong points to me (up to a point when scaling up). I guess there is a point where it all breaks down but I didn't reach it yet.
  - smoe 5y ago
    Sorry to hijack the thread a bit, but yesterday I watched a supabase tutorial on row level security and I'm very intrigued to try out more. But what I couldn't figure on quick search is, what the best practices are in order to know what the current users permissions are before hitting a permission denied. E.g to show/hide certain actions in the frontend. Do I have to duplicate the logic somewhere else or am I missing something to get them from Postgres/Supabase easily?
    - kiwicopple 5y ago
      I see what you mean - yes these rules would live separately from RLS. However we are also about to add this functionality to the Supabase Dashboard, and we will open source our solution to every Supabase project. (We will come on with a very general RBAC/ABCA solution)
throwaway81523 5y ago
Can someone give a 2-3 sentence summary of what this is? Some kind of javascript API to Postgres? Not an ORM though? Is the idea to have client connections to it from user browsers or anything like that? That sounds scary.
- AndrewOMartin 5y ago
  PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. https://postgrest.org/en/v9.0/index.html https://postgrest.org/en/v9.0/index.html
  - ninkendo 5y ago
    Is the idea to hit the database directly from the web browser, without a server component? Do people actually do this? I mean, I can see the appeal, but I feel that having a layer of indirection between your database and your API is useful to keep the database somewhat normalized while making your API endpoints logically useful for the problem domain. But, I haven’t been in the web programming game in over 5 years and pretty much stuck with plain old rails back then, so I think I’m just out of touch in general.
    - rat9988 5y ago
      You can add a layer of indrection if you want. > to keep the database somewhat normalized while making your API endpoints logically useful for the problem domain. Not necessarily needed, and if so you can add a layer of indirection.
      - claytonjy 5y ago
        To be more specific, you can add that layer of indirection in your database. Have a schema called something like "api", and only let it contain views/functions/procedures that reference one or more "real" schemas underneath. Point PostgREST at that. Now you can change your data model without breaking your API contract, and can do so transactionally which is a benefit you don't get with other indirection layers. This is a good approach even when you do have a traditional API middleware layer, but why have more than one? Biggest downside is many folks are SQL-shy so the middleware devs may not want to or may not be able to transition in this way.
varispeed 5y ago
A running joke in one of the companies I know was that whenever backend developers messed up something, frontend developers would raise an idea to have it replaced by PostgREST :-)
polycaster 5y ago
I find the idea of PostgREST very intriguing at first sight. Someone around with experience running this in production? Did you find any major pain points when integrating in a client app (let's say React, Svelte or alike)? The trouble I felt in the past with the concept of DB as source of truth was to manage business logic (eq. in stored procedures) in a way, that works for a distributed team efficently.
- 0x62 5y ago
  I tried it via Supabase (open source Firebase clone that uses PostgREST under the hood). At the time there was no join support which was incredibly limiting, and we ended up using Hasura instead. Overall though, the user experience was generally very good and we still use it for some smaller standalone components on otherwise static websites (e.g. a mailing list signup form) where you don't need complex joins.
  - andix 5y ago
    Can’t you use views for joins? Usually you should be fine with a few views, in most schemas, there are just a limited amount of tables you can join in a meaningful way.
  - kiwicopple 5y ago
    > no join support Perhaps you mean no left-join support? There has been support for inner-join support in PostgREST for a few years :) (example: https://supabase.com/docs/reference/javascript/select#query-foreign-tables https://supabase.com/docs/reference/javascript/select#query-...)
- ratww 5y ago
  It works pretty well with frontend frameworks, as long as you keep the interfaces simple and don't try to cheat and put business logic into the frontend. IMO the the source of truth for Views and Stored Procedures should be a git repository, where developers change them in a .sql file, and the CI tests and updates them in the databases.
dinkleberg 5y ago
I really like the idea of this, but the one hesitation I always have is where does the non-CRUD functionality go? Do you need to create a separate service to handle your other business logic? I know technically you can store procedures in some DBs like Postgres, but that doesn’t seem ideal to me.
- a-saleh 5y ago
  This is the reason why postgrest is mostly solution looking for a problem to solve. I.e. last time I had great usecase, I ran into the problem that I needed good integration with our identity-service and didn't want to do the workaround that the project suggests.
  - steve-chavez 5y ago
    If your identity-service can generate a JWT, then you can integrate your auth with PostgREST. There's a related example with Auth0[1] in the docs. [1]: https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0 https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0
- rubyist5eva 5y ago
  You use an api-gateway in front of this and send those requests to your application server, generally though you should try to keep your business logic in triggers and stored procedures.
  - deleted 5y ago
    [deleted]
  - lenzm 5y ago
    Writing all of my business logic in triggers and stored procedures seems like a high cost to avoid writing crud. All the value comes from business logic. Making the business logic easier to read and work with is usually my top priority.
    - 5y ago
pull_my_finger 5y ago
A similar project built with intention around this idea is Tarantool[1]. I never hear much about it, but if you're interested in compute close to your data, this is definitely something that would warrant consideration. [1]: https://github.com/tarantool/tarantool https://github.com/tarantool/tarantool
- httgp 5y ago
  This looks fascinating!
zanethomas 5y ago
This looks very useful; I've not seen it before. Gotta love Hacker News, I check Hacker News several times daily and have learned so much.
maest 5y ago
Any recommendations for an easy-to-use pg Python library? AFAICT psycopg2 requires users to do: import psycopg2 from psycopg2 import Error try: # Connect to an existing database connection = psycopg2.connect(user="postgres", password="pynative@#29", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() cursor.execute("SELECT version();") record = cursor.fetchone() Having to manage the connection object myself is meh. I'd like to be able to do: import pg_interface pg = pg_interface.init(db_config) pg.command(some_sql_string, some_params) or even import pg_interface pg_interface.command(some_sql_string, some_params, db_config) and have the lib deal with initialising and managing any connection pools/objects.
- ipmb 5y ago
  https://www.encode.io/databases/ https://www.encode.io/databases/ maybe?
- throwawayboise 5y ago
  So write those functions. Would be a minimal one-time investment that you could re-use everywhere.
- bb88 5y ago
  class PG: _pg_conn = None @classmethod def pg(cls): if cls._pg_conn is None: cls._pg_conn = psycopg2.connect(...) return cls._pg_conn connection = PG.pg()
  - michelpp 5y ago
    Another approach would be to use a lazy property: https://pypi.org/project/lazy-property/ https://pypi.org/project/lazy-property/
    - bb88 5y ago
kgeist 5y ago
A year ago the Russian-based Lingualeo (20 mln users) presented an article where they described their new architecture which has a similar concept. They have a simple proxy server in Go and all the logic happens in Postgres, i.e. frontend directly manipulates DB. Their claimed advantages were superior performance and less boilerplate/faster iteration times. In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. Another problem they had was that many core devs left because they perceived the new architecture a step backwards. Many also noted that they don't have adequate tools for version control anymore. I don't know how PostgREST compares, maybe it doesn't have such issues.
- michelpp 5y ago
  I heard there was this one website that released software with a bug in it once that leaked data.
  - kgeist 5y ago
    Of course, it was a bug in their code, which they promptly fixed. But it's the only case of a company with a large userbase using this kind of architecture that I know of, and their experience was far from smooth, so I thought I'd share.
    - michelpp 5y ago
      My point was your anecdote has nothing to do with PostgREST. It's a foggy hand wave that could be laid over just about anything with an HTTP socket on one end and a database on the other.
      - fluidcruft 5y ago
        There's something to be said about layers of security providing redundancy to catch errors--that's part of what goes into highly reliable operations used in aerospace and health care. If you get the postgres wrong and that's all you're relying on, then that's all you've got. If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. Of course the flip side is that's even more complex and more difficult to implement. The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. Whether you're focusing on the postgres layer or some higher later (python or whatever), that's still just one layer of protection.
gffadsfsfsda 5y ago
PostgREST is great. I am using it with row-level security for a Next.js app backed by Supabase at https://consensus.j4e.name https://consensus.j4e.name
michelpp 5y ago
There are a couple of key features that PostgREST provides that I have not seen in other web frameworks. That's not to say others don't exist, but there are some definitely modeling and security anti-patterns that have become unfortunately deeply entrenched in many frameworks I've had to work with: 1. Your web framework logging into the database as a superuser. This is huge: "kitchen-sink" frameworks tend to log into the database as a superuser, typically so they can "do migrations" from some SQL-degenerate ORM. PostgREST (as documented) logs into the database as a user with NO privileges at all, and switches roles (with SET ROLE) to the role defined in the signed JWT token. If you never sign a token with a superuser role, users can never run queries as a superuser. You can also enforce host based security so that your web servers can't even SET ROLE even if they had a bogus signed token. 2. Buffering full database query responses as garbage collected "objects" and then serialize them to JSON. Another problem proxy frameworks have is that they compose a SQL query, run it (typically as a superuser, sigh), then buffer then entire response in memory, typically as "objects" that need to be immediately garbage collected, and then rendered into JSON in another buffer! Finally the response is then streamed to the browser. Postgres speaks JSON, and can produce rows of data as JSON natively. This data can be streamed directly to the browser before the query completes. This is a huge cost. Django workers typically need several gigabytes of RAM, proportional to the largest possible query response your database can produce. PostgREST does no buffering, parsing, or rendering, it just streams the json response directly from the database. Processes typically need only one or two hundred megabytes of RAM at most. 3. Tables and Views are elements of an object oriented type system. Not just a storage mechanisms. Postgres is an Object-Oriented Relational Database Management System (OORDBMS). Tables are not just places you stick stuff, they are types and rows of data are instances of those types. Views are types composed of sub-types, which can in turn be views or tables. Postgres rewrites a query until all the views are expanded, like macros, until it gets one big query you never have to ever see. PostgREST leverages all of this power of the database, you can model in the language that is the best for modeling data, SQL.
tomlagier 5y ago
The issue I have with these automatic "backend from your DB" service is: * For the simple case, they seem like they are as good as a simple backend service, albeit easier to set up. * For more complex cases (e.g. external dependencies, complex business logic), they seem so much more difficult to make work that I can't imagine anyone _not_ setting up a separate service to handle those scenarios. Maybe you can do everything with stored procedures, but Hasura, Postgraphile, et al. all seem to suggest that you should stand up a separate service for this. * Once you've got a separate service where your complexity lives, that's going to be the meat of your project. * Is it really worth it to introduce a separate dependency & pattern just to scaffold the easy parts more quickly? The consistency of having a single data access pattern between the client and all parts of your service seems like it would outweigh not needing to write a little code to proxy queries through your backend by far. I guess it really depends on the app - if it's really just a view layer, or if the RUD* has very little logic to it and it's expected that the complexity of the backend logic really won't grow over time (never seen this...) then sure, it could be a fit. Until then it seems like yet another MVP optimization that gets thrown away as soon as the app needs to grow up.
- michelpp 5y ago
  > * For the simple case, they seem like they are as good as a simple backend service, albeit easier to set up. Agreed. > * For more complex cases (e.g. external dependencies, complex business logic), they seem so much more difficult to make work that I can't imagine anyone _not_ setting up a separate service to handle those scenarios. Maybe you can do everything with stored procedures, but Hasura, Postgraphile, et al. all seem to suggest that you should stand up a separate service for this. You should stand up a separate service for this regardless of your choice of web server. A web server should not be handling complex business logic cases, for many reasons but one obvious one is synchronous wait times. PostgREST or not, bigger job should go into a work queue, which should take just a few milliseconds. The second reason a work queue is better is that it is more secure. Your web process can be given the absolute minimal privileges, say INSERT only on the queue table. The queue worker can have more privileges. The important upside is that user facing systems cannot take "complex business logic" actions by way of security enforcement. > * Once you've got a separate service where your complexity lives, that's going to be the meat of your project. I agree, but that will happen regardless of your web server. > * Is it really worth it to introduce a separate dependency & pattern just to scaffold the easy parts more quickly? Quickness is only one tiny advantage of PostgREST. Native security on database objects, streaming responses without buffering, using every available postgres feature that no other database supports without third-party plugins to your web framework, that's just the beginning, but yeah it's a hell of a lot quick and easier to use too.

284 points by kiwicopple 5y ago

Wish PostgresQL has built-in version control for every DDL changes. For example, i want a history of changes when i run command like CREATE OR REPLACE FUNCTION... Else programming inside database is still a pain.

manojlds 5y ago
EdgeDB seems like a good way to approach this https://www.edgedb.com/ https://www.edgedb.com/
grzm 5y ago
One way I've approached this in the past is to dump the database schema after each schema modification and use version control on those schema. There's a python project out there (apologies: I can't recall the name of it right now) now that leverages pg_dump and its options to make this easier. Not perfect, but useable. And better than nothing! Found it: https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor
jayd16 5y ago
You can use DB versioning tools like FlywayDB or Liquibase. Makes it consistent at least.
vincnetas 5y ago
You dont write code directly in db as you dont write code directly in python web app. Even though you can edit production live files, you dont do this. Instead you do changes in version control and deploy. Same goes for DB. Change fluway scripts, version control them and do deployment.
- peterth3 5y ago
  Come here to say this, here’s a link to flyway if anyone is curious - https://github.com/flyway/flyway https://github.com/flyway/flyway. Many ORMs come with migration systems. For example, SQLAlchemy’s migration tool is Alembic - https://github.com/sqlalchemy/alembic https://github.com/sqlalchemy/alembic.
- laurent92 5y ago
  But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds of “schema upgraders”, all based on their flavor of XML, JSON, text files and their associated name comventions, precisely because the canonical SQL way of doing it is limping.
  - Cthulhu_ 5y ago
    Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.
  - necovek 5y ago
    I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).
    - mhitza 5y ago
      > If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
      - necovek 5y ago
thejosh 5y ago
What is your application, and does it not handle migrations?
guyromm 5y ago
that's actually precisely what i implemented in my postgrest-svelte starter template (https://github.com/guyromm/svelte-postgrest-template https://github.com/guyromm/svelte-postgrest-template). ./pg_schema_dump.sh breaks down the schema into an entity-per-file structure at ./sql/schema, while. ./db_init.sh knows how to create a fresh database schema from this dump. the per-file breakdown allows to nicely version the schema in git.
michelpp 5y ago
I'm not sure why you're getting downvoted, this is a fine question. One approach is to use Point In Time Recovery. When you run your migration, you take note of the LSN (the point in the WAL stream) just before you make your changes. Then you can roll back to the point in time right before you applied the migration. Note that you will need some other mechanism for restoring any data you added after the migration point.
Dystopian 5y ago
While general best practice is that you version your changes in git and deploy migrations with something like FlyWay I've also run into the problem of needing some form of 'audit log' to make sure no DDL changes are being made in prod (and if they are, moving them over into version control). I have a migration which is something like this one: https://www.alibabacloud.com/help/doc-detail/169290.html https://www.alibabacloud.com/help/doc-detail/169290.html which uses a trigger on `ddl_command_end` in PG to copy over the query which made the DDL change from `pg_stat_activity` to a new audit schema to stash. Can definitely help with maintenance and finding out what happened when.
cultofmetatron 5y ago
I do this with migrations. its not perfect but it works well enough.

kiwicopple 5y ago

PostgREST v9.0.0 was released last night. A few neat features include better support for Partitioned Tables, better handling for Postgres functions with unnamed parameters, and a (much requested) addition for LEFT JOIN support. Big shout out to the PostgREST maintainers and contributors (https://postgrest.org/en/v9.0/releases/v9.0.0.html#thanks https://postgrest.org/en/v9.0/releases/v9.0.0.html#thanks). The last release was only a few months ago so it's great to see so much momentum.

gowthamgts12 5y ago

I'm using sandman2[0] as a wrapper for psql and mysql databases. how is it different or any feature/differentiating factor I should consider for my future wrappers? [0] - https://github.com/jeffknupp/sandman2 https://github.com/jeffknupp/sandman2

SahAssar 5y ago
Does sandman support joining, filtering to only fetch some columns, full-text-search, authentication to use different roles based on a token, defining callable functions in sql? Looking at the sandman docs those were some features that I've used a lot in postgrest but weren't mentioned in sandman.
- gowthamgts12 5y ago
  ah i never needed those for my usecase. didn't knew postgrest can do these - would come in handy in future. thanks

dorianmariefr 5y ago

Using this is a code smell

mnd999 5y ago
Maybe, an app I inherited used this as it’s interaction with the database. The main issue was that because it wasn’t really sophisticated enough to do the queries they wanted so they wrote a bunch of stored procedures (in JavaScript?!) and used it to call them. This fairly rapidly led to the database getting overloaded. I guess it’s maybe a smell because you’re limiting the database capabilities down from SQL, which is very powerful, down to the postgrest set of abstractions. Maybe that’s okay for your app now and maybe it always will be. But in this case, it wasn’t.
- claytonjy 5y ago
  what does PostgREST take away from you over SQL? Sure it has limitations in what it can API-ify, but couldn't you stick anything more exotic in a function or procedure?
lvturner 5y ago
why?
- pulse7 5y ago
  Not the author of the post, but I guess the author meant that using JavaScript to communicate directly with PostgreSQL database over PostgREST is "architectural code smell": in this case database can easily be hacked and everybody could write to/remove anything from such an unprotected database...
  - afavour 5y ago
    But Postgres has row level security, so the OP would be wrong in that case.
  - TranquilMarmot 5y ago

praveen9920 5y ago

This reminds me of parse.com but more simplistic

juangburgos 5y ago
I do not find it more simple, parse has very simple to use, time-saving, client side libraries. PostgREST does not seem to have anything close to that. There seem to be 3rd party client side libraries, but mostly abandon-ware.

guyromm 5y ago

postgREST is the bomb and, together with svelte, changed my life, productivity wise - no less. here's a short video explaining how this project can allow a front-end developer unleash PostgreSQL's power without having to write backend code. https://drive.google.com/file/d/1ncp00Mb7L3TO9TIRgNEF6pRdt-bbpHdI/view https://drive.google.com/file/d/1ncp00Mb7L3TO9TIRgNEF6pRdt-b...

Jasp3r 5y ago
If you use this, also take a look at GraphQL, AWS has a hosted GraphQL service called Appsync, and if you look into self hosted you can also use Prisma. Imagine postgREST with a better query format.
- xdanger 5y ago
  Checkout postgraphile at https://www.graphile.org/ https://www.graphile.org/ , they have a whole ecosystem build around postgresql, typescript, graphql.
  - axhl 5y ago
    Just to +1 this and add that maintainer responsiveness and its community is excellent.
  - rand0mdude 5y ago
    Just curious, how does it compare to Hasura? Looks pretty similar but without the ui
    - xdanger 5y ago
      Someone called it: "hasura for adults” It's highly customisable, works directly with postgresql row levels security and the performance is quite good. It has a custom GraphiQL gui to work on queries/mutations. To really see how it all works together checkout the starter project: https://github.com/graphile/starter https://github.com/graphile/starter it has migrations, job queue, graphql-codegen etc. Benjie (https://github.com/benjie https://github.com/benjie) is one of the greatest maintainers I've ever seen!

boomskats 5y ago

This is awesome progress. Shout out to @steve-chavez his new colleagues at Supabase. Hopefully you'll get another logo for your Sponsors section soon.

steve-chavez 5y ago
Many thanks! Also want to mention that I could not do the work without the help of the PostgREST Team[1] :) [1]: https://github.com/orgs/PostgREST/people https://github.com/orgs/PostgREST/people

strikeling1010 5y ago

Ah, great just what I needed, to query my database over http. Should only be a few orders of magnitude slower, who cares!

strikeling1010 5y ago
Maybe for my next project, I'll make sure to expose my database to the internet to make use of this. I want to make sure it's as easy as possible to write to my database. I've always found security groups to get in the way as well, so I can just add in a rule for 0.0.0.0/0. Then to avoid DDoS's, I can stick my database behind cloudflare, should only add another 100ms or so #yolo. Then what I'll have is a fully functioning database over HTTP connected to the internet behind cloudflare just to avoid connecting to my database normally in my application code!
- afavour 5y ago
  Next time this instinct strikes you try doing 30 seconds of research. I’ve not used it but have briefly read about PostgREST and every single complaint you’ve listed here is inaccurate.

joss82 5y ago

PostgREST seems super cool. I'm curious so know if anyone here is using it in production, for a commercial project?

kiwicopple 5y ago
PostgREST is used inside every Supabase project. We have >50K projects now[0], some of them making (tens of) millions of API requests through PostgREST daily. It's a fantastic piece of tech, especially when coupled with PostgreSQL Row Level Security. We have some benchmarks here in case you're concerned about performance: https://github.com/supabase/benchmarks/issues/2 https://github.com/supabase/benchmarks/issues/2 [0] hosted platform. We don't add telemetry to self-hosting
- joss82 5y ago
  Thanks, it's good to know! I was not concerned about performance at all. Actually, Postgresql's performance is one of its strong points to me (up to a point when scaling up). I guess there is a point where it all breaks down but I didn't reach it yet.
- smoe 5y ago
  Sorry to hijack the thread a bit, but yesterday I watched a supabase tutorial on row level security and I'm very intrigued to try out more. But what I couldn't figure on quick search is, what the best practices are in order to know what the current users permissions are before hitting a permission denied. E.g to show/hide certain actions in the frontend. Do I have to duplicate the logic somewhere else or am I missing something to get them from Postgres/Supabase easily?
  - kiwicopple 5y ago
    I see what you mean - yes these rules would live separately from RLS. However we are also about to add this functionality to the Supabase Dashboard, and we will open source our solution to every Supabase project. (We will come on with a very general RBAC/ABCA solution)

throwaway81523 5y ago

Can someone give a 2-3 sentence summary of what this is? Some kind of javascript API to Postgres? Not an ORM though? Is the idea to have client connections to it from user browsers or anything like that? That sounds scary.

AndrewOMartin 5y ago
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations. https://postgrest.org/en/v9.0/index.html https://postgrest.org/en/v9.0/index.html
- ninkendo 5y ago
  Is the idea to hit the database directly from the web browser, without a server component? Do people actually do this? I mean, I can see the appeal, but I feel that having a layer of indirection between your database and your API is useful to keep the database somewhat normalized while making your API endpoints logically useful for the problem domain. But, I haven’t been in the web programming game in over 5 years and pretty much stuck with plain old rails back then, so I think I’m just out of touch in general.
  - rat9988 5y ago
    You can add a layer of indrection if you want. > to keep the database somewhat normalized while making your API endpoints logically useful for the problem domain. Not necessarily needed, and if so you can add a layer of indirection.
    - claytonjy 5y ago
      To be more specific, you can add that layer of indirection in your database. Have a schema called something like "api", and only let it contain views/functions/procedures that reference one or more "real" schemas underneath. Point PostgREST at that. Now you can change your data model without breaking your API contract, and can do so transactionally which is a benefit you don't get with other indirection layers. This is a good approach even when you do have a traditional API middleware layer, but why have more than one? Biggest downside is many folks are SQL-shy so the middleware devs may not want to or may not be able to transition in this way.

varispeed 5y ago

A running joke in one of the companies I know was that whenever backend developers messed up something, frontend developers would raise an idea to have it replaced by PostgREST :-)

polycaster 5y ago

I find the idea of PostgREST very intriguing at first sight. Someone around with experience running this in production? Did you find any major pain points when integrating in a client app (let's say React, Svelte or alike)? The trouble I felt in the past with the concept of DB as source of truth was to manage business logic (eq. in stored procedures) in a way, that works for a distributed team efficently.

0x62 5y ago
I tried it via Supabase (open source Firebase clone that uses PostgREST under the hood). At the time there was no join support which was incredibly limiting, and we ended up using Hasura instead. Overall though, the user experience was generally very good and we still use it for some smaller standalone components on otherwise static websites (e.g. a mailing list signup form) where you don't need complex joins.
- andix 5y ago
  Can’t you use views for joins? Usually you should be fine with a few views, in most schemas, there are just a limited amount of tables you can join in a meaningful way.
- kiwicopple 5y ago
  > no join support Perhaps you mean no left-join support? There has been support for inner-join support in PostgREST for a few years :) (example: https://supabase.com/docs/reference/javascript/select#query-foreign-tables https://supabase.com/docs/reference/javascript/select#query-...)
ratww 5y ago
It works pretty well with frontend frameworks, as long as you keep the interfaces simple and don't try to cheat and put business logic into the frontend. IMO the the source of truth for Views and Stored Procedures should be a git repository, where developers change them in a .sql file, and the CI tests and updates them in the databases.

dinkleberg 5y ago

I really like the idea of this, but the one hesitation I always have is where does the non-CRUD functionality go? Do you need to create a separate service to handle your other business logic? I know technically you can store procedures in some DBs like Postgres, but that doesn’t seem ideal to me.

a-saleh 5y ago
This is the reason why postgrest is mostly solution looking for a problem to solve. I.e. last time I had great usecase, I ran into the problem that I needed good integration with our identity-service and didn't want to do the workaround that the project suggests.
- steve-chavez 5y ago
  If your identity-service can generate a JWT, then you can integrate your auth with PostgREST. There's a related example with Auth0[1] in the docs. [1]: https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0 https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0
rubyist5eva 5y ago
You use an api-gateway in front of this and send those requests to your application server, generally though you should try to keep your business logic in triggers and stored procedures.
- deleted 5y ago
  [deleted]
- lenzm 5y ago
  Writing all of my business logic in triggers and stored procedures seems like a high cost to avoid writing crud. All the value comes from business logic. Making the business logic easier to read and work with is usually my top priority.
  - 5y ago

pull_my_finger 5y ago

A similar project built with intention around this idea is Tarantool[1]. I never hear much about it, but if you're interested in compute close to your data, this is definitely something that would warrant consideration. [1]: https://github.com/tarantool/tarantool https://github.com/tarantool/tarantool

httgp 5y ago
This looks fascinating!

zanethomas 5y ago

This looks very useful; I've not seen it before. Gotta love Hacker News, I check Hacker News several times daily and have learned so much.

maest 5y ago

Any recommendations for an easy-to-use pg Python library? AFAICT psycopg2 requires users to do: import psycopg2 from psycopg2 import Error try: # Connect to an existing database connection = psycopg2.connect(user="postgres", password="pynative@#29", host="127.0.0.1", port="5432", database="postgres_db") cursor = connection.cursor() cursor.execute("SELECT version();") record = cursor.fetchone() Having to manage the connection object myself is meh. I'd like to be able to do: import pg_interface pg = pg_interface.init(db_config) pg.command(some_sql_string, some_params) or even import pg_interface pg_interface.command(some_sql_string, some_params, db_config) and have the lib deal with initialising and managing any connection pools/objects.

ipmb 5y ago
https://www.encode.io/databases/ https://www.encode.io/databases/ maybe?
throwawayboise 5y ago
So write those functions. Would be a minimal one-time investment that you could re-use everywhere.
bb88 5y ago
class PG: _pg_conn = None @classmethod def pg(cls): if cls._pg_conn is None: cls._pg_conn = psycopg2.connect(...) return cls._pg_conn connection = PG.pg()
- michelpp 5y ago
  Another approach would be to use a lazy property: https://pypi.org/project/lazy-property/ https://pypi.org/project/lazy-property/
  - bb88 5y ago

kgeist 5y ago

A year ago the Russian-based Lingualeo (20 mln users) presented an article where they described their new architecture which has a similar concept. They have a simple proxy server in Go and all the logic happens in Postgres, i.e. frontend directly manipulates DB. Their claimed advantages were superior performance and less boilerplate/faster iteration times. In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. Another problem they had was that many core devs left because they perceived the new architecture a step backwards. Many also noted that they don't have adequate tools for version control anymore. I don't know how PostgREST compares, maybe it doesn't have such issues.

michelpp 5y ago
I heard there was this one website that released software with a bug in it once that leaked data.
- kgeist 5y ago
  Of course, it was a bug in their code, which they promptly fixed. But it's the only case of a company with a large userbase using this kind of architecture that I know of, and their experience was far from smooth, so I thought I'd share.
  - michelpp 5y ago
    My point was your anecdote has nothing to do with PostgREST. It's a foggy hand wave that could be laid over just about anything with an HTTP socket on one end and a database on the other.
    - fluidcruft 5y ago
      There's something to be said about layers of security providing redundancy to catch errors--that's part of what goes into highly reliable operations used in aerospace and health care. If you get the postgres wrong and that's all you're relying on, then that's all you've got. If you've got the same postgres wrapped in a more rigid validation then arguably there are more bugs that need to align for a mishap. Of course the flip side is that's even more complex and more difficult to implement. The reality is people probably tend to skimp on the postgres layer's security if they're burying it deep under other things. Whether you're focusing on the postgres layer or some higher later (python or whatever), that's still just one layer of protection.

gffadsfsfsda 5y ago

PostgREST is great. I am using it with row-level security for a Next.js app backed by Supabase at https://consensus.j4e.name https://consensus.j4e.name

michelpp 5y ago

There are a couple of key features that PostgREST provides that I have not seen in other web frameworks. That's not to say others don't exist, but there are some definitely modeling and security anti-patterns that have become unfortunately deeply entrenched in many frameworks I've had to work with: 1. Your web framework logging into the database as a superuser. This is huge: "kitchen-sink" frameworks tend to log into the database as a superuser, typically so they can "do migrations" from some SQL-degenerate ORM. PostgREST (as documented) logs into the database as a user with NO privileges at all, and switches roles (with SET ROLE) to the role defined in the signed JWT token. If you never sign a token with a superuser role, users can never run queries as a superuser. You can also enforce host based security so that your web servers can't even SET ROLE even if they had a bogus signed token. 2. Buffering full database query responses as garbage collected "objects" and then serialize them to JSON. Another problem proxy frameworks have is that they compose a SQL query, run it (typically as a superuser, sigh), then buffer then entire response in memory, typically as "objects" that need to be immediately garbage collected, and then rendered into JSON in another buffer! Finally the response is then streamed to the browser. Postgres speaks JSON, and can produce rows of data as JSON natively. This data can be streamed directly to the browser before the query completes. This is a huge cost. Django workers typically need several gigabytes of RAM, proportional to the largest possible query response your database can produce. PostgREST does no buffering, parsing, or rendering, it just streams the json response directly from the database. Processes typically need only one or two hundred megabytes of RAM at most. 3. Tables and Views are elements of an object oriented type system. Not just a storage mechanisms. Postgres is an Object-Oriented Relational Database Management System (OORDBMS). Tables are not just places you stick stuff, they are types and rows of data are instances of those types. Views are types composed of sub-types, which can in turn be views or tables. Postgres rewrites a query until all the views are expanded, like macros, until it gets one big query you never have to ever see. PostgREST leverages all of this power of the database, you can model in the language that is the best for modeling data, SQL.

tomlagier 5y ago

The issue I have with these automatic "backend from your DB" service is: * For the simple case, they seem like they are as good as a simple backend service, albeit easier to set up. * For more complex cases (e.g. external dependencies, complex business logic), they seem so much more difficult to make work that I can't imagine anyone _not_ setting up a separate service to handle those scenarios. Maybe you can do everything with stored procedures, but Hasura, Postgraphile, et al. all seem to suggest that you should stand up a separate service for this. * Once you've got a separate service where your complexity lives, that's going to be the meat of your project. * Is it really worth it to introduce a separate dependency & pattern just to scaffold the easy parts more quickly? The consistency of having a single data access pattern between the client and all parts of your service seems like it would outweigh not needing to write a little code to proxy queries through your backend by far. I guess it really depends on the app - if it's really just a view layer, or if the RUD* has very little logic to it and it's expected that the complexity of the backend logic really won't grow over time (never seen this...) then sure, it could be a fit. Until then it seems like yet another MVP optimization that gets thrown away as soon as the app needs to grow up.

michelpp 5y ago
> * For the simple case, they seem like they are as good as a simple backend service, albeit easier to set up. Agreed. > * For more complex cases (e.g. external dependencies, complex business logic), they seem so much more difficult to make work that I can't imagine anyone _not_ setting up a separate service to handle those scenarios. Maybe you can do everything with stored procedures, but Hasura, Postgraphile, et al. all seem to suggest that you should stand up a separate service for this. You should stand up a separate service for this regardless of your choice of web server. A web server should not be handling complex business logic cases, for many reasons but one obvious one is synchronous wait times. PostgREST or not, bigger job should go into a work queue, which should take just a few milliseconds. The second reason a work queue is better is that it is more secure. Your web process can be given the absolute minimal privileges, say INSERT only on the queue table. The queue worker can have more privileges. The important upside is that user facing systems cannot take "complex business logic" actions by way of security enforcement. > * Once you've got a separate service where your complexity lives, that's going to be the meat of your project. I agree, but that will happen regardless of your web server. > * Is it really worth it to introduce a separate dependency & pattern just to scaffold the easy parts more quickly? Quickness is only one tiny advantage of PostgREST. Native security on database objects, streaming responses without buffering, using every available postgres feature that no other database supports without third-party plugins to your web framework, that's just the beginning, but yeah it's a hell of a lot quick and easier to use too.