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:
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:
Navigate to the "Functions" tab.
Click "Create new function" and give your function a descriptive name
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.
Here’s the full list of supported languages
BEGIN
if TG_OP = 'UPDATE' and OLD.description = NEW.description then
return null;
end if;
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)
)
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.
Select the social_posts table
Select insert & update operations, which will only activate this trigger for that operation on the social_posts table
Change the trigger type to After the event, allowing for async operations
Change the orientation to Row, allowing the trigger to run per row rather than a single statement, which could affect multiple tables
Create the trigger
Here’s a great doc by Supabase that dives deeper into how triggers and functions work in Postgres
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.
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!
