4 ms·

Not to mention code reuse. Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need t

Not to mention code reuse. Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". In entity framework it's easy to save that as an expression that can be reused in multiple places. It's hard to do that in sql in a maintainable way. Not to mention refactoring issues. If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL.

polygotdomain 8y ago
> It's hard to do that in sql in a maintainable way <cough>Views</cough>
- btilly 8y ago
  I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. Keeping the logic in one place is better than the alternative. And of the two, the tools to manage source control are better.
  - lenkite 8y ago
    We use liquibase https://www.liquibase.org/ https://www.liquibase.org/ for tracking, managing and applying any schema changes. The liquibase changelogs are part of our project releases.
    - OJFord 8y ago
      > Supports XML, YAML, JSON and SQL formats XML is the default on the page, but wow the SQL is _so_ much shorter and more readable than the others! To the extent that I wonder what's the point, the obvious is machine readabaility, but one not three, and is SQL really so machine-unreadable that it's worth the extra depth and complexity?
      - radiowave 8y ago
        With Liquibase, when you write changesets using SQL, you have to write the rollback code yourself. When you write them with Liquibase's built-in operations in (for example) XML, the rollback is derived automatically. I write SQL almost daily, but for the things that can easily be expressed with Liquibase's operations, I can write them faster and with fewer errors in XML, so long as I'm using a text editor with XML schema support (hence: autocompletion and validation as-you-type). PyCharm works well for this.
  - polygotdomain 8y ago
    >I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Just like concepts like inheritance can be a burden when overused, views can be overused as well. Like inheritance, having views go too many levels deep generally winds up causing more harm than good. Used correctly though, views are able to capture and centralize concepts in a way that are reusable, can be more easily tuneable, and make maintenance easier in the long run. Queries can be simpler and encompass less responsibility as the common logic is implemented by the view, and the logic that is specific to that query is more readily apparent. IMHO thinking of the DB as just a collection of tables misses out on a key db feature. >Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. I disagree that there's no good solution, but I understand that it's not as "out of the box" as many ORMs offer. That being said, writing SQL scripts that coordinate schema changes is not hard, and any CI/CD pipeline that can run commandline utilities can run a sql script against a database. At my work, we use DbUp (https://dbup.github.io/ https://dbup.github.io/) and are able to easily write small, isolated SQL scripts that correspond with code changes and get committed to source control, run all of those scripts with our CI/CD pipeline across multiple sharded databases, and do so quite seamlessly. We don't have any questions about migrations or what a tool did, as running the SQL script individually is the same as it runs on deployment. Coordinating db changes on developer machines is a breeze as well. >And of the two, the tools to manage source control are better. All of our sql scripts are committed into source control. It's not any easier or harder than looking at a code file, an html file, or a config file to get to the logic your working with.
    - btilly 8y ago
      In the common setup where there is a central database shared by developers, you really can't keep the database in sync with all of the code. Furthermore the manual nature of keeping things in sync means that there will be errors. Hopefully those are caught in staging, but I've seen this not always work.
dragonwriter 8y ago
> Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". [...] It's hard to do that in sql in a maintainable way. Seems a textbook case of using a view for sharing query logic. > If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL. If you can do it in C# instead of SQL, you must be talking about a change in the interface used by an app (so, with good DB design, a view, not a base table). But this is very safe in SQL, you are changing a view definition and, if its a read/write view, insert and update triggers.
- Dayshine 8y ago
  >Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update. You can't combine views easily, you can't cache results, you don't get static type checking.
  - GordonS 8y ago
    I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you? > difficult to perform performance analysis on I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes > hard to update It's just SQL, and for views typically rather basic SQL at that. I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.

polygotdomain 8y ago

> It's hard to do that in sql in a maintainable way <cough>Views</cough>

btilly 8y ago
I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. Keeping the logic in one place is better than the alternative. And of the two, the tools to manage source control are better.
- lenkite 8y ago
  We use liquibase https://www.liquibase.org/ https://www.liquibase.org/ for tracking, managing and applying any schema changes. The liquibase changelogs are part of our project releases.
  - OJFord 8y ago
    > Supports XML, YAML, JSON and SQL formats XML is the default on the page, but wow the SQL is _so_ much shorter and more readable than the others! To the extent that I wonder what's the point, the obvious is machine readabaility, but one not three, and is SQL really so machine-unreadable that it's worth the extra depth and complexity?
    - radiowave 8y ago
      With Liquibase, when you write changesets using SQL, you have to write the rollback code yourself. When you write them with Liquibase's built-in operations in (for example) XML, the rollback is derived automatically. I write SQL almost daily, but for the things that can easily be expressed with Liquibase's operations, I can write them faster and with fewer errors in XML, so long as I'm using a text editor with XML schema support (hence: autocompletion and validation as-you-type). PyCharm works well for this.
- polygotdomain 8y ago
  >I have spent enough of my life untangling a mess of views that create performance problems to doubt that views are maintainable. Just like concepts like inheritance can be a burden when overused, views can be overused as well. Like inheritance, having views go too many levels deep generally winds up causing more harm than good. Used correctly though, views are able to capture and centralize concepts in a way that are reusable, can be more easily tuneable, and make maintenance easier in the long run. Queries can be simpler and encompass less responsibility as the common logic is implemented by the view, and the logic that is specific to that query is more readily apparent. IMHO thinking of the DB as just a collection of tables misses out on a key db feature. >Also, there is usually no good solution for keeping the database in sync with code releases. With the result that database+code change synchronization tends to be a source of deployment complexity. I disagree that there's no good solution, but I understand that it's not as "out of the box" as many ORMs offer. That being said, writing SQL scripts that coordinate schema changes is not hard, and any CI/CD pipeline that can run commandline utilities can run a sql script against a database. At my work, we use DbUp (https://dbup.github.io/ https://dbup.github.io/) and are able to easily write small, isolated SQL scripts that correspond with code changes and get committed to source control, run all of those scripts with our CI/CD pipeline across multiple sharded databases, and do so quite seamlessly. We don't have any questions about migrations or what a tool did, as running the SQL script individually is the same as it runs on deployment. Coordinating db changes on developer machines is a breeze as well. >And of the two, the tools to manage source control are better. All of our sql scripts are committed into source control. It's not any easier or harder than looking at a code file, an html file, or a config file to get to the logic your working with.
  - btilly 8y ago
    In the common setup where there is a central database shared by developers, you really can't keep the database in sync with all of the code. Furthermore the manual nature of keeping things in sync means that there will be errors. Hopefully those are caught in staging, but I've seen this not always work.

dragonwriter 8y ago

> Say for instance we have one query that finds eligible bachelors near me and another that finds newly eligible bachelors. We need two queries but they both rely on the same underlying domain concept of "eligible bachelors". [...] It's hard to do that in sql in a maintainable way. Seems a textbook case of using a view for sharing query logic. > If I decide to change a datetime field called time to split it into a datetime and offset that change is far safer in c# than it is SQL. If you can do it in C# instead of SQL, you must be talking about a change in the interface used by an app (so, with good DB design, a view, not a base table). But this is very safe in SQL, you are changing a view definition and, if its a read/write view, insert and update triggers.

Dayshine 8y ago
>Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update. You can't combine views easily, you can't cache results, you don't get static type checking.
- GordonS 8y ago
  I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you? > difficult to perform performance analysis on I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes > hard to update It's just SQL, and for views typically rather basic SQL at that. I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.