Dev.to

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', 'admin@example.com'); 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 MAIL_FROM_ADDRESS=no

Mar 27, 2025 - 15:23

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', 'admin@example.com');
            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
MAIL_FROM_ADDRESS=noreply@example.com
MAIL_FROM_NAME="Database Backup System"
BACKUP_RECIPIENT_EMAIL=admin@example.com

4. Running the Backup Manually

To test the backup manually, run:

php artisan supabase:backup-pdo

If successful, you'll receive an email with the .sql backup file attached.