How do I Migrate the Database?

I am stuck with DB migration, How to create and migrate the remote db?

Edit your .env.development.remote with a DATABASE_URL. Then you can run:

JETS_ENV_REMOTE=1 jets db:migrate

This assumes that you’re running it on a machine with access to the remote database. Here’s a little more info on JETS_ENV_REMOTE http://rubyonjets.com/docs/env-files/ Hope that helps.

Hi,

what if my local machine does not have access to the remote database?
I deploy a jets app into a VPC connecting to an Aurora MySQL serverless, which can only run inside a VPC and is not accessible from outside. I managed already to make this setup work after an entire weekend of digging :slight_smile:
My lambda function are accessible through API gateway and the app connects to aurora serverless. The only think now is to do this db:migrate part.

@artur2000 One approach is to run the command on AWS Lambda itself. Here’s an example: jobs/command_job.rb Also here for convenience:

class CommandJob < ApplicationJob
  def execute
    command = event['command'] || 'uptime'
    sh command
  end

  def migrate
    sh "jets db:migrate"
  end

private
  def sh(command)
    puts "=> #{command}"
    puts `#{command}`
  end
end

Usually, migrations are ran before deployment. So you can deploy the code with migrations to a new environment first:

JETS_ENV_EXTRA=2 jets deploy

Then you can use the AWS Lambda Console to run the migrate job or any arbitrary command. Afterwards you can delete the environment:

JETS_ENV_EXTRA=2 jets delete

That’s one workaround if you don’t have a machine with access to the remote RDS db.

Another way is an ssh tunnel. Forward your machine to a server that has access, and then you can migrate locally. Only works if you can at least ssh into the machine. This article talks about ssh forwarding:

3 SSH tips: Ssh-agent, Tunnel, and Escaping from the Dead

1 Like

Hello,

I tried to apply some solutions that as you telling here, but there didn’t work properly. I was starting developing in local with my MySQL instance and now I’m trying to deploy this petproject in AWS. I created an aurora rds instance and thinking in CI/CD I want to automatize all of the steps.

I don’t see how can I deploy with db:create and db:migrate as deploy task.

Any idea?

May need a little more info on the error you’re seeing. A shot in the dark here. With an Aurora DB, I believe you need to be within the same VPC as the Aurora DB. In this thread, there are essentially 3 options listed:

  1. Run command from on an ec2 instance that is in the same VPC and has access
  2. Create that command_job.rb function and configure it with a vpc_config function property. More info here: Jets Function Properties and Considerations VPC
  3. Create an SSH tunnel from your local machine to ec2 instance set up as described in number 1. Then you can run jets db:migrate https://blog.boltops.com/2017/09/21/3-ssh-tips-ssh-agent-tunnel-and-escaping-from-the-dead

If you’re trying technique #2, please make sure you configure vpc_config and make sure that subnet contains a NAT gateway.

Just to add something to the mix of options above I used Cloud9 to setup an environment within the VPC of the serverless RDS. That seems to work pretty well and is nice to have everything setup if I am on the go and need to just hop in and fix something. Also pauses after 30 minutes so I don’t have to remember to stop the instance.

However, I am having an issue and don’t know if its a NAT gateway or something. I have a VPC with this cloud9 environment and I also have another SG with my lambdas. The outbound of my lambda SG is open to all traffic, the inbound to my Serverless RDS cluster is allowing traffic from my Cloud9 SG and my Lambda SG (both to Postgres ports). However, the cloud9 instance can connect and the lambda functions timeout. I don’t think it is a NAT issue because the cloud9 instance wouldn’t work either correct?

Just to verify my functions are configured to use vpc_config and are in the proper VPC with proper security group ids and subnet ids. Everything appears to be showing up but still can’t get the connection from lambda to RDS inside the VPC. Did you have any thoughts @tung?

RE: Just to verify my functions are configured to use vpc_config and are in the proper VPC with proper security group ids and subnet ids.

And

RE: don’t know if its a NAT gateway

One thought is that maybe the RDS is connected to a public subnet with an IGW? Have found lambdas configured with vpc_config to only work with private subnets with a NAT. Docs here under “NAT Gateway Required”: https://rubyonjets.com/docs/considerations/vpc/

The Lambda functions vpc_config need to contain private subnets that have a NAT Gateway. Public subnets with an Internet Gateway did not work when I tested. The Lambda function would time out.