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

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

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

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:

SQL
Copied
1
CREATE TABLE users (
2
    id SERIAL PRIMARY KEY,
3
    account_id integer,
4
    email text,
5
    name text,
6
    title text,
7
    role text,
8
    created_at timestamp with time zone DEFAULT now(),
9
    is_suspended boolean DEFAULT false
10
);
11

12
CREATE TABLE accounts (
13
  id SERIAL PRIMARY KEY,
14
  company_name text,
15
  signup_date timestamp WITH time zone DEFAULT NOW(),
16
  total_dollars integer DEFAULT 0,
17
  country text DEFAULT 'USA' :: text
18
);
19

20
INSERT INTO users (id, account_id, email, name, title, role, created_at, is_suspended)
21
VALUES
22
(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);
25

26
INSERT INTO
27
  accounts (
28
    id,
29
    company_name,
30
    signup_date,
31
    total_dollars,
32
    country
33
  )
34
 VALUES
35
 (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

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.

shell
Copied
1
# mkdir airplane-getting-started
2
# cd airplane-getting-started
3
airplane 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.

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

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

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. 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:

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

And the query arguments will just be the parameter values:

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.