SQL is a powerful tool that enables engineers to perform many functions, such as creating databases, managing security, and more. It's flexible, accessible, and affordable for most organizations. While SQL is a powerful tool, SQL queries can be large, complex, and challenging, especially for employees with limited technical abilities.
Fortunately, Airplane provides a method to streamline SQL queries as SQL-based tasks. Using this methodology, you can execute almost any SQL query as a pre-defined task in Airplane. The platform provides users with an easy-to-use interface and easy access to the full suite of SQL capabilities.
In this article, we’ll demonstrate how you can use Airplane Tasks (functions that anyone at your organization can execute) to eliminate manual, repetitive work and allow non-technical users to manipulate database information in a simplified format.
Requirements
For this tutorial, you’ll need the following:
- A free Airplane account
- Airplane CLI
- Access to an external MySQL database
- KitchenShop SQL file
- The connection string information for your MySQL database
If you do not have a MySQL database available for testing, create a free account for a cloud platform, like Heroku, and install a MySQL add-on, like JawsDB MySQL.
Before diving into our example use case, open and run the KitchenShop SQL file to create the database schema and populate it with the sample data for this tutorial.
Example use case: Airplane Tasks for Kitchen Shop
For this tutorial, we'll walk through how to use Airplane to help a fictional company named Kitchen Shop make processes more efficient.
Kitchen Shop sells kitchen utensils, pans, and more. Kitchen Shop employees often open support tickets requesting functionality that isn’t available. These requests require engineers to perform database operations, including queries, inserts, and updates.
To give Kitchen Shop employees more autonomy and eliminate manual executions of SQL commands, Kitchen Shop will use Airplane as its internal tool.
To start off, let's create a new database resource using MySQL in Airplane for Kitchen Shop.
Create a new MySQL database resource
After logging into your Airplane account, you can access Airplane Resources to connect to databases, APIs, and other external systems. Note that Airplane doesn’t host the database itself. Instead, you need to host it externally and access it with your credentials.
To create the new database resource in Airplane, open the resources page, click New Resource, then click the MySQL resource type in the Databases section.
Now, name the resource “airplane-mysql” and fill in the information for your MySQL database connection string, including the name, the host’s IP address, and the port number.
Then click Create.
That’s it! Tasks and sessions can now use your new SQL resource.
Create a SQL query task
Airplane makes it easy to create a task that executes a SQL query. The only code needed for SQL tasks is the SQL query or command. There is no installation, deployment, or other configuration required.
Now, let's create an example SQL task (called select_products.sql) for Kitchen Shop.
This task aims to query products and filter by product name or category name. We’ll use the Airplane CLI to build this task.
Navigate to the folder you want to create your task files in and run the following CLI command:
Then log in, name the new task select_products.sql, and select SQL for the type:
Next, press Enter twice to accept the suggestions for the script file and the definition file for this task:
When the airplane init command completes, you’ll see the following instructions in the terminal:
Airplane CLI created two files in the same directory that's running the airplane init command:
- the
select_products.sqlquery file - the
select_products.task.yamldefinition file
The database resource configures the connection to your database. Navigate to the directory running the Airplane CLI commands and open the select_products.task.yaml definition file to configure the resource for your task.
Edit the resource parameter in the sql section and define the resource name as airplane-mysql:
Task parameters allow you to prompt users for input before triggering your task. Provide a query parameter for the task:
Then, provide the queryArgs parameter in the sql section to bind the query parameter to the SQL script:
Now that you’ve created the task, you can write the SQL query in the SQL script. The script may contain one or more SQL queries to execute when running the task.
Open the select_products.sql script and add the following SQL code:
Deploy and run the task in Airplane
Now, run the following CLI command to deploy your task to Airplane:
Once the task deployment succeeds, go to the tasks page and click the task name.
Provide the Query parameter needed to retrieve products with their categories.
Click Execute task. Note that the output appears in a table in the Airplane UI.
Create a SQL task using INSERT
Now, let’s say Kitchen Shop periodically releases new products that must be added to its catalog. We'll create a simple task that helps non-technical employees add new products to the database when needed.
To create this functionality, begin by running the following CLI command to create a new task called insert_product:
Queries can be both "read queries" (e.g., SELECT) and "write queries" (e.g., INSERT, UPDATE, DELETE). In this case, let's use SQL code to insert a new product and its inventory simultaneously.
Open the insert_product.sql file and add the following code:
Now, open the insert_product.task.yaml file and add the following description:
Next, we'll need two parameters:
- The new product’s description
- The new product's category ID
Add the following code to input both parameters:
Input airplane-mysql as the name of the database resource:
Define the description and category_id parameters as arguments for the SQL script:
Now, run the following CLI command to deploy your task to Airplane:
Open your Airplane Library page and click the insert_product task.
Provide the Description and the Category ID parameters as follows:
- Description: Roaster pan
- Category ID: 8
Click Execute Task.
Now, let's ensure you inserted the product correctly. Go to the select_products task and execute it.
The new pan should appear in the Airplane table UI after executing the select_products task.
Create a complex SQL query task
Finally, let’s say the sales department is looking to identify Kitchen Shop’s top customers. This can also be streamlined through Airplane.
Let’s implement a new task to provide the sales department with this list. Given Kitchen Shop’s database structure, this query is complicated. We’ll need to join multiple tables, and group, order, limit and use the sum aggregate to return the top customer results.
To begin, run the following CLI command to create a new task:
Open the top_customers.sql file and add the following code:
Now open the top_customers.task.yaml file and add the following description:
Enter "airplane-mysql" as the database resource name:
Now, run the following CLI command to deploy your task to Airplane:
Open your Airplane Library page and click the top_customers task.
Click Execute task and you should see the top three customers in the Airplane UI.
This Airplane Task is available as a simple internal tool for the sales team to run themselves, eliminating manual and repetitive task runs for Kitchen Shop engineers.
Conclusion
In this article, we explored how to use Airplane to easily convert SQL queries into an internal app in just a few minutes. Oftentimes, customer support, operations, and other customer-facing teams have to open tickets for repeated tasks like running a query to get a weekly sales report. Engineers can become bottlenecks for these types of operations.
By transforming improvised scripts into safe, internal tools with minimal effort, Airplane Tasks can help your organization work more efficiently. In addition to Airplane Tasks, Airplane offers Views (easy-to-build UIs), task scheduling, and more to make organizations more efficient.
To learn more about Airplane and try it out yourself, you can sign up for a free account. Also, check out the Airplane blog for additional task-related posts, such as creating tasks using Python, scheduling PostgreSQL tasks, replacing cron with scheduled Airplane Tasks, and more.
