This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/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

Here's a powerful mindset trick: think of SQL views as an sort of a REST API, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical).

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's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View.

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/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data.

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/indirection, and SQL views provide exactly that.

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 "API" remains consistent throughout.

Two questions:

Do you have any example code that shows how this works? I get what you’re saying intuitively but example code will help me bring it to table.

What about cross cutting concerns? I’ve found stored procedures to be a performant solution here. By version controlling them, and limiting to pure functions, I found them quite maintainable. Would you instead just define a new view, or extend an existing one, or refactor into a separate view that’s then joined into the existing views?

I haven’t delved as far as views, admittedly. One app featured a bit of complicated logic and eschewing the ORM in favour of raw SQL helped (instead of getting tangled up in Demeter chains). Despite new developers, who have used purely ORM for years, shitting their pants at the raw SQL, both of us who worked on it felt it was the right call. We feel much better about leveraging more of the database in new projects.

In fact, when we took our experience to a Django project, my colleague wrote a Manager method in such a way that an ORM favouring developer questioned because it looked too much like SQL. But it was the obvious implementation to us after using raw SQL. And, after benchmarking, the most performant.

1. Let me try with a simple example. Suppose you have a fact table A with fields (ItemID, Item, Amt) where Amt is in USD. Rule of thumb is: don't expose A to the consumer; instead write a SQL View V_A and expose that instead:

  CREATE VIEW V_A AS SELECT ItemID, Item, Amt FROM A

  CREATE VIEW V_A_EURO AS SELECT ItemID, Item, Amt * 0.92 AS AmtEUR FROM V_A

Suppose the underlying table A now changes. There's been a merger and the company now stops reporting currencies in USD, and everything is now in British Pounds so your DBA adds a field AmtGBP and starts populating that field instead. Amt still contains historical data, but moving forward the data in Amt will be NULLs; AmtGBP is the new internal baseline currency. From a VIEW perspective, all you have to do is:

  ALTER VIEW V_A AS SELECT ItemID, Item, ISNULL(Amt, AmtGBP * 1.23) AS Amt FROM A

  Table A <- View V_A <- View V_A_Euro

(Possibly a bad idea for currency conversion for various reasons, but just wanted to mention it since this type of migration may be just right for other data)

For production, I created a command on the app that loads the stored procedures into the DB idempotently on each deployment/configuration. This won’t work if the app server scales but allowed us to store stored procs in VC.

For development, we ran the command on each page load as a sort of hacky “live reload”. It didn’t work well (which highlighted the issue with scalability in production) because Postgres, fairly, doesn’t like parallel redefinitions of the same stored proc.

I’m not sure how best to automate this. For production, seems like a case of running a command once per DB server.

And in development, using a fs watcher that loads changes in.

But I don’t know, this is new territory for us and I couldn’t find anything out there to manage it within the context of a web framework. Perhaps I’m searching for the wrong thing.

(.sql/.rb/.py, it doesn't matter).

And you have a "migrations" table in your database that contains the numbers of the migrations that have been run:

  select * from migrations;
      version
  ----------------
   1765
   2891

How to do with functions/procedures?

You commit the function definitions in a functions folder to your version system like:

  db/functions/report_x.sql
  CREATE or REPLACE function report_x() returns ...

  class UpdateReportXFun < ActiveRecord::Migration[5.2]
    def up
      execute File.read(
        Rails.root.join('db','functions','report_x.sql')
      )
    end
  end

I'm doing a lot of work in a Rails codebase where I edit views/functions/procedures all the time. My setup is quite usable.

My current setup: I edit those .sql files and run them with psql in my local while developing (without writing any migration yet).

I have some like this running on one screen to make sure the modified files are executed by psql immediately as I change them (you could use `guard` too):

  find ~/projectx/db/functions -type f -name "*.sql" | entr -d -p psql db_name -f /_

--

Once I finish and I know everything is great, I just add the migration. The migration is simply an indicator of which files I've modified and to specify the right order to run them (which is useful if they are dependencies), like:

  # migration
  def up
    execute File.read(function1_sql_file)
    execute File.read(function2_sql_file)
  end

[ I'm also using pgTAP to write tests for functions, it's quite nice :) ]

I’ll have a look at pgTAP too. Naturally we want to test in CI, I can see this working really well. I did look at myTAP too, since we have a few MySQL instances.

You are conflating many disparate things here.

SQL is a language (DSL) for accessing data. REST is a protocol and a data transport method (one could surmise a way to do REST without HTTP, but when reasonable people refer to REST they mean HTTP (over TCP (over IP (etc.)))).

Even REST is not an API. You can't do anything with a GET or a POST without other abstractions built on top of that. So I don't understand how anyone could make performance claims beyond something like "HTTP is slow" and "binary transport is faster", with regards to SQL vs. REST/HTTP.

SQL does not define how you receive your data. Databases have different methods of sending SQL and responding to SQL. Oracle, MSSQL, MySQL, etc.

> This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers

That's the entire point of an API. Any API. REST APIs, even.

If you want to make simple views that expose useful nouns I am down with it, but I have seen it taken too far too many times.

If you are building something from scratch, or your consumers don't have a hard requirement for going through REST, I would go directly to the database view.

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.

Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.

> stored procedures. These define a strong contract with your DB

Exactly. It took me years to grasp this, but when I did my code became much simpler.

A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost).

Whereas a SQL interface returns data in its native tabular format with all the correct types.

1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess.

2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again.

I do wonder - what do people generally do in practice for overcoming these objections? Does anyone have any other objections around using stored procedures?

2) TBH we never built anything complex enough to need this, and I would tend to think that if you do need this you're probably overcomplicating your DB. But you could probably do something that creates a temp DB, populates example data, and then runs tests.

My previous employer used some homespun systems for the same. A bit clunky, but easier to do than most people thinks.

You also have graphical debuggers for them.

Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.

Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop

I'm working on a project with a Postgres database, and as it gets more complex I'm moving more stuff into stored procedures, pretty much wherever a single action requires multiple statements in series (e.g. check if this thing exists, check a value, get the id of some other thing, on success update another table).

Of course I would prefer to leave as much of it in the middle tier as possible because the ergonomics are better there but I don't want to sacrifice performance.

Yeah, if all of your queries are slow, you've got more work to do, but that doesn't change the overall process of optimization.

High performance SQL code should stay on the database, instead of doing wire round trips.

That pretty much describes my career, except it was more than 2-3 weeks. I agree there is value in being familiar with the ins and outs of SQL, but...

> As soon as people get a basic comfort level with SQL, it become almost automatic.

I don't know about that so much. I find that as soon as I need to write something much more complex than a simple select statement I am all but guaranteed to get caught by SQLs many gotchas. Eventually you realize the mistake, and that experience provides the knowledge to know how to correct it, but a good language helps guide you away from being trapped by those mistakes in the first place.

SQL is not a good language. It was clever for its time, but we've learned a lot about language theory in the many decades since. It is a travesty that we haven't put more effort into designing a modern database query language. To use SQL in 2020 is like writing software in COBOL when you could be writing software in Rust. Where is the declarative query language equivalent of Rust, Haskell, etc.?

The NoSQL movement was supposed to be about improving on query languages, but sadly it soon turned into "NotRelational" instead, which killed off any momentum away from SQL that was built.

Maybe Cypher from Neo4j?

I did this (actually for longer), but it always washes away.

One challenge for me is I don't see an incentive to learn complex SQL setups unless absolutely necessary. Since I tend to work at startups, development velocity is way more important than optimized codebases. I'd rather write the "dumb" solution that nearly every engineer can grok than the "elegant" solution that goes above people's heads.

That aggregate function, yes, I know I can do it 100% in SQL. However, it's much easier for me to selectively pull parts into memory and modify them with language tools I work with everyday. When the junior needs to modify that function, they can do it with tools they're most familiar with.

Implementing logic is single SQL queries can definitely be a bit challenging, but I thought it was also quite rewarding and liberating - Raw SQL is incredibly powerful!

Then I was empowered and did not miss for loops.

Now I still miss for loops.

I'll show off the POC if I ever get around to smoothing the rough edges.

i did tons of sql couple of years ago on a reporting team. Now I do android dev fulltime and don't remember any SQL beyond basics, highly doubt it will all come back to me if i tried.