Build a SQL task
Airplane makes it incredibly easy to take simple SQL queries and turn them into apps ("tasks") that
you and your teammates can use.
In this guide, we'll create SQL tasks to search and edit users in a demo database.
Before you begin
If you haven't yet, first install the Airplane CLI by following the instructions at
Installing the CLI. (If you have already installed the
Airplane CLI, ensure you have the latest version by
Upgrading the CLI.)
Create a Demo DB
If you haven't yet, you'll need a demo database for this task. Assuming you have the CLI installed,
you can run:
shellCopied1airplane demo create-db
This will create a DB with tables including
users
and accounts
:users
id
integername
texttitle
textemail
textaccount_id
integer
accounts
id
integercompany_name
textcountry
text
Create a SQL task to search users
To create a SQL task, you can
init
from the CLI. For this guide, we recommend running init
from
a new, empty directory.shellCopied1# mkdir airplane-getting-started2# cd airplane-getting-started3airplane tasks init
You'll be prompted for details. Make sure to choose
SQL
for the kind of task.Copied1? What should this task be called?2> Search users3? What kind of task should this be?4> SQL5? Where is the script for this task?6> search_users.sql7? Where should the definition file be created?8> search_users.task.yaml
You can edit
search_users.sql
to specify a SQL query:sqlCopied1-- search_users.sql2select * from users3join accounts on accounts.id = users.account_id4where users.name ILIKE :search5or users.title ILIKE :search6or users.email ILIKE :search7or accounts.company_name ILIKE :search
This query performs a search over user and the user's account using
ilike
for case-insensitive
matching. We've specified :search
above - this is a query argument that we'll fill in separately.
Edit search_users.task.yaml
to configure:yamlCopied1# search_users.task.yaml2# Task slug uniquely identifies the task and is used in places like views3slug: search_users4name: Search users5parameters:6- name: Query7slug: query8type: shorttext9required: false10sql:11resource: "[Demo DB]"12entrypoint: search_users.sql13queryArgs:14search: "%{{params.query}}%"
- This task takes in a parameter of
query
, which is what a user might enter. - The
:search
argument is set inqueryArgs
- you can interpolate in values like parameters, and configs here using{{ }}
syntax. (The%
is a SQL syntax for wildcard matching.) query
is optional (required: false
). If no query is set,%{{params.query}}%
evaluates to%%
andsearch
matches all values.
Test your task locally
You can test the task locally before deploying by running
airplane dev
to start the
Studio. The task will execute against
local resources created in the Studio.
Alternatively, run the command with the --env
flag to test your task with
remote resources.bashCopied1$ airplane dev search_users.task.yaml
Deploy your task
You can deploy the task via the CLI:
shellCopied1airplane deploy search_users.task.yaml
Once deployed, find the task in your Library to see your task in
action!

That's it! Try executing a search for
alex
:
Create a SQL task to update
Similarly, let's create a second SQL task to edit a user.
shellCopied1airplane tasks init
Call this task "Update user":
Copied1? What should this task be called?2> Update user3? What kind of task should this be?4> SQL5? Where is the script for this task?6> update_user.sql7? Where should the definition file be created?8> update_user.task.yaml
This time, we'll execute an
update
query:sqlCopied1-- update_user.sql2update users3set name = :name, email = :email4where id = :user_id5returning *
With the following task definition:
yamlCopied1# update_user.task.yaml2slug: update_user3name: Update user4parameters:5- slug: user_id6name: User ID7type: integer8- slug: name9name: Name10type: shorttext11- slug: email12name: Email13type: shorttext14sql:15resource: "[Demo DB]"16entrypoint: update_user.sql17queryArgs:18user_id: "{{params.user_id}}"19name: "{{params.name}}"20email: "{{params.email}}"
Deploy the second task
You can deploy this like the other task:
shellCopied1airplane deploy update_user.task.yaml2# Or, pass in a directory and airplane will discover tasks:3airplane deploy .
Because we're
returning
in the SQL query, we can get back the updated version of the user in our
task output:
Wrapping up
That's it! It took just a few simple commands to deploy your first two tasks to Airplane.