Create tables and query data

You create a table in SQL Database, insert rows into it, read them back, and update or delete them from Azion Console, the Azion API, or the azion library. The three interfaces run the same SQL against the same database.

Every statement on this page runs against a database that already exists. To create one, refer to Create and manage databases.

Prerequisites

• A database whose status reads created. The API addresses it by the integer identifier the create response returns, and the azion library addresses it by name. Refer to Create and manage databases.
• SQL Database enabled on your account. The product is in Preview and is not enabled by default, so request access through Technical Support.
• The Edit SQL Database permission, which grants permission to create and edit databases and their data through the Azion API. View SQL Database grants permission to view them. Refer to Teams Permissions.
• Access to Azion Console, for the Console procedures. Refer to How to access Azion Console.
• A personal token, for the API procedures.
• Node and the azion package, for the library procedures. The library reads your token from AZION_TOKEN, and AZION_DEBUG turns on request logging. Refer to Azion SQL library.

The statements themselves are SQLite’s SQL. For the syntax each one accepts, refer to the SQLite language reference.

Create a table using Azion Console

The Editor tab of a database runs SQL against that database, and a CREATE TABLE statement runs there like any other. To create the users table:

The Tables tab lists users. A database that holds no table shows the empty state “No tables yet”, with the line “Create your first table to store your data.”

The editor carries three more controls beside Run query. Templates loads a shipped statement into the editor, among them “Create a basic users table with auto-increment ID and timestamp” and “Insert sample user records into the users table”. Prettify reformats what the editor holds, and Delete query clears it.

Create a table using the API

Send a POST request to the query endpoint of the database. The body carries one key, statements, holding an array of SQL strings, and Azion runs them in the order the array lists. To create the users table:

The database holds a users table with the columns id, name, and email. The same endpoint runs every statement that follows: reads and writes are not split across two paths.

Create a table using the azion library

azion/sql runs the same statements from Node and TypeScript. useExecute runs the statements that change the database, useQuery runs the ones that return rows, and both take the database name first and an array of statements second:

The database holds the users table. data carries state and a results array with one entry per statement, each entry naming the SQL verb it ran, and error carries a message and the operation that failed.

Insert rows

A row is stored with an INSERT statement, and one call carries as many of them as the table needs. The two rows below are the data every statement further down this page reads.

Azion Console

In the Editor tab of the database, enter both statements and select Run query:

The users table holds two rows. Selecting the table in the Tables tab reaches the controls Insert Data, Insert Column, Count Records, Schema Info, Foreign Keys, and Delete Table, and an export menu with Export all to .csv, Export all to .json, and Export all to .xlsx.

The API

Send both INSERT statements in one statements array:

A SQL string literal is quoted with a single quote, and the whole body travels as a single-quoted shell argument, so every single quote inside it is written '\''.

Each statement returns its own entry under data, in the order the array listed them. An INSERT returns no rows, so its entry carries empty columns and rows, and it reports the rows it wrote in rows_written. The two rows are stored under the identifiers 1 and 2.

The azion library

Pass both statements to useExecute:

data names the verb each statement ran and returns neither columns nor rows:

Query rows

A SELECT statement returns what the table holds. The response reports the column names once and each row as an array of values in column order, not as an object keyed by column name.

Azion Console

In the Editor tab of the database, enter the statement and select Run query:

The rows appear in the result area of the editor, which reads “Execute a query to see the results here” until a query runs. The editor keeps a history of the queries you run against the database.

The API

Send the SELECT statement to the query endpoint:

The API answers HTTP 200 with both rows:

columns carries the column names of the result set, and rows carries one array per row with the values in column order. rows_read and rows_written are the two metrics SQL Database is billed on, and the API reports them for every statement, beside the query_duration_ms the statement took. For the usage each plan includes, refer to SQL Database limits.

The azion library

Pass the statement to useQuery:

data carries state and a results array, and each entry names the statement’s verb beside its columns and rows:

The library drops rows_read, rows_written, and query_duration_ms from what it returns. An account that meters its own consumption reads those three from the API rather than from the library.

Update and delete rows

An UPDATE statement changes the values a row holds, and a DELETE statement removes rows. Both run through the query endpoint, like every other statement, and both act on the rows their WHERE clause names.

Azion Console

In the Editor tab of the database, enter the statements and select Run query:

The first statement changes the address of one row, and the second removes that row. The users table holds one row.

The API

Send both statements to the query endpoint:

The API answers HTTP 200 with "state": "executed" and one entry per statement. Neither statement returns rows, so both entries carry empty columns and rows, and each reports what it wrote in rows_written.

Read the table back with the SELECT statement from the section above. One row is left:

The azion library

Both statements change the database, so both run through useExecute:

data.results names the verb of each statement, and neither entry carries columns or rows.

Read the result of every statement

A statement that fails does not fail the request. The call answers HTTP 200, the envelope still reports "state": "executed", and the entry for the failed statement carries error in place of results:

A client that reads only the HTTP status code treats this as a success. Read data[].error on every entry the response carries, and treat an entry that holds it as a statement that did not run.

The azion library reports the same failure on both halves of its return value: data.results[0].error carries the message, and the top-level error carries a message and operation: "apiQuery".

Two failures do answer with an error status. A body with no statements key returns HTTP 400 with error 10059, Required Field, and source.pointer set to /data/statements. Statements Azion cannot execute return HTTP 422 with error 14005, Execute SQL Exception, and meta.database_name names the database.

For more information, refer to Troubleshooting.

Next steps