How to drop a database in Laravel 6?

Question

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.

Gabbro

Does the db user definitely have permission to drop the database? I've just tested using DB::statement(...) and that seems to work fine. – Creativity

@Creativity Yeah the user definitely has permission. Just tested and it works, what I can't do is bind a variable to the statement is what I meant to say. I'll update the question now. – Gabbro

AFAIK, you can't substitute table names. – Creativity

@Creativity that's fine if not. I still wouldn't like to be the one handling the sanitisation of the database name going in to the query. 1. It looks dirty, 2. Security, and 3. Laravel already has this amazing database library, surely this is something it can handle (even with a little work)? – Gabbro

Backtick escaping is enough to sanitise database/table/column names as far as I know. Also, the purpose of prepared statements with parameters is to sanitise user input because we don't trust the user. Do you really want something like a database deletion to be done by a website client? (I'm not saying you shouldn't I'm just saying it's something you need to think about very seriously) – Lightweight

@Lightweight well, the user will never be entering the database name, this is generated by the system and used for the mult-tenant connections. It should never be malicious, but I still would like to have Laravel handle this considering it used to. Even if I don't want a user to submit the request which handles this, then I would still like to have a artisan command which could be used. Either way I still need to be able to do it? – Gabbro

Yes in this case the caller of artisan is a "user" (even if it's a trusted one). It's not a Laravel feature though, MySQL doesn't support prepared statements with the database/table/column name as the parameter. Backtick escaping is the only thing you can do, but it's also a sufficient thing to do (just make sure you also remove backtick characters from $databaseName). – Lightweight

@Lightweight I'm aware of that, I don't think you fully understand what I'm trying to say. The limitations of MySQL I can understand, however, there was a command in a previous version of laravel to handle the dropping of databases. This no longer exists. – Gabbro

I'm not aware of one ever existing though I've not used Laravel before version 5.2 – Lightweight

Answer 1

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.

Gabbro

Answer 2

1. Open terminal

2.Copy/Paste

 $ php artisan db:wipe

msg "Dropped all tables successfully."

Microgram

Thanks for the answer. I replied to this myself with the solution I found in the end. I've since moved away from the whole idea as it wasn't a good idea in the first place. – Gabbro

this is wrong as it doesn't drop database but all tables, views, and types. – Colorable

Answer 3

You can create a laravel command to drop database. e.g:

php artisan db:drop $database_name

1. php artisan make:command dbDrop

2. inside commands/dbDrop.php :

   protected $signature = 'db:drop {database_name}';
   
   protected $description = 'Drop a database.';
   
   public function __construct()
   {
       parent::__construct();
   }
   
   public function handle()
   {
       // Fetch the defined database name
       $db_type = \Config::get('database.default');
       $connection = \Config::get('database.connections.'.$db_type);
       $host = $connection['host'];
       $username = $connection['username'];
       $password = $connection['password'];
       $database = $connection['database'];
       $this->dropDB($host, $username, $password, $database);
   }
     protected function dropDB($host, $username, $password, $database)
    {
   
   
   
           try
           {
               $db = $this->argument('database_name');
               // Create connection
               $conn = new \mysqli($host, $username, $password);
               $this->info(json_encode($conn));
               // return $conn;
               // Check connection
               if ($conn->connect_error) {
                   die("Connection failed: " . $conn->connect_error);
               } 
               // Drop database
               $sql = "DROP DATABASE `$db`";
               if ($conn->query($sql) === TRUE) {
                   echo "Sucessfully dropped database $db!";
               } else {
                   echo "Error dropping database: " . $conn->error;
               }
               $conn->close();
           }
           catch(Exception $e){
               $this->info('');
               echo "Error dropping database: $db";
               $this->info('');
               echo json_encode($e->getMessage());
               $this->info('');
               $this->info('You can try the mysql shell.');
           }
   
       }
   } 
   

Stites

This isn't what I want to do though, I want to use the Laravel QueryBuilder in order to use a better standard. – Gabbro

Ok. Please post in this thread if you can find anything like that in the future. best of luck – Stites

Answer 4

To delete records from the table:

DB::table('table_name')->delete();

If you wish to truncate the entire table, which will remove all rows and reset the auto-incrementing ID to zero, you may use the truncate method:

DB::table('table_name')->truncate();

Or Delete table using Schema::dropIfExists('table_name');

To delete daabase Schema::getConnection()->getDoctrineSchemaManager()->dropDatabase("database");

Drescher

I need to be able to delete a database, but thanks. :) – Gabbro

I'd already posted the solution, also you'll find your correction already in the question. Thanks though. :) – Gabbro