Databases and queries

A database is the artifact SQL Database creates. It stores relational data and accepts SQL in SQLite’s dialect. The Azion API v4 addresses it by an integer identifier and exposes five operations on it: four on the database object, and one that runs SQL statements against its contents. This page lists the fields of a database, the five operations, the result a statement returns, and the errors the API and the statements each return.

Database names

A database name is chosen at creation and cannot be changed afterwards.

A name outside those bounds returns 14000, with 10048 alongside it when the name is shorter than 6 characters. A name the account already holds returns 14001. Because the name is fixed for the life of the database, a name that records what the database holds stays readable, as in orders-eu.

Database fields

A create request accepts name and active, and nothing else. No field is editable afterwards: PATCH and PUT both return 10007 Method Not Allowed, so a database is never renamed and active is never changed.

Operations

Every operation is authenticated and sits under https://api.azion.com/v4/workspace/sql.

The four database operations act on the object in the table above. The query operation reaches past it, into the tables and rows the database holds.

Create a database

The response carries 202 and the database that is being provisioned:

The envelope’s state and the database’s status are two different fields. state describes the request, and pending means the platform accepted it. status describes the database, and creating means it does not accept queries yet.

Retrieve a database

The response carries 200 and no state key:

Provisioning takes about 15 seconds. Poll this operation after a create request until status is created, and send the first query then. An identifier that belongs to no database returns 10004.

List databases

The response carries 200, one database per entry under results, and the pagination fields around them:

Four query parameters narrow and order the list:

A page_size above 100 returns 10097. Read a longer list one page at a time, with page.

Delete a database

The response carries 202 and {"state": "pending"}, and the database answers 10004 within seconds. A delete is accepted while the status is still creating. Deletion is permanent, and the rows the database held cannot be recovered.

Execute a query

The response carries 200 and one entry per statement:

Query

The query body carries one key, statements, holding an array of SQL strings. Each statement returns one entry in data, in the order the array lists them. The two statements below create a table and read it back:

Each one is a string in the array:

A statement that succeeds carries a results object with five fields.

rows_read and rows_written are the two metrics SQL Database is billed on, and the API returns them for every statement. For the usage each plan includes, refer to SQL Database limits.

An empty statements array returns 200 with "data": []. A single call carrying 100 statements succeeds. The statements themselves are SQLite’s SQL: for the syntax each one accepts, refer to the SQLite language reference.

Failed statements

A statement that fails does not fail the request. The call returns HTTP 200, and the entry for that statement carries error in place of results:

A client that reads only the HTTP status treats this response as a success. It then reads a results key that is not there. Check data[].error on every entry before you read data[].results, and report an entry that carries error as a failed statement.

When the statements cannot be executed at all, the request returns 422 with 14005 Execute SQL Exception instead, and meta.database_name names the database. The two rejections are therefore read in two different places: a 422 in the errors array, and a failed statement inside a 200.

Authentication

Every request carries a personal token and asks for JSON:

A request that carries a body also carries Content-Type: application/json.

The account needs the SQL Database permissions for the operation. View SQL Database grants permission to view the created databases and their data through the Azion API. Edit SQL Database grants permission to create and edit databases and their data through the Azion API. For more information, refer to Teams Permissions.

Errors

A rejected request carries one errors array. Each entry names the code, the title, a detail, the status, and the field it applies to under source.pointer.

A statement that fails returns its error as a string inside a 200, and never in an errors array.

For the vector types and the functions that build and compare them, refer to SQL Database Vector Search.

Limits

A database name runs 6 to 50 characters, a table holds up to 2,000 columns, and a list response returns up to 100 databases. Every bound on a database, what the platform does past each value, and the usage each plan includes are in SQL Database limits.

The runtime API

The operations above are HTTP calls, addressed to api.azion.com and authenticated with a personal token. A function running in Azion Runtime reaches the same database a second way: it imports the azion:sql module and calls Database.open, which takes the database name, carries no token, and opens a connection to the read replica.

That module belongs to the runtime rather than to SQL Database, so its connection, statement, and row objects are documented with the other runtime bindings, in SQL Database API.

The azion library

The azion npm package exports azion/sql, which wraps the same operations for Node and TypeScript. It reads the token from the AZION_TOKEN environment variable.

The library is not a passthrough, and two differences change what you can read from it. It camelCases the database fields to lastModified, lastEditor, and productVersion. Its query result drops rows_read, rows_written, and query_duration_ms, so an account that meters its own consumption reads those three from the API rather than from the library. For the functions the library exports, refer to Azion SQL library.

Related resources