Migrating from Planetscale to Supabase with automated backups

I've been using PlanetScale's free tier (Hobby) to run my MySQL database for a while now. It's honestly been one of the best developer experiences I've had with managing a database. Prisma integration was seamless, especially for migrations. Unlike other setups, PlanetScale handled migrations in a developer-friendly way that minimized the need for database resets, even with complex changes. Unfortunately, the current cost makes it difficult for me to keep using PlanetScale.

From here on, I will be using PS as PlanetScale.

How do we migrate?

Colab - This option uses Python notebooks with three execution blocks. The final block initiates the migration. However, Colab notebooks currently lack support for specifying secure connections, which are mandatory for Planetscale databases. This limitation prevented a successful connection to my Planetscale database.

pgloader - This tool worked for me after some adjustments. However, similar to Colab notebooks, pgloader doesn't allow adding "?sslaccept=strict" to the connection string, another requirement for secure connection with Planetscale. To overcome this, I used pscale connect DB_NAME to establish a secure local connection to Planetscale on port 3306 (127.0.0.1:3306). Since I was already connected locally, pgloader could then leverage this secure connection for the migration.

How to use pgLoader?

Remember, since you are migrating from mysql to postgres, there might be some column types mismatch. So first run this command:

npx prisma db push

This will create all the tables.  At this point, we know that our structure is correct.

Next, create a config.load file and paste this command. pgLoader can execute this file.

LOAD database
  FROM mysql://user:password@host/source_db
  INTO postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:5432/postgres
WITH data only, quote identifiers
ALTER schema 'public' owner to 'postgres';
SET wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';

Here, we are moving only data. In supabase, the schema is public and its owner is postgres. You will have permission issues, if you do not set the role of public schema.

While this is running, you might encounter warnings which you can ignore.

Backups in Supabase

In the free tier, supabase do not allow backups. But using a script you can back up your database at regular intervals. In my case, I wanted the backup script to perform the following tasks:

• Create a backup of Supabase Database
• Import the backup into local postgres database
• Finally upload the backup and keep it safe.
• Bonus: Set a cron job.

const { execute } = require('@getvim/execute');
const axios = require('axios');
const FormData = require('form-data');
const cron = require('node-cron');

const dotenv = require('dotenv');
dotenv.config();


const date = new Date();
const currentDate = `${date.getFullYear()}.${date.getMonth() + 1}.${date.getDate()}.${date.getHours()}.${date.getMinutes()}`;
const fileName = `database-backup-${currentDate}.tar`;

function backup() {
    const {DB_USER, DB_PASSWORD, DB_HOST, PORT, DB_NAME} = process.env;
    execute(
        `pg_dump "postgres://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${PORT}/${DB_NAME}" -n public -O -F t -b -v -f ${fileName}`,
    ).then(async () => {
        restore();
        console.log("Finito");
    }).catch(err => {
        console.log(err);
    })
}

function restore() {
    execute(`pg_restore --clean -d 'postgresql://postgres:postgres@127.0.0.1:54322/postgres' ${fileName}`)
        .then(async () => {
            console.log("Restored");
        }).catch(err => {
            console.log(err);
        })
}

function sendToBackupServer(fileName) {
    const form = new FormData();
    form.append('file', fileName);
    axios.post('[BACKUP_URL]', form, {
        headers: form.getHeaders(),
    }).then(result => {
        // Handle result…
        console.log(result.data);
    }).catch(err => {
        console.error(err);
    });
}

function startSchedule() {
    cron.schedule('0 */2 * * *', () => {
        backup()
        sendToBackupServer();
    }, {});
}

module.exports = {
    startSchedule,
    backup
}

backup()

1. Dependencies:

@getvim/execute: Likely a library for executing shell commands.

axios: Used for making HTTP requests (potentially to upload backups).

form-data: Used for creating multipart form data (likely for file uploads).

node-cron: Library for scheduling cron jobs.

dotenv: Used to load environment variables from a .env file.

2. Environment Variables:

The script expects the following environment variables to be defined in a .env file:

• DB_USER: Username for your Postgres database.
• DB_PASSWORD: Password for your Postgres database.
• DB_HOST: Hostname or IP address of your Postgres server.
• PORT: Port number of your Postgres server (default for Postgres is 5432).
• DB_NAME: Name of the database to be backed up.

3. Backup Function (backup):

• Retrieves environment variables for database credentials.
• Constructs a pg_dump command using retrieved credentials and desired options:
  • -n public: Excludes the "public" schema from the backup.
  • -O: Includes object ownership information.
  • -F t: Creates a tar archive.
  • -b: Includes large objects (BLOBs) in the backup.
  • -v: Enables verbose mode for logging.
  • -f ${fileName}: Specifies the filename for the backup archive.
• Executes the pg_dump command using the execute library.
• On successful backup:
  • Calls the restore function 
  • Prints "Finished" to the console.
• On error:
  • Prints the error message to the console.

4. Restore Function (restore):

• Constructs a pg_restore command assuming a local Postgres instance running on port 54322 with username "postgres" and password "postgres" (Caution: Using hardcoded credentials is not secure!).
• Specifies the backup filename (fileName) to be restored.
• Executes the pg_restore command using the execute library.
• On successful restore:
  • Prints "Restored" to the console.
• On error:
  • Prints the error message to the console.

5. Send Backup Function (sendToBackupServer):

• Creates a FormData object.
• Appends the backup file (fileName) to the form data.
• Makes a POST request to a URL defined by the [BACKUP_URL] placeholder (Note: This URL needs to be replaced with the actual backup server URL).
• On successful upload:
  • Logs the response data from the server (specific handling depends on the server implementation).
• On error:
  • Prints the error message to the console.

6. Start Schedule Function (startSchedule):

• Defines a cron job schedule using node-cron:
  • The current schedule runs the backup every 2 hours (0 */2 * * *).
• Within the cron job:
  • Calls the backup function.
  • Calls the sendToBackupServer function.

7. Execution:

• The script exports the startSchedule and backup functions.
• At the bottom, the script directly calls the backup function, triggering a single backup execution.

Important Note:

If you intend to schedule backups, it's recommended to use the startSchedule function to define the cron job for periodic backups and uploads.

You might want to create a small project and run it in vercel. And then using a cron job you can fire this script at regular intervals.