PostgreSQL

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

Dynamically Configure Host and Database

ToolJet allows you to configure the Host and Database directly within the query instead of setting them in the data source configuration.

This is particularly useful in multi-tenant applications, where the same ToolJet application needs to connect to different databases based on the active tenant. Instead of creating multiple data sources for each tenant, you can define the host and database dynamically within the query.

To enable this feature, turn on the Allow dynamic connection parameters toggle on the data source configuration page.

Once you enable Allow dynamic connection parameters, you can write custom logic directly inside the query editor to determine which Host and Database to use based on the current logged-in user.

Example Logic

You can use the following code to dynamically configure the host based on the current user's email domain:

{{(() => {
    const domainMap = {
      'tooljet.com': 'db1.internal.company.com',
      'tenantA.com': 'db-tenant-a.company.com',
      'tenantB.com': 'db-tenant-b.company.com',
      'tenantC.com': 'db-tenant-c.company.com'
    };
    const email = globals.currentUser.email || '';
    const domain = email.split('@')[1] || '';
    
    return domainMap[domain] || 'default-db.company.com';
  })()}}

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.

Querying in SQL Mode

1. Create a new query and select the PostgreSQL data source.
2. Select the SQL query mode from the dropdown and enter the query.
3. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

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:

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

Example:

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.

PostgreSQL Dynamic Functions and System Variables

PostgreSQL offers dynamic functions that provide runtime information about the current session, connection, database, and server settings. These can help you write queries that automatically adapt to different environments without hardcoding values.

Function / Variable	Description	Example Output
current_database()	Returns the name of the current database	tooljet_db
current_user	Returns the name of the current user	app_user
session_user	Returns the session user (same as current_user unless role changes)	app_user
version()	Returns the PostgreSQL server version	PostgreSQL 15.3 on x86_64-pc-linux-gnu...
inet_server_addr()	Returns the IP address of the server	192.168.1.10
inet_server_port()	Returns the server port	5432
pg_backend_pid()	Returns the process ID of the current backend	56789

Querying in GUI Mode

1. Create a new query and select the PostgreSQL data source.
2. Select the GUI mode from the dropdown.
3. Select the operation Bulk update using primary key.
4. Provide the Table name and the Primary key column name.
5. Then, in the editor, input the records as an array of objects.
6. Click on the Preview button to preview the output or Click on the Run button to trigger the query.

[
  {
    "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.