SQL

Airplane makes it easy to write SQL-based tasks. With SQL tasks, the only code you have to write is the SQL query—no installation, deployment, or other configuration required!

Getting started
This guide will get you up and running with an example SQL task.
We'll use the Airplane CLI airplane to demonstrate how to get started with a SQL task.
If you haven't yet, first install the Airplane CLI by following the instructions at Installing the CLI.

Create your task
Once you've installed and logged in to the CLI, navigate to the folder where you want to create your task and run:
bash
Copied
1
airplane tasks init
You'll be prompted for some information:
  • Enter a name for the task.
  • Select SQL as the kind.
  • Enter a name for the script and the definition file. The script contains the SQL query (e.g. delete_user_by_email.sql) while the definition file contains task metadata (e.g. delete_user_by_email.task.yaml).

Resource
The database resource configures the connection to your database. If you don't have an existing resource, visit the Resources settings page to add a new resource.
Open up the definition file (the file with extension .task.yaml) to configure the resource for your task.
Example:
yaml
Copied
1
sql:
2
  # Where prod_db is the slug of a created resource.
3
  resource: prod_db

Parameters and query args
Parameters allow you to prompt users for input before triggering your task. See Parameters for more information about Airplane parameters.
Open up the definition file (the file with extension .task.yaml) to add parameters to the task.
Example:
yaml
Copied
1
parameters:
2
  - name: User ID
3
    slug: user_id
4
    type: shorttext
5
    description: The ID of the user
Parameters can be passed into your SQL script using query args.
Parameters can be used as the value of a query arg as {{params.slug}}.
yaml
Copied
1
sql:
2
  queryArgs:
3
    # You can reference this query arg in your SQL script as :id.
4
    id: "{{params.user_id}}"
Query args support JS templates.

Config variables
Config variables are team-wide values that can be shared across multiple tasks. See Config variables for more information on config variables.
Open up the definition file (the file with extension .task.yaml) to attach configs to the task.
Example:
yaml
Copied
1
sql:
2
  configs:
3
    - DEFAULT_SQL_PAGE_SIZE
Configs can then be used as the value of a query arg as {{configs.name}}, in a similar manner to parameters.
yaml
Copied
1
sql:
2
  queryArgs:
3
    # You can reference this query arg in your SQL script as :page_size.
4
    page_size: "{{configs.DEFAULT_SQL_PAGE_SIZE}}"

Transaction mode
You can also configure what kind of transaction, if any, to use for this task. By default you can let Airplane decide, or you can explicitly choose a transaction mode. If a transaction is used, it is committed only if all queries successfully execute. The effective transaction mode is indicated in the task logs.
yaml
Copied
1
sql:
2
  # Other valid options: readOnly, readWrite, none
3
  transactionMode: auto

Develop your script
Now that you have created a task, you'll write the actual SQL query in the SQL script.
Open up the script (the file with extension .sql). The script contains one or more SQL queries that are executed when your task is run.
Queries can be both read queries and write queries. Output from read queries will be displayed as a table in the Airplane UI.
You can reference query args in your SQL script as :queryArgName.
Example:
sql
Copied
1
UPDATE users SET name = :name WHERE id = :id;
2
SELECT * from users WHERE id = :id;
For security purposes (to defend against SQL injection attacks), you should not use parameters directly in the query itself. Use query arguments instead to pass parameter values to the query. See the examples below.

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
# Start the Studio and execute delete_user_by_email against prod resources
2
$ airplane dev delete_user_by_email.task.yaml --env prod

Deploy to Airplane
Finally, run deploy to push your task to Airplane:
bash
Copied
1
airplane deploy delete_user_by_email.task.yaml
Once deployed, go to the Tasks page to run and share your task!

Common queries and examples

Simple reads

 
sql
Copied
1
SELECT id, name FROM users where id = :user_id;
Query arguments
user_id
{{params.user_id}}

Run an update, then show the output

 
sql
Copied
1
UPDATE users SET name = :name WHERE id = :id RETURNING id, name;
Query arguments
id
{{params.id}}

Searching multiple fields in a table

 
sql
Copied
1
SELECT * FROM users WHERE
2
  name ILIKE :query
3
  OR email ILIKE :query
4
  OR username ILIKE :query;
Query arguments
query
{{params.query}}

Working with optional parameters
Optional strings can be used, in which case the parameter will be undefined—you simply need to handle that case in your query.
sql
Copied
1
SELECT * FROM users
2
  {{ params.team ? "WHERE users.team = :team" : "" }}
Query arguments
team
{{params.team ?? ""}}

Interpolation limitations
For security purposes (to defend against SQL injection attacks), parameter interpolation syntax uses prepared statements under the hood. In MySQL, this is like using ? for variables, and in Postgres this is similar to using $1 etc. User input is automatically escaped for you, but there are limitations on where you can use interpolation.
The following query is valid:
sql
Copied
1
SELECT * FROM users WHERE name ILIKE :query;
2
-- Equivalent valid query:
3
-- SELECT * FROM users WHERE name ILIKE $1;
The following is not valid, since parameters can't be embedded inside another string:
sql
Copied
1
SELECT * FROM users WHERE name ILIKE '%:query%';
2
-- Equivalent invalid query:
3
-- SELECT * FROM users WHERE name ILIKE '%$1%';
Instead, you can use CONCAT or, in Postgres only, the shorter || syntax.
sql
Copied
1
SELECT * FROM users WHERE name ILIKE '%' || :query || '%';
Or, concatenate the % characters in the query arguments:
sql
Copied
1
SELECT * FROM users WHERE name ILIKE :query;
Query arguments
query
%{{params.query}}%

Dynamic queries
You can also use templating to conditionally generate your query string. For example, if you want to conditionally search on one of two fields (e.g., email and name) you could do the following:
sql
Copied
1
SELECT * FROM users WHERE
2
  {{ params.email ? "email ILIKE :email" : "name ILIKE :name }}
Query arguments
name
%{{params.name ?? ""}}%
email
%{{params.email ?? ""}}%
Keep in mind, it's best practice to not reflect user-provided inputs into your SQL queries or else you are at risk of SQL injection. For that, always use a SQL parameter.
Next: Task configuration
PrivacyTermsSupport