Migrations - Using DBMate

[SteveChurch]

The Problem

Supabase don't really offer a good solution for handling migrations for those of us who want to self-host. So, the below solution overcomes this using dbmate

The solution

I have a working concept for handling migrations within Supabase Self Hosted if anyone is interested I can create an MR.

I decided to go with dbmate which runs within a cron on the Droplet. This triggers are script migrate.sh which runs through all of the migration files.

files/migrate.sh.tftpl

#!/bin/bash

# Check for file initialized
if [ ! -f /root/supabase/initialized ]; then
  echo "Whoops, looks like you haven't initialized the database yet.. Lets wait a bit longer"
  exit 0
fi
export $(grep -v '^#' .env | xargs -d '\n')
dbmate --migrations-dir "/root/supabase/migrations" --url "postgres://supabase_admin:${POSTGRES_PASSWORD}@0.0.0.0:${POSTGRES_PORT}/${POSTGRES_DB}?sslmode=disable" migrate

locals.tf

....
migration_files = [for f in fileset("${path.module}/files/migrations", "*.sql") :
        {
            path            = "/root/supabase/migrations/${replace(basename(f), "_", "-")}"
            content         = base64encode(<<-END
                -- migrate:up
                ${file("${path.module}/files/migrations/${f}")}
                END
            )
            owner           = "root:root"
            encoding        = "b64"
            permissions     = "0644"
        }
    ]

    cloud_config = <<-END
    #cloud-config
    ${jsonencode(
        {
            "write_files" = concat([
                {
                    "path"        = "/root/supabase/.env"
                    "content"     = base64encode("${local.env_file}")
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0644"
                },
                {
                    "path"        = "/root/supabase/digitalocean.ini"
                    "content"     = base64encode("${local.do_ini}")
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0644"
                },
                {
                    "path"        = "/root/supabase/.htpasswd"
                    "content"     = base64encode("${local.htpasswd}")
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0644"
                },
                {
                    "path"        = "/root/supabase/volumes/api/kong.yml"
                    "content"     = base64encode("${local.kong_file}")
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0644"
                },
                {
                    "path"        = "/root/supabase/migrate.sh"
                    "content"     = base64encode("${local.migrate_file}")
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0755"
                },
                {
                    "path"        = "/etc/cron.d/migrate"
                    "content"     = base64encode(file("${path.module}/files/migrate.cron"))
                    "owner"       = "root:root"
                    "encoding"    = "b64"
                    "permissions" = "0644"
                },
            ], local.migration_files)
        }
    )}
    END
....

files/migrate.cron

* * * * * root /root/supabase/migrate.sh  >> /var/log/migrations 2>&1

droplet.tf

...
 part {
        content_type = "text/x-shellscript"
        filename     = "init.sh"
        content      = <<-EOF
            #!/bin/bash
            mkdir -p /mnt/supabase_volume/supabase/data
            cd /root/supabase
            /usr/bin/docker compose -f /root/supabase/docker-compose.yml up -d
            curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
            chmod +x /usr/local/bin/dbmate
            touch /root/supabase/initialized
            EOF
    }
....

What is great about this solution is you can take the migration files from your local dev Supabase, which have been generated by the supabase-cli and push them straight into Terraform. The draw back now however is that the lifecycle on the Droplet has been changed to make sure user_data changes always re-create.

My next steps will be to set up some sort of Blue/Green deployments as the downtime with this solution sits at around 10 minutes.

@timothymamo shall I place this within an MR as I feel like migrations are a base requirement for using Supabase?

Gotchas

I use a symbilic link between the generated migration files from the supabase-cli and my terraform/files so that I do not need to copy anything over. If anyone know of a way of letting terraform access files outside of it;s base path it woudl be good to know so that I no longer need to do this. My project structure is like this:

-- supabase
     -- migrations
-- infrastructure
    -- terraform
        -- files
            --migrations. -->> symlinked from supabase/migrations
    -- packer
-- app

[timothymamo]

Thanks so much for taking the time to do this write up @SteveChurch, this is awesome and I'm sure the rest of the community will find great use for it.

[timothymamo]

@SteveChurch just going over this again (as just got off holiday) and I've got a few questions/comments:

1. Does the cron need to run every minute? If the migration files are linked to the lifecycle of the Droplet wouldn't running the script once be sufficient?
2. With regards to referencing the files outside of the module path I believe you cloud use ${path.module}/../../supabase/migrations (following your structure).
3. Unsure if this needs to be a PR, I'd like to get feedback from the community to see if this is a use case for more people.

But again thanks for the write up, this is great 😄

[SteveChurch]

@timothymamo I am going to work on this tonight as I have faced a new problem. In my infinite wisdom I forgot that there is a limit usually on user_data - I have recently hit that limit.

You was right yes, there was no need for the cron as it was always re-deploying when new files were found. I am however going to move this into it's own container, this would negate any need to re-create on each deployment as the user_data won't change on each deployment.

[SteveChurch]

Ill try to do a new write up tonight on the new setup with migrations. It now does require a re-run of packer, but that's no big deal.

[SteveChurch]

Hold up a second here. https://supabase.com/docs/reference/cli/supabase-db-push

I thought this (--db-url)was removed from the CLI - Just checked through the release logs and don't see anything remotely signifying it's been put back in, but it's on the docs. Well that is an added bonus.