Retrieve Data with SQL Database & Functions

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

View as Markdown

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

go to SQL Database reference

Requirements

Creating a database and adding a table

Replace {{token}} with your personal token and {{db_id}} with the ID of the database.

  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"
}'

When the database is created, keep its ID. This information is required in the next steps.

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

The creation of a database is an async task. It might take a few minutes for the DB to receive and perform actions.

  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 a function to communicate with SQL Database

  1. Access Azion Console.
  2. On the upper-left corner, select Functions in the Edge Libraries section.
  3. Click the + 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))
);

Since this example is for retrieving data, the method was set to be only GET. You can implement the business logic necessary to meet your needs.

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

go to How to instantiate functions in your application