How to build a RESTful API with Edge Functions and Edge SQL

This guide demonstrates how to build a complete RESTful API for task management using Azion Edge Functions and Edge SQL. You’ll learn to implement full CRUD (Create, Read, Update, Delete) operations at the edge, providing fast, scalable API endpoints globally.

Requirements

Before you begin, ensure you have:

An Azion account
Azion CLI installed and configured
Node.js version 18 or higher

Getting started

Step 1: Create a new Edge SQL database

Create a database using Azion API

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

You should receive the following response:

{
  "state": "pending",
  "data": {
    "id": 118,
    "name": "my-database",
    "client_id": "6832h",
    "status": "creating",
    "created_at": "2024-04-18T11:22:59.468536Z",
    "updated_at": "2024-04-18T11:22:59.468586Z",
    "deleted_at": null
  }
}

For more details, see Edge SQL documentation.

Step 2: Set up the database schema

You can use the following SQL commands to set up the database schema.

Connect to your Edge SQL instance and create the tasks table:

CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Optionally, insert some sample data for testing:

INSERT INTO tasks (title, completed) VALUES
    ('Complete API documentation', FALSE),
    ('Test edge function deployment', FALSE),
    ('Review security configurations', TRUE);

Code

This is a code example of how to create a RESTful API with Azion Edge Functions and Edge SQL. The complete code example you can find in this GitHub repository.

Database integration

import { useExecute, useQuery } from "azion/sql";

const DATABASE_NAME = process.env.DATABASE_NAME || "tasks";

export interface Task {
  id: number;
  title: string;
  completed: boolean;
}

export const getTasks = async (): Promise<Task[]> => {
  const { data, error } = await useQuery(DATABASE_NAME, [
    "SELECT * FROM tasks",
  ]);
  if (error) {
    throw error;
  }
  return (
    data?.results?.[0]?.rows?.map(
      (row) =>
        ({
          id: row[0],
          title: row[1],
          completed: row[2] === 1,
        } as Task)
    ) || []
  );
};

export const getTask = async (id: number): Promise<Task | null> => {
  const { data, error } = await useQuery(DATABASE_NAME, [
    `SELECT * FROM tasks WHERE id = ${id}`,
  ]);
  if (error) {
    throw error;
  }
  return (
    (data?.results?.[0]?.rows?.[0] &&
      ({
        id: data?.results?.[0]?.rows?.[0][0],
        title: data?.results?.[0]?.rows?.[0][1],
        completed: data?.results?.[0]?.rows?.[0][2] === 1,
      } as Task)) ||
    null
  );
};

export const createTask = async (task: Omit<Task, "id">): Promise<Task> => {
  const { data, error } = await useExecute(DATABASE_NAME, [
    `INSERT INTO tasks (title, completed) VALUES ('${task.title}', ${task.completed}) RETURNING id`,
  ]);
  if (error) {
    throw error;
  }
  const newId = data?.results?.[0]?.rows?.[0]?.[0] || "";
  return { ...task, id: Number(newId) };
};

export const updateTask = async (
  id: number,
  task: Partial<Omit<Task, "id">>
): Promise<Task | null> => {
  const existingTask = await getTask(id);
  if (!existingTask) {
    return null;
  }

  const updatedTitle =
    task.title !== undefined ? task.title : existingTask.title;
  const updatedCompleted =
    task.completed !== undefined ? task.completed : existingTask.completed;

  const { data, error } = await useExecute(DATABASE_NAME, [
    `UPDATE tasks SET title = '${updatedTitle}', completed = ${updatedCompleted} WHERE id = ${id}`,
  ]);
  if (error) {
    throw error;
  }
  return { ...existingTask, title: updatedTitle, completed: updatedCompleted };
};

export const deleteTask = async (id: number): Promise<boolean> => {
  const { data, error } = await useExecute(DATABASE_NAME, [
    `DELETE FROM tasks WHERE id = ${id}`,
  ]);

  if (error) {
    throw error;
  }

  return data?.state === "executed" || data?.state === "pending";
};

Application

import { Hono } from 'hono';
import { fire } from 'hono/service-worker';
import {
  createTask,
  deleteTask,
  getTask,
  getTasks,
  updateTask,
} from './db';

const app = new Hono();

app.get('/tasks', async (c) => {
  try {
    const tasks = await getTasks();
    return c.json(tasks);
  } catch (error) {
    return c.json({ error: 'Failed to fetch tasks' }, 500);
  }
});

app.get('/tasks/:id', async (c) => {
  try {
    const { id } = c.req.param();
    const task = await getTask(Number(id));
    if (!task) {
      return c.json({ error: 'Task not found' }, 404);
    }
    return c.json(task);
  } catch (error) {
    return c.json({ error: 'Failed to fetch task' }, 500);
  }
});

app.post('/tasks', async (c) => {
  try {
    const { title, completed } = await c.req.json();
    const newTask = await createTask({ title, completed: completed || false });
    return c.json(newTask, 201);
  } catch (error) {
    console.log(error);
    return c.json({ error: 'Failed to create task' }, 500);
  }
});

app.put('/tasks/:id', async (c) => {
  try {
    const { id } = c.req.param();
    const { title, completed } = await c.req.json();
    const updatedTask = await updateTask(Number(id), { title, completed });
    if (!updatedTask) {
      return c.json({ error: 'Task not found' }, 404);
    }
    return c.json(updatedTask);
  } catch (error) {
    return c.json({ error: 'Failed to update task' }, 500);
  }
});

app.delete('/tasks/:id', async (c) => {
  try {
    const { id } = c.req.param();
    const success = await deleteTask(Number(id));
    if (!success) {
      return c.json({ error: 'Task not found' }, 404);
    }
    return c.json({ message: 'Task deleted' });
  } catch (error) {
    return c.json({ error: 'Failed to delete task' }, 500);
  }
});

fire(app);

Deploying to Azion

Step 1: Authenticate with Azion

azion login

Follow the authentication prompts to connect your CLI with your Azion account.

Step 2: Create a new Edge Application from a template

Initialize a new Edge Application:

azion init

Select the template Hono Boilerplate to use for your browserless application.
Follow the prompts to configure your new Edge Application.

Step 3: Deploy the Edge Function

Deploy your RESTful API to Azion’s edge network:

azion deploy

The deployment process will:

Upload your Edge Function code
Configure the edge application
Set up routing rules for all API endpoints
Configure database connections
Provide you with a unique domain

Step 5: Access your API

After deployment, you’ll receive a domain like https://xxxxxxx.map.azionedge.net. Your RESTful API will be available at this URL within a few minutes after DNS propagation.

Understanding the implementation

Edge Function structure

The RESTful API Edge Function includes:

Router setup: Handling different HTTP methods and routes
Database connection: Connecting to Edge SQL
CRUD operations: Implementing create, read, update, delete operations
Error handling: Managing database and request errors
Response formatting: Returning consistent JSON responses

Key benefits

Performance: API processing at the edge reduces latency
Scalability: Automatically scales with demand
Global distribution: Edge locations provide worldwide coverage
Data consistency: Edge SQL ensures ACID compliance
Cost efficiency: Optimized data access and transfer

Troubleshooting

Common issues and solutions

Database connection errors: Verify Edge SQL credentials and connection string
API endpoint not found: Check routing configuration and deployment
SQL errors: Validate database schema and query syntax
Performance issues: Optimize SQL queries and database indexing

Debugging tips

Enable logging: Add comprehensive logging for debugging
Test locally: Use local development server to isolate issues
Monitor performance: Track API response times and database queries
Error handling: Implement proper error responses and status codes

Next steps

Implement advanced filtering and sorting
Add real-time updates with WebSockets
Integrate with authentication providers
Implement API rate limiting and caching
Add comprehensive API documentation with OpenAPI/Swagger