Adding AI translation to your Supabase Postgres database from the dashboard

01 Nov 2024 • 3 min read

Yoeven D Khemlani

Share this article

Video tutorial: https://youtu.be/loOJxuwgn2g

Translations can be difficult to manage at scale in your database, especially for social generated content that consistently gets added and changed. Here’s how you can add AI translation right into your Supabase dashboard.

We assume you have the following:

• A Supabase database

• A JigsawStack API key

Creating the social table

Create a table where you want the content to be stored and translated, or you can use an existing table in your database.

Example table we’ll be using:

The description will be a required field containing content created by the user. The description_zh field will store the AI-generated translation.

Creating a Postgres Function

Create a PostgreSQL function to handle the translation task. This function will interact with the JigsawStack’s AI translation API to retrieve translated text and update the relevant column in your table.

To set up the function:

1. Navigate to the "Functions" tab.

2. Click "Create new function" and give your function a descriptive name

3. Copy and paste SQL code below into the “Definition” section

The SQL code does three things:

• Do nothing if descriptions don't change in update operations

• Makes API requests to JigsawStack's AI translation API

• Stores translated text into the description_zh column we created previously.

This is a great starting point, but you might want to edit the code for the specific languages you want and your table schema definition.

BEGIN
-- 1. Do nothing if descriptions don't change in update operations
if TG_OP = 'UPDATE' and OLD.description = NEW.description then
  return null;
end if;

-- 2. Makes API requests to JigsawStack's AI translation API
WITH
  desc_zh AS (
    SELECT
      "content"::jsonb ->> 'translated_text' AS translated_text
    FROM
      http ((
        'POST',
        'https://api.jigsawstack.com/v1/ai/translate',
        ARRAY[http_header('x-api-key','<jigsawstack_api_key>')],
        'application/json',
        jsonb_build_object('target_language','zh','text',NEW.description)
      )::http_request)
  )

-- 3. Stores translated text into the description_zh column we created previously 
UPDATE public.social_posts
SET
  description_zh = (
    SELECT
      translated_text
    FROM
      desc_zh
  )
WHERE
  id = NEW.id;

RETURN NEW;
END;

Here is how your function should look like:

Setting up Postgres Triggers

Next, set up PostgreSQL triggers to automatically carry out the translation whenever text is added or updated in the description column.

Head over to the trigger section, create a new trigger, and give it a name.

1. Select the social_posts table

2. Select insert & update operations, which will only activate this trigger for that operation on the social_posts table

3. Change the trigger type to After the event, allowing for async operations

4. Change the orientation to Row, allowing the trigger to run per row rather than a single statement, which could affect multiple tables

5. Create the trigger

Try it out!

Head back to your table editor and update or create a new row with a description. You’ll see the description_zh column automatically getting updated.

Now you a have built in AI translation right in your Supabase database! In the famous words of Supabase, “It’s just Postgres”, you can apply the same techniques in any Postgres database.

Some improvements that can be made for a production database is separating your translation into another table, allowing you to easily add support for more translations. You could also update the SQL function we created above to dynamically translate to any language rather than being fixed to one language.

Common mistakes:

No errors were shown, and translation isn’t working

You might have forgotten to add a valid JigsawStack API key. You can also view all logs of each request made in the JigsawStack dashboard.

Function throwing errors

Make sure the return type of the Postgres function is set to trigger and you’re mapping to the right columns.

👥 Join the JigsawStack Community

Have questions or want to show off what you’ve built? Join the JigsawStack developer community on Discord and Twitter. Let’s build something amazing together!