How to check if the database exists or not in rails before doing a rake db:setup

Question

How to check if the database exists or not in rails before doing a rake db:setup?

I would like to check if a database already exists before a db:create is being done . I have not seen a specific way in rails so far but i know this can be done using mysql scripts

Answer 1

Here is a method that checks if the database already exists:

def database_exists?
  ActiveRecord::Base.connection
rescue ActiveRecord::NoDatabaseError
  false
else
  true
end

References

• ActiveRecord::Base.connection
• ActiveRecord::NoDatabaseError

Answer 2

I made a rake task that expands on some of the previous answers. I use this a lot in a Vagrant+Docker setup, so I can very easily issue a single command and either create a new database or issue a migrate to the current database. I use a branched database paradigm for development. I constantly need to seed a new database or update my existing one.

In lib/tasks/db_exists.rake:

namespace :db do
  desc "Checks to see if the database exists"
  task :exists do
    begin
      Rake::Task['environment'].invoke
      ActiveRecord::Base.connection
    rescue
      exit 1
    else
      exit 0
    end
  end
end

So now I can run a simple bash command:

rake db:exists && rake db:migrate || rake db:setup

Which I have then further automated into a Makefile (trimmed for brevity):

.PHONY database
database:
        rake db:exists && rake db:migrate || rake db:setup

Translates into:

make database

For all of my local database needs.

Answer 3

You can also count on the fact that rake db:migrate returns an error if the database does not yet exist.

I used something like this in my scripts:

rake db:migrate 2>/dev/null || rake db:setup

(Inspired by [penguincoder]

Remark that this is only useful in cases like the original question's issue (see the additional comments of the author below the question itself) where the exit code of the expression matters (think of any sort of pipeline): in most cases rake db:setup will do exactly the same as rake db:migrate 2>/dev/null || rake db:setup, except for the exit code.

Answer 4

Here are some bash scripts I made for this purpose:

Postgres

if echo "\c $PGDATABASE; \dt" | psql | grep schema_migrations 2>&1 >/dev/null
then
   bundle exec rake db:migrate
else
   bundle exec rake db:setup
fi

Mysql

 if echo "use $MYSQLDATABASE; show tables" | mysql | grep schema_migrations 2>&1 > /dev/null
 then
     bundle exec rake db:migrate
 else
     bundle exec rake db:setup
 fi

These check for the presence of the schema_migrations table to determine whether rake db:setup has been run previously.

Answer 5

This will return false if the DB doesn't exist or if the connection is not active (at least in Rails 4+).

::ActiveRecord::Base.connection_pool.with_connection(&:active?) rescue false

Answer 6

Rails 6 now has a rails db:prepare task.

db:prepare will run db:migrate. If db:migrate fails, then db:create, db:seed, followed by db:migrate are run.

See all rails tasks with rails --tasks

...
rails db:exists                             # Checks to see if the database exists
...
rails db:prepare                            # Runs setup if database does not exist, or runs migrations if it does
...
rails db:setup                              # Creates the database, loads the schema, and initializes with the seed data (use db:reset to also drop the database first)
...

NOTE: db:setup will remove any data currently in the database. See Joshua Pinters's comment.

Answer 7

TRY THIS

 IF EXISTS 
       (
         SELECT name FROM master.dbo.sysdatabases 
        WHERE name = N'New_Database'
        )
    BEGIN
        SELECT 'Database Name already Exist' AS Message
    END
    ELSE
    BEGIN
        CREATE DATABASE [New_Database]
        SELECT 'New Database is Created'
    END

Answer 8

Here is what I use to check the state of the DB:

if db_version=$(bundle exec rake db:version 2>/dev/null)
then
    if [ "$db_version" = "Current version: 0" ]; then
        echo "DB is empty"
    else
        echo "DB exists"
    fi
else
    echo "DB does not exist"
fi