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 an (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 {{slug}}.
Parameters show the slug underneath. For example, this parameter "User Email" has a slug "user_email":
You'll be able to use the slug in a query in the next step: SELECT name FROM users WHERE email = {{user_email}}.
You should not put quotes around {{user_email}} - the interpolation process will automatically format the data correctly for you.
This is wrong: SELECT name FROM users WHERE email = '{{user_email}}'

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.

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 / Examples

Simple reads

1
SELECT id, name FROM users where id = {{user_id}};
Copied!

Run an update, then show the output

You can run multiple SQL queries—they are executed in the same transaction, making it useful to report back at the end on the final copy of the data:
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}};
Copied!

Searching multiple fields in a table

Postgres
MySQL
1
SELECT * FROM users WHERE
2
name ILIKE '%' || {{query}} || '%'
3
OR email ILIKE '%' || {{query}} || '%'
4
OR username ILIKE '%' || {{query}} || '%';
Copied!
1
-- MySQL does not have ILIKE, so use LOWER to make case-insensiive
2
SELECT * FROM users WHERE
3
LOWER(name) LIKE CONCAT('%', LOWER({{query}}), '%')
4
OR LOWER(email) LIKE CONCAT('%', LOWER({{query}}), '%')
5
OR LOWER(email) LIKE CONCAT('%', LOWER({{query}}), '%');
Copied!

Working with optional parameters

Optional strings can be used, in which case the parameter will be ''—you simply need to handle that case in your query:
1
SELECT * FROM users WHERE
2
-- Default to all users if no team is specified
3
({{team}} = '')
4
-- Otherwise filter by team
5
OR (users.team = {{team}});
Copied!

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 escape for you, but there are limitations on where you can use interpolation.
The following query is valid:
1
SELECT * FROM users WHERE name ILIKE {{query}};
2
-- Equivalent valid query in MySQL:
3
-- SELECT * FROM users WHERE name ILIKE ?;
4
-- Equivalent valid query in Postgres:
5
-- SELECT * FROM users WHERE name ILIKE $1;
Copied!
The following is not valid–parameters can't be embedded inside another string, since prepared statements do not support it:
1
SELECT * FROM users WHERE name ILIKE '%{{query}}%';
2
-- Equivalent invalid query in MySQL:
3
SELECT * FROM users WHERE name ILIKE '%?%';
4
-- Equivalent invalid query in Postgres:
5
SELECT * FROM users WHERE name ILIKE '%$1%';
Copied!
Instead, you can use CONCAT or, in Postgres only, the shorter || syntax.
1
-- MySQL
2
SELECT * FROM users WHERE name ILIKE CONCAT('%', {{query}}, '%');
3
-- Postgres
4
SELECT * FROM users WHERE name ILIKE '%' || {{query}} || '%';
Copied!
Interpolation cannot be used for dynamic table / column names:
1
-- INVALID
2
SELECT * FROM {{table_name}};
Copied!

Transactionality

All queries in a single task are run in a single transaction. The transaction is committed only if all queries successfully execute.
Last modified 1mo ago