28 ms·

Not to mention: - super hard to cache if any client can basically generate an infinite number of different queries. - which dialect of SQL ? If you change DB

Not to mention: - super hard to cache if any client can basically generate an infinite number of different queries. - which dialect of SQL ? If you change DB or upgrade version, do your ask all clients to change as well ? - how do you do query that involves several data sources ? Some response data come from redis + elastic search + postgres. That's why FB created graphql. That's also why it's so limited in scope, so that complexity is manageable. - do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?

dragonwriter 6y ago
> do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ? No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
dvdkon 6y ago
Direct SQL from the client definitely isn't for everyone, but these problems aren't insurmountable. Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement. Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features). Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins. Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level. This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.

dragonwriter 6y ago

> do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ? No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.

dvdkon 6y ago

Direct SQL from the client definitely isn't for everyone, but these problems aren't insurmountable. Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement. Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features). Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins. Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level. This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.