Skip to main content
Data > Databases connects Triggr to a PostgreSQL database you run, so workflows can query and change it with Execute SQL and forms can list options from it. You need the Admin role.

Connect a database: connecting PostgreSQL, querying it from a workflow and filling a dropdown from it (1:28).

The Databases page, Manage your external database connections, with Refresh and New Connection buttons and one connection, Docs sample – MSP data, type postgresql, database msp_data, with Test Connection, Edit and delete actions.

​
Connect a database

1

Let Triggr in

The database needs a public address. Allow Triggr’s IP addresses to its port (usually 5432), and create a database user with only the rights your workflows need.
2

Start a connection

Click New Connection, then Connection String (or Manual Setup to type each detail).
The Create New Database Connection dialog offering Manual Setup, to enter database connection details manually, or Connection String, to paste your database connection string.
3

Paste the connection string

Paste it as postgresql://user:password@host:5432/database?sslmode=require and click Continue.
The Enter Connection String dialog with a connection string for user triggr_app, a password placeholder and host db.yourmsp.com entered, and Back and Continue buttons.
4

Check the details

Rename the connection (it starts as the database name), type the password if the string had none or it has symbols in it, and click Create Connection.
The Create New Database Connection form with Name Docs sample – MSP data, Type PostgreSQL, Host db.yourmsp.com, Port 5432, Database Name msp_data, Username triggr_app, a masked Password, SSL Mode Require, and Back and Create Connection buttons.
Leave SSL Mode on Require, or choose Verify Full to also check the server’s certificate against its host name.
5

Test it

Click Test Connection on the connection’s row: it should say “successfully connected to the database” (otherwise see Troubleshooting).

​
Query it from a workflow

Add Execute SQL from Database, choose the database and replace the example query in SQL Query with yours; this one finds yesterday’s failed backups in a backup_results table.
The Find Failed Backups step: Database Docs sample – MSP data and a SQL Query selecting client, device and finished_at from backup_results where status is failed and finished_at is within the last day, ordered by client.
The output lists the rows in data, every value as text (empty for NULL).
Loop over the rows with a For Loop on ctx.steps['Find Failed Backups'].data, or read one value with {{ ctx.steps['Find Failed Backups'].data[0].client }}. A single row with a column named count, sum, avg, min, max or total also arrives as a number in value.

​
Change data

INSERT, UPDATE and DELETE work too; add RETURNING id to get the new rows back in data. Create tables with your own database tool. Each query has 30 seconds.

​
Put values in a query

Templates become part of the SQL text, so quote text values and only use values you trust:
An apostrophe in a value, such as O'Brien, breaks the query and could change what it does. For anything a person or another system typed, double the apostrophes in a Code step first, as the Evaluate step does in the uptime monitor.

​
Use it in forms

A Select field can list options from your database: set Options Source to Use database, choose the connection and write a query that returns a value column and a label column. {{ ctx.client.variableName }} inserts a client variable of the person’s client. Test Query runs the query as typed, so test it without templates first.
A Select field's Options Source set to Use database, Database Connection Docs sample – MSP data (postgresql), a SQL Query selecting site_id and site_name from client_sites where halo_client_id equals the client's haloId variable, Value Column site_id and Label Column site_name.
Forms with Microsoft or Google sign-in can also check a database before letting someone in (Optional Authorization in Form authentication).

​
Security

Triggr stores the password encrypted, and every Execute SQL step runs as the database user you entered, whoever runs the workflow. Anyone with the Admin role can edit connections and write queries against them.

​
Troubleshooting

The host isn’t a public DNS name; use the database’s internet-facing address.
The Test Database Connection dialog with a red cross and the message failed to connect to database: dial tcp: lookup db.yourmsp.com: no such host.
Port 5432 is blocked from Triggr. Allow Triggr’s IP addresses in your firewall, and check PostgreSQL listens on the public address.
Click Edit on the connection and retype the password; one pasted in a connection string keeps symbols such as @ encoded.
Turn on SSL in PostgreSQL so the password and data are encrypted on their way to Triggr.
Triggr checks each query before running it; the rest of the message is the problem: a syntax error, a missing table, a statement such as CREATE TABLE, or a connection error like those above.
A failed Find Failed Backups step in a run, with Status error, Type execute_sql and Output error SQL query failed: invalid SQL query: dial tcp: lookup db.yourmsp.com: no such host.
Match postgresql://user:password@host:5432/database, or choose Manual Setup and type the details.
PostgreSQL. For small registers you don’t need SQL for, use Data tables.