# Azion SQL library

import LinkButton from 'azion-webkit/linkbutton'

The **SQL Database** library provides methods to interact with the **SQL Database** API, allowing you to create, delete, and query databases. This client is configurable and supports both debug mode and environment variable-based configuration.

<LinkButton link="/en/documentation/products/azion-lib/overview/" label="Go to Azion Libraries Overview" severity="secondary" />

You can interact with the API using a `client` or calling the methods directly from the library. When making direct calls, you can use the environment variables to configure the `client` without passing the token and debug parameters directly.

This is an example of how a `.env` file with your environment variables may look like:

```sh
AZION_TOKEN=<your-api-token>
AZION_DEBUG=true
```

| Variable | Description |
|----------|-------------|
| `AZION_TOKEN` | Your Azion API token. |
| `AZION_DEBUG` | Enable debug mode (true/false). |

:::tip
Setting `AZION_DEBUG` to `true` enables **debug mode**. This mode provides detailed logging of the API requests and responses.
:::

If you want to create a specific `client` for interacting with **SQL**, do it by calling the `createClient` method from the library:

```typescript
import { createClient } from 'azion/purge';
import type { AzionSQLClient, AzionSQLResponse, AzionSQL } from 'azion/purge';

const client: AzionSQLClient = createClient({ token: 'your-api-token', options: { debug: true } });

const { data, error } = await client.createDatabase('my-new-db');
if (data) {
  console.log(`Database created with ID: ${data.id}`);
} else {
  console.error('Failed to create database', error);
}
```

The `createClient` method has the following **parameters** and **return value**:

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `config` | `Partial<{ token: string; options?: OptionsParams }>` | Configuration options for the SQL client. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `AzionSQLClient` | An object with methods to interact with SQL. |

:::note
In the following examples, the methods are called directly without the creation of a `client`. For more information on how to interact with services and products using a `client`, check the [Azion Lib Client documentation](/en/documentation/products/azion-lib/client/).
:::

---

## Usage

### `createDatabase`

Creates a new database.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | `string` | Name of the new database. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the creation. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabase>>` | The created database object or error. |

**Example**:

```typescript
import { createDatabase, AzionDatabase } from 'azion/sql';
import type { AzionDatabaseResponse, AzionDatabase } from 'azion/sql';

const { data, error }: AzionDatabaseResponse<AzionDatabase> = await createDatabase('my-new-database', { debug: true });
if (data) {
  const database: AzionDatabase = data;
  console.log(`Database created with ID: ${database.id}`);
} else {
  console.error('Failed to create database', error);
}
```

### `deleteDatabase`

Deletes a database by its ID.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `id` | `number` | ID of the database to delete. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the deletion. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabaseDeleteResponse>>` | Object confirming deletion or error. |

**Example**:

```typescript
import { deleteDatabase } from 'azion/sql';
import type { AzionDatabaseResponse, AzionDatabaseDeleteResponse } from 'azion/sql';

const { data, error }: AzionDatabaseResponse<AzionDatabaseDeleteResponse> = await deleteDatabase(123, { debug: true });
if (data) {
  console.log(`Database ${data.id} deleted successfully`);
} else {
  console.error('Failed to delete database', error);
}
```

### `getDatabase`

Retrieves a database by its name.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | `string` | Name of the database to retrieve. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the retrieval. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabase>>` | The retrieved database object or error. |

**Example**:

```typescript
import { getDatabase } from 'azion/sql';
import type { AzionDatabaseResponse, AzionDatabase } from 'azion/sql';

const { data, error }: AzionDatabaseResponse<AzionDatabase> = await getDatabase('my-db', { debug: true });
if (data) {
  const database: AzionDatabase = data;
  console.log(`Retrieved database: ${database.id}`);
} else {
  console.error('Database not found', error);
}
```

### `getDatabases`

Retrieves a list of databases with optional filtering and pagination.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `params` | `AzionDatabaseCollectionOptions` (optional) | Optional parameters for filtering and pagination. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the retrieval. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabaseCollections>>` | Array of database objects or error. |

**Example**:

```typescript
import { getDatabases } from 'azion/sql';
import type { AzionDatabaseResponse, AzionDatabaseCollections } from 'azion/sql';

const { data: allDatabases, error }: AzionDatabaseResponse<AzionDatabaseCollections> = await getDatabases(
  { page: 1, page_size: 10 },
  { debug: true },
);
if (allDatabases) {
  console.log(`Retrieved ${allDatabases.count} databases`);
} else {
  console.error('Failed to retrieve databases', error);
}
```

### `useQuery`

Executes a query on a specific database.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | `string` | Name of the database to query. |
| `statements` | `string[]` | Array of SQL statements to execute. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the query. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` | Query result object or error. |

**Example**:

```typescript
import { useQuery, AzionDatabaseQueryResponse, AzionDatabaseResponse } from 'azion/sql';

const { data: result, error }: AzionDatabaseResponse<AzionDatabaseQueryResponse> = await useQuery(
  'my-db',
  ['SELECT * FROM users'],
  {
    debug: true,
  },
);
if (result) {
  console.log(`Query executed. Rows returned: ${result.rows.length}`);
} else {
  console.error('Query execution failed', error);
}
```

### `useExecute`

Executes a set of SQL statements on a specific database.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | `string` | Name of the database on which the statements will be executed. |
| `statements` | `string[]` | Array of SQL statements to execute. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the execution. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` | Execution result object or error. |

**Example**:

```typescript
import { useExecute, AzionDatabaseQueryResponse } from 'azion/sql';

const result: AzionDatabaseQueryResponse | null = await useExecute(
  'my-db',
  ['INSERT INTO users (name) VALUES ("John")'],
  {
    debug: true,
  },
);
if (result?.state === 'executed') {
  console.log('Executed with success');
} else {
  console.error('Execution failed');
}
```

### `getTables`

Retrieves a list of tables in a specific database.

**Parameters**:

| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | `string` | Name of the database to retrieve tables from. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the retrieval. |

**Returns**:

| Return Type | Description |
|-------------|-------------|
| `Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` | Query result containing table information or error. |

**Example**:

```typescript
import { getDatabase } from 'azion/sql';
import type { AzionDatabaseResponse, AzionDatabase, AzionDatabaseQueryResponse } from 'azion/sql';

// First get the database
const { data: database, error }: AzionDatabaseResponse<AzionDatabase> = await getDatabase('my-db', { debug: true });

if (database) {
  // Then get the tables using the database object method
  const { data: tables, error: tablesError }: AzionDatabaseResponse<AzionDatabaseQueryResponse> = await database.getTables();
  if (tables) {
    console.log('Tables:', tables.data);
  } else {
    console.error('Failed to get tables', tablesError);
  }
}
```

---

## Types

These are the types used by the **SQL** Library and its methods:

### `ClientConfig`

Configuration options for the SQL client.

| Parameter | Type | Description |
|-----------|------|-------------|
| `token` | `string` (optional) | Your Azion API token. |
| `options` | `AzionClientOptions` (optional) | Optional parameters for the client configuration. |

### `AzionSQLClient`

An object with methods to interact with SQL databases.

| Method | Parameters | Return Type |
|--------|------------|-------------|
| `createDatabase` | `name: string` | `Promise<AzionDatabaseResponse<AzionDatabase>>` |
| `deleteDatabase` | `id: number` | `Promise<AzionDatabaseResponse<AzionDatabaseDeleteResponse>>` |
| `getDatabase` | `name: string` | `Promise<AzionDatabaseResponse<AzionDatabase>>` |
| `getDatabases` | `params?: AzionDatabaseCollectionOptions` | `Promise<AzionDatabaseResponse<AzionDatabaseCollections>>` |
| `useQuery` | `name: string, statements: string[], options?: AzionClientOptions` | `Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` |
| `useExecute` | `name: string, statements: string[], options?: AzionClientOptions` | `Promise<AzionDatabaseResponse<AzionDatabaseExecutionResponse>>` |

### `AzionDatabase`

The database object.

| Property | Type | Description |
|----------|------|-------------|
| `id` | `number` | Unique identifier for the database. |
| `name` | `string` | The name of the database. |
| `clientId` | `string` | The client ID associated with the database. |
| `status` | `string` | The current status of the database. |
| `createdAt` | `string` | The timestamp of when the database was created. |
| `updatedAt` | `string` | The timestamp of when the database was last updated. |
| `deletedAt` | `string \| null` | Timestamp of when the database was deleted, or null if not deleted. |
| `query` | `(statements: string[], options?: AzionClientOptions) => Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` | Executes a query on the database. |
| `execute` | `(statements: string[], options?: AzionClientOptions) => Promise<AzionDatabaseResponse<AzionDatabaseExecutionResponse>>` | Executes a SQL statement on the database. |
| `getTables` | `(options?: AzionClientOptions) => Promise<AzionDatabaseResponse<AzionDatabaseQueryResponse>>` | Retrieves a list of tables in the database. |

### `AzionDatabaseResponse<T>`

The response object from a database operation.

| Property | Type | Description |
|----------|------|-------------|
| `data` | `T` (optional) | The data returned from the operation. |
| `error` | `{ message: string, operation: string }` (optional) | The error details if the operation fails. |

### `QueryResult`

| Property | Type | Description |
|----------|------|-------------|
| `state` | `string` | The state of the query execution. |
| `columns` | `string[]` | An array of column names. |
| `statement` | `string` | The SQL statement executed. |
| `rows` | `(number \| string)[][]` | A 2D array of rows, where each row is an array of values. |

### `AzionClientOptions`

Optional parameters for the client configuration.

| Property | Type | Description |
|----------|------|-------------|
| `debug` | `boolean` (optional) | Enables debug mode. |
| `force` | `boolean` (optional) | Forces the operation to be executed. |

### `AzionDatabaseCollectionOptions`

Optional parameters for filtering and pagination.

| Property | Type | Description |
|----------|------|-------------|
| `ordering` | `string` (optional) | Specifies the ordering of the results. |
| `page` | `number` (optional) | The page number for pagination. |
| `page_size` | `number` (optional) | The number of items per page. |
| `search` | `string` (optional) | A search query to filter the results. |

### `AzionDatabaseQueryResponse`

The response object from a query execution.

| Property | Type | Description |
|----------|------|-------------|
| `state` | (`'executed' \| 'pending' \| 'executed-runtime' \| 'failed'`) | The state of the query execution. |
| `data` | `QueryResult[]` | The data returned from the operation. |
| `toObject` | `() => JsonObjectQueryExecutionResponse` (optional) | A method to convert the response to a JSON object. |

### `AzionDatabaseExecutionResponse`

The response object from a query execution.

| Property | Type | Description |
|----------|------|-------------|
| `state` | (`'executed' \| 'pending' \| 'executed-runtime' \| 'failed'`) | The state of the query execution. |
| `data` | `QueryResult[]` | The data returned from the operation. |
| `toObject` | `() => JsonObjectQueryExecutionResponse` (optional) | A method to convert the response to a JSON object. |

### `AzionQueryExecutionParams`

Parameters for query execution.

| Property | Type | Description |
|----------|------|-------------|
| `statements` | `string[]` | An array of SQL statements. |
| `params` | `(AzionQueryParams \| `Record\<`string`, `AzionQueryParams`>`)[]` (optional) | An array of query parameters. |

### `AzionQueryParams`

Query parameters.

| Property | Type | Description |
|----------|------|-------------|
| `param` | `string | number | boolean | null` | The parameter for the SQL statement. |