How to translate content in PostgreSQL database with Localazy CLI?

Localazy is a complete localization suite built with developers in mind. Localazy allows you to fully automate the localization of your projects. The most common use case is when your localizable texts are placed in a file (or multiple files) in one of the supported formats (such as JSON, Android XML, iOS’s .strings, etc.), then you can comfortably use the Localazy CLI and integrate your project with our platform literally within minutes.

But what to do if you store translations in your database? One approach would be to use the Localazy API, which is a very flexible choice that can solve even the most demanding scenarios. But usually, it can be unnecessarily complicated because what we are trying to achieve is also possible to do with a few simple bash scripts.

The solution is usually simple, and our CLI is perfectly ready to handle such situations when chained with other commands.

Let’s have a look at how to translate texts stored in a PostgreSQL database, one of the most popular open-source databases out there.

🐘 Introduction 🔗

PostgreSQL has great support for manipulating JSON files, and for Localazy, JSON is being the most often used file format, so let’s stick with a simple workflow:

Upload:

Extract data from PostgreSQL and store them as JSON
Upload the JSON file to Localazy for translating

Download:

Download translated JSON files from Localazy
Import downloaded JSON files back to PostgreSQL

To make this tutorial fully actionable so that you can test everything on your own, we use step-by-step instructions using PostgreSQL running in a docker container.

This guide is designed for Linux systems as such are most often used for CI/CD or production systems, but it should be simple to update the behavior for any other operating system - we aren’t going to use any advanced commands, but some of the scripts may need alternative solution. You can also run the scripts in Linux-based docker container.

🚩 Initial Localazy Configuration 🔗

Create a new project 🔗

First of all, set up a new account in Localazy or sign in and create a new project.

Then find JSON in the list of integrations and select "Command Line Interface" as the integration method.

Now you can follow the rest of this tutorial, keep the tab open or copy the readKey and writeKey somewhere because you'll need them soon.

Install the Localazy CLI 🔗

Make sure that the localazy command is available on your computer by installing the Localazy CLI.

➡️ Follow the instructions in our documentation

If you use a specific version (JVM or docker), just update the scripts below accordingly to account for different command execution.

Prepare the folder structure and config 🔗

Create a folder pg-localazy which we'll use in this guide. In that folder, create a new file called localazy.json with a very basic configuration to upload a source language file locales/en.json and store translated files in the locales folder.

Learn more about basic CLI commands in the docs.

We'll also use filter_untranslated in features as we don’t want Localazy to populate missing translations with their source language version.

{
 
  "writeKey": "...",
  "readKey": "...",
  
  "upload": {  
    "type": "json",
    "features": ["filter_untranslated"],
    "files": "locales/en.json"         
  },
  
  "download": {
    "includeSourceLang": true,
    "files": "locales/${lang}.json"
  }
  
}

Note: Please remember to use your own writeKey and readKey as mentioned above.

And that’s it! Localazy is configured, and we can move to the database part.

🌎 Upload & download translations 🔗

Prepare the script and upload texts 🔗

In pg-localazy folder, create a new file called init.sql with this content:

-- Create a new database for testing purposes
CREATE DATABASE localazy_test;

-- Connect to the newly created database
\c localazy_test;

-- Create a new table for translations
CREATE TABLE translations( 
	id VARCHAR(256), 
	locale VARCHAR(12),
	content VARCHAR (4096),
	PRIMARY KEY (id, locale)
);

-- Populate translations table with some content
INSERT INTO translations (id, locale, content) VALUES 
	('settings', 'en', 'Settings'),
	('main_menu', 'en', 'Main Menu'),
	('share', 'en', 'Share'),
	('error_connection', 'en', 'Connection error occurred!');

-- We can verify the content of the table
SELECT * FROM translations;

Launch testing PostgreSQL database using the latest docker container. Run the command in pg-localazy folder so $(pwd) is correctly pointing to it, making it available inside the container as /pg-localazy. To make things easier, just use host networking.

docker run --rm --name pg --network host -v $(pwd):/pg-localazy -e POSTGRES_PASSWORD=pwd postgres

Run psql command in the docker container so that we can create a new database and populate it with some data for our demo.

docker exec -i pg psql --user postgres < init.sql

Now, we have our testing database ready, along with four testing texts.

Let’s create a simple bash script for extracting data from the database and uploading them to Localazy. In the pg-localazy folder, create a new file upload.sh with this content:

#!/bin/bash

# Create temporary folder for language files
mkdir -p locales

# Run PSQL command, connect to the running PostgreSQL instance and issue SQL designed to
# return data of the table for English in key-value JSON format. 
docker run --rm --name psql --network host -v $(pwd):/pg-localazy postgres psql --host localhost --user postgres -t -d localazy_test -c "SELECT json_object_agg(translations.id  
, translations.content) FROM translations WHERE locale = 'en';" > locales/en.json

# Upload data to Localazy
localazy upload

# Remove the temporary folder
rm -Rf locales

Make the upload.sh file executable and run it. In just a few seconds, texts from the database are successfully uploaded to Localazy.

Now, just add a few new languages in Localazy, and you can start translating.

Download translations 🔗

Once you are done with translating, we can implement a mechanism to get translated data back to our database.

Let’s create another simple bash script for importing data back to the database. In the pg-localazy folder, create a new file download.sh with the content below. It downloads translated files from Localazy and stores them in a folder locales with a name {lang}.json (e.g., en.json, cs.json, de.json). Then, it goes through all JSON files in locales folder, and processes each of them using simple psql script.

#!/bin/bash

# Create temporary folder for language files
mkdir -p locales

# Download translated files from Localazy
localazy download

# Process all JSON files in locales folder
cd locales
for file in *.json; do
langCode="${file%.*}"

echo "Processing ${langCode}..."

docker run --rm --name psql -i --network host -v $(pwd):/pg-localazy postgres psql --host localhost --user postgres -t -d localazy_test << EOF

-- Set the content from file
\\set content \`cat /pg-localazy/${file}\`

-- Upsert data to database from the loaded JSON file
INSERT INTO translations(id, locale, content) SELECT key AS id, '${langCode}' AS locale, value AS content FROM json_each(:'content'::json)
ON CONFLICT(id, locale) DO UPDATE SET content = excluded.content WHERE translations.content != excluded.content;

EOF

done

# Remove the temporary folder
cd ..
rm -Rf locales

Make the download.sh file executable and run it. Voilá! It only takes a while to download files from Localazy and reimport them back to our database.

Run a simple command to verify what we have in our translations table now. The result is exactly what we would expect.

docker run --rm --name psql --network host -v $(pwd):/pg-localazy postgres psql --host localhost --user postgres -d localazy_test -c "SELECT * FROM translations;"

✔️ Conclusion 🔗

The purpose of this article was to demonstrate that it’s easily possible to translate content in the PostgreSQL database using Localazy CLI.

Of course, this article is just a simple example with a single database table. But you can easily tweak this mechanism to cover more tables, and you can also adapt queries to handle tables of different structures. It’s completely up to you how you approach it. You can also choose different file formats for temporary files etc.