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

Visit the New Task page to get started.

Define your task

In this first step, you'll be asked to give your task a name and (optional) description.

Parameters

Like other tasks, SQL tasks support parameters. Parameters have slugs (a human-readable ID) and can be interpolated into queries by entering {{params.slug}}.
Parameters show the slug next to the name. For example, this parameter "User Email" has a slug "user_email":
You'll be able to use the slug in the query in the next step.

Build your task

In this step, you'll write the actual SQL query.
First select the database resource you'd like to query. If you don't have any configured, you'll see a link that takes you to create a new resource.
Then, enter one or more SQL queries. Queries can be both read queries and write queries. Output from read queries will be displayed as a table in the Airplane UI. All queries are performed in the same transaction.
For security purposes (to defend against SQL injection attacks), you should not use parameters directly in the query itself. Use SQL parameters instead to pass parameter values to the query. See the examples below.

Configure settings

In this last step, you can optionally configure permissions, timeouts, and run constraints. These settings are optional.
That's it! Finish the task creation wizard and you have a shrinkwrapped task for safely running SQL queries.

Common queries and examples

Simple reads

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

Run an update, then show the output

You can run multiple SQL queries in a single task; they are executed in the same transaction, making it useful to report back at the end on the final copy of the data.
sql
Copied
1
UPDATE users SET name = :name WHERE id = :id;
2
-- Updated user record gets displayed in the Airplane UI
3
SELECT id, name FROM users WHERE id = :id;
SQL parameters
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;
SQL parameters
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" : "" }}
SQL parameters
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 SQL parameters:
sql
Copied
1
SELECT * FROM users WHERE name ILIKE :query;
SQL parameters
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 }}
SQL parameters
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.

Transactionality

All queries in a single task are run in a single transaction. The transaction is committed only if all queries successfully execute.