4 ms·

usually, every interaction with your database is its own unique code path and query it's pretty rare for queries to be dynamically composed from arbitrary sub-

usually, every interaction with your database is its own unique code path and query it's pretty rare for queries to be dynamically composed from arbitrary sub-queries if this is a problem you need to solve then ORMs certainly make more sense, but even in this case I find query builders to be more effective the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types

Daishiman 3y ago
You never built admin interfaces, faceted search or dynamic query filters?
- preseinger 3y ago
  sure, sometimes, rarely -- these are exceptions, not rules in general, it should not be possible for user input to produce arbitrarily complex queries against your database each input element in an HTML form should map to a well-defined parameter of a SQL query builder, like, you shouldn't be dynamically composing sub-queries based on the value of a text field, the value should add a where or join or whatever other clause to the single well-defined query sometimes this isn't possible but these should be super rare exceptions
  - Daishiman 3y ago
    I prefer using something like Rails or Django to build 10 fully working CRUD interfaces with well-defined yet dynamic filters in a day instead of spending two weeks needlessly writing the equivalent code by hand.
    - preseinger 3y ago
      why would it take you two weeks to write 10 SQL simple queries?
      - Daishiman 3y ago
        10 simple CRUDs you mean? With dynamic filters, admin UI, auth, tables, and so on? Because these frameworks allow you to do that in a single day.
        preseinger 3y ago
        i'm running out of ways to say that a CRUD endpoint should not have dynamism in the sense that you mean /users/:id should map to 1 endpoint that's parameterized on userid /search?userid=:userid&tag=:tag should map to 1 endpoint that's parameterized on userid and tag(s) endpoints should be simple to write
        kaba0 3y ago
        Yeah and programs should also be simple, but there would be no value to them that way.
        ehutch79 3y ago
        You’ve never actually implemented a real world implementation, have you? You’re going to have parameters that are compound. You’re going to end up filtering on objects 3 relations removed, or deal with nasty syncing of normalization. You’ll have endpoints with generic relations, like file uploads, where the parent isnt a foreign key. It’s going to be a mess. They will NOT always be simple to write.
  - jupp0r 3y ago
    Have you ever heard of APIs?
lmm 3y ago
> it's pretty rare for queries to be dynamically composed from arbitrary sub-queries I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not. > the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.
- preseinger 3y ago
  huh? when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method fn search(q string) -> result return db.query(`SELECT id, text FROM table WHERE text LIKE $1;`, q) this is a single query, not multiple the db accepts a string and parses it to an AST, it does not accept a typed value this means the interface is the string unbalanced parens and whatever other invalid syntax is obviously caught by tests
  - lmm 3y ago
    > when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method > this is a single query, not multiple And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them. > the db accepts a string and parses it to an AST, it does not accept a typed value > this means the interface is the string The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime. > unbalanced parens and whatever other invalid syntax is obviously caught by tests Tests are a poor substitute for types.

Daishiman 3y ago

You never built admin interfaces, faceted search or dynamic query filters?

preseinger 3y ago
sure, sometimes, rarely -- these are exceptions, not rules in general, it should not be possible for user input to produce arbitrarily complex queries against your database each input element in an HTML form should map to a well-defined parameter of a SQL query builder, like, you shouldn't be dynamically composing sub-queries based on the value of a text field, the value should add a where or join or whatever other clause to the single well-defined query sometimes this isn't possible but these should be super rare exceptions
- Daishiman 3y ago
  I prefer using something like Rails or Django to build 10 fully working CRUD interfaces with well-defined yet dynamic filters in a day instead of spending two weeks needlessly writing the equivalent code by hand.
  - preseinger 3y ago
    why would it take you two weeks to write 10 SQL simple queries?
    - Daishiman 3y ago
      10 simple CRUDs you mean? With dynamic filters, admin UI, auth, tables, and so on? Because these frameworks allow you to do that in a single day.
      - preseinger 3y ago
        i'm running out of ways to say that a CRUD endpoint should not have dynamism in the sense that you mean /users/:id should map to 1 endpoint that's parameterized on userid /search?userid=:userid&tag=:tag should map to 1 endpoint that's parameterized on userid and tag(s) endpoints should be simple to write
        kaba0 3y ago
        Yeah and programs should also be simple, but there would be no value to them that way.
        ehutch79 3y ago
        You’ve never actually implemented a real world implementation, have you? You’re going to have parameters that are compound. You’re going to end up filtering on objects 3 relations removed, or deal with nasty syncing of normalization. You’ll have endpoints with generic relations, like file uploads, where the parent isnt a foreign key. It’s going to be a mess. They will NOT always be simple to write.
- jupp0r 3y ago
  Have you ever heard of APIs?

lmm 3y ago

> it's pretty rare for queries to be dynamically composed from arbitrary sub-queries I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not. > the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.

preseinger 3y ago
huh? when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method fn search(q string) -> result return db.query(`SELECT id, text FROM table WHERE text LIKE $1;`, q) this is a single query, not multiple the db accepts a string and parses it to an AST, it does not accept a typed value this means the interface is the string unbalanced parens and whatever other invalid syntax is obviously caught by tests
- lmm 3y ago
  > when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method > this is a single query, not multiple And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them. > the db accepts a string and parses it to an AST, it does not accept a typed value > this means the interface is the string The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime. > unbalanced parens and whatever other invalid syntax is obviously caught by tests Tests are a poor substitute for types.