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