Skip to main content

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
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
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.

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​