Migrate_Project_&_Storage.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mansueli/Supa-Migrate/blob/main/Migrate_Project_%26_Storage.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "![Supabase](https://raw.githubusercontent.com/supabase/supabase/master/packages/common/assets/images/supabase-logo-wordmark--light.svg)\n",
        "\n"
      ],
      "metadata": {
        "id": "ygevkg_zCkmo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Setting the Environment variables:"
      ],
      "metadata": {
        "id": "LL4Furs6VliO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "OLD_SUPAVISOR_URL='postgres://postgres.oldproject:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres'\n",
        "NEW_SUPAVISOR_URL='postgres://postgres.newproject:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres'\n",
        "OLD_SERVICE_KEY = 'eyJ0000J9.eyJQ.oPyK-LSECRET-aC1I'\n",
        "NEW_SERVICE_KEY = 'eyJ0000J9.eyJpSERVICE_ROLE.d4tffFJoc8iHsk_KEY'"
      ],
      "metadata": {
        "id": "_gS4xrZzVkl3"
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Installing PSQL, Downloading the scripts:"
      ],
      "metadata": {
        "id": "scZlpumHVbi6"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "WqBSjmZxVOn1",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "034c48ad-95e7-4cc2-d995-dfe9692b74a7"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).\n",
            "OK\n",
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "libmagic1 is already the newest version (1:5.41-3ubuntu0.1).\n",
            "libmagic1 set to manually installed.\n",
            "0 upgraded, 0 newly installed, 0 to remove and 34 not upgraded.\n"
          ]
        }
      ],
      "source": [
        "!sudo sh -c 'echo \"deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main\" > /etc/apt/sources.list.d/pgdg.list'\n",
        "!wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -\n",
        "!sudo apt-get update &>log\n",
        "!sudo apt-get -y install postgresql &>log\n",
        "!wget https://raw.githubusercontent.com/mansueli/Supa-Migrate/main/migrate_project.sh &>log\n",
        "!pip install supabase &>log\n",
        "!pip install python-magic &>log\n",
        "!sudo apt install libmagic1"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Running migration & moving storage objects:"
      ],
      "metadata": {
        "id": "giay7Rs5WkqD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from supabase import create_client\n",
        "import os\n",
        "import re\n",
        "import magic\n",
        "filedata = ''\n",
        "\n",
        "def convert_supavisor_string_to_api_url(supavisor_url):\n",
        "    # Extracting relevant parts from the supavisor URL\n",
        "    match = re.match(r\"postgres:\\/\\/postgres\\.([^:]+):[^@]+@aws-\\d+-([^\\.]+)\\.pooler\\.supabase\\.com:\\d+\\/([^\\/]+)\", supavisor_url)\n",
        "\n",
        "    if match:\n",
        "        project_ref = match.group(1)\n",
        "        region = match.group(2)\n",
        "        return f\"https://{project_ref}.supabase.co\"\n",
        "    else:\n",
        "        return \"Invalid Supavisor URL\"\n",
        "\n",
        "# Function to recursively copy files and directories\n",
        "def copy_files(old_client, new_client, bucket_name, path):\n",
        "    objects = old_client.storage.from_(bucket_name).list(path=path)\n",
        "\n",
        "    for obj in objects:\n",
        "        if obj['metadata'] is None:  # It's a directory\n",
        "            subdirectory = os.path.join(path, obj['name'])\n",
        "            # Create the directory if it doesn't exist\n",
        "            if not os.path.exists(subdirectory):\n",
        "                os.makedirs(subdirectory)\n",
        "            copy_files(old_client, new_client, bucket_name, subdirectory)\n",
        "        else:\n",
        "            try:\n",
        "                print(f\"Downloading {path}/{obj['name']}\")\n",
        "                # Download file\n",
        "                download_path = os.path.join(path, obj['name'])\n",
        "                with open(download_path, 'wb') as f:\n",
        "                    res = old_client.storage.from_(bucket_name).download(f\"{path}/{obj['name']}\")\n",
        "                    f.write(res)\n",
        "\n",
        "                # Get MIME type\n",
        "                mime_type = magic.from_file(os.path.abspath(download_path), mime=True)\n",
        "\n",
        "                # Upload file\n",
        "                print(f\"Uploading file {path}/{obj['name']}\")\n",
        "                with open(download_path, 'rb') as file_object:\n",
        "                    new_client.storage.from_(bucket_name).upload(f\"/{path}/{obj['name']}\", file_object, file_options={\"content-type\": mime_type, \"x-upsert\": 'true'})\n",
        "\n",
        "                # Delete local file after uploading\n",
        "                os.remove(download_path)\n",
        "            except Exception as e:\n",
        "                print(\"Error: \", e)\n",
        "\n",
        "with open('migrate_project.sh', 'r') as file :\n",
        "  filedata = file.read()\n",
        "  filedata = filedata.replace('postgres://postgres.oldproject:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres', OLD_SUPAVISOR_URL)\n",
        "  filedata = filedata.replace('postgres://postgres.newproject:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres',  NEW_SUPAVISOR_URL)\n",
        "\n",
        "with open('migrate_project.sh', 'w') as file:\n",
        "  file.write(filedata)\n",
        "\n",
        "#Run the database migration script:\n",
        "!bash ./migrate_project.sh\n",
        "\n",
        "#creating the clients for the old & new projects\n",
        "print(f\"createclient({convert_supavisor_string_to_api_url(OLD_SUPAVISOR_URL)} , {OLD_SERVICE_KEY})\")\n",
        "old_supabase_client = create_client(convert_supavisor_string_to_api_url(OLD_SUPAVISOR_URL), OLD_SERVICE_KEY)\n",
        "new_supabase_client = create_client(convert_supavisor_string_to_api_url(NEW_SUPAVISOR_URL), NEW_SERVICE_KEY)\n",
        "\n",
        "#Create all buckets\n",
        "buckets = old_supabase_client.storage.list_buckets()\n",
        "for bucket in buckets:\n",
        "    print(\"Copying objects from \"+bucket.name)\n",
        "    objects = old_supabase_client.storage.from_(bucket.name).list()\n",
        "    try:\n",
        "      new_supabase_client.storage.create_bucket(bucket.name, options={\"public\": bucket.public})\n",
        "    except Exception as e:\n",
        "      print(\"\\n---\\nUnable to create bucket \" + str(e))\n",
        "    finally:\n",
        "      copy_files(old_supabase_client, new_supabase_client, bucket.name, \"\")"
      ],
      "metadata": {
        "id": "NFI8Y6SzF-Aj"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}