How to retrieve data from a database with Edge SQL and Edge Functions

See how to retrieve data from an existing database (DB) with Edge SQL and Edge Functions.

Requirements

Azion Edge Functions enabled.
An active Azion Personal Token.

Creating a database and adding a table

Create a database by executing the following cURL:

curl -X POST https://api.azion.com/v4/edge_sql/databases \
-H "Authorization: Token {{token}}" \
-H "Content-Type: application/json" \
-d '{
    "name": "mydatabase"
}'

Add a table named users:

curl -X POST 'https://api.azion.com/v4/edge_sql/databases/{{db_id}}/query' \
-H 'Authorization: Token {{token}}' \
-H 'Content-Type: application/json' \
-d '{
    "statements": [
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"
    ]
}'

Populate the table with data:

curl -X POST https://api.azion.com/v4/edge_sql/databases/{{db_id}}/query \
-H "Authorization: Token {{token}}" \
-H "Content-Type: application/json" \
-d '{
    "statements": [
        "INSERT INTO users VALUES (1, '\''user 1'\'');",
        "INSERT INTO users VALUES (2, '\''user 2'\'');",
        "INSERT INTO users VALUES (3, '\''user 3'\'');"
    ]
}'

Now, the DB is created and populated with rows.

Creating an edge function to communicate with Edge SQL

Access Azion Console.
On the upper-left corner, select Edge Functions in the Edge Libraries section.
Click the + Edge Function button.
Choose a name for your function.
Delete the placeholder function that is inside the code editor.
Paste the following code:

import { Database } from "azion:sql";

async function db_query() {
  let connection = await Database.open("mydatabase");
  let rows = await connection.query("select * from users");
  let column_count = rows.columnCount();
  let column_names = [];
  for (let i = 0; i < column_count; i++) {
    column_names.push(rows.columnName(i));
  }
  let response_lines = [];
  response_lines.push(column_names.join("|"));
  let row = await rows.next();
  while (row) {
    let row_items = [];
    for (let i = 0; i < column_count; i++) {
      row_items.push(row.getString(i));
    }
    response_lines.push(row_items.join("|"));
    row = await rows.next();
  }
  const response_text = response_lines.join("\n");
  return response_text;
}

async function handle_request(request) {
  if (request.method != "GET") {
    return new Response("Method not allowed", { status: 405 });
  }
  try {
    return new Response(await db_query());
  } catch (e) {
    console.log(e.message, e.stack);
    return new Response(e.message, { status: 500 });
  }
}

addEventListener("fetch", (event) =>
  event.respondWith(handle_request(event.request))
);

This edge function was created to communicate with Edge SQL. Now, it’s necessary to instantiate this function in a running edge application:

Go to How to instantiate edge functions in your application

Learn how to retrieve data effortlessly with Edge Functions. Watch the video below: