1. In the Setup menu, click Run Query.

The screen reroutes to the Run Query screen, as shown in the image below.

2. Enter queries in the text editor at the top of the screen.

3. While entering the query or viewing results, to extend the text editor or extend the results display, click the gray line and move the arrow up or down.

4. Click the Toggle Editor Fullscreen icon to expand the text editor to full screen.

a. Click the Toggle Editor Fullscreen icon to exit fullscreen.

5. To start a new query with new filters, parameters, etc., without losing your current query data, click the Addition (+) icon to add a new tab.

6. Click and drag tabs to rearrange them.

7. Hover over the tab and click the black X to close the tab.

a. In the confirmation pop-up window, click OK.

8. Right click on any of the query text editor tabs for additional options including:

• Close Tab: Closes the current tab you right-clicked on, removing the associated query.
• Close Others: Closes all other tabs except the one you right-clicked on. This is useful if you want to clear out distractions and focus on a single query.
• Close Tabs to Right: Closes only the tabs that appear to the right of the selected tab, based on the tab order. Helpful for cleaning up without closing everything.
• Close All Tabs: Closes every open tab, allowing you to start fresh.
• Rename: Lets you assign a custom label to the selected tab, making it easier to identify specific queries (e.g., rename Query 3 to Attendance Query).

9. Click on any SQL syntax element within the query to highlight all matching instances throughout the query. For example, clicking on last_name will highlight every occurrence of last_name, making it easier to track column names, table references, or variables within the code.

10. Formatted queries have additional settings. Anywhere the arrow displays, you can collapse the range of code, such as functions, loops, or large blocks of SQL, so you can focus on specific parts without being distracted by the entire script. This is especially useful for navigating complex or lengthy queries, as you can hide unnecessary parts of the code temporarily and only view what's relevant.

Once collapsed, click the arrow again to expand the range of code.

1. In the Setup menu, click Run Query.

The screen reroutes to the Run Query screen, as shown in the image below.

2. Enter queries in the text editor at the top of the screen.

3. The Table List is open by default. Click Table List to hide the Tables, History, and Parameters tabs.

a. Click Table List again or click Show Sidebar to display the tabs.

b. Hover over the Table List panel to extended the column for a better view of Tables, History, or Parameters.

4. To aid in writing queries, the Tables tab defaults to open, displaying a complete list of available tables. This feature provides quick access to table structures, column names, and data types, helping users construct queries more efficiently.

a. Click the listed table of interest to open associated fields. You can then click Create Select Query to automatically generate a query to pull data as it pertains to the select table.

The table list shows data type lengths (e.g., VARCHAR(50), CHAR(10)) next to applicable columns, making it easy to understand table structures at a glance.

b. Start typing the name of a table or a field to find the corresponding table in the Search tables or columns text box, then click the magnifying glass icon or press Return/Enter.

c. The page numbers of tables available displays. In the example displayed, there are 55 pages of tables. Click the displayed page numbers to jump to that page.

d. Hover over the Table List panel to extend the column and view the field names and the column types side by side.

5. Click the History tab to open a history of the last queries ran, which includes the date, time, and query.

a. To run the query again, click the Run Query button.

b. To edit the query, click the Edit Query button.

c. Press and hold the Shift key while clicking the Edit Query button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add a query from the History tab to the existing query, you can hold the Shift key and click the Edit Query button to add it to the query editor.

6. Click the Parameters tab to add parameters and view existing parameters, which allows for dynamic value substitution in queries. This feature is particularly useful for reusable queries that require different inputs. Parameters are especially helpful for filtering data, setting date ranges, or dynamically updating query criteria.

a. Click Add Parameter to add a new parameter.

b. Enter the parameter's Name and Value in the provided text boxes.

You can also use the workspace below to enter the name and value.

c. Click Add Parameter again to add another parameter. You can add as many parameters as you need to a single query.

d. Click the gray x to delete a parameter.

e. Click the gray line and move the arrow up or down to extend the listed parameters name and value or workspace.

In the example used, if you set the grade parameter to 10, the query will retrieve all students in grade 10. This approach allows you to reuse the same query for different grade levels without editing the SQL code each time.

As shown in the image below, the parameter for grade 10 has been used to retrieve student enrollment records for all students' whose next grade level is grade 10.

7a. Select Readonly from the pull-down to run queries without making actual changes to the database, which is useful when running queries that will be used to update, insert, or delete data.

7b. Select Transaction from the pull-down to make all of the queries you've typed in the run text box into a single transaction instead of having a separate transactions for each query. This is useful if you're testing something and need to be able to use the ROLLBACK, TRY, etc. functionality of SQL. I.e. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Suppose you're testing a process that inserts a new student record and updates their enrollment status. Using a transaction ensures both actions succeed together  or neither action happens at all if something goes wrong.

• BEGIN; starts the transaction.
• The INSERT and UPDATE statements are part of the same transaction.
• COMMIT; finalizes the transaction, applying both changes to the database.

If something goes wrong  for example, the INSERT works but the UPDATE fails  you can add a ROLLBACK to undo everything.

7c. Select No Transaction from the pull-down to run the queries without initiating any transaction context, meaning no changes will be made to the database. This is useful when you're simply retrieving data or testing a query that doesn't require the transactional features of SQL, such as ROLLBACK or TRY. Without a transaction, each query is executed independently, and no intermediate state is saved, so if something goes wrong, you dont have the ability to undo changes across multiple queries as you would in a transaction. This option ensures that the query runs purely for data retrieval or analysis purposes, with no impact on the database's state.

8a. Select Export as INSERT Statements and click the Run Query button to format the output of a SELECT query into an INSERT query syntax with a [table] placeholder. This allows you to generate INSERT statements based on the data returned by the SELECT query, where you can later replace the [table] placeholder with the actual table name. This is useful for quickly creating bulk data insertions into a table, especially when you need to replicate or migrate data from one database to another, or when preparing test data for new tables. Once you have the INSERT statements, you can modify or run them to insert the queried data into the desired table.

8b. Select Export CSV from the pull-down and click Run Query to download the query results in a CSV file to your computer.

8c. Select Export JSON from the pull-down and click Run Query to download the query results in a JSON file (JavaScript Object Notation).

8d. Select No Export from the pull-down to run your queries without exporting the results to an external file or format. This option allows you to execute the query and view the results directly within the system without generating an output file, such as CSV or INSERT statements. It's useful when you're only interested in viewing or testing the query results internally, without the need to save or export the data.

9. Click the Format button after entering a query in the provided text box in order to format the query.

The query displayed below is an example of non-formatted.

The query displayed below is an example of formatted.

10. Select the Dark Mode toggle to apply the dark mode to Run Query.

Click the Dark Mode toggle again to turn it off.

11. Select the Explain Query check box and click Run Query to enable the feature that provides an analysis of your SQL query's execution plan. This option allows you to view detailed insights into how the database engine processes your query, including which indexes are being used, estimated costs, and potential performance bottlenecks. It helps you understand how to optimize your query for better performance, especially useful when working with complex queries or large datasets.

In the example used, the following information is contained in the explanation:

Execution Plan:

• Insert on students  (cost=0.00..0.03 rows=1 width=17140) (actual time=15.017..15.017 rows=0 loops=1)
• Result  (cost=0.00..0.03 rows=1 width=17140) (actual time=0.106..0.107 rows=1 loops=1)
• Planning Time: 0.245 ms
• Trigger f:t:b:i:students: time=0.830 calls=1
• Execution Time: 15.214 ms

Explanation:

• Cost: The estimated cost for the query ranges from 0.00 to 0.03, indicating a very lightweight insert operation with minimal resource usage.
• Insert on students: This step represents the actual insertion into the students table. The operation is simple and efficient, involving a single row.
• Result: The result node shows the system successfully processed one row, with actual execution time between 0.106 and 0.107 ms.
• Planning Time: It took 0.245 ms for the database to plan the query before execution.
• Trigger: A trigger on the students table ran during the insert, executing once and taking 0.830 ms.
• Execution Time: The total time to execute the query was 15.214 ms, which is fast and confirms the operation completed successfully.

If there is an error with your query, the explanation displays information about the error.

Explanation:

• The error message indicates that the column "enrollment_status" does not exist in the student_enrollment table.
• The error occurs during the EXPLAIN ANALYZE execution, as the database cannot find the specified column to update.
• SQLSTATE[42703] refers to an undefined column error, which means the column name provided in the query does not match any existing column in the table.
• The caret (^) symbol shows the position of the issue within the SQL query, pointing to the column name enrollment_status.

a. Upon applying Explain Query or running an erroneous query, click the AI Helper to leverage AI assistance for improving or troubleshooting your query.

AI Error Assistant

• Explains why an error occurred in your query.
• In some cases, it will provide a corrected version of the query.
• Includes the Additional Notes section for alternate solutions or best practices.

AI Optimize Assistant

• Provides recommendations to improve query performance, even when no errors are present.
• Suggests a stronger, optimized version of your query that you can either copy or insert directly into Run Query.
• Includes Key Changes for important adjustments and offers Additional Recommendations for further insights.

i. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

ii. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

iii. Click Cancel or click the black X to close the AI Helper.

12. From the Editor Settings pull-down, select the Auto Pairs check box to automatically match pairs of characters like quotes, parentheses, and brackets as you type, making it easier to write and edit queries.

When typing a single quote ', the system will automatically add the closing quote ', placing your cursor in between:

SELECT * FROM students WHERE last_name = '';

After typing the first ', the closing ' appears automatically, so you can immediately type the value (e.g., 'Smith').

The same applies to:

• Parentheses: typing ( becomes ()
• Brackets: typing [ becomes []
• Double quotes: typing " becomes ""

13. From the Editor Settings pull-down, select the Word Wrap Editor check box to apply word wrapping in the SQL editor, allowing long lines of text to break and continue on the next line for easier editing.

14. From the Editor Settings pull-down, select the Autocomplete check box to enable real-time query suggestions as you type. This feature helps streamline your workflow by providing recommended table names, column names, and SQL keywords based on what you've entered.

For example:

• Typing SEL will suggest SELECT.
• Typing student_ may display options like student_enrollment, student_grades, or student_attendance.

15. From the Editor Settings pull-down, select the Custom Schema Autocomplete check box to enable autocomplete suggestions for tables and columns within custom database schemas--beyond the default public schema--while writing queries. Note: This option is only available if Autocomplete is enabled.  

16. From the Editor Settings pull-down, select the Save Queries check box to enable automatic saving of your most recent queries. This allows you to retain your SQL work between sessions, so you can easily return to and reuse previous queries without having to retype or copy them manually.

17. From the Table Settings pull-down, select the Single Query check box and click Run Query to execute your entire SQL block as a single query. This is essential for creating objects like stored procedures or functions, which contain multiple SQL statements that must run together as a unit.

If Single Query is not selected, the PDO driver (which transmits SQL code to the database) treats each statement separately. This can cause errors when executing multi-statement logic that relies on being processed as a single block.

When creating a function in SQL, it must be processed as a single query because functions--or any query containing double dollar signs ($$)are treated as a block of code. By default, the Run Query tool attempts to parse and execute queries by splitting them at semicolons (;). However, functions often contain multiple semicolon-terminated statements within a single block, meaning they should not be executed individually.

Enabling Single Query ensures the function is recognized as a single executable unit. Running multiple unrelated queries in Single Query mode will result in an error.

Example show below: Creating a Function to Retrieve the Current School Year

This function retrieves the current school year (syear) from the program_config table and allows an optional integer input ($1) to modify the returned year. See the image below on how to use this function in a query, which will retrieve student enrollment records for the current school year:

18. From the Table Settings pull-down, select the Paginate check box to break down query results into pages as opposed to having all results displays on a single page where you would have to scroll through all of the results.

The example displayed below shows results when not paginated.

The image below shows results when the Paginate check box is selected.

19. From the Table Settings pull-down, select the Preprocess check box to adjust SQL syntax for compatibility, which can be helpful when copying SQL from code. It converts Postgres type casts, handles empty arrays, and modifies LIMIT/OFFSET clauses for cross-DB execution.

20. Select the View Custom Schemas check box to display custom database schemas in the table list. This is helpful if your database includes additional schemas beyond the default (such as public) and you need to reference tables stored there.

21. From the Table Settings pull-down, select the Word Wrap Table check box to apply word wrapping in the query results table, making wide data values easier to view without horizontal scrolling.

22. Once a query has been entered and all selections have been made, click Run Query for query results, which display on the bottom of the screen.

a. Click the Stop button to stop the query from running. This is an option as long as the query is running.

23. Click the Analyze & Optimize icon to open the AI Optimize Assistant or the AI Error Assistant depending on whether or not errors have been found in the query. See AI Error Assistant and/or AI Optimize Assistant for more information.

24. The results displayed is based on the query displayed. If the query is changed in the provided text box, you can repopulate the text box with the previously run query by clicking the query link.

25. Click the Export CSV button to download the query results to an Excel spreadsheet.

26. The Time is took for the query to pull the applicable results displays, as well as the number of Rows, and the number of records Showing.

27. If the query results are paginated, click the page number to jump to the page or click the double arrows to jump to the last set of pages.

28. To sort results, you can click the headers. Click once for ascending results; click twice for descending results.

1. In the Setup menu, click Run Query.

2. Enter your query, and apply all applicable filters, parameters, etc.

3. Click the Run Query button.

4. Upon running an erroneous query, click the AI Helper or click the Analyze & Optimize icon to leverage AI assistance for improving or troubleshooting your query.

The AI Error Assistant explains why an error occurred in your query. In some cases, it provides a corrected version of the query and includes an Additional Notes section with alternate solutions or best practices.

a. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

b. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

i. Press and hold the Shift key while clicking the Insert into editor button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add the listed query from the AI Helper to the existing query, you can hold the Shift key and click the Insert into editor button to add it to the query editor.

c. Click Cancel or click the black X to close the AI Helper.

In this example, the AI Error Assistant made it clear that the parameter was not set for the SYEAR. Upon adding the parameter, the query now runs.

In another example, the AI Error Assistant identifies a missing comma as the cause and provides a corrected version of the query.

Scroll down to Key Changes to review the modifications made.

1. In the Setup menu, click Run Query.

2. Enter your query and click the Run Query button.

3a. Click the Analyze & Optimize icon.

3b. Select the Explain Query check box and run the query, then click the AI Helper icon.

4. The AI Optimize Assistant provides recommendations to Optimize Join Order, Push Down Filters, Review Index Usage, Limit Rows in Joins, Analyze Table Statistics, and other options that may display depending on the query, such as Use CE for Complex Filters.

a. Click Optimize Join Order to rearrange the join order, which can reduce the number of rows processed starting with the most selective filters first.

For example, to optimize the original SQL query, we can rearrange the join order to start with the most selective filters first, particularly focusing on the student_enrollment table with the conditions on syear and end_date. This approach should help reduce the number of rows processed in subsequent joins.

b. Click Push Down Filters to filter conditions from the WHERE clause into the joins where applicable. This can reduce the number of rows being processed in the join operations.

c. Click Review Index Usage to evaluate whether existing indexes on key tables--such as student_enrollment and students--are effectively supporting the query's filters and joins. This tool helps identify opportunities to optimize performance by suggesting index adjustments, such as adding or modifying columns included in the index.

d. Click Limit Rows Early to add additional filters to limit the number of rows returned from the applicable table, such as student_enrollment, before performing joins. This can significantly reduce execution time.

e. Click Analyze Table Statistics to ensure that the statistics for the involved tables are up to date. Running 'ANALYZE' on the tables can help the planner make better decisions based on the actual data distribution.

5. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

6. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

a. Press and hold the Shift key while clicking the Insert into editor button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add the listed query from the AI Helper to the existing query, you can hold the Shift key and click the Insert into editor button to add it to the query editor.

7. Click Cancel or click the black X to close the AI Helper.

1. In the Setup menu, click Run Query.

2. Enter your query in a new tab.

3. Click the Parameters tab to add parameters and view existing parameters, which allows for dynamic value substitution in queries.

4. Click Add Parameter to add a new parameter.

5. Enter the parameter's Name and Value in the provided text boxes.

You can also use the workspace below to enter the name and value.

6. Click Add Parameter again to add another parameter. You can add as many parameters as you need to a single query.

7. Click the gray x to delete a parameter.

8. Click the gray line and move the arrow up or down to extend the listed parameters name and value or workspace.

9. Click Run Query.

In the example used, if you set the grade parameter to 10, the query will retrieve all students in grade 10. This approach allows you to reuse the same query for different grade levels without editing the SQL code each time.

As shown in the image below, the parameter for grade 10 has been used to retrieve student enrollment records for all students' whose next grade level is grade 10.