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:
bashCopied1airplane 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:
yamlCopied1sql:2# Where prod_db is the slug of a created resource.3resource: 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:
yamlCopied1parameters:2- name: User ID3slug: user_id4type: shorttext5description: 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}}
.yamlCopied1sql:2queryArgs:3# You can reference this query arg in your SQL script as :id.4id: "{{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:
yamlCopied1sql:2configs: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.yamlCopied1sql:2queryArgs:3# You can reference this query arg in your SQL script as :page_size.4page_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.
yamlCopied1sql:2# Other valid options: readOnly, readWrite, none3transactionMode: 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:
sqlCopied1UPDATE users SET name = :name WHERE id = :id;2SELECT * 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.bashCopied1# Start the Studio and execute delete_user_by_email against prod resources2$ airplane dev delete_user_by_email.task.yaml --env prod
Deploy to Airplane
Finally, run deploy to push your task to Airplane:
bashCopied1airplane 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
sqlCopied1SELECT id, name FROM users where id = :user_id;
Query arguments
user_id
{{params.user_id}}
Run an update, then show the output
sqlCopied1UPDATE users SET name = :name WHERE id = :id RETURNING id, name;
sqlCopied1UPDATE users SET name = :name WHERE id = :id;2-- Updated user record gets displayed in the Airplane UI3SELECT id, name FROM users WHERE id = :id;
Query arguments
id
{{params.id}}
Searching multiple fields in a table
sqlCopied1SELECT * FROM users WHERE2name ILIKE :query3OR email ILIKE :query4OR username ILIKE :query;
sqlCopied1-- MySQL doesn't have ILIKE, so use LOWER for case insensitivity2SELECT * FROM users WHERE3LOWER(name) LIKE LOWER(:query)4OR LOWER(email) LIKE LOWER(:query)5OR LOWER(username) LIKE LOWER(: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.sqlCopied1SELECT * FROM users2{{ 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:
sqlCopied1SELECT * FROM users WHERE name ILIKE :query;2-- Equivalent valid query:3-- SELECT * FROM users WHERE name ILIKE $1;
sqlCopied1SELECT * FROM users WHERE LOWER(name) LIKE LOWER(:query);2-- Equivalent valid query:3-- SELECT * FROM users WHERE LOWER(name) LIKE LOWER(?);
The following is not valid, since parameters can't be embedded inside another string:
sqlCopied1SELECT * FROM users WHERE name ILIKE '%:query%';2-- Equivalent invalid query:3-- SELECT * FROM users WHERE name ILIKE '%$1%';
sqlCopied1SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%:query%');2-- Equivalent invalid query:3-- SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%?%');
Instead, you can use
CONCAT
or, in Postgres only, the shorter ||
syntax.sqlCopied1SELECT * FROM users WHERE name ILIKE '%' || :query || '%';
sqlCopied1SELECT * FROM users WHERE LOWER(name) LIKE CONCAT('%', LOWER(:query), '%');
Or, concatenate the
%
characters in the query arguments:sqlCopied1SELECT * FROM users WHERE name ILIKE :query;
sqlCopied1SELECT * FROM users WHERE LOWER(name) LIKE LOWER(: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:
sqlCopied1SELECT * FROM users WHERE2{{ 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.