Skip to main content

MySQL

This page provides information for connecting your application to your MySQL database and for using queries to manage its content.

Appsmith supports MySQL versions 5.5, 5.6, 5.7, and 8.0.

Connect MySQL​

caution

If you are a cloud user, you should whitelist the IP address of the Appsmith deployment 18.223.74.85 and 3.131.104.27 on your database instance or VPC before connecting to the database.

Connection parameters​

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

MySQL Datasource configuration page
Configuring a MySQL query.
info

MySQL databases are queried using standard SQL syntax. For a language reference, see MySQL's Language Structure.

Fetch data​

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

In the above example, UsersTable is the name of the Table widget used to display the data using server-side pagination to control how much data is queried at once.

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. For more information on how to insert data, see Insert Data.

Update data​

UPDATE users
  SET email = {{ EmailInput.text }}
  WHERE id = {{ UsersTable.selectedRow.id }};

In the above example, EmailInput is the name of the Input widget used to capture the email entered by the user. UsersTable is the Table widget where the user selects the row to update the user's email. For more information on how to update Table data, see Update Data Guide.

Delete data​

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

In the above example, UsersTable 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.

SQL modes​

SQL_MODE is a system variable in MySQL that can:

  • Configure the server's strictness when accepting input data,
  • Adjust syntax to conform more closely to standard SQL,
  • Provide better compatibility with other databases.

In particular, strict mode can help you ensure the integrity of your data by preventing the database from automatically inserting default values for missing or invalid data.

For more information, see the docs for MySQL's SQL Modes.

Troubleshooting​

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

See Also​

  • Display and Lookup Data in Table - Learn how to display query results in a Table and enable users to look up data with ease.
  • Search and Filter Table Data - Guide on adding search and filter functionality to Tables for better data navigation.
  • 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.