There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures)

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.

This is a very interesting comment!

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.

Briefly,

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

Then suppose a European counterpart wants to use the same API but needs the amounts to be in Euros. You can write another view: (in practice the conversion 0.92 shouldn't be a static number, this is just for illustration)

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

Expose this to the Europeans. You can keep stacking views on top of other views. Your U.S. consumers will always see the data through the lens of V_A and your European consumers will always see it through V_A_Euro.

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

Your V_A and V_A_EURO consumers (could be Tableau, Excel, other SQL views, etc.) will still happily receive data per usual, unaware of the internal changes (the British are coming!) that have occurred. Contract kept.

  Table A <- View V_A <- View V_A_Euro

2. Cross cutting concerns come in many forms so not sure if I can address. Stored Procedures are definitely an acceptable abstraction -- they accept parameters and can return tabular results just like VIEWs. They do however work in a procedural manner (like subroutines) and can produce side effects, which is sometimes necessary to accomplish very specific tasks. VIEWs on the other hand are more similar to pure functions (unless random number generation is involved) with no side effects. Because views are dynamic, they flex with your data and VIEW definitions.

There's another step that could be added there, too: After the ALTER VIEW, V could be slowly incrementally updated over however long you need to back-populate AmtGBP, and the views will continue to just work the whole time. Once done, V_A can be simplified to remove the ISNULL and Amt, then Amt dropped from V. That way you don't get build-up of cruft over the years, and the experience isn't interrupted for the migration.

(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)

Is there anything you recommend for handling SQL definitions in version control, development and production envs?

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.

Web frameworks like Rails/Django use the idea of migrations to make changes to the database. The idea is that you have a set of migration scripts like: migrations/1765_create_table_users.sql migrations/2891_store_procedure_x.sql migrations/5892_change_store_procedure_x.sql

(.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

Every time you deploy to production automatically check which scripts in your db/migrations folder don't exist in the migrations table and run them. (In the current example, you'd run the 5892_change_store_procedure_x.sql that hasn't been run yet).

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 ...

When you change this file, nothing happens, you need to create a migration to re-run this code once. In rails migrations would be:

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

Yeah, I’m aware of that, thank you. I was wondering if there was a way with a faster feedback loop and allowed for bug fixes without creating a new migration.

You don't need to write the migration until you're done. It's possible to have a very tight feedback loop in any case.

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 /_

and I edit the db/functions/*.sql files freely, adding things, changing behaviour of functions and they are updated on the fly. (I can run tests -or try things in the browser- to verify my changes work as I expect).

--

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 could have an alias that automates generating that migration but it's just 4 lines...

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

Oh wow, now I see what you mean. Thank you! That’s work great. I wasn’t aware of ‘entr’ either, that’s exactly what I had in mind!

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.

Agreed, and a good example of this is implementing search. You can define a view on top of your searchable entities that includes the urls to the entities, as well as searchable metadata (entity descriptions or whatever). So when you add new searchable items, you just update the view to include them and the code to select from the view doesn't change.

> highly performant API that outperformed REST at scale -- it's called SQL

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.

Not only that, but VIEWs can have INSTEAD OF triggers, which then lets you build powerful abstractions in SQL.

Amazing. I didn't know it was possible to write to a VIEW.

Changes everything no?

Right, and this is normal except that people take it too far, I love to find 10(!) depth nested views hiding table valued functions and scalar functions everywhere - you cant reason about the rat's nest created.

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.

What do you make of tools like LoopBack which automatically map REST to SQL (without you having to write code for each mapping)? https://loopback.io/doc/en/lb4/Database-connectors.html

Postgres has something similar called pgREST too. I think I would only adopt these kinds of interfaces if the consumer insists on accessing the data through a REST interface.

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.

Are you also promoting CQRS?

Not really. CQRS adds too much complexity in many cases.

This is trying to avoid the reality that the data is in a database. A database is not just a flat storage area that can be poked by code from outside. It has a lot of relationships and integrity constraints that need to be maintained by the DB itself, and all this is expressed in SQL/stored procs. Trying to avoid this is basically removing the power of a DB and converting it into just an expensive and cumbersome storage area.

But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable.

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.

> Instead of REST endpoints (or gRPC or whatever), create

> 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.

I remember people I used to work with arguing against stored procedures for two main reasons.

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?

1) I find this kinda funny. Why are you worried about this in SQL but not for other code? It's not like it's hard to chuck Python, JS, Ruby, ASP, etc code straight into prod, you just don't do that because it's stupid. Don't do it for SQL either. If you really want to, build out user permissions that only allow your CICD system to change them.

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.

I'm using Red-gate tools for all of the above. They are covering source control and unit testing and a lot more, but only for SQL Server.

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

Both Oracle and SQL Server do support versioning tools and unit tests for their stored procedures.

You also have graphical debuggers for them.

But going too far the other way is how you end up with performance 100-1000x worse than it should be. Which is an actual thing that happens quite often in the wild. Also often ends up heavily dependent on some ORM or framework, making rewrites or multi-client DB access dangerous and painful.

You can create pretty complex SQL queries with SqlAlchemy's ORM, without going back and forth to the DB.

This is the exact fear of SQL that's being talked about. You can make a mess of anything but its not hard to have a well maintained relational DB with minimal cruft.

In my observations, ORM use has a perspicuous relationship to piles of arcane spaghetti code.

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.

When you give an object the power to access to the db, you invariably end up with db access everywhere you can pass that object.

There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc). I worked on DBs writing SQL for several years with only minimal code as stored procedures.

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

How did you minimize roundtrips between server and DB, or did you find that they were not a big concern?

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.

I think they're saying that you profile your changes on an individual query.

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.

Given how strong typed PL/SQL is, based on Ada, graphical tooling like SQL Developer exists, I wonder why it is much worse than either Java or PHP.

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