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 an Airplane Postgres database.
Before you begin
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
Updating the CLI.)
Create a database
Create a database
To create a database, visit resources for your
team, click the "New" button. You can connect to your own database, or select Airplane Postgres to
have Airplane provision one.
To create a new Airplane Postgres resource, and select the Airplane Postgres resource type.

Create sample data
Create sample data
When you first create an Airplane Postgres resource, the database will be empty. On the
resource settings page, you can run this
query to create some sample data:

SQLCopied1CREATE TABLE users (2id SERIAL PRIMARY KEY,3account_id integer,4email text,5name text,6title text,7role text,8created_at timestamp with time zone DEFAULT now(),9is_suspended boolean DEFAULT false10);1112CREATE TABLE accounts (13id SERIAL PRIMARY KEY,14company_name text,15signup_date timestamp WITH time zone DEFAULT NOW(),16total_dollars integer DEFAULT 0,17country text DEFAULT 'USA' :: text18);1920INSERT INTO users (id, account_id, email, name, title, role, created_at, is_suspended)21VALUES22(0, 0, 'pigeon.pilot@airplane.dev', 'Pigeon Pilot', 'SVP', 'Aviation Expert', '2023-05-05 18:31:18.667265+00', FALSE),23(1, 1, 'carolyn.garcia@futuregolfpartners.com', 'Carolyn Garcia', 'Manager', 'Sales', '2023-05-05 18:31:18.667265+00', FALSE),24(2, 1, 'frances.hernandez@futuregolfpartners.com', 'Frances Hernandez', 'Vice President', 'Astronaut', '2023-05-05 18:31:18.667265+00', FALSE);2526INSERT INTO27accounts (28id,29company_name,30signup_date,31total_dollars,32country33)34VALUES35(0, 'Future Golf Partners', '2020-03-21 04:48:23.532+00', 1655427, 'Brazil'),36(1, 'Amalgamated Star LLC', '2020-07-16 00:40:30.103+00', 43403102, 'Canada'),37(2, 'Blue Sky Corp', '2019-04-18 10:14:24.71+00', 1304097, 'France');
Create a SQL task to search users
Create a SQL task to search users
We'll be developing in Studio, Airplane's development tool for building
tasks and views. For this guide, we recommend using Studio with a local development server in a new,
empty directory.
shellCopied1# mkdir airplane-getting-started2# cd airplane-getting-started3airplane dev
Navigate to Studio in your browser.
To create a new task, click the
New
button in the upper right.
Select
Task
to create a new task.
Choose
SQL
to specify a SQL task. Name your task Search users
. You can add a description to the
task if you like.
Click
Create
to create your task. This will generate two Airplane files for you,
search_users.task.yaml
and search_users.sql
.First, we'll add a parameter to our task, which will enable operators to supply inputs to the task.
Click the
Add parameter
button, at the bottom of the Define
section:
This opens up a modal that allows you to configure your parameter. We'll name it
Query
and make it
optional, but otherwise leave it as a short text parameter without a default.Click
Add
to save the new parameter to your task.
Next, we'll attach a resource to our task. Scroll to the
Build
section of the configuration panel
and select [Demo DB]
from the dropdown.
Next, we'll specify the SQL query for this task. Copy this into the
Query
text input.sqlCopied1select * from users2join accounts on accounts.id = users.account_id3where users.name ILIKE :search4or users.title ILIKE :search5or users.email ILIKE :search6or 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 below.
You can interpolate values like parameters and configs using
{{ }}
syntax.
Without interpolation, all values are literals. Learn more about JS
templates.Test your task locally
Test your task locally
You can test the task locally before deploying by executing your task in Studio. The task will
execute against local resources created in the Studio,
or against the database from your default environment.
Deploy your task
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
Create a SQL task to update
Similarly, let's create a second SQL task to edit a user. We'll call this task "Update user".

Add three parameters:
User ID
(required integer), Name
(required short text), and Email
(required short text).
Attach the
[Demo DB]
to this task, too. This time, we'll execute an update
query:sqlCopied1update users2set name = :name, email = :email3where id = :user_id4returning *
And the query arguments will just be the parameter values:

Deploy the second task
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
Wrapping up
That's it! It took just a few simple commands to deploy your first two tasks to Airplane.