AppMaster offers extensive capabilities for working with the database. For example, using the Search block, you can find the necessary data, attach related tables to it, sort it in the correct order, etc. However, in certain situations, this may not be enough, and then the SQL Exec block comes to the rescue. It allows you to run any database queries using the full power of SQL.
Let's consider the operation of the block using the example of a database that contains a catalog of books.
Let's do a little prep work. It is necessary to create a business process that, in its simplest form, would allow sending requests, as well as receiving their results.
You will also need to create an endpoint that will allow you to access this business process.
At the initial stage, this should be enough. You can publish your application and move on to testing. For this, it is very convenient to use Swagger, which is automatically created when publishing.
We use a simple query that should request all books from the database.
SELECT * FROM public.book
Please note that the table itself (like all other tables created in the database editor) has a prefix indicating the schema - public.
You can verify that the request was completed successfully and the result was received.
But the problem is that it is very difficult to perceive the answer in this form. A possible solution is to modify the request a little and leave only the necessary fields in it—for example, the title of the book and the number of pages. In addition, it would be reasonable to set a limit, request not all books from the database, but limit yourself to ten.
SELECT name, pages FROM public.book LIMIT 10
Much better, but still not suitable for real use. After all, in a business process, you need not only to receive a request but also to do something with its result. For this, it is not enough to have the result in text form; you need to turn it into a model suitable for further use.
To do this, let's go back to the business process and refine it a little. In the end block, we will add a new variable - an array of book models.
You will also need a block that will convert the JSON received as a result of the request into a model. Deserialize JSON To Model.
Let's repeat the previous request and make sure that the result has become much more suitable, both for visual perception and for further use in BP.
Now, we can move on to more complex logic. Let's create a business process that:
- Receives the title of the book as input.
- Determines which category (genre) it belongs to.
- Returns 3 random books from the same category as a result (in this case, the book from the request should not be among them)
To do this, we will create a new business process that combines search using standard blocks and the use of SQL queries.
The first block is to find a book by its title, and also determine which categories it belongs to. To do this, we use the search block with the following parameters:
- _With = Categories - in addition to the book itself, the query result requires information from the associated category table.
- _Limit = 1 - only one book needs to be found.
- _Ilike = False - the name must exactly match the requested one.
- Name - index of the book title, passed from the Start block.
Using the Array Element block with index 0, we take the first (and only) book from the result.
A book can belong to several different categories at the same time, and in this case, any of them will suit us. It can be selected randomly using the Random element block
After this, we have all the necessary data, and all that remains is to create the request itself, which may look like this:
SELECT * FROM public.book WHERE id IN (SELECT rel1_id FROM public.book_categorys_category_books_pivot WHERE book_categorys_category_books_pivot.rel2_id = X) AND id <> Y ORDER BY random() LIMIT 3
Where X is the id of the book category, and Y is the id of the book itself.
Please note that this query includes a subquery. First, from the book_categorys_category_books_pivot table (it is used to store information about the relationships between two tables), all book identifiers corresponding to the selected category are found. After this, a query is executed that finds random 3 books that match the specified range, excluding the book ID, whose title was originally passed to the business process.
For a more detailed study of the project database structure, you can use the Open DB button in the Deploy Plans settings.
It will allow you to open the database in the editor and get direct access to viewing and editing data. However, you should be careful and take into account the fact that changing the data structure itself in the editor will make it impossible to further publish the project.
Let's return to creating a business process. It is necessary to complete the compilation of the request and to do this, convert the book and category ID from integer to string and also assemble the final request using the Concat Strings (Multiple) block.
The last step is to execute the query, convert the result into a model, and send it to the End block as the query result.
You can save your changes, create an endpoint, publish your project, and verify that the request works correctly. In this case, one SQL Exec block with a complex compound query was used to replace many other blocks and simplify the structure of the business process.
The use of the SQL Exec block is not limited to data retrieval and can be used in a wide variety of scenarios. Let's take a closer look at a few more options.
- Counting the number of comments for a book with id=X
SELECT COUNT(id) FROM public.comment WHERE book_id = X - Calculation of the average rating of a book, taking into account all ratings given in the comments:
SELECT AVG(rate) FROM public.comment WHERE book_id = X - Deleting all comments written before 2023 (can be used, for example, to quickly clear the log).
DELETE * FROM public.comment WHERE created_at < '2023-01-01'
In conclusion, it is worth noting that to improve security, a filter has been added to the SQL Exec block for dangerous operations that can lead to schema changes.
CREATE/ALTER/DROP/TRUNCATE for TABLE|COLUMN|INDEX|CONSTRAINT|SEQUENCE|SCHEMA|DATABASE are disabled by the filter if the application is hosted on AppMaster servers. When hosting on-premise - by default, any requests are available without restrictions.
