4 ms·

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

by wenc 7y ago

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.

jwdunne 7y ago
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.
- wenc 7y ago
  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.
  - Izkata 7y ago
    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)
cgh 7y ago
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.
cryptonector 7y ago
Not only that, but VIEWs can have INSTEAD OF triggers, which then lets you build powerful abstractions in SQL.
- wenc 7y ago
  Amazing. I didn't know it was possible to write to a VIEW.
  - cryptonector 7y ago
    Changes everything no?
deckard1 7y ago
> 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.
- deleted 7y ago
  [deleted]
projektfu 7y ago
Are you also promoting CQRS?
- wenc 7y ago
  Not really. CQRS adds too much complexity in many cases.
hobs 7y ago
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.
MaxBarraclough 7y ago
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 https://loopback.io/doc/en/lb4/Database-connectors.html
- wenc 7y ago
  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.

by wenc 7y ago

jwdunne 7y ago

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.

wenc 7y ago
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.
- Izkata 7y ago
  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)

cgh 7y ago

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.

cryptonector 7y ago

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

wenc 7y ago
Amazing. I didn't know it was possible to write to a VIEW.
- cryptonector 7y ago
  Changes everything no?

deckard1 7y ago

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

deleted 7y ago
[deleted]

projektfu 7y ago

Are you also promoting CQRS?

wenc 7y ago
Not really. CQRS adds too much complexity in many cases.

hobs 7y ago

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.

MaxBarraclough 7y ago

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 https://loopback.io/doc/en/lb4/Database-connectors.html

wenc 7y ago
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.