SQL

Perform SQL queries using the SQL built-in.
Airplane's SQL SDK makes it easy to issue one-off SQL queries from a task. To use this SDK, you'll need to configure a SQL resource:

Query

Execute a SQL query against a SQL resource.
typescript
Copied
1
export default airplane.task(
2
{
3
slug: "find_user_by_name",
4
// Attach SQL resource to task
5
resources: ["my_sql_db"],
6
parameters: { name: "shorttext" },
7
},
8
async (params) => {
9
const run = await airplane.sql.query<{ id: string; name: string; email: string }>(
10
// The slug of the SQL resource to query
11
"my_sql_db",
12
// SQL query to execute
13
"select * from users where name ilike :name",
14
// Query arguments
15
{ args: { name: `%${params.name}%` } },
16
);
17
return run.output.Q1;
18
},
19
);
To protect against SQL injection, it is heavily encouraged that all user-provided input is passed as a query argument. Each query argument has a unique identifier that can be referenced from the query by prepending the identifier with a semicolon. For example, the following query has a query arg identified by name:
sql
Copied
1
-- Query
2
select * from users
3
where name ilike :name;
4
-- Query Args
5
{ "name": "%colin%" }
airplane.sql.query(sqlResource, query, opts)
sqlResource
REQUIRED
string

Slug of SQL resource to use. See Resources.

query
REQUIRED
string

The SQL query to execute. Can include multiple queries separated by semicolons which will be executed within a single transaction.

opts.args
Default
{}
object

Values that can be safely referenced from a query.

opts.transactionMode
Default
auto
"auto" | "readOnly" | "readWrite" | "none"

By default, Airplane will parse the query and choose the most relevant kind of transaction. If readOnly, write operations will fail. If none, queries will be applied one-at-a-time without a transaction.

Output

If query returns rows (e.g. a SELECT or INSERT... RETURNING statement) then sql.query will return:
Q1
object[]

List of rows returned. Each row is an object mapping column headers to column values.

Otherwise, the following will be returned:
Q1.rows_affected
number

The number of rows that inserted, updated, or deleted by the query.

If query includes multiple SQL statements, the response will include a response for each query. Each query's response will be identified by a field with its query index (Q1, Q2, Q3, ...).