SQL

The @aziontech/sql package is the Azion Lib library for 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:

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 from the AZION_TOKEN environment variable. To pass the token in code, create a client with createClient and set its token field.

A .env file with both variables looks like this:

For how every Azion Lib package reads the token and the debug setting, refer to How Azion Lib works.

Response envelope

Each function resolves to an 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 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 fills data with { state: 'pending' } only. The envelope carries no id.
• 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:

Output:

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.

createClient

Creates a client that holds a token and request options. createClient is also the default export of the package.

Returns an 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, useExecute, or the database methods for that. For one client that covers every Azion Lib module, refer to Client.

This sample creates a client and a database with it:

Output:

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 or getDatabases.

Returns data as the created AzionDatabase. An account holds a limited number of databases; past that number, the call fills error with the message listed in Errors.

Output:

getDatabase

Returns one database by its name.

Returns data as an AzionDatabase, with the 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.

Output:

getDatabases

Lists the databases of the account, one page at a time.

Returns data as an AzionDatabaseCollections: databases holds the page, and count holds the number of databases.

Output:

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.

Returns data as an AzionDatabaseDeleteResponse, { state: 'pending' }. The response carries no id, so the sample logs the ID it passed.

Output:

useExecute

Runs SQL statements, such as an INSERT or a CREATE TABLE, on the database you name.

Returns data as an 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:

Output:

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.

Returns data as an 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.

Output:

getTables

Lists the tables of a database by running PRAGMA table_list on it.

Returns data as an 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 returns also carries getTables, as a method that takes no name. This sample reads a database, then lists its tables with that method:

Output:

Database methods

The database that getDatabase returns carries three methods that act on that database. They return the same envelope as the matching functions and take no database name:

This sample inserts and counts rows through the methods, then lists the tables with the standalone getTables function:

Output:

Errors

A failed call returns one of these messages in error.message, and error.operation names the request.

A name that matches no database does not reach this table when you call getDatabase: the function returns {}, with no error.

Types

The package exports the types below. Import them with import type.

AzionSQLClient

The client that createClient returns.

AzionClientOptions

Request options that every function takes in options, and that createClient applies to all its calls.

AzionEnvironment

The environment a call goes to.

AzionDatabaseResponse

The envelope every function returns. For how to read it, refer to Response envelope.

AzionSQLError

The error a failed call returns in error.

AzionDatabase

A database. The package also exports its fields alone as Database.

AzionDatabaseCollections

A page of databases.

AzionDatabaseCollectionOptions

Pagination and filtering for getDatabases.

AzionDatabaseDeleteResponse

What deleteDatabase returns in data.

AzionDatabaseQueryResponse

What useQuery, useExecute, getTables, and the database methods return in data. The package also exports it as AzionDatabaseExecutionResponse.

QueryResult

The result of one statement.

AzionQueryExecutionParams

Statements with their parameters. No function on this page takes this type as an argument.

AzionQueryParams

A parameter value of a SQL statement.

Related resources