Capistrano deploy removes tables from database.

I use capistrano to deploy my Rails app to my VPS, but after every deploy when I visit my page I get an error. The log says:

I, [2014-11-04T08:20:16.659289 #12482]  INFO -- : Started GET "/" for 82.73.170.71 at 2014-11-04 08:20:16 -0500
I, [2014-11-04T08:20:16.662717 #12482]  INFO -- : Processing by HomeController#index as HTML
I, [2014-11-04T08:20:16.665979 #12482]  INFO -- : Completed 500 Internal Server Error in 3ms
F, [2014-11-04T08:20:16.670152 #12482] FATAL -- :
ActiveRecord::StatementInvalid (Could not find table 'users'):
  app/controllers/application_controller.rb:18:in `current_user'
  app/helpers/sessions_helper.rb:26:in `logged_in?'
  app/controllers/home_controller.rb:4:in `index'

I have to ssh into my VPS and go to my Rails root and run RAILS_ENV=production bundle exec rake db:migrate . In my db folder I do still have the production.sqlite3 file, but it's empty.

My deploy.rb

# config valid only for Capistrano 3.1
lock '3.1.0'

set :application, 'movieseat'
set :repo_url, 'git@github.com:alucardu/movieseat.git'

set :deploy_to, '/home/deploy/movieseat'

set :linked_files, %w{config/database.yml config/secrets.yml}
set :linked_dirs, %w{bin log tmp/pids tmp/cache tmp/sockets vendor/bundle public/system}

require 'capistrano-rbenv'

namespace :deploy do

  desc 'Restart application'
  task :restart do
    on roles(:app), in: :sequence, wait: 5 do
      execute :touch, release_path.join('tmp/restart.txt')
    end
  end

  after :publishing, 'deploy:restart'
  after :finishing, 'deploy:cleanup'
end

So why is Capistrano removing my database when I deploy?

So every time you deploy with capistrano, it creates a new folder for the "release". This means all the files that were created in the previous directory (like your sqlite database for example) will be in that old folder. Your restart will happen and it will then serve up only from the new directory. Your database still exists, but it is in a different folder that isn't accessible anymore!

This is why you want to use a shared database of some sort in production. You can move this production.sqlite3 file to the shared folder and then put it in your linked_files option just like you did with database.yml and secrets.yml. This way you'll only have one copy of the sqlite3 database file and it will get linked into the app each time. Same goes for the database.yml and secrets.yml files. You only have one, and it creates a link to it on deploy so your Rails app can access it.

You're completly right. I also posted the question on StackOverflow > http://stackoverflow.com/questions/26736507/capistrano-deploy-removes-table/26736546?noredirect=1#comment42063814_26736546 if anyone wants a more detailed answer on the question.

As Chris mentioned, you'd be better off moving to a shared database such as MySQL or PostgreSQL. This way the data persists in production. All you need to do is configure the database information and connector in your database.yml file. sqlite is fine for small apps and playing around in development but I'd highly recommend moving to Postgres or the like. It's also really helpful to have the same database engine in development as you do in production. That's why I run Postgres locally and in production.

You can move the production.sqlite3 file into the app/shared folder and symlink it, but you're better off in the long run moving to a real database.