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:

shell
Copied
1
airplane demo create-db

This will create a DB with tables including users and accounts:

users

id integer
name text
title text
email text
account_id integer

accounts

id integer
company_name text
country 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.

shell
Copied
1
# mkdir airplane-getting-started
2
# cd airplane-getting-started
3
airplane tasks init

You'll be prompted for details. Make sure to choose SQL for the kind of task.


Copied
1
? What should this task be called?
2
> Search users
3
? What kind of task should this be?
4
> SQL
5
? Where is the script for this task?
6
> search_users.sql
7
? Where should the definition file be created?
8
> search_users.task.yaml

You can edit search_users.sql to specify a SQL query:

sql
Copied
1
-- search_users.sql
2
select * from users
3
join accounts on accounts.id = users.account_id
4
where users.name ILIKE :search
5
  or users.title ILIKE :search
6
  or users.email ILIKE :search
7
  or 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:

yaml
Copied
1
# search_users.task.yaml
2
# Task slug uniquely identifies the task and is used in places like views
3
slug: search_users
4
name: Search users
5
parameters:
6
  - name: Query
7
    slug: query
8
    type: shorttext
9
    required: false
10
sql:
11
  resource: "[Demo DB]"
12
  entrypoint: search_users.sql
13
  queryArgs:
14
    search: "%{{params.query}}%"

This task takes in a parameter of query, which is what a user might enter.
The :search argument is set in queryArgs - 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 %% and search 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.

bash
Copied
1
$ airplane dev search_users.task.yaml

Deploy your task

You can deploy the task via the CLI:

shell
Copied
1
airplane 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.

shell
Copied
1
airplane tasks init

Call this task "Update user":


Copied
1
? What should this task be called?
2
> Update user
3
? What kind of task should this be?
4
> SQL
5
? Where is the script for this task?
6
> update_user.sql
7
? Where should the definition file be created?
8
> update_user.task.yaml

This time, we'll execute an update query:

sql
Copied
1
-- update_user.sql
2
update users
3
set name = :name, email = :email
4
where id = :user_id
5
returning *

With the following task definition:

yaml
Copied
1
# update_user.task.yaml
2
slug: update_user
3
name: Update user
4
parameters:
5
  - slug: user_id
6
    name: User ID
7
    type: integer
8
  - slug: name
9
    name: Name
10
    type: shorttext
11
  - slug: email
12
    name: Email
13
    type: shorttext
14
sql:
15
  resource: "[Demo DB]"
16
  entrypoint: update_user.sql
17
  queryArgs:
18
    user_id: "{{params.user_id}}"
19
    name: "{{params.name}}"
20
    email: "{{params.email}}"

Deploy the second task

You can deploy this like the other task:

shell
Copied
1
airplane deploy update_user.task.yaml
2
# Or, pass in a directory and airplane will discover tasks:
3
airplane 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.