7 ms·

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing

Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters. But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.: Signed blob: { sql: "SELECT name, email, ... FROM users WHERE users.id = %(userid)s" allowedParameters: [] } Such that the client is allowed to send this query, but they're not allowed to provide any parameters, the backend has to provide the userid. That seems workable to me. Likewise: { sql: "SELECT order.price, order.... FROM orders WHERE orders.user_id = %(userid)s AND orders.id = %(orderid)s" allowedParameters: ["orderid"] } This would allow the frontend to provide an order ID, but not the user ID - so the user still can't look at other people's orders and if not logged in, it's an invalid query without a userid. And the client isn't allowed to change the "sql" or parameters sent or the signature is invalid. Edit: Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend. That's very cool, but the average startup isn't using bazel/buck/etc. Those tools aren't yet small app friendly enough (which is unfortunate, IMO.)

smallnamespace 6y ago
I’m basically doing this for an app - each endpoint is just a parametrized query and RLS in the database enforces security/auth constraints. The middleware doesn’t do anything except populate query parameters and do a bit of sanity checking.
asguy 6y ago
Or you could could use Row Level Security, and/or stored functions/procedures for access controls. You can put these constraints in the DB, and just give each of your users a DB role.
zachrip 6y ago
Another issue is part of what makes a company valuable is their biz logic. Moving all of that to the frontend means you can no longer guard it. Even if you can extract it to the backend, how many REST/GQL endpoints use a single query? And where do the parameters get supplied if not from a route specific handler?
throwawaygh 6y ago
This is a really interesting interface design, but seems like for any non-trivial database it would be too easy to make mistakes and give the client too much access. Also seems like it's be really easy to end up with performance issues. Also, there's basically zero information hiding. The database is your interface. Changing the schema in any way whatsoever is a breaking change to the API! You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. You've just turned your database schema into the API :'( But I haven't actually seen a codebase that does this, so maybe I'm wrong. Have you used this approach in a project with more than a few developers and a large database? How did it go?
- dane-pgp 6y ago
  > You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. Another way to look at it is that changing the database schema is already a problem for the API which has to talk to it, so you're not really saving yourself any work by putting an extra layer between the frontend and the database. In fact, if anything, you're doubling the amount of work, because there are now two interfaces (DB to API, and API to frontend) whose backwards compatibility you have to worry about.
  - throwawaygh 6y ago
    Yes. The decision calculus here really boils down to how many clients the API has and how much you care about those clients. If you have 100 other teams each with 5+ developers consuming your API -- or especially if your API is a product -- then keeping the API to frontend interface relatively stable with rare breaking changes is much more valuable than saving yourself even a major time commitment on DB to API work. On the other hand, if you have relatively few clients -- or if the clients are "lower status"/"cheaper" teams -- saving time on the DB to API work a the expense of client teams might make sense.
- vaughan 6y ago
  > You've just turned your database schema into the API It might actually be quite easy to version an SQL api, because you have the DDL migrations files and could actually make things backwards compatible to some degree. You would have a translation layer to modify the queries. Of course, your migrations would need to be more detailed tracking how things map to each other. But probably it would get too complex in the end. But not that much different to changes to a REST api really.
ownagefool 6y ago
Not quite sure I get what's signing the request. Presumably if it's the client, it can be tampered with, so we're guessing that there's still an API layer signing things? That said, I guess you could just add row / column ACLs to the database user, and then map the database user to the app user.
- lelanthran 5y ago
  > Not quite sure I get what's signing the request. Server sends the query so server can send the signature hash with it, so the server signs the query with the server's key and sends both query and signature. If the server ever receives a query without a signature or with a signature that is incorrect it can ignore or generate an error.
vaughan 6y ago
> you can statically extract the SQL/GraphQL Yeh, that's the best idea. A babel plugin could do it easily. I already have have watcher script calling `graphql-codegen` to add types to my graphql queries, which works fine in the workflow.
jayd16 6y ago
I don't think you would use SQL directly. You do want something like GraphQL or FirebaseDB where the language is designed around untrusted users.
dragonwriter 6y ago
> But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters Or...you could just expose a schema with views and sprocs where their definitions use only the SQL you are comfortable with against the base tables, which live off in their own schema isolated from external users. Same effect, but doesn’t require a whole new custom layer on top to reimplement what has been a basic feature of RDBMS engines for decades.
gervwyk 6y ago
> Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend. We’ve taken this approach for implementing database queries in Lowdefy [0] (SQL support will be live in next version, implementation using knex [1]. Also a “shared state” between backend and frontend makes parsing paramaters to queries on the backend really seamless and in return a great dev experience. Also allows to to only parse some parameters like secrets only on the backend. What we are experiencing with Lowdefy apps is that bringing the data model closer to the UI makes coding and UI maintainability of simple projects very easy. This approach works exceptional for us in the BI reporting space where you primarily aggregate data. However as soon as you move out of the CRUD space to more complex backend logic, extracting the logic to a single interface again simplified the implementation a lot, in fact apps very quickly becomes hard to maintain in my experience. This does not mean that there is no place for easy CRUD dev tools like OP, but I do believe that an API like solution is required when life gets more complex or even just transactional. But there is also more creative ways to solve this problem. [0] - https://github.com/lowdefy/lowdefy https://github.com/lowdefy/lowdefy [1] - https://github.com/knex/knex https://github.com/knex/knex

smallnamespace 6y ago

I’m basically doing this for an app - each endpoint is just a parametrized query and RLS in the database enforces security/auth constraints. The middleware doesn’t do anything except populate query parameters and do a bit of sanity checking.

asguy 6y ago

Or you could could use Row Level Security, and/or stored functions/procedures for access controls. You can put these constraints in the DB, and just give each of your users a DB role.

zachrip 6y ago

Another issue is part of what makes a company valuable is their biz logic. Moving all of that to the frontend means you can no longer guard it. Even if you can extract it to the backend, how many REST/GQL endpoints use a single query? And where do the parameters get supplied if not from a route specific handler?

throwawaygh 6y ago

This is a really interesting interface design, but seems like for any non-trivial database it would be too easy to make mistakes and give the client too much access. Also seems like it's be really easy to end up with performance issues. Also, there's basically zero information hiding. The database is your interface. Changing the schema in any way whatsoever is a breaking change to the API! You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. You've just turned your database schema into the API :'( But I haven't actually seen a codebase that does this, so maybe I'm wrong. Have you used this approach in a project with more than a few developers and a large database? How did it go?

dane-pgp 6y ago
> You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. Another way to look at it is that changing the database schema is already a problem for the API which has to talk to it, so you're not really saving yourself any work by putting an extra layer between the frontend and the database. In fact, if anything, you're doubling the amount of work, because there are now two interfaces (DB to API, and API to frontend) whose backwards compatibility you have to worry about.
- throwawaygh 6y ago
  Yes. The decision calculus here really boils down to how many clients the API has and how much you care about those clients. If you have 100 other teams each with 5+ developers consuming your API -- or especially if your API is a product -- then keeping the API to frontend interface relatively stable with rare breaking changes is much more valuable than saving yourself even a major time commitment on DB to API work. On the other hand, if you have relatively few clients -- or if the clients are "lower status"/"cheaper" teams -- saving time on the DB to API work a the expense of client teams might make sense.
vaughan 6y ago
> You've just turned your database schema into the API It might actually be quite easy to version an SQL api, because you have the DDL migrations files and could actually make things backwards compatible to some degree. You would have a translation layer to modify the queries. Of course, your migrations would need to be more detailed tracking how things map to each other. But probably it would get too complex in the end. But not that much different to changes to a REST api really.

ownagefool 6y ago

Not quite sure I get what's signing the request. Presumably if it's the client, it can be tampered with, so we're guessing that there's still an API layer signing things? That said, I guess you could just add row / column ACLs to the database user, and then map the database user to the app user.

lelanthran 5y ago
> Not quite sure I get what's signing the request. Server sends the query so server can send the signature hash with it, so the server signs the query with the server's key and sends both query and signature. If the server ever receives a query without a signature or with a signature that is incorrect it can ignore or generate an error.

vaughan 6y ago

> you can statically extract the SQL/GraphQL Yeh, that's the best idea. A babel plugin could do it easily. I already have have watcher script calling `graphql-codegen` to add types to my graphql queries, which works fine in the workflow.

jayd16 6y ago

I don't think you would use SQL directly. You do want something like GraphQL or FirebaseDB where the language is designed around untrusted users.

dragonwriter 6y ago

> But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters Or...you could just expose a schema with views and sprocs where their definitions use only the SQL you are comfortable with against the base tables, which live off in their own schema isolated from external users. Same effect, but doesn’t require a whole new custom layer on top to reimplement what has been a basic feature of RDBMS engines for decades.

gervwyk 6y ago

> Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend. We’ve taken this approach for implementing database queries in Lowdefy [0] (SQL support will be live in next version, implementation using knex [1]. Also a “shared state” between backend and frontend makes parsing paramaters to queries on the backend really seamless and in return a great dev experience. Also allows to to only parse some parameters like secrets only on the backend. What we are experiencing with Lowdefy apps is that bringing the data model closer to the UI makes coding and UI maintainability of simple projects very easy. This approach works exceptional for us in the BI reporting space where you primarily aggregate data. However as soon as you move out of the CRUD space to more complex backend logic, extracting the logic to a single interface again simplified the implementation a lot, in fact apps very quickly becomes hard to maintain in my experience. This does not mean that there is no place for easy CRUD dev tools like OP, but I do believe that an API like solution is required when life gets more complex or even just transactional. But there is also more creative ways to solve this problem. [0] - https://github.com/lowdefy/lowdefy https://github.com/lowdefy/lowdefy [1] - https://github.com/knex/knex https://github.com/knex/knex