SQL Database API

The Azion Runtime SQL Database API lets a function read an SQL Database database. The function opens the database by name, runs SQL queries with positional or named parameters, prepares statements, and reads the result row by row. Five objects make up the API: Database, Connection, Statement, Rows, and Row.

Under azion dev, Azion.Sql is undefined, and the function fails with Cannot destructure property 'Database' of 'Azion.Sql' as it is undefined. Test database reads on a deployed function.

Access

The Database class is on the Azion.Sql global. Read it at the top of the function:

const { Database } = Azion.Sql;

The module import import { Database } from "azion:sql" does not build: the build stops with Could not resolve "azion:sql". Use the global instead.

Database

Database opens a connection to a database of your account. It takes the database name and no token.

Method	Description	Parameters	Returns
`static async open(name)`	Opens a connection to the read replica of the database.	`name`: string	`Connection`

The connection is read-only. A statement that writes, such as insert or delete, fails with Error: SQLite failure: `attempt to write a readonly database` . To write rows, run the statements through the Azion API. For more information, refer to Databases and queries.

Connection

A Connection is the channel to one database. Database.open() returns it.

Method	Description	Parameters	Returns
`async query(sql, params)`	Runs an SQL statement and returns its result set.	`sql`: string; `params`: array or object	`Rows`
`async execute(sql, params)`	Runs an SQL statement without a result set.	`sql`: string; `params`: array or object	—
`async prepare(sql)`	Prepares an SQL statement for later runs.	`sql`: string	`Statement`

A connection also has the methods close() and tryClose().

Parameters

The sql string takes two kinds of parameter, and params carries their values:

Positional: a ? in the statement, with the values in an array, in order. query("select name from users where id = ?", [2]) returns User2.
Named: a :<param_name> in the statement, with the values in an object. Each key keeps the colon: { ":id": 3 } binds :id and returns User3. The key id without the colon binds nothing, and the query returns no row.

Integer values bind. A JavaScript string passed as a parameter value is refused on query and execute with TypeError: unknown variant `String`, expected one of `Null`, `Integer`, `Real`, `Text`, `Blob` .

Statement

A Statement is an SQL statement prepared once and run with its parameter values. Connection.prepare() returns it.

Method	Description	Parameters	Returns
`async query(params)`	Runs the statement with the values in `params` and returns its result set.	`params`: array or object	`Rows`
`parameterCount()`	Returns the number of parameters in the statement, for example `1` for one `?`.	—	integer
`parameterName(index)`	Returns the name of the parameter at `index`. A `?` parameter has no name, and the method returns `null`.	`index`: integer	string or `null`
`columns()`	Returns one object per column of the result: `name`, `origin_name`, `table_name`, `database_name`, and `decl_type`.	—	array of objects

A statement also has the methods execute(), which runs it without a result set, and tryClose().

Pass the parameter values to the statement’s query(), not to prepare(). Values passed to prepare() are not bound: prepare("select name from users where id = ?", [1]) followed by query() returns no row.

For select name from users where id = ? on a users table, columns() returns:

JSON

01[

02 {

03 "name": "name",

04 "origin_name": "name",

05 "table_name": "users",

06 "database_name": "main",

07 "decl_type": "TEXT"

08 }

09]

Rows

A Rows object is the result set a query returns. Read it one row at a time with next().

Method	Description	Parameters	Returns
`async next()`	Returns the next row of the result, or `null` after the last row.	—	`Row` or `null`
`columnCount()`	Returns the number of columns in the result.	—	integer
`columnName(index)`	Returns the name of the column at `index`.	`index`: integer	string
`columnType(index)`	Returns the type code of the column at `index`: `1` for an `INTEGER` column, `3` for a `TEXT` column.	`index`: integer	integer

Row

A Row holds the values of one row of a result set. Columns are addressed by index, starting at 0.

Method	Description	Parameters	Returns
`columnName(index)`	Returns the name of the column at `index`.	`index`: integer	string
`columnType(index)`	Returns the type code of the column at `index`: `1` for an `INTEGER` column, `3` for a `TEXT` column.	`index`: integer	integer
`getValue(index)`	Returns the value in its own type: a number for an `INTEGER` column, a string for a `TEXT` column.	`index`: integer	number or string
`getString(index)`	Returns the value as a string, for example `"1"` for the integer `1`.	`index`: integer	string

Errors

Database.open(), query(), and execute() throw the errors below. Catch the error and read its name and message.

Error	Cause
`EdgeSqlError: Database not found: <name>, maybe it wasn't propagated to edge yet`	`Database.open()` received a name that matches no database of the account. Check the name.
Error: SQLite failure: `no such table: <table>`	The statement names a table the database does not have.
Error: SQLite failure: `attempt to write a readonly database`	The statement writes. The connection reads a replica; write through the Azion API.
TypeError: unknown variant `String`, expected one of `Null`, `Integer`, `Real`, `Text`, `Blob`	A parameter value is a JavaScript string.

Example

This function reads every row of the users table in my-database and returns the table as text, one row per line, with | between the values:

JavaScript

01const { Database } = Azion.Sql;

03async function db_query() {

04 let connection = await Database.open("my-database");

05 let rows = await connection.query("select * from users");

06 let column_count = rows.columnCount();

07 let column_names = [];

08 for (let i = 0; i < column_count; i++) {

09 column_names.push(rows.columnName(i));

10 }

11 let response_lines = [];

12 response_lines.push(column_names.join("|"));

13 let row = await rows.next();

14 while (row) {

15 let row_items = [];

16 for (let i = 0; i < column_count; i++) {

17 row_items.push(row.getString(i));

18 }

19 response_lines.push(row_items.join("|"));

20 row = await rows.next();

21 }

22 const response_text = response_lines.join("\n");

23 return response_text;

24}

26async function handle_request(request) {

27 if (request.method != "GET") {

28 return new Response("Method not allowed", { status: 405 });

29 }

30 try {

31 return new Response(await db_query());

32 } catch (e) {

33 console.log(e.message, e.stack);

34 return new Response(e.message, { status: 500 });

35 }

36}

38addEventListener("fetch", (event) =>

39 event.respondWith(handle_request(event.request))

40);

With a users table of two columns, id and name, and four rows, a GET request to the deployed function returns:

Text

01id|name

021|User1

032|User2

043|User3

054|User4

Databases and queries

Create a database and write its rows through the Azion API.

SQL Database limits

The bounds on a database, such as name length and columns per table.

Azion SQL library

The @aziontech/sql package, which wraps the Azion API operations on a database for Node and TypeScript.

Handlers

The handler shapes a function exports, and the request each one receives.