# SQL The `@aziontech/sql` package is the Azion Lib library for [SQL Database](/en/documentation/platform/sql-database/). It creates, lists, reads, and deletes databases, and it runs SQL statements on a database you name, through Azion API v4. Its functions take positional arguments, and every function reports a failure inside the object it returns. Install the package: ```bash npm install @aziontech/sql ``` Every sample on this page is an ES module that uses top-level `await` and runs in Node.js. The TypeScript samples bring in types with `import type`, so they still load after their type annotations are stripped. --- ## Authentication Each function takes your [personal token](/en/documentation/fundamentals/personal-tokens/) from the `AZION_TOKEN` environment variable. To pass the token in code, create a client with [createClient](#createclient) and set its `token` field. | Variable | Description | | ------------- | ------------------------------------------------------------------- | | `AZION_TOKEN` | Your Azion personal token. | | `AZION_DEBUG` | With `true`, the functions log the response bodies the API returns. | A `.env` file with both variables looks like this: ```bash AZION_TOKEN=[TOKEN VALUE] AZION_DEBUG=true ``` For how every Azion Lib package reads the token and the debug setting, refer to [How Azion Lib works](/en/documentation/devtools/azion-lib/how-it-works/). --- ## Response envelope Each function resolves to an [AzionDatabaseResponse](#aziondatabaseresponse), `{ data?, error? }`. A call that succeeds fills `data`. A call that fails fills `error` with `{ message, operation }`, where `operation` names the request that failed, such as `post database` or `apiQuery`. A statement that fails inside [useQuery](#usequery) also fails the call: `error` holds the database error, such as `no such table: nope`, and `data` is unset. Two results differ from that shape: - [deleteDatabase](#deletedatabase) fills `data` with `{ state: 'pending' }` only. The envelope carries no `id`. - [getDatabase](#getdatabase) returns an empty object, `{}`, for a name that matches no database. Neither `data` nor `error` is set. A statement run with `useExecute` resolves to this envelope. The `results` array holds one entry per statement: ```javascript import { useExecute } from '@aziontech/sql'; console.log(JSON.stringify(await useExecute('my-database', ['CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)']))); ``` Output: ```text {"data":{"state":"executed","results":[{"statement":"CREATE"}]}} ``` The query result leaves out `rows_read`, `rows_written`, and `query_duration_ms`, which the Azion API returns for each statement. To meter consumption, read those fields from the API. For more information, refer to [Databases and queries](/en/documentation/platform/sql-database/databases-and-queries/). --- ## createClient Creates a client that holds a token and request options. `createClient` is also the default export of the package. ```typescript function createClient(config?: Partial<{ token?: string; options?: AzionClientOptions; }>): AzionSQLClient; ``` | Parameter | Type | Required | Description | | --------- | ------------------------------------------- | -------- | ------------------------------------------------ | | `token` | `string` | No | Your Azion personal token. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options for every call the client makes. | Returns an [AzionSQLClient](#azionsqlclient) with four methods: `createDatabase`, `deleteDatabase`, `getDatabase`, and `getDatabases`. They take the same arguments as the functions of the same name, without `options`. The client runs no statements: use [useQuery](#usequery), [useExecute](#useexecute), or the [database methods](#database-methods) for that. For one client that covers every Azion Lib module, refer to [Client](/en/documentation/devtools/azion-lib/client/). This sample creates a client and a database with it: ```typescript import { createClient } from '@aziontech/sql'; import type { AzionSQLClient } from '@aziontech/sql'; const client: AzionSQLClient = createClient({ token: process.env.AZION_TOKEN, options: { debug: false } }); const { data, error } = await client.createDatabase('my-client-database'); if (data) { console.log(`Database created with ID: ${data.id} (status: ${data.status})`); } else { console.error('Failed to create database', error); } ``` Output: ```text Database created with ID: 1864 (status: creating) ``` --- ## createDatabase Creates a database. The function returns while the database is still being provisioned, so its `status` reads `creating`. The status changes to `created` within seconds; read it with [getDatabase](#getdatabase) or [getDatabases](#getdatabases). ```typescript function createDatabase(name: string, options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | --------- | ------------------------------------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `name` | `string` | Yes | The name of the database. For the characters and length a name accepts, refer to [Database names](/en/documentation/platform/sql-database/databases-and-queries/#database-names). | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as the created [AzionDatabase](#aziondatabase). An account holds a limited number of databases; past that number, the call fills `error` with the message listed in [Errors](#errors). ```typescript import { createDatabase } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabase } from '@aziontech/sql'; const { data, error }: AzionDatabaseResponse = await createDatabase('my-database', { debug: false }); if (data) { const database: AzionDatabase = data; console.log(`Database created with ID: ${database.id} (status: ${database.status})`); } else { console.error('Failed to create database', error); } ``` Output: ```text Database created with ID: 1865 (status: creating) ``` --- ## getDatabase Returns one database by its name. ```typescript function getDatabase(name: string, options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | --------- | ------------------------------------------- | -------- | ------------------------- | | `name` | `string` | Yes | The name of the database. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabase](#aziondatabase), with the [database methods](#database-methods). The function searches the databases of the account by the name and returns the first match. A name that matches no database returns `{}`: `data` and `error` are both unset, so the sample's `else` branch logs `undefined` for `error`. ```typescript import { getDatabase } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabase } from '@aziontech/sql'; const { data, error }: AzionDatabaseResponse = await getDatabase('my-database', { debug: false }); if (data) { const database: AzionDatabase = data; console.log(`Retrieved database: ${database.id} (${database.name}, ${database.status})`); } else { console.error('Database not found', error); } ``` Output: ```text Retrieved database: 1865 (my-database, created) ``` --- ## getDatabases Lists the databases of the account, one page at a time. ```typescript function getDatabases(params?: Partial, options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | --------- | ------------------------------------------------------------------- | -------- | --------------------------------- | | `params` | [`AzionDatabaseCollectionOptions`](#aziondatabasecollectionoptions) | No | Pagination, search, and ordering. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabaseCollections](#aziondatabasecollections): `databases` holds the page, and `count` holds the number of databases. ```typescript import { getDatabases } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabaseCollections } from '@aziontech/sql'; const { data: allDatabases, error }: AzionDatabaseResponse = await getDatabases( { page: 1, page_size: 10 }, { debug: false }, ); if (allDatabases) { console.log(`Retrieved ${allDatabases.count} databases`); for (const db of allDatabases.databases ?? []) console.log(db.id, db.name, db.status); } else { console.error('Failed to retrieve databases', error); } ``` Output: ```text Retrieved 5 databases 1865 my-database created 1812 my-store-db created 1821 my-redirects created 1822 my-guestbook created 1862 my-app-db created ``` --- ## deleteDatabase Deletes a database by its ID. The deletion is asynchronous: the API accepts the request, and the database leaves the list within seconds. Deletion is permanent, and the rows the database held cannot be recovered. ```typescript function deleteDatabase(id: number, options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | --------- | ------------------------------------------- | -------- | --------------------------------- | | `id` | `number` | Yes | The ID of the database to delete. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabaseDeleteResponse](#aziondatabasedeleteresponse), `{ state: 'pending' }`. The response carries no `id`, so the sample logs the ID it passed. ```typescript import { deleteDatabase } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabaseDeleteResponse } from '@aziontech/sql'; const databaseId = 1864; const { data, error }: AzionDatabaseResponse = await deleteDatabase(databaseId, { debug: false }); if (data) { console.log(`Database ${databaseId} deletion requested (state: ${data.state})`); } else { console.error('Failed to delete database', error); } ``` Output: ```text Database 1864 deletion requested (state: pending) ``` --- ## useExecute Runs SQL statements, such as an `INSERT` or a `CREATE TABLE`, on the database you name. ```typescript function useExecute(name: string, statements: string[], options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | ------------ | ------------------------------------------- | -------- | ------------------------------------ | | `name` | `string` | Yes | The name of the database. | | `statements` | `string[]` | Yes | The SQL statements to run, in order. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabaseQueryResponse](#aziondatabasequeryresponse). Read `state` from `data`, not from the envelope: `data.state` is `executed` after a successful run. The function looks the database up by its name first, then sends the statements. This sample inserts a row into a `users` table with `id` and `name` columns: ```typescript import { useExecute } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabaseQueryResponse } from '@aziontech/sql'; const { data: result, error }: AzionDatabaseResponse = await useExecute( 'my-database', ["INSERT INTO users (name) VALUES ('John')"], { debug: false, }, ); if (result?.state === 'executed') { console.log('Executed with success'); } else { console.error('Execution failed', error); } ``` Output: ```text Executed with success ``` Write string literals in single quotes inside a statement, as the sample does. --- ## useQuery Runs SQL queries, such as a `SELECT`, on the database you name, and returns the rows they read. ```typescript function useQuery(name: string, statements: string[], options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | ------------ | ------------------------------------------- | -------- | ------------------------------------ | | `name` | `string` | Yes | The name of the database. | | `statements` | `string[]` | Yes | The SQL statements to run, in order. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabaseQueryResponse](#aziondatabasequeryresponse). Each statement has one entry in `data.results`, in the order of `statements`. The rows of the first statement are in `data.results[0].rows`, and its column names are in `data.results[0].columns`. `data.toObject()` returns the same rows as objects keyed by column name. ```typescript import { useQuery } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabaseQueryResponse } from '@aziontech/sql'; const { data: result, error }: AzionDatabaseResponse = await useQuery( 'my-database', ['SELECT * FROM users'], { debug: false, }, ); if (result) { const [first] = result.results ?? []; console.log(`Query executed. Rows returned: ${first?.rows?.length}`); console.log('Columns:', first?.columns, 'Rows:', first?.rows); console.log('As objects:', JSON.stringify(result.toObject())); } else { console.error('Query execution failed', error); } ``` Output: ```text Query executed. Rows returned: 1 Columns: [ 'id', 'name' ] Rows: [ [ 1, 'John' ] ] As objects: {"state":"executed","results":[{"statement":"SELECT","rows":[{"id":1,"name":"John"}]}]} ``` --- ## getTables Lists the tables of a database by running `PRAGMA table_list` on it. ```typescript function getTables(databaseName: string, options?: AzionClientOptions): Promise>; ``` | Parameter | Type | Required | Description | | -------------- | ------------------------------------------- | -------- | ------------------------- | | `databaseName` | `string` | Yes | The name of the database. | | `options` | [`AzionClientOptions`](#azionclientoptions) | No | Request options. | Returns `data` as an [AzionDatabaseQueryResponse](#aziondatabasequeryresponse). `data.results[0]` holds one row per table, with the columns `schema`, `name`, `type`, `ncol`, `wr`, and `strict`. The table name is the second value of each row. The list includes the SQLite tables `sqlite_schema` and `sqlite_temp_schema`. The database that [getDatabase](#getdatabase) returns also carries `getTables`, as a method that takes no name. This sample reads a database, then lists its tables with that method: ```typescript import { getDatabase } from '@aziontech/sql'; import type { AzionDatabaseResponse, AzionDatabase, AzionDatabaseQueryResponse } from '@aziontech/sql'; // First get the database const { data: database, error }: AzionDatabaseResponse = await getDatabase('my-database', { debug: false }); if (database) { // Then get the tables using the database object method const { data: tables, error: tablesError }: AzionDatabaseResponse = await database.getTables(); if (tables) { console.log('Tables:', tables.results?.[0]?.rows?.map((row) => row[1])); } else { console.error('Failed to get tables', tablesError); } } else { console.error('Database not found', error); } ``` Output: ```text Tables: [ 'users', 'sqlite_schema', 'sqlite_temp_schema' ] ``` --- ## Database methods The database that [getDatabase](#getdatabase) returns carries three methods that act on that database. They return the same envelope as the matching functions and take no database name: | Method | Arguments | Matching function | | ----------- | ---------------------------------------------------- | ------------------------- | | `query` | `statements: string[], options?: AzionClientOptions` | [useQuery](#usequery) | | `execute` | `statements: string[], options?: AzionClientOptions` | [useExecute](#useexecute) | | `getTables` | `options?: AzionClientOptions` | [getTables](#gettables) | This sample inserts and counts rows through the methods, then lists the tables with the standalone `getTables` function: ```typescript import { getDatabase, getTables } from '@aziontech/sql'; const { data: database, error } = await getDatabase('my-database'); if (!database) throw new Error(error?.message ?? 'Database not found'); const { data: inserted } = await database.execute(["INSERT INTO users (name) VALUES ('Ana')"]); console.log('database.execute:', inserted?.state, inserted?.results); const { data: counted } = await database.query(['SELECT count(*) AS n FROM users']); console.log('database.query:', JSON.stringify(counted?.toObject())); const { data: tables } = await getTables('my-database'); console.log('getTables(name):', tables?.results?.[0]?.columns, tables?.results?.[0]?.rows); ``` Output: ```text database.execute: executed [ { statement: 'INSERT', columns: undefined, rows: undefined, error: undefined } ] database.query: {"state":"executed","results":[{"statement":"SELECT","rows":[{"n":2}]}]} getTables(name): [ 'schema', 'name', 'type', 'ncol', 'wr', 'strict' ] [ [ 'main', 'users', 'table', 2, 0, 0 ], [ 'main', 'sqlite_schema', 'table', 5, 0, 0 ], [ 'temp', 'sqlite_temp_schema', 'table', 5, 0, 0 ] ] ``` --- ## Errors A failed call returns one of these messages in `error.message`, and `error.operation` names the request. | Message | Cause | What to do | | --------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `Database not found` | The name passed to [useQuery](#usequery) matches no database in the account. `error.operation` is `apiQuery`. | Check the name with [getDatabases](#getdatabases). | | `no such table: ` | A statement names a table the database does not hold. `error.operation` is `apiQuery`. | Check the table names with [getTables](#gettables). | | `The maximum number of databases has been reached.` | The account already holds the maximum number of databases. The API answers `403`, and `error.operation` is `post database`. | Delete a database you no longer need with [deleteDatabase](#deletedatabase). For the number each plan allows, refer to [Limits per plan](/en/documentation/platform/sql-database/limits/#limits-per-plan). | A name that matches no database does not reach this table when you call [getDatabase](#getdatabase): the function returns `{}`, with no `error`. --- ## Types The package exports the types below. Import them with `import type`. ### AzionSQLClient The client that [createClient](#createclient) returns. | Method | Arguments | Returns | | ---------------- | ----------------------------------------- | ------------------------------------------------------------- | | `createDatabase` | `name: string` | `Promise>` | | `deleteDatabase` | `id: number` | `Promise>` | | `getDatabase` | `name: string` | `Promise>` | | `getDatabases` | `params?: AzionDatabaseCollectionOptions` | `Promise>` | ### AzionClientOptions Request options that every function takes in `options`, and that [createClient](#createclient) applies to all its calls. | Property | Type | Required | Description | | ---------- | --------------------------------------- | -------- | -------------------------------------------------------------- | | `debug` | `boolean` | No | Logs the response bodies the API returns. | | `force` | `boolean` | No | Forces the operation, even when it can destroy data. | | `env` | [`AzionEnvironment`](#azionenvironment) | No | The environment the calls go to. | | `external` | `boolean` | No | Forces the REST API instead of the API built into the runtime. | ### AzionEnvironment The environment a call goes to. ```typescript type AzionEnvironment = 'development' | 'staging' | 'production'; ``` ### AzionDatabaseResponse The envelope every function returns. For how to read it, refer to [Response envelope](#response-envelope). | Property | Type | Required | Description | | -------- | --------------------------------- | -------- | --------------------------- | | `data` | `T` | No | The result of the call. | | `error` | [`AzionSQLError`](#azionsqlerror) | No | The error of a failed call. | ### AzionSQLError The error a failed call returns in `error`. | Property | Type | Required | Description | | ----------- | ------------------------- | -------- | ----------------------------------- | | `message` | `string` | Yes | The error message. | | `operation` | `string` | Yes | The request that failed. | | `metadata` | `Record` | No | Additional details about the error. | ### AzionDatabase A database. The package also exports its fields alone as `Database`. | Property | Type | Required | Description | | ------------------------------- | --------------------------------------- | -------- | ------------------------------------------- | | `id` | `number` | Yes | The ID of the database. | | `name` | `string` | Yes | The name of the database. | | `status` | `'creating' \| 'created' \| 'deleting'` | Yes | The provisioning state of the database. | | `active` | `boolean` | Yes | Whether the database is active. | | `lastModified` | `string` | Yes | When the database last changed. | | `lastEditor` | `string \| null` | Yes | The account that last changed the database. | | `productVersion` | `string` | Yes | The product version. | | `query`, `execute`, `getTables` | functions | Yes | The [database methods](#database-methods). | ### AzionDatabaseCollections A page of databases. | Property | Type | Required | Description | | ----------- | ----------------------------------- | -------- | -------------------------- | | `databases` | [`AzionDatabase[]`](#aziondatabase) | No | The databases on the page. | | `count` | `number` | No | The number of databases. | ### AzionDatabaseCollectionOptions Pagination and filtering for [getDatabases](#getdatabases). | Property | Type | Required | Description | | ----------- | -------- | -------- | --------------------------------------------------------------------------------------- | | `page` | `number` | No | The page number. | | `page_size` | `number` | No | The number of databases per page, from 1 to 100. The API returns 10 when it is omitted. | | `search` | `string` | No | A term that filters the databases. | | `ordering` | `string` | No | The field that orders the results. | ### AzionDatabaseDeleteResponse What [deleteDatabase](#deletedatabase) returns in `data`. | Property | Type | Required | Description | | -------- | ------------------------------------- | -------- | ------------------------------------------------------------------------ | | `state` | `'pending' \| 'failed' \| 'executed'` | Yes | The state of the deletion. A deletion the API accepts returns `pending`. | ### AzionDatabaseQueryResponse What [useQuery](#usequery), [useExecute](#useexecute), [getTables](#gettables), and the database methods return in `data`. The package also exports it as `AzionDatabaseExecutionResponse`. | Property | Type | Required | Description | | ---------- | ----------------------------------------------------------- | -------- | ------------------------------------------------------------------------------------------------------------ | | `state` | `'pending' \| 'failed' \| 'executed' \| 'executed-runtime'` | Yes | The state of the run. | | `results` | [`QueryResult[]`](#queryresult) | No | One entry per statement, in the order of `statements`. | | `toObject` | `() => JsonObjectQueryExecutionResponse \| null` | Yes | Returns `{ state, results }`, where each entry holds `statement` and `rows` as objects keyed by column name. | ### QueryResult The result of one statement. | Property | Type | Required | Description | | ----------- | ------------------------ | -------- | --------------------------------------------------------------- | | `statement` | `string` | No | The kind of statement, such as `SELECT`, `INSERT`, or `CREATE`. | | `columns` | `string[]` | No | The column names. Set on a statement that returns rows. | | `rows` | `(string \| number)[][]` | No | The rows, each an array of values in the order of `columns`. | | `error` | `string` | No | The error of the statement. | ### AzionQueryExecutionParams Statements with their parameters. No function on this page takes this type as an argument. | Property | Type | Required | Description | | ------------ | ------------------------------------------------------------- | -------- | --------------------------------- | | `statements` | `string[]` | Yes | The SQL statements. | | `params` | `Array>` | Yes | The parameters of the statements. | ### AzionQueryParams A parameter value of a SQL statement. ```typescript type AzionQueryParams = string | number | boolean | null | { type: string; value: string | number | boolean | null; }; ``` --- ## Related resources - [Azion Lib](/en/documentation/devtools/azion-lib.md): Every library Azion Lib ships and the package each one comes in. - [SQL Database](/en/documentation/platform/sql-database.md): What a database holds and how applications and functions reach it. - [Databases and queries](/en/documentation/platform/sql-database/databases-and-queries.md): The API operations these functions call, with their fields and error codes. - [SQL Database API](/en/documentation/devtools/runtime/api-reference/sql-database.md): How a function opens a database inside Azion Runtime without a token.