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.

Go to Edge SQL reference

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

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


Contributors