Automating Supabase Database Backups in Laravel with PDO

Why Automate Supabase Backups?

Manual database backups can be time-consuming and prone to errors. Automating this process offers several benefits:

Consistency: Ensures that backups are taken at regular intervals.
Security: Reduces the risk of human errors.
Convenience: Automatically emails the backup file for safekeeping.
Disaster Recovery: In case of data loss, you can restore the database quickly.

Implementing the Supabase Backup Command

1. Creating a Laravel Console Command

To create a new Artisan command, run the following:

php artisan make:command SupabaseBackupViaPDO

This will generate a command file in app/Console/Commands/. Open SupabaseBackupViaPDO.php and update it with the following code:

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Carbon;

class SupabaseBackupViaPDO extends Command
{
    protected $signature = 'supabase:backup-pdo';
    protected $description = 'Backup Supabase DB and email the .sql file';

    public function handle()
    {
        try {
            $startTime = now();
            $filename = 'supabase_backup_' . $startTime->format('Y-m-d_H-i-s') . '.sql';
            $path = storage_path("app/backups/{$filename}");

            if (!is_dir(storage_path('app/backups'))) {
                mkdir(storage_path('app/backups'), 0755, true);
            }

            Log::info('Starting Supabase database backup process');
            $pdo = DB::connection()->getPdo();

            $tables = $pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
                ->fetchAll(\PDO::FETCH_COLUMN);

            Log::info('Found ' . count($tables) . ' tables to backup');
            $sql = "-- Supabase Database Backup\n-- " . now() . "\n\n";

            foreach ($tables as $table) {
                $quotedTable = "\"$table\"";
                $sql .= "-- Table: {$table}\n";

                $columns = $pdo->prepare("SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = :table ORDER BY ordinal_position");
                $columns->execute(['table' => $table]);
                $cols = $columns->fetchAll(\PDO::FETCH_ASSOC);

                $sql .= "CREATE TABLE {$quotedTable} (\n";
                foreach ($cols as $col) {
                    $line = "  \"{$col['column_name']}\" {$col['data_type']}";
                    if ($col['column_default']) $line .= " DEFAULT {$col['column_default']}";
                    if ($col['is_nullable'] === 'NO') $line .= " NOT NULL";
                    $sql .= $line . ",\n";
                }
                $sql = rtrim($sql, ",\n") . "\n);\n\n";

                Log::info("Backing up data for table: {$table}");
                $rows = $pdo->query("SELECT * FROM {$quotedTable}")->fetchAll(\PDO::FETCH_ASSOC);
                foreach ($rows as $row) {
                    $cols = array_map(fn($col) => "\"$col\"", array_keys($row));
                    $vals = array_map(fn($val) => $val === null ? 'NULL' : $pdo->quote($val), array_values($row));
                    $sql .= "INSERT INTO {$quotedTable} (" . implode(", ", $cols) . ") VALUES (" . implode(", ", $vals) . ");\n";
                }

                $sql .= "\n";
            }

            file_put_contents($path, $sql);
            Log::info("Backup completed successfully", ['filename' => $filename]);

            $recipientEmail = env('BACKUP_RECIPIENT_EMAIL', '[email protected]');
            Mail::raw("Database backup completed successfully. Please find the attached backup file.", function ($message) use ($path, $filename, $recipientEmail) {
                $message->to($recipientEmail)
                    ->subject('Database Backup Completed')
                    ->attach($path, ['as' => $filename, 'mime' => 'application/sql']);
            });

            Log::info("Backup email sent successfully to {$recipientEmail}");
            $this->info("✅ Database backup completed and email sent.");
        } catch (\Throwable $th) {
            Log::error('Backup failed', ['error' => $th->getMessage()]);
            $this->error('Backup failed: ' . $th->getMessage());
        }
    }
}

2. Scheduling the Command

To automate this process, add the command to Laravel's scheduler in app/Console/Kernel.php:

protected function schedule(Schedule $schedule)
{
    $schedule->command('supabase:backup-pdo')->dailyAt('02:00');
}

This will run the backup every day at 2 AM.

3. Configuring Email Settings

Ensure that your .env file includes proper mail configuration:

MAIL_MAILER=smtp
MAIL_HOST=smtp.mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=your-username
MAIL_PASSWORD=your-password
MAIL_ENCRYPTION=tls
[email protected]
MAIL_FROM_NAME="Database Backup System"
[email protected]