4 ms·

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 co

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 6y 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 6y 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)
  - jwdunne 6y ago
    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.
    - skdv 6y ago
      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

wenc 6y 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 6y 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)
- jwdunne 6y ago
  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.
  - skdv 6y ago
    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