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:status returns an error if the database does not yet exist.

I'm using something like this in my scripts:

(rake db:migrate:status 2>/dev/null || rake db:setup) && rake db:migrate

(Inspired by [penguincoder]

Answer 4

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 5

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 6

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 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

Answer 9

My version in bash:

database_version() {
    local DB_VERSION=$(bundle exec rake db:version 2>/dev/null)

    if [[ "$DB_VERSION" =~ ^Current\ version\:\ ([[:digit:]]+)$ ]]; then
        if [[ "${BASH_REMATCH[1]}" != "" ]]; then
            echo $(("${BASH_REMATCH[1]}"))
        else
            echo -1
        fi
        return
    fi

    echo -1
}

if [[ $(($(database_version))) > 0 ]]; then
    echo "Database exists"
else
    echo "Database does not exist"
fi