Query a database from a function

Read rows from SQL Database inside a function with the azion:sql runtime module, and return them in the response the function sends.

Last updated

View as Markdown Agent setup

You read rows from a database in SQL Database inside a function, during the request the function handles. The azion:sql runtime module opens the connection and runs the SQL.

This page carries one worked function. For every method the module exposes, refer to SQL Database API.

Prerequisites

  • An application to instantiate the function on. Refer to Instantiate a function on an application.
  • A personal token to authorize the API requests.
  • The Edit SQL Database permission, which grants permission to create and edit databases and their data through the Azion API. Refer to Teams Permissions.
  • SQL Database enabled on your account. The product is in Preview and is not enabled by default, so request access through Technical Support.

Create the database and the table

The function reads a table that already exists, so the two calls below are the shortest path to one. To create the database and fill it with rows:

Create the database
Shell
01curl --location --request POST 'https://api.azion.com/v4/workspace/sql/databases' \
02--header 'Accept: application/json' \
03--header 'Content-Type: application/json' \
04--header 'Authorization: Token [TOKEN VALUE]' \
05--data '{"name":"mydatabase"}'

The API answers with HTTP 202 and returns the identifier the next call takes in its path:

JSON
01{
02 "state": "pending",
03 "data": {
04 "id": 1234,
05 "name": "mydatabase",
06 "status": "creating",
07 "active": true,
08 "last_modified": "2026-01-01T12:00:00.000000Z",
09 "last_editor": "user@example.com",
10 "product_version": "1.0"
11 }
12}

Provisioning takes roughly 15 seconds. Send GET /databases/{database_id} until status reads created.

Create the table and insert the rows
Shell
01curl --location --request POST 'https://api.azion.com/v4/workspace/sql/databases/<database-id>/query' \
02--header 'Accept: application/json' \
03--header 'Content-Type: application/json' \
04--header 'Authorization: Token [TOKEN VALUE]' \
05--data '{"statements":["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);","INSERT INTO users VALUES (1, '\''user 1'\'');","INSERT INTO users VALUES (2, '\''user 2'\'');","INSERT INTO users VALUES (3, '\''user 3'\'');"]}'

The API answers with HTTP 200 and "state": "executed", and data carries one entry per statement, in the order you sent them.

The database mydatabase holds a users table with three rows. The name you set here is the name the function passes to Database.open.

A statement that fails does not fail the request. The call still answers HTTP 200, and the entry for that statement carries error in place of results, so read data[].error before you treat the call as done.

Write the function

Database.open opens a connection to the read replica of the database, so a function reads the data and does not write to it. The handler below answers a GET request with the contents of users:

JavaScript
01import { Database } from "azion:sql";
02
03async function db_query() {
04 let connection = await Database.open("mydatabase");
05 let rows = await connection.query("select * from users");
06 let column_count = rows.columnCount();
07 let column_names = [];
08 for (let i = 0; i < column_count; i++) {
09 column_names.push(rows.columnName(i));
10 }
11 let response_lines = [];
12 response_lines.push(column_names.join("|"));
13 let row = await rows.next();
14 while (row) {
15 let row_items = [];
16 for (let i = 0; i < column_count; i++) {
17 row_items.push(row.getString(i));
18 }
19 response_lines.push(row_items.join("|"));
20 row = await rows.next();
21 }
22 const response_text = response_lines.join("\n");
23 return response_text;
24}
25
26async function handle_request(request) {
27 if (request.method != "GET") {
28 return new Response("Method not allowed", { status: 405 });
29 }
30 try {
31 return new Response(await db_query());
32 } catch (e) {
33 console.log(e.message, e.stack);
34 return new Response(e.message, { status: 500 });
35 }
36}
37
38addEventListener("fetch", (event) =>
39 event.respondWith(handle_request(event.request))
40);

connection.query returns a rows handle. rows.columnCount() and rows.columnName(i) name the columns, rows.next() advances one row and returns a falsy value once the result set is exhausted, and row.getString(i) reads a cell by its position. The sample joins the cells of a row with | and the rows with a line break, then sends the text as the response body. A failure inside the module is logged with e.message and e.stack and returned as HTTP 500.

The sample reads data, so it answers GET and rejects every other method with 405. Change the guard to the methods your own logic serves.

Instantiate and run the function

A function runs only after it is bound to an application and a rule on that application selects it. To run the function and read the rows:

Create the function in Azion Console

Access Azion Console and create a function that carries the code above. Refer to Functions.

Instantiate the function on an application

Bind the function to the application that serves your domain, then add the rule that runs the instance. Refer to Instantiate a function on an application.

Request the application
curl 'https://<your-application-domain>/'

The response carries the column names on the first line and one line per row of users:

Text
01id|name
021|user 1
032|user 2
043|user 3

Next steps

SQL Database APIEvery method the azion:sql module exposes to a function, with its signature.Create tables and query dataDefine the tables the function reads, insert rows, and read them back with SQL.Instantiate a function on an applicationBind the function to an application, name the instance, and pass its Args in JSON.Databases and queriesEvery field, operation, envelope, and error code of the SQL Database endpoints.