Database migrations and git flow


(Mike) #1

Hi there, folks.

I am currently migrating some forum to Discourse.
I have zero experience with RoR development, so I have few questions about Discourse and its infrastructure.

First of all, I’m planning to use the Discourse forum as-is, without custom plugins or source code modifications.
I, however, would like to write database migrations from time to time to simplify data transition from the staging instance of a forum to the production one.
I want to put these migrations in a git repository to fetch and apply them when rebuilding the app.

I would like to know the “true” way of achieving that.
Any help is highly appreciated, and links for further reading are welcome too.


(Jay Pfaffman) #2

I wrote a couple discourse importers before I knew ruby, so that’s good news. Just start with an importer that has the features you want and change the database queries, basically.

The importers have code to let you run them against a newer database, skipping the data imported already. Typically you’ll just run the importer on your development machine and upload the database to the production machine.

Having the production machine live and then importing more data is to be avoided.


(Mike) #3

Thanks, Jay.

I think there is a misunderstanding: I already have all the data required on the new Discourse-based forum.

I am just looking for the proper way to keep migrations I will write in the future in a separate repo and to apply them when rebuilding the Docker container with the forum.


(Jay Pfaffman) #4

What migrations? Did you modify discourse? I guess I will don’t understand your question.

If you’ve modified discourse it should be in a plugin and it should just work.


(Mike) #5

@pfaffman, I do not modify Discourse.

I do not wish to manually apply changes via the administration panel both on the production and the staging servers, but to use migrations for it.

Example use-case: I need to modify contents of the default email templates.

Here is the preferred flow:

  • Write the migration to update texts.
  • Commit and push changes.
  • Deploy the forum to the staging server.
  • Make sure everything works properly.
  • Deploy the forum to the production server.

(Jay Pfaffman) #6

Migrations refers to changes in the database structure. You’re not talking about pushing source code changes, which is what git is for.

Changing mail templates, and key much everything else not involving a custom plugin is done via the web interface and can’t really hurt anything, so it’s not the kind of thing that many people feel the need to do on a staging server.

If you were to develop a custom plugin that needed testing you’d host it in github and test it on a development or staging server and then rebuild to pull in the new code from git.

It sounds like you’re not planning to do that.


(Richard - DiscourseHosting.com) #7

@pfaffman although it’s not a very common approach, one can use database migrations not only for schema changes but for content editing as well. I’m a big fan of that, using ansible for almost everything, so I can script and automate instead of click my way through a web interface.

@mschekotov for this approach you don’t need to use Ruby specifically. You will be using this next to (or on top of) the Discourse setup, you’ll just be writing to the same database. If you’re familiar with any other kind of programming language or deployment system, just use that.

Below is something simple that I sometimes use. It’s just a bash script with a subdirectory called migrations. All you need to do is fill the migrations subdirectory with files and run the script. Or check it into git and use it on your production server :slight_smile:

Rules (as with any migrations concept)

  • to maintain the correct order of excecution, name your files YYYYMMDDHHMM-description.sql
  • never ever modify an existing file after it has been checked in and deployed, always create a new migration

It uses mysql to store the migrations. You can even keep it like that, or change it to postgresql. If you use the same database as your Discourse installation, make sure you use a table name that will never be used in Discourse. If you don’t use the same database or database middleware, make sure you flush it whenever you reinstall your Discourse.

#!/bin/bash
mysql << EOF

CREATE DATABASE IF NOT EXISTS meta DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS meta.migrations (
  migration varchar(255) NOT NULL,
  executed_at datetime NOT NULL,
  UNIQUE KEY migration (migration)
) DEFAULT CHARSET=utf8 ;

EOF

FILES=$(ls migrations/*.sql | sort)
for FILE in $FILES
do
    NUM=$(echo "SELECT migration FROM meta.migrations WHERE migration='$FILE';"  | mysql -N|wc -l)
    if [ "$NUM" == "0" ] 
    then
        echo "Running migration $FILE"
        LOG=log/$(basename $FILE).log
        echo "Running migration $FILE @ $DATE" > $LOG
        mysql -v < $FILE  >> $LOG 2> >(tee -a $LOG >&2)
        if [ $? -eq 0 ] 
        then
           echo "INSERT INTO meta.migrations VALUES('$FILE', NOW());" | mysql
        else 
           exit $?
        fi
    fi
done

(Jay Pfaffman) #8

OOOOh! That’s the kind of spend-a-hundred-hours-to-save-ten-minutes kind of thing that I love. I need to revisit Ansible.

It’s not immediately apparent how I would use that snippet to automate stuff in Discourse, but it’s definitely got me thinking.