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.

In the Modernize a monolithic application without a rewrite use case, this section takes these values:

SettingValue
Databasestores-db

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

Return rows as JSON

A function that answers an API route returns the rows as a JSON array instead of text. The example answers GET /api/stores with the rows of a stores table in a database named stores-db, created as Create the database and the table shows, on the domain www.example.com. The function reads the database through the Azion.Sql global, which takes the database name and no token.

To create the stores table and load its rows, replace <database-id> with the id of stores-db:

Shell
01curl --request POST \
02 --url https://api.azion.com/v4/workspace/sql/databases/<database-id>/query \
03 --header 'Accept: application/json' \
04 --header 'Authorization: Token [TOKEN VALUE]' \
05 --header 'Content-Type: application/json' \
06 --data {
07 "statements": [
08 "CREATE TABLE IF NOT EXISTS stores (id INTEGER, name TEXT, city TEXT);",
09 "INSERT INTO stores (id, name, city) VALUES (1, '\''Downtown'\'', '\''Sao Paulo'\''), (2, '\''Harbor'\'', '\''Rio de Janeiro'\'');"
10 ]
11}

The API answers 200 with one entry per statement. Read data[].error on each entry: a failed statement also returns 200, with error in place of results.

Create a function named stores-route with this code, and instantiate it on the application as stores-route. For the steps in each interface, refer to Functions quickstart, which creates a function and instantiates it on an application.

JavaScript
01export default {
02 async fetch(request, env, ctx) {
03 const { Database } = Azion.Sql;
04 try {
05 const connection = await Database.open('stores-db');
06 const rows = await connection.query('select id, name, city from stores order by id', []);
07 const stores = [];
08 let row = await rows.next();
09 while (row) {
10 stores.push({ id: row.getValue(0), name: row.getValue(1), city: row.getValue(2) });
11 row = await rows.next();
12 }
13 return new Response(JSON.stringify(stores), {
14 headers: { 'Content-Type': 'application/json' },
15 });
16 } catch (error) {
17 // A failed open or query answers 503, so the client can retry and the error is visible per route.
18 return new Response(JSON.stringify({ error: error.message }), {
19 status: 503,
20 headers: { 'Content-Type': 'application/json' },
21 });
22 }
23 },
24};

The function reads every row in order and answers with a JSON array. A failure to open or query the database answers 503, so a fault in the route shows up as a 5xx on that path alone.

To confirm, run the instance on GET /api/stores, as Run a function on one path, and roll it back shows, and request the route:

curl -s https://www.example.com/api/stores

The body holds the rows of the stores table:

[{"id":1,"name":"Downtown","city":"Sao Paulo"},{"id":2,"name":"Harbor","city":"Rio de Janeiro"}]

The Modernize a monolithic application without a rewrite use case uses the values of this example.

These checks confirm the Modernize a monolithic application without a rewrite use case.

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.Modernize a monolithic application without a rewriteMove one route of a monolith at a time to a function, and roll each one back.