# SQL Database API

The **SQL Database API** provides an interface for interacting with an [SQL Database](/en/documentation/products/store/sql-database/) database from Functions running on the Azion Web Platform. This document outlines the key components of the API and provides code samples for each.

import LinkButton from 'azion-webkit/linkbutton'

<LinkButton link="/en/documentation/products/store/sql-database/" label="go to SQL Database reference" severity="secondary" />

---

## Database  

Creates a connection to an SQL 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 SQL Database | `static async open(name)` | `name: string` | `Connection`


---

## Connection

Represents the communication channel with a specific SQL 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> `<br />` - '?' positional parameter `<br />` - :<param_name> `<br />` params:  `<br />`- Array of positional values`<br />` - Dictionary of values`| `-`
Query    | Executes an SQL command | `async query(sql, params)` | `sql: <string> `<br />` - '?' positional parameter `<br />` - :<param_name> `<br />` params:  `<br />`- Array of positional values`<br />` - 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> `<br />` - '?' positional parameter `<br />` - :<param_name> `<br />` params:  `<br />`- Array of positional values`<br />` - 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> `<br />` - '?' positional parameter `<br />` - :<param_name> `<br />` params:  `<br />`- Array of positional values`<br />` - Dictionary of values` |  `-`
Query    | Executes an SQL command | `async query(sql, params)` | `sql: <string> `<br />` - '?' positional parameter `<br />` - :<param_name> `<br />` params:  `<br />`- Array of positional values`<br />` - 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`.

```javascript
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
```