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:

BigQuery

SQL Server

MongoDB

MySQL

Airplane Postgres

Postgres

Redshift

Snowflake

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:

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, ...).

python
Copied
1
import airplane
2

3
@airplane.task(
4
    resources=[
5
        # Attach SQL resource to task
6
        airplane.Resource("my_sql_db"),
7
    ]
8
)
9
def find_user_by_name(name: str):
10
    run = airplane.sql.query(
11
        # The slug of the SQL resource to query
12
        sql_resource="my_sql_db",
13
        # SQL query to execute
14
        query="select * from users where name ilike :name",
15
        # Query arguments
16
        query_args={"name": f"%{name}%"},
17
    )
18
    return run.output["Q1"]

sql
Copied
1
-- Query
2
select * from users
3
where name ilike :name;
4
-- Query Args
5
{ "name": "%colin%" }

airplane.sql.query(sql_resource, query, query_args, transaction_mode, dedent)

sql_resource

REQUIRED

str

Slug of SQL resource to use. See Resources.

query

REQUIRED

str

The query to run on the SQL resource. Multiple queries can be separated by semicolons.

query_args

optional

Default

None

Optional[Dict[str, Any]]

Optional map of query arg names to values to insert into the query.

transaction_mode

optional

Default

TransactionMode.Auto

airplane.sql.TransactionMode

Optional transaction mode with which to run the query. It has the following options: AUTO, READ_ONLY, READ_WRITE, NONE.

dedent

optional

Default

True

bool

Whether or not to omit leading whitespace from query.

Output

The id, task id, param values, status and outputs of the executed run.

The outputs of the run will be a map of query index to query result. For example, if you execute two queries:

python
Copied
1
result = sql.query(
2
  "my_db",
3
  '''
4
    SELECT email FROM users;
5
    SELECT name FROM teams;
6
  ''',
7
)

result.output will be a dictionary with keys Q1 and Q2.
result.output["Q1"] will contain a dictionary of {"email": ...} from the SELECT email FROM users; query.
result.output["Q2"] will contain a dictionary of {"name": ...} from the SELECT name FROM teams; query.

In the case that your query is a DDL query without a RETURNING clause, the output will be a dictionary with key rows_affected containing the number of rows affected by the query.

Raises

HTTPError

If the prompt cannot be created properly.

RunTerminationException

If the run fails or is cancelled.