PostgreSQL

This page gives information to connect Appsmith to a PostgreSQL database and to read and write data in your applications.

Connect PostgreSQL

caution

If you are a cloud user, you must configure the pg_hba.conf file to whitelist the IP addresses 18.223.74.85 and 3.131.104.27 of the Appsmith deployment on your database instance before connecting to a database. See Client Authentication for more details.

Connection parameters

The following section is a reference guide that provides a complete description of all the parameters to connect to a PostgreSQL database.

Connect PostgreSQL Database

Connection Mode

Specifies the mode in which the Appsmith application can interact with the database.

Options:

• Read Only: This mode permits read-only transactions by default.
• Read/Write: This mode permits both read-write transactions by default.

Host Address

The network location of the PostgreSQL server that you want to connect. This can be a domain name or an IP address. To connect to a local PostgreSQL database, see Connect Local Database for directions on configuring the connection parameters.

Port

The port number to connect to at the server host. Appsmith connects to port 5432 by default if you do not specify one.

Database Name

The name of the database you want to connect.

Username

The username that you want to use to authenticate with the PostgreSQL server.

Password

Password to use if the server demands password authentication.

SSL Mode

Determines with what priority a secure SSL TCP/IP connection is negotiated with the server.

Options:

• Default: The default SSL Mode is Prefer.
• Allow: First try a non-SSL connection; if that fails, try an SSL connection. The client can connect with or without SSL.
• Prefer: First try an SSL connection; if that fails, try a non-SSL connection. The client tries to connect with SSL but falls back to an unencrypted connection if SSL is unavailable.
• Require: Only try an SSL connection. Rejects the connection if SSL is not available.
• Disable: Only try a non-SSL connection. Disallows all administrative requests over HTTPS. It uses a plain unencrypted connection.

For more information, see SSL Support.

Query PostgreSQL

The following section provides examples of creating basic CRUD queries on PostgreSQL.

Write SQL commands in the query editor

info

For the SQL syntax, see the official PostgreSQL documentation.

Fetch data

SELECT * FROM users LIMIT {{ tableUsers.pageSize }} OFFSET {{ tableUsers.pageOffset }};

In the above example, tableUsers is the name of the Table widget used to display the data using server-side pagination.

See how to guide on Fetch and Filter data in SQL.

Insert data

INSERT INTO users
  (name, gender, email)
VALUES
  (
    {{ nameInput.text }},
    {{ genderDropdown.selectedOptionValue }},
    {{ emailInput.text }}
  );

In the above example,  nameInput,  genderDropdown, and emailInput are the names of the widgets used to capture input from the user for name, gender and email fields, respectively.

See how-to guide on Insert and Update data in SQL.

Update data

UPDATE users
  SET email = '{{emailInput.text}}'
  WHERE id = {{ tableUsers.selectedRow.id}};

In the above example, emailInput is the name of the Input widget used to capture the email entered by the user. tableUsers is the Table widget where the user selects the row to update the user's email.

Delete data

DELETE FROM users WHERE id = {{tableUsers.selectedRow.id}};

In the above example, tableUsers is the name of the Table widget where the user selects the row for deletion. For information on how to delete data in a Table, see Delete Data in Table.

info

Prepared statements are turned on by default in your queries to help prevent SQL injection attacks. For more details, see Prepared Statements.

Row Level Security (RLS)

Row level security is a PostgreSQL security feature the database provides to limit what table rows are visible for querying. It allows the database admins to define security policies to control how the rows of a given table can be accessed or modified. Watch the video series for details on using RLS on your apps.

Troubleshooting

If you're experiencing difficulties, you can refer to to the Datasource troubleshooting guide, or contact the support team using the chat widget at the bottom right of this page.

See also

• Fetch and Filter Data in SQL - Learn how to fetch and filter data from SQL databases using queries.
• Insert and Update Data in SQL - Step-by-step guide on inserting new records and updating existing data in SQL databases.
• Update Data - Understand how to update data in your application using Form widget.
• Insert Data - Step-by-step instructions on inserting new records into your database using Form widget.