# SQL Database API

The Azion Runtime SQL Database API lets a function read an [SQL Database](/en/documentation/platform/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`.

> **Note**
>
> 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:

```javascript
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](/en/documentation/platform/sql-database/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
[
 {
  "name": "name",
  "origin_name": "name",
  "table_name": "users",
  "database_name": "main",
  "decl_type": "TEXT"
 }
]
```

---

## 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
const { Database } = Azion.Sql;

async function db_query() {
  let connection = await Database.open("my-database");
  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))
);
```

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

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

---

## Related resources

- [Databases and queries](/en/documentation/platform/sql-database/databases-and-queries.md): Create a database and write its rows through the Azion API.
- [SQL Database limits](/en/documentation/platform/sql-database/limits.md): The bounds on a database, such as name length and columns per table.
- [Azion SQL library](/en/documentation/devtools/azion-lib/sql.md): The `@aziontech/sql` package, which wraps the Azion API operations on a database for Node and TypeScript.
- [Handlers](/en/documentation/devtools/runtime/api-reference/handlers.md): The handler shapes a function exports, and the request each one receives.