Script to push database to a server using an ssh tunnel

push-db.rb

#!/usr/bin/env ruby
# ./bin/push-db

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'highline'
  gem 'kamal'
  gem 'net-ssh-gateway'
end

cli = HighLine.new

exit unless HighLine.agree(
  cli.color(
    'DANGER. This will drop production. Do you wish to proceed?', :red, :bold
  )
)

def message(string)
  cli = HighLine.new
  puts cli.color(string, :green, :bold)
end

RAILS_DATABASE_YAML = YAML.load_file(
  File.expand_path("../config/database.yml", __dir__),
  aliases: true,
  symbolize_names: true
)

RAILS_DATABASE_YAML => { development: { primary: { database: database } } }
LOCAL_DATABASE_NAME = database
DUMP_NAME = 'tmp/db.dump'
KAMAL_CONFIG = File.expand_path("../config/deploy.yml", __dir__)
KAMAL_ACCESSORY_NAME = 'db'

message %(Dumping local database `#{LOCAL_DATABASE_NAME}` to `#{DUMP_NAME}`)

`pg_dump --clean --format=custom --exclude-schema=final #{LOCAL_DATABASE_NAME} > #{DUMP_NAME}`

message %(Fetching remote database address)

def with_kamal_database_url(&)
  require 'uri'
  config_file = Pathname.new(KAMAL_CONFIG)
  config = Kamal::Configuration.create_from(config_file:)
  db = config.accessories.find { _1.name == KAMAL_ACCESSORY_NAME }
  db.hosts => [host]
  db.port.split(':') => pghost, pgport, _pgdockerport
  url = URI.parse(config.secrets['DATABASE_URL'])

  gateway = Net::SSH::Gateway.new(
    host,
    config.ssh.user,
    **config.ssh.ssh_config.symbolize_keys
  )

  gateway.open(pghost, pgport) do |tunnel_port|
    url.host = 'localhost'
    url.port = tunnel_port
    yield(url.to_s)
  end
end

with_kamal_database_url do |url|
  message %(Dropping schema on remote server)
  `psql -d '#{url}' -c 'drop schema if exists public cascade; create schema public;'`

  message %(Restoring database on remote server)
  `pg_restore --no-owner -d '#{url}' tmp/db.dump`
end

puts '🚀🚀🚀'