3 ms·

Once you give users direct access to your db, you can no longer change it. That's why there are abstractions which allow changing the underlying data layer with

Once you give users direct access to your db, you can no longer change it. That's why there are abstractions which allow changing the underlying data layer without changing the public API.

mooreds 8y ago
I was writing to agree with this and then thought about column aliases and views, which are two ways to hide the underlying database structure that SQL provides out of the box. From the sqler readme I couldn't tell if either of those were available but I have used columns aliases when I have used other SQL to API gateways (like http://restsql.org http://restsql.org ). Such db level features definitely don't give you as much power to abstract as a full middle tier, but at least it is something. The trade may be worthwhile for the speed of API building. (I have worked with Google sheets as an API provider and that was far less flexible than sqler appears.) It is always possible (though perhaps not pleasant) to swap out an API built on this type of direct SQL access technology with one built on a traditional middle tier.
frou_dh 8y ago
One of the suggested ways to use PostgREST is that rather than pointing it at the Postgres schema called "public" (where created tables live by default), you point it at a schema consisting of nothing but SQL Views which communicate with those tables. That way, the underlying table structure can be altered without affecting the clients that consume the auto-generated HTTP interface. It's more effort to have to maintain some extra SQL code for those views, but with all the labour-saving PostgREST delivers in the first place, you still come out ahead.
- duncan-donuts 8y ago
  But if you’re going to do all that why not just put an abstraction in code (that could actually have some automated tests)?
  - michelpp 8y ago
    There are test frameworks for postgres. https://pgtap.org/ https://pgtap.org/
edsykes 8y ago
what can be very bad is if the database provided api is really just a sql executor - binding you to the database without you realising it. Doing rest, but finding you just tied all your clients to your database representation
- michelpp 8y ago
  Nope, that's what views are for. https://www.postgresql.org/docs/current/tutorial-views.html https://www.postgresql.org/docs/current/tutorial-views.html
michelpp 8y ago
Views are the abstraction layer. https://www.postgresql.org/docs/current/tutorial-views.html https://www.postgresql.org/docs/current/tutorial-views.html

Once you give users direct access to your db, you can no longer change it. That's why there are abstractions which allow changing the underlying data layer without changing the public API.

mooreds 8y ago

I was writing to agree with this and then thought about column aliases and views, which are two ways to hide the underlying database structure that SQL provides out of the box. From the sqler readme I couldn't tell if either of those were available but I have used columns aliases when I have used other SQL to API gateways (like http://restsql.org http://restsql.org ). Such db level features definitely don't give you as much power to abstract as a full middle tier, but at least it is something. The trade may be worthwhile for the speed of API building. (I have worked with Google sheets as an API provider and that was far less flexible than sqler appears.) It is always possible (though perhaps not pleasant) to swap out an API built on this type of direct SQL access technology with one built on a traditional middle tier.

frou_dh 8y ago

One of the suggested ways to use PostgREST is that rather than pointing it at the Postgres schema called "public" (where created tables live by default), you point it at a schema consisting of nothing but SQL Views which communicate with those tables. That way, the underlying table structure can be altered without affecting the clients that consume the auto-generated HTTP interface. It's more effort to have to maintain some extra SQL code for those views, but with all the labour-saving PostgREST delivers in the first place, you still come out ahead.

duncan-donuts 8y ago
But if you’re going to do all that why not just put an abstraction in code (that could actually have some automated tests)?
- michelpp 8y ago
  There are test frameworks for postgres. https://pgtap.org/ https://pgtap.org/

edsykes 8y ago

what can be very bad is if the database provided api is really just a sql executor - binding you to the database without you realising it. Doing rest, but finding you just tied all your clients to your database representation

michelpp 8y ago
Nope, that's what views are for. https://www.postgresql.org/docs/current/tutorial-views.html https://www.postgresql.org/docs/current/tutorial-views.html

michelpp 8y ago

Views are the abstraction layer. https://www.postgresql.org/docs/current/tutorial-views.html https://www.postgresql.org/docs/current/tutorial-views.html