[FIXED] How to drop a database in Laravel 6?

Issue

So I have an issue where I have a multi-tenant application, and I have a system where I can manage all of these tenants with standard CRUD operations. I want to be able to drop the tenant databases when the tenant gets deleted but I cannot seem to find anything in the documentation about being able to do this, nor can I find a series of methods in the API documentation which can be used.

I have seen the below online (the only information I can find), but it's 2 years old but all of these methods have either been deprecated or have been moved in Laravel 6.

Schema::getConnection()->getDoctrineSchemaManager()->dropDatabase("`{$database_name}`");

Through my own tests, running the following command does work.

DB::statement('DROP DATABASE `foo`');

What I can't do though is bind a variable to this statement:

DB::statement(DB::raw('DROP DATABASE ?', $database_name));

I also want to use the standard Laravel query builder instead of sanitising the information myself. So ideally I would like to be able to do something like this:

DatabaseManager::dropDatabase($database_name);

or this:

$database = DatabaseManager::connect($database_name);
$database->dropDatabase();

My guess would be that I need to create a new connection to MySQL where I'm not connecting directly to an individual database. Then execute a command to drop a database, and then close the connection.

I will be looking to build this myself, but just wondering if anyone had any thoughts on this one, or if anyway has done this before? All help is much appreciated.

Solution

The Findings

After some research and tinkering, I have two options.

1. Doctrine DBAL

I looked into the Doctrine DBAL library, and found exactly what I was looking for. A library to manage the execution of the command.

It is simple to use, as mentioned in the question above you need to execute this piece of code:

Schema::getConnection()->getDoctrineSchemaManager()->dropDatabase("`{$database_name}`");

NOTE: in order to do that, you first need to require the library through composer:

composer require doctrine/dbal

Having looked deeper into this method, it really isn't worth the extra code, let alone the inclusion of a library as the code that it executes is as follows [github]:

/**
 * Returns the SQL snippet to drop an existing database.
 *
 * @param string $database The name of the database that should be dropped.
 */
public function getDropDatabaseSQL(string $database) : string
{
    return 'DROP DATABASE ' . $database;
}

which is exactly the same as what you would be doing in option 2.

2. Laravel DB::statement()

This method does exactly the same thing, and is much simpler. All that is needed is the following piece of code:

DB::statement("DROP DATABASE `{$database_name}`");

Conclusion

TLDR; Use Laravel's DB Facade to do this instead of a third party solution. It's clearner, easier, and uses less code.

Now I know there may not be a lot of reason for doing this, as pointed out by @Rwd but I will be looking to use it in some way in order to automate the process of purging redundant databases. I will probably build some form of container DatabaseManager, where each manager will contain an instance based version of the database information, and include a method to handle the dropping of the database.

Thanks to @Rwd and @apokryfos for the discussion.

Answered By - ogagh

Answer Checked By - Candace Johnson (PHPFixing Volunteer)