<story><title>Things I wished more developers knew about databases</title><url>https://medium.com/@rakyll/things-i-wished-more-developers-knew-about-databases-2d0178464f78</url></story><parent_chain><item><author>TremendousJudge</author><text>&amp;gt; The application language was a pass through later between the client and the database.&lt;p&gt;This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL&amp;#x2F;SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with arcane and inscrutable logic</text></item><item><author>brightball</author><text>I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.&lt;p&gt;IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database.&lt;p&gt;Your goal was to accomplish as much as possible in a single query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left&amp;#x2F;inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience.&lt;p&gt;And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling &amp;amp; bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole &amp;quot;NoSQL&amp;quot; situation, along with the idea that it was somehow better to repeat the same data thousands of times over.&lt;p&gt;A whole lot of people pushed back against this and eventually, it mostly ran its course. I&amp;#x27;ve often seen resistance to SQL to be driven by &lt;i&gt;fear&lt;/i&gt; of SQL more so than  anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.</text></item><item><author>wenc</author><text>I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn&amp;#x27;t really grok SQL and that he&amp;#x27;d rather work with a &amp;quot;real&amp;quot; procedural programming language to just store and retrieve data.&lt;p&gt;Part of it may be due to the fact SQL isn&amp;#x27;t really a programming language but a declarative DSL for manipulating sets and tables. Things like GROUP BYs and PARTITION BYs (window functions) that come naturally to mathematical types&amp;#x2F;functional programmers are less intuitive to procedural programmers.&lt;p&gt;I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it&amp;#x27;s more attuned to a programmatic mindset.&lt;p&gt;(this is not universally true of course -- many programmers have no issues with SQL at all.)</text></item></parent_chain><comment><author>wenc</author><text>There&amp;#x27;s a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures)&lt;p&gt;Here&amp;#x27;s a powerful mindset trick: think of &lt;i&gt;SQL views as an sort of a REST API&lt;/i&gt;, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical).&lt;p&gt;I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API that outperformed REST at scale -- it&amp;#x27;s called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables&amp;#x2F;schemas were to change, your consumers don&amp;#x27;t care as long as they can access the SQL View.&lt;p&gt;SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add&amp;#x2F;transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I&amp;#x27;ve built many systems by composing SQL views and they&amp;#x27;re very maintainable and very flexible. They&amp;#x27;re kind of like function compositions but on tabular data.&lt;p&gt;The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction&amp;#x2F;indirection, and SQL views provide exactly that.&lt;p&gt;This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The &amp;quot;API&amp;quot; remains consistent throughout.</text></comment>
<story><title>Things I wished more developers knew about databases</title><url>https://medium.com/@rakyll/things-i-wished-more-developers-knew-about-databases-2d0178464f78</url></story><parent_chain><item><author>TremendousJudge</author><text>&amp;gt; The application language was a pass through later between the client and the database.&lt;p&gt;This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL&amp;#x2F;SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with arcane and inscrutable logic</text></item><item><author>brightball</author><text>I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.&lt;p&gt;IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database.&lt;p&gt;Your goal was to accomplish as much as possible in a single query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left&amp;#x2F;inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience.&lt;p&gt;And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling &amp;amp; bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole &amp;quot;NoSQL&amp;quot; situation, along with the idea that it was somehow better to repeat the same data thousands of times over.&lt;p&gt;A whole lot of people pushed back against this and eventually, it mostly ran its course. I&amp;#x27;ve often seen resistance to SQL to be driven by &lt;i&gt;fear&lt;/i&gt; of SQL more so than  anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.</text></item><item><author>wenc</author><text>I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn&amp;#x27;t really grok SQL and that he&amp;#x27;d rather work with a &amp;quot;real&amp;quot; procedural programming language to just store and retrieve data.&lt;p&gt;Part of it may be due to the fact SQL isn&amp;#x27;t really a programming language but a declarative DSL for manipulating sets and tables. Things like GROUP BYs and PARTITION BYs (window functions) that come naturally to mathematical types&amp;#x2F;functional programmers are less intuitive to procedural programmers.&lt;p&gt;I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it&amp;#x27;s more attuned to a programmatic mindset.&lt;p&gt;(this is not universally true of course -- many programmers have no issues with SQL at all.)</text></item></parent_chain><comment><author>breischl</author><text>But now you&amp;#x27;re filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable.&lt;p&gt;I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code.&lt;p&gt;Of course you &lt;i&gt;can&lt;/i&gt; abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn&amp;#x27;t. There&amp;#x27;s no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.</text></comment>