Edge SQL

Azion Edge SQL is an edge-native SQL solution designed for serverless applications. It’s fully ACID-compliant and utilizes SQLite’s SQL dialect to provide a familiar development environment, facilitating fast integration. Azion Edge SQL implements a Main/Replicas architecture to enable ultra-low latency querying at the network edge, enhancing performance for distributed applications.

With Edge SQL, you can perform different tasks, including:

• Store real-time telemetry data, ideal for IoT.
• Manage inventory of distributed e-commerce/retail distribution centers.
• Store and analyze access logs, quickly identifying and mitigating security attacks.
• Store and manage access data.

There are two ways to build a database through Edge SQL: using the Edge SQL REST API or writing an edge function. The REST API allows you to create, read, update, delete, and list databases. An edge function allows you to retrieve data from a database.

Implementation

Business rules

Writing databases, through REST API or an edge function, is only possible in the main instance. All replicas are read-only.

During the creation of a database, especially your first one, there’s a propagation time for it to be replicated on the edge nodes. Your POST request response will exhibit a creating state, and you can run GET requests to check once it’s created.

You can also use a JavaScript edge function to query and analyze if the snapshots have been merged into replicas.

Data resilience

With a distributed architecture running across Azion’s edge nodes, Edge SQL guarantees data is kept safe and available. Your data is created in a main instance and propagated through replicas across Azion’s edge nodes.

Databases

A database is a set of structured tables that allow to write and read data in SQL language. Organized in columns and rows, users are able to add data logically, with columns representing specific information in the vertical axis, while rows represent individual entry data, related to a column, in the horizontal axis.

Creating a database is the first step to use Edge SQL and add data:

curl --location 'https://api.azion.com/v4/edge_sql/databases' \
--header 'Authorization: Token [TOKEN VALUE]' \
--header 'Content-Type: application/json' \
--data '{
   "name": "my-database"
}'

Query

A query is how you structure your table or a search on rows on a previously created database. It’s possible to query based on relations and constraints.

For example, to create a table via cURL:

curl --location 'https://api.azion.com/v4/edge_sql/databases/{id_database}/query' \
--header 'Authorization: Token [TOKEN VALUE]' \
--header 'Content-Type: application/json' \
--data '{
    "statements": [
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"
    ]
}'

To query data on the already created table:

curl --location 'https://api.azion.com/v4/edge_sql/databases/{id_database}/query' \
--header 'Authorization: Token [TOKEN VALUE]' \
--header 'Content-Type: application/json' \
--data '{
    "statements": [
        "SELECT * FROM users;"
    ]
}'

Interacting with Edge SQL via Terminal

You can interact with Edge SQL directly from your terminal using the edgesql-shell project. This tool provides a command-line interface for executing SQL commands against your Edge SQL databases.

Instances

Main instance

The primary storage instance, it receives all write commands and synchronizes the received data with the replicas. It also allows read actions.

All databases are created and all queries are written on the main instance. After the automatic process handled by Azion, your data is processed on edge locations, and snapshots are created and merged into replicas.

Replicas

Replicas are read-only copies of the main instance. Replicas are updated within different propagation time, so they’ll eventually be in sync in the main instance, but it doesn’t occur instantly.

The replication process occurs automatically through Azion back-end systems, in which snapshots are created and then merged in each replica.

They handle data storage, processing, and management in proximity to the devices reading the data, helping with data distribution, availability, and low latency.

Supported query languages

As Azion Edge SQL is based on SQLite, it supports the same SQL languages SQLite does.

See SQL languages understood to create queries.

Limits

These are the default limits:

These are the default limits for each Service Plan: