Tutorials
How to sync from Trello to PostgreSQL with CloudQuery
We have recently released the CloudQuery Trello integration and in this guide, I will walk you through the full setup of a sync from Trello to a PostgreSQL database using our PostgreSQL destination integration.
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 configuration, 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 dowload CloudQuery CLI. Find the full example config at the end of this post on CloudQuery example configurations.
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 into the CloudQuery Platform 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:[email protected]/database?sslmode=requireIn the next step, select the Trello source integration. The 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 configuration 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}'Create your sync now #
Ready to get started with CloudQuery? You can try out CloudQuery locally with our quick start guide or explore the CloudQuery Platform (currently in beta) for a more scalable solution.
Got feedback or suggestions? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.
