Queries

The information you want to present in any application is typically spread over numerous tables in a well-designed database. A query can collect data from different tables and pull it together for display. You can either request data from your database or act on the data, or both. 
To write and execute queries, you should understand the basics of connecting to a Database or Connecting to an API.
A query can add, update, or remove data from a database, conduct calculations, integrate data from other databases, and much more. You can create either static queries or dynamic queries in Appsmith.
APIs, Database Queries, and Integrations that fetch / update data from third-party services are collectively termed as Queries in Appsmith
Static Query
When you need to execute the same query statements for each input row but wish to alter the data in the query, create a static query. When using the static query, the preprocessor decides how to access the database in advance and executes that decision, so the user cannot run queries during runtime. Static SQL is usually employed when data is distributed uniformly.
The following static SQL query uses query parameters that bind to the Student_ID and Class input fields:
SELECT * from users where status in ({{studentID.text}} , {{Class.text}})
Dynamic Queries  
The method you use to write SQL queries so that they are dynamically created alongside application operations is known as "dynamic SQL." A dynamic query is automatically refreshed each time it is used.
You generate the query on the fly based on some parameters and then execute it. For example, {{Query_to_Execute.text}} where the Text Widget (Query_to_Execute) has the query that will be executed, which could be generated on the fly based on some logic in the code.
Dynamic queries are required for various application types, such as those that show user's online status or applications that query a database where the data definitions of tables are constantly changing.
For example, if you want to select users based on the status selected on the application you can use a query as below:
SELECT * from users where status in = ANY ({{userStatus.selectedOptionValues}})s
The sample query is for PostgreSQL, MySQL, or MSSQL data sources.
You can create queries to fetch and update data from a datasource using the Appsmith query editor for each datasource type. 
Setting up a Query
Go to the page that needs to run this query.
Queries are auto-saved, so you never lose your work. These changes will be reflected in your published application only after you deploy.
1.Go to Datasources → +.
2.Choose the connected datasource and click + New Query
3.You will be taken to the query editor to configure the query.
4.Note that the query is created with a default name. It’s recommended that you rename it for readability and access.
5.Write your query in the syntax that is valid for your database type.
6.APIs can be configured using a REST interface​
A query and its results can be accessed from only the page it is a part of. To use a query on another page, click the context menu next to the query name and clone it.
Naming a Query
A query must have a unique name that acts as an identifier. It is used to access the query results. In that sense, a name is like a variable in a programming language. You can access the various properties of the query and its data using the query name.
Note that JavaScript keywords and the window object methods and properties are not valid as query names.
Running a Query
Click on the Run button or hit cmd + enter to execute a query. If the query succeeds, a success message will pop-up on the screen in the top right corner, along with the results below the query.
All query results are stored in the data property of the query object. This object is immutable and cannot be changed. They can be accessed using javascript
{{ Query1.data }}
DB Queries fetch data in the form of an array of objects where each object is a row, and each key in the object is a column.
All API Queries return the exact API response of the endpoint
To understand queries, read this How to Use Prepared Statements guide.
Using Queries in applications
Once you have successfully run a Query, you can use it in your application to
​Display Data​
​Capture Data​

Core Concepts - Previous

Data Access and Binding

Query Settings

Last modified 18d ago

Copy link

Edit on GitHub

Outline

Using Queries in applications