tutorial
How to sync from Trello to PostgreSQL with CloudQuery
Michal Brutvan •
We have recently released the Trello plugin and in this guide, I will walk you through the full setup of a sync from Trello to a PostgreSQL database.
Trello is a popular tool for task management in DevOps and engineering teams because it is simple, but sometimes you just need a bit more reporting to understand how your team is doing and how you could improve.
To sync with Trello, you will need to create a Trello Power-Up and get its API key and a secret token. When I first learned about it, I was worried it would be too complicated and I'd need to write a lot of code, at least that's what Atlassian's documentation made me think at the first look. It is luckily much simpler than that, there's zero code to write.
Let's get started.
Setting up the Power-Up
First make sure you have a Trello account and that you are logged in.
Next visit the Power-Ups administration. If this is your first time on this page, you may need to read and agree to the Trello Developer Terms. After that, you will get to your Apps dashboard.
Click New and fill in the form to create a new Power-Up. You do not need to worry about the Iframe connector URL. Despite being marked as required, it actually isn't.
After you submit the form, you can generate the API key.
The first text box contains the API key to use in CloudQuery config, save it.
Next, generate the app token. Click the blue Token link in the hint next to the API key (highlighted red in the screenshot above). This will take you to an authorization page.
Scroll down, click Allow, and you'll get the token to use with CloudQuery. Save it.
Now the only thing remaining is setting up the actual sync. I'll use CloudQuery Cloud Sync but if you prefer running locally, here's how you can get started with the CLI. Find the full example config at the end of this blog post.
Setting up the Sync
For this step, you will need the API key and the token from the Trello Power-Up created above, and a connection string to a PostgreSQL database.
If you don't have a PostgreSQL at hand, just head to Neon and sign up for a free account. It's really easy to start with.
Log in to CloudQuery Cloud and from the Syncs tab, create a new Sync.
First, define the destination. Since the plan is to sync to a PostgreSQL database, you can leave the defaults and just fill in the connection string in the Secrets section. If you use Neon, it will probably look like this:
postgresql://user:password@instance-name.eu-central-1.aws.neon.tech/database?sslmode=requireIn the next step, select the Trello source plugin. It's default config will load and all you need to do is to fill in the API key and the token in the Secrets section again.
With the configuration ready, progress to the next page, and run the sync. It should only take a few seconds for the sync to finish. You can then connect to the PostgreSQL database and explore the synced data.
Here's an example query to list all overdue cards with their assignees:
SELECT tbc.name, tbc.short_url, tbc.due, string_agg(tbm.username, ', ') AS assignees FROM trello_board_cards tbc
JOIN trello_board_lists l on l.id = tbc.id_list
LEFT OUTER JOIN trello_board_members tbm on tbm.id = ANY(tbc.id_members)
WHERE l.name in ('Todo', 'In Progress')
AND tbc.due < current_timestamp
AND tbc.due_complete = false
GROUP BY tbc.name, tbc.short_url, tbc.dueExample Sync Configuration for local syncs
Here's a full example config using environment variables in case you want to run the sync with CloudQuery CLI locally:
kind: source
spec:
name: "trello"
path: "cloudquery/trello"
registry: "cloudquery"
version: "v1.1.0"
tables: ["*"]
destinations:
- "postgresql"
spec:
# required
api_key: "${TRELLO_API_KEY}"
# required
token: "${TRELLO_APP_TOKEN}"
---
kind: destination
spec:
name: postgresql
path: cloudquery/postgresql
registry: cloudquery
version: "v6.1.3"
spec:
connection_string: "${PG_CONNECTION_STRING}"