PostgreSQL - Tooljet

ToolJet has the capability to connect to PostgreSQL databases for data retrieval and modification.

Establishing a Connection

To establish a connection with the PostgreSQL data source, you can either click on the + Add new Data source button located on the query panel or navigate to the Data Sources page from the ToolJet dashboard and choose PostgreSQL as the data source.

ToolJet offers two connection types to connect to your PostgreSQL database:

Manual connection
Connection string

Manual Connection

To connect to PostgreSQL using Manual connection parameters, select Manual connection as the connection type and provide the following details:

Host
Port
SSL
Database Name
Username
Password
Connection Options
SSL Certificate

Connection String

To connect to PostgreSQL using a connection string, select Connection String as the connection type and provide the following details:

Connection String

Note: We recommend creating a new PostgreSQL database user to have control over ToolJet's access levels.

:::info Please make sure the Host/IP of the database is accessible from your VPC if you have self-hosted ToolJet. If you are using ToolJet cloud, please whitelist our IP. :::

</div> <div style={{paddingTop:'24px'}}>

Querying PostgreSQL

Click on + Add button of the query manager at the bottom panel of the editor.
Select the PostgreSQL datasource added in previous step.
Select the query mode from the dropdown and enter the query.
Click on the Preview button to preview the output or Click on the Run button to trigger the query.

SQL Mode

To execute SQL queries, select the SQL mode from the dropdown and enter your query in the editor.

Parameterized Queries:

ToolJet offers support for parameterized SQL queries, which enhance security by preventing SQL injection and allow for dynamic query construction. To implement parameterized queries:

Use :parameter_name as placeholders in your SQL query where you want to insert parameters.
In the Parameters section below the query editor, add key-value pairs for each parameter.
The keys should match the parameter names used in the query (without the colon).
The values can be static values or dynamic values using the {{ }} notation.

Example:

yaml

Query: SELECT * FROM users WHERE username = :username
SQL Parameters:
  Key: username
  Value: oliver // or {{ components.username.value }}

Query Timeout

You can set the timeout duration for SQL queries by adding the PLUGINS_SQL_DB_STATEMENT_TIMEOUT variable to the environment configuration file. By default, it is set to 120,000 ms.

GUI Mode

Choose the GUI mode from the dropdown and select the operation Bulk update using primary key. Provide the Table name and the Primary key column name. Then, in the editor, input the records as an array of objects.

json

[
  {
    "customer_id": 1,
    "country": "India"
  },
  {
    "customer_id": 2,
    "country": "USA"
  }
]

:::tip

You can apply transformations to the query results. Refer to our transformations documentation for more details: Transformation Tutorial
Check out this how-to guide on bulk updating multiple rows from a table component. :::

</div>