Skip to main content

Postgres

Overview

Connect Superblocks to Postgres to build apps with read and write access to a Postgres database:

  • Read data from a Postgres database and utilize it in API steps or UI components

  • Save information retrieved by other API steps or UI components by writing data to a database

Setting up Postgres

1. Select Postgres from the in-app integrations page

Select Postgres from the in-app integrations page and fill out the form with the following configuration:

  • Display Name - Name that will be displayed to users when selecting this integration in Superblocks
  • Host - Hostname or IP address to connect to
  • Port - Port to use when connecting to PostgreSQL
  • Database Name - Name of database to connect to
  • Database Username - Username to use to connect
  • Database Password - Password for database username
  • Enable SSL - Connect via SSL if selected
  • Use a self-signed SSL certificate - Use a self-signed SSL certificate if selected, for example when connecting to Cloud SQL

2. Fill out the form and click "Create Integration"

info

If using Superblocks Cloud, add these Superblocks IPs to your allowlist (not necessary for On-Premise-Agent)

3. Set environments

After creating the integration, you can optionally set different connection settings for your Production and Staging environments. Be sure to click Test Connection for each environment.

success

Postgres Connected You can now write SQL to read from and write to Postgres in any Application, Workflow, or Scheduled Job.

Creating Postgres steps

Connect to your Postgres integration from Superblocks by creating steps in Application APIs, Workflows and Scheduled Jobs. You can configure a Superblocks Postgres integration with read only, read/write, or write only users. You can then configure a Superblocks Postgres step to read from a database, as well as modify/delete data. To get started, enter your SQL query into the text area of the Postgres step. Then click "Run" to see the Result and Executed SQL statement below.

Basic CRUD guide using Postgres

Read table data

  1. Add a Table component to the canvas
  2. Update the Table Header to be the name of the table
  3. Create a new API for your Postgres database and update the API name to readTableData
  4. Use the following SQL query to grab the table data:
    SELECT * FROM <yourtablename>
  5. Update the Table Data to be {{readTableData.response}}
  6. Add a Button component and update the Label to "Refresh"
  7. Update the onClick to {{readTableData.run()}}

Add table data

  1. Follow the read table data guide first
  2. Add a Button component to the canvas and update the Label to "Add Data"
  3. Update onClick to create a new slideout, call this slideout AddData
  4. Update the top text box on the slideout panel to "Add Data to Table"
  5. Add a Form component to the the slideout panel
  6. Add n Input components to the Form and update their Labels to the field name as they correspond to the columns in the table
  7. Make the Input components required by checking the box on each component that is required for data entry
  8. On the Form submit button, update the onClick field to include {{addRow.run()}} and {{AddData.close()}} (Note: addRow hasn't been created yet, so this may throw an error, you can safely ignore it for now)
  9. Create a new API for your Postgres database, call it addRow
  10. Use the following SQL query to add the table data from the form:
    INSERT INTO <yourtablename>(<col1>, <col2>, ..., <coln>)
    VALUES ('{{Input1.value}}', '{{Input2.value}}', ..., '{{Inputn.value}}');
    info

    Be sure to update the query to match the column names and the input component names that you created.

  11. Update the slideout panel's onClose to {{readTableData.run()}}

Update table data

  1. Follow the read table data guide first
  2. Update the onRowClicked of the Table component to open a new slideout panel, call it UpdateData
  3. Update the top text box on the slideout panel to "Update/Delete Data"
  4. Add a Form component to the the slideout panel
  5. Add n Input components to the Form and update their Labels to the field name as they correspond to the columns in the table
  6. Make the Input components required by checking the box on each component that is required for data entry
  7. On the Form submit button, update the onClick field to include {{updateRow.run()}} and {{UpdateData.close()}} (Note: updateRow hasn't been created yet, so this may throw an error, you can safely ignore it for now)
  8. Create a new API for your Postgres database, call it updateRow
  9. Use the following SQL query to update the table data for the selected row from the form:
    UPDATE <yourtablename>
    SET <col1> = '{{Input1.value}}', <col2> = '{{Intpu2.value}}', ..., <coln> = '{{Inputn.value}}'
    WHERE <col1> = '{{Table1.selectedRow.<col1>}}' AND <col2> = '{{Table1.selectedRow.<col2>}}' ... AND <coln> = '{{Table1.selectedRow.<coln>}}';
    info

    Be sure to update the query to match the column names and the input component names that you created.

  10. Update the slideout panel's onClose to {{readTableData.run()}}

Delete table data

  1. Follow the read table data and update table data guides first
  2. In the same UpdateData slideout panel, add a Button component
  3. Update the Button's label to "Delete Data" and update its color to red.
  4. In the onClick field, create a new slideout panel and call it DeleteConfirmation [optional]
  5. Update the top text box to "Are you sure you want to delete the entire row?" [optional]
  6. Add a Checkbox component to the canvas [optional]
  7. Update the Label to "I understand this action is irreversible and data will be deleted from the database"
  8. Uncheck "default selected"
  9. Add a Button component to the slideout panel
  10. Update the Button's Label to "Delete Data"
  11. Update the color to red
  12. Click the <> Code button and paste the following {{!Checkbox1.isChecked}} (this disables the button if the checkbox is not checked) [optional, but required if you followed step 6]
  13. Update onClick for the "Delete Data" Button to {{deleteRow.run()}} and {{DeleteConfirmation.close()}} (Note: deleteRow hasn't been created yet, so this may throw an error, you can safely ignore it for now)
  14. Create a new API for your Postgres database, call it deleteRow
  15. Use the following SQL query to delete the table data from the selected row:
    DELETE FROM <yourtablename>
    WHERE <col1> = '{{Table1.selectedRow.<col1>}}' AND <col2> = '{{Table1.selectedRow.<col2>}}' ... AND <coln> = '{{Table1.selectedRow.<coln>}}';
    info

    Be sure to update the query to match the column names in your table.

  16. Update the slideout panel's onClose to {{readTableData.run()}}

Use cases

Applications

Create an admin dashboard that reads from Postgres and other sources. Mix in APIs to allow support agents to refund orders, update shipping statuses, or transform large datasets in a single UI.

Workflows

Enrich customer order data within Postgres with shipping data from snowflake or other datastores.

Scheduled Jobs

Copy data from Postgres to a Google Sheet on a daily basis for teams who need to see info on new bookings, but don't have access to the database.

Troubleshooting

Check out our guide on common errors across database integrations, in addition to common Postgres connection issues. If you are encountering an error that you don't see in the guide, or the provided steps are insufficient to resolve the error, please contact us at help@superblockshq.com.