I’d go further: read from views, write to APIs that call database transactions.
Exposing your database to arbitrary inserts, updates, and deletes eventually results in corrupt data and should be avoided as a rule. I like to call SQL’s insert, update, and delete “data assembly” instructions, because they are typically too low level to implement meaningful operations atomically.
In many applications reading patterns are also somewhat constrained or predictable, and views or stored procedures (implementing “parametrized views”) satisfy all access needs. Combine this with suitable roles and permissions. Follow the least privilege principle.
At a bank I worked the DBAs gave out read-only access through stored procedures. The reason for stored procedures rather than views was so the DBAs could control exactly the queries that could be performed. If I remember correctly each team got its own stored procedure, even if they retrieved the same data as another team. This meant that when a team wanted a change to the data set they requested, only their stored procedure needed changing. It also meant that the DBAs could change schemas, and amending teams’ stored procedures as necessary.
At what point do you end up just making specific views that effectively are your limited API? I can imagine someone who has full read could make some obnoxious DoS queries.
The whole point to an API, at least in my opinion, is you know relatively well what kind of things are expected to come in and go out. Obviously like all turing machines, all sorts of bad can happen, but I can’t imagine a function that takes a user ID and returns specific data is less predictable than exposing some user table with a full blown query language (read-only even).
I don’t think the article is suggesting your user-facing API should just be ‘give us a SQL query and we’ll execute it’, but more that building an additional read layer on top of your database that your user-facing API has to go through is unnecessary.
I’ve poked at GraphQL a couple times and repeatedly come to the conclusion that it makes a bunch of very domain specific trade-offs and can’t really be described as a query language at all.
Pretty much any form of accepting a subset of SQL is more general purpose, has better support for complex data models, can do graph adjacency or pivot tables and such which is where I’ve seen most ORMs even struggle.
I wish there were less clojure-flavored datalogs as query interfaces to be had.
I’ve been using Hasura, and I can join to other tables (can’t do GROUP BY, though). I guess it depends on how you implement things?
(There may be some limits to Hasura’s joining that I’m not aware of - I’ve only tried to do very basic “follow the foreign-key and fetch some fields from that table” kind of things.)
Except that one major difficulty of GraphQL is precisely to resolve GraphQL queries to SQL queries in an efficient way. Except if you use something like Hasura or similar which will do the work for you.
We curently use postgraphile for reads and some basic write operations. Anything more complicated we make an API for it. Seems to work pretty well overall.
I’d go further: read from views, write to APIs that call database transactions.
Exposing your database to arbitrary inserts, updates, and deletes eventually results in corrupt data and should be avoided as a rule. I like to call SQL’s insert, update, and delete “data assembly” instructions, because they are typically too low level to implement meaningful operations atomically.
In many applications reading patterns are also somewhat constrained or predictable, and views or stored procedures (implementing “parametrized views”) satisfy all access needs. Combine this with suitable roles and permissions. Follow the least privilege principle.
Or: read from views, write to views with replacement triggers
At a bank I worked the DBAs gave out read-only access through stored procedures. The reason for stored procedures rather than views was so the DBAs could control exactly the queries that could be performed. If I remember correctly each team got its own stored procedure, even if they retrieved the same data as another team. This meant that when a team wanted a change to the data set they requested, only their stored procedure needed changing. It also meant that the DBAs could change schemas, and amending teams’ stored procedures as necessary.
At what point do you end up just making specific views that effectively are your limited API? I can imagine someone who has full read could make some obnoxious DoS queries. The whole point to an API, at least in my opinion, is you know relatively well what kind of things are expected to come in and go out. Obviously like all turing machines, all sorts of bad can happen, but I can’t imagine a function that takes a user ID and returns specific data is less predictable than exposing some user table with a full blown query language (read-only even).
I don’t think the article is suggesting your user-facing API should just be ‘give us a SQL query and we’ll execute it’, but more that building an additional read layer on top of your database that your user-facing API has to go through is unnecessary.
Alternatively: GraphQL. Less dangerous than SQL, less tight coupling to the exact DB structure, but about as powerful.
Disclaimer: haven’t used it so far
That’s a pretty major disclaimer!
I’ve poked at GraphQL a couple times and repeatedly come to the conclusion that it makes a bunch of very domain specific trade-offs and can’t really be described as a query language at all.
Pretty much any form of accepting a subset of SQL is more general purpose, has better support for complex data models, can do graph adjacency or pivot tables and such which is where I’ve seen most ORMs even struggle.
I wish there were less clojure-flavored datalogs as query interfaces to be had.
Less?
May I suggest Preql ?
(warning: shameless self-promotion)
GraphQL is not a query language. It’s an RPC API abstraction.
I’m not sure “not even having joins” is “about as powerful”.
Indeed. I think the “QL” in GraphQL is misleading, unless you have a very basic definition of “query”.
I’ve been using Hasura, and I can join to other tables (can’t do
GROUP BY, though). I guess it depends on how you implement things?(There may be some limits to Hasura’s joining that I’m not aware of - I’ve only tried to do very basic “follow the foreign-key and fetch some fields from that table” kind of things.)
Except that one major difficulty of GraphQL is precisely to resolve GraphQL queries to SQL queries in an efficient way. Except if you use something like Hasura or similar which will do the work for you.
We curently use postgraphile for reads and some basic write operations. Anything more complicated we make an API for it. Seems to work pretty well overall.