Things I wished more developers knew about databases

Re:Things I wished more developers knew about databases by skdv on Friday April 24, 2020 @02:53AM (#22965345)

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

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by jwdunne on Friday April 24, 2020 @03:08AM (#22965425)

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.

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by skdv on Friday April 24, 2020 @04:25AM (#22965798)

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 :) ]

[ Parent | on HN ]

Re:Things I wished more developers knew about databases by jwdunne on Friday April 24, 2020 @01:20PM (#22970579)

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.

[ Parent | on HN ]