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

Creating a database and adding a table

  1. 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"
}'
  1. 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);"
    ]
}'
  1. 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

  1. Access Azion Console.
  2. On the upper-left corner, select Edge Functions in the Edge Libraries section.
  3. Click the + Edge Function button.
  4. Choose a name for your function.
  5. Delete the placeholder function that is inside the code editor.
  6. 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

Contributors