Edge SQL API

The Edge SQL API provides an interface for interacting with an EdgeSQL database from Edge Functions running on the Azion Edge Platform. This document outlines the key components of the API and provides code samples for each.

Database

Creates a connection to an EdgeSQL database for your application. Returns a Connection type object.

Methods

Method	Description	Signature	Parameters	Return
Open	Opens a connection to the read replica of an EdgeSQL database	`static async open(name)`	`name: string`	`Connection`

Connection

Represents the communication channel with a specific EdgeSQL database. This object can be obtained by opening a database.

Methods

Method	Description	Signature	Parameters	Return
Execute	Executes an SQL command	`async execute(sql,params)`	`sql: <string>` `- '?' positional parameter` `- :<param_name>` `params:` `- Array of positional values` `- Dictionary of values`	`-`
Query	Executes an SQL command	`async query(sql, params)`	`sql: <string>` `- '?' positional parameter` `- :<param_name>` `params:` `- Array of positional values` `- Dictionary of values`	`Rows`
Prepare	Prepares an SQL command for execution from the combination of the command and parameters	`async prepare(sql,params)`	`sql: <string>` `- '?' positional parameter` `- :<param_name>` `params:` `- Array of positional values` `- Dictionary of values`	`Statement`

Statement

An abstraction of an SQL command, the Statement object allows for the execution of the represented command. It can be created from a Connection.prepare command.

Methods

Method	Description	Signature	Parameters	Return
Execute	Executes an SQL command	`async execute(sql, params)`	`sql: <string>` `- '?' positional parameter` `- :<param_name>` `params:` `- Array of positional values` `- Dictionary of values`	`-`
Query	Executes an SQL command	`async query(sql, params)`	`sql: <string>` `- '?' positional parameter` `- :<param_name>` `params:` `- Array of positional values` `- Dictionary of values`	`Rows`

Attributes

Attribute	Description	Signature	Parameters	Return
`parameterCount`	Returns the number of parameters related of statement	`parameterCount()`	`-`	`int32`
`parameterName`	Returns the name of parameter	`parameterName(index)`	`index: int32`	`String`
`columns`	Returns the list of columns related of the statement	`columns()`	`-`	`Object`

Rows

Represents the result set returned by an SQL query.

Methods

Method	Description	Signature	Parameters	Return
next	Returns the next line of the query response	`async next()`	`-`	`<Row>` or `null`

Attributes

Attribute	Description	Signature	Parameters	Return
`columnCount`	Returns the number of columns from the query result	`columnCount()`	`-`	`int32`
`columnName`	Returns the column’s name	`parameterName(index)`	`index: int32`	`String`
`columnType`	Returns the column’s type	`columnType(index)`	`index: int32`	`int32`

Row

Represents the set of attributes and values in a row of a result set.

Attributes

Attribute	Description	Signature	Parameters	Return
`columnName`	Returns the column’s name	`columnName(index)`	`index: int32`	`String`
`columnType`	Returns the column’s type	`ColumnType(index)`	`index: int32`	`int32`
`getValue`	Returns the attribute value	`getValue(index)`	`index: int32`	`Value`
`getString`	Returns the attribute value as string	`getString(index)`	`index: int32`	`String`

Code sample

The code sample shown below presents a way to interact with a database and retrieve data from a table. This example uses a db called mydatabase and the table users.

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))
);

Considering the database and table both exist, the output would be:

id|name
1|User1
2|User2
3|User3
4|User4