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.



Creating a database and adding a table

Section titled 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

Section titled 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