​Connect ​Supabase ​Database to ​Kestra

Connect your Supabase Database to your workflows using the PostgreSQL plugin.

Overview

Supabase is an open-source Backend-as-a-service (BaaS) platform that helps developers build applications faster and more efficiently. They provide a number of services, including hosted PostgreSQL databases, which can be used within Flows in Kestra.

To get started, make sure you have a Supabase account set up and an installation of Kestra running.

Setting up a Database in Supabase

Once you've logged into Supabase, you'll need to set up an organization where you will create projects to access resources such as a database.

Once your organization is created, you'll be prompted to create a new project. You will need to set a password for this project which we will use later to authenticate with the database inside of Kestra.

Once your project is created, you will now be able to access resources inside of Supabase. Head to the menu on the left side and select Database. You will be prompted to create a new table inside of your database, as well as configure any columns you want to use. We can leave the columns blank for now and modify these later once we know what data we want to copy into our database.

Connecting Supabase to Kestra

Now that we have a database set up in Supabase, we can move into Kestra to set up our connection. While there's no official Supabase plugin, we can connect using the PostgreSQL plugin, which supports a number of tasks such as Query, CopyIn, and CopyOut.

Inside of Supabase, select the Connect button at the top to get information about our databases connection. Select Type and change this JDBC. This will give us 3 ways of connecting with a Connection String. As we're only connecting to the database when our workflow runs, the Transaction pooler is a good option to use.

To connect, we can copy the URL provided for the Transaction pooler and replace [YOUR-PASSWORD] with the password set earlier. To prevent exposing the password in our flow, we can store it as secrets or in the KV Store.

By using Plugin Defaults, we can configure our connection to Supabase once for all tasks inside of our flow rather than individually for each task.

Once configured, our connection in Kestra will look like the example below:

pluginDefaults:
  - forced: true
    type: io.kestra.plugin.jdbc.postgresql
    values:
      url: "jdbc:postgresql://aws-0-eu-west-2.pooler.supabase.com:6543/postgres?user=postgres.nqxaafovehwkjapsqqlk&password={{kv('SUPABASE_PASSWORD')}}"

pluginDefaults:
  - forced: true
    type: io.kestra.plugin.jdbc.postgresql
    values:
      url: "jdbc:postgresql://aws-0-eu-west-2.pooler.supabase.com:6543/postgres"
      username: "postgres.nqxaafovehwkjapsqqlk"
      password: "{{ kv('SUPABASE_PASSWORD') }}

Copying a CSV file into Supabase DB inside of a Flow

Using this example CSV, we can copy the data into our table directly from Kestra. You can either set up the columns directly in Supabase or add a task in Kestra to add them automatically like this:

id: supabase_db_add_columns
namespace: company.team

tasks:
  - id: create_columns
    type: io.kestra.plugin.jdbc.postgresql.Queries
    sql: |
      ALTER TABLE kestra_example
      ADD COLUMN order_id int,
      ADD COLUMN customer_name text,
      ADD COLUMN customer_email text,
      ADD COLUMN product_id int,
      ADD COLUMN price double precision,
      ADD COLUMN quantity int,
      ADD COLUMN total double precision;

pluginDefaults:
  - forced: true
    type: io.kestra.plugin.jdbc.postgresql
    values:
      url: "jdbc:postgresql://aws-0-eu-west-2.pooler.supabase.com:6543/postgres?user=postgres.nqxaafovehwkjapsqqlk&password={{kv('SUPABASE_PASSWORD')}}"

Once your columns are configured, you can use the CopyIn task combined with the HTTP Download task to download the CSV file and copy it directly into our database. As we set up the database connection with our Plugin Defaults, the CopyIn task will connect directly and copy the CSV file into the database.

id: supabase_db_copyin
namespace: company.team

tasks:
  - id: download
    type: io.kestra.plugin.core.http.Download
    uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv

  - id: copy_in
    type: io.kestra.plugin.jdbc.postgresql.CopyIn
    table: "kestra_example"
    from: "{{ outputs.download.uri }}"
    header: true
    columns: [order_id,customer_name,customer_email,product_id,price,quantity,total]
    delimiter: ","

pluginDefaults:
  - forced: true
    type: io.kestra.plugin.jdbc.postgresql
    values:
      url: "jdbc:postgresql://aws-0-eu-west-2.pooler.supabase.com:6543/postgres?user=postgres.nqxaafovehwkjapsqqlk&password={{kv('SUPABASE_PASSWORD')}}"

Once this flow completes, we can view the contents of our database in Supabase: