Build a RESTful tasks API with Functions and SQL Database

Create a database in SQL Database, write CRUD routes with Hono, and deploy a task API that runs as a function.

Last updated

View as Markdown Agent setup

In this tutorial, you will build a RESTful tasks API that runs as a function and keeps its records in SQL Database. You will create the database and its table, write the data layer and the Hono routes, deploy the project with Azion CLI, and call every endpoint.

The finished project is the restful-tasks example on GitHub.

Prerequisites

1. Create the database

The data layer addresses the database by name, and it falls back to tasks when no name is configured. Name the database tasks so that fallback resolves.

Send a POST request to the databases endpoint, replacing [TOKEN VALUE] with your personal token:

Shell
01curl --location 'https://api.azion.com/v4/workspace/sql/databases' \
02--header 'Authorization: Token [TOKEN VALUE]' \
03--header 'Content-Type: application/json' \
04--data {
05 "name": "tasks"
06}

The response carries the identifier of the database and its status:

JSON
01{
02 "state": "pending",
03 "data": {
04 "id": 118,
05 "name": "tasks",
06 "client_id": "6832h",
07 "status": "creating",
08 "created_at": "2024-04-18T11:22:59.468536Z",
09 "updated_at": "2024-04-18T11:22:59.468586Z",
10 "deleted_at": null
11 }
12}

Record the id. Every request that follows addresses the database by that value.

Creation is not instantaneous. Send GET requests to the same endpoint until status reads created. For the full set of database operations, refer to How to manage an SQL Database database.

2. Create the tasks table

The API stores one row per task, with a title, a completion flag, and two timestamps.

Both requests send a POST to the query endpoint. Replace <your-database-id> with the id of your database:

Create the table
Shell
01curl --location 'https://api.azion.com/v4/workspace/sql/databases/<your-database-id>/query' \
02--header 'Authorization: Token [TOKEN VALUE]' \
03--header 'Content-Type: application/json' \
04--data {
05 "statements": [
06 "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);"
07 ]
08}

The response reports one result object per statement:

JSON
01{
02 "state": "executed",
03 "data": [
04 {
05 "results": {
06 "columns": [],
07 "rows": []
08 }
09 }
10 ]
11}
Insert three rows

The endpoints need rows to return:

Shell
01curl --location 'https://api.azion.com/v4/workspace/sql/databases/<your-database-id>/query' \
02--header 'Authorization: Token [TOKEN VALUE]' \
03--header 'Content-Type: application/json' \
04--data {
05 "statements": [
06 "INSERT INTO tasks (title, completed) VALUES ('\''Complete API documentation'\'', FALSE);",
07 "INSERT INTO tasks (title, completed) VALUES ('\''Test function deployment'\'', FALSE);",
08 "INSERT INTO tasks (title, completed) VALUES ('\''Review security configurations'\'', TRUE);"
09 ]
10}

Three statements return three result objects:

JSON
01{
02 "state": "executed",
03 "data": [
04 {
05 "results": {
06 "columns": [],
07 "rows": []
08 }
09 },
10 {
11 "results": {
12 "columns": [],
13 "rows": []
14 }
15 },
16 {
17 "results": {
18 "columns": [],
19 "rows": []
20 }
21 }
22 ]
23}

The database holds the tasks table and three rows.

3. Create the project

Azion CLI scaffolds the project from a Hono template.

Authenticate the CLI

Run the login command and follow the prompts:

azion login

The CLI stores the credentials locally and authorizes every later command against your account.

Start the project

Run the init command:

azion init
Name the project

Accept the suggested name, or enter your own:

? Your applications name: (black-thor)
Select the Hono preset
Shell
01? Choose a preset: [Use arrows to move, type to filter]
02 Angular
03 Astro
04 Docusaurus
05 Eleventy
06 Emscripten
07 Gatsby
08 Hexo
09> Hono
10 Hugo
11 Javascript
12 ...
Select the Hono Boilerplate template
Answer N to the local development server
? Do you want to start a local development server? (y/N)
Answer N to the deploy prompt
? Do you want to deploy your project? (y/N)
Go to the project directory
cd <your-project-name>

The CLI creates the project directory with the template source. The build, dev, and deploy commands run from inside it.

4. Write the data layer

The data layer calls useQuery and useExecute from the azion/sql library. Both take the database name first and an array of SQL statements second. For the full library surface, refer to Azion SQL library.

Replace the contents of src/db.ts:

TypeScript
01import { useExecute, useQuery } from "azion/sql";
02
03const DATABASE_NAME = Azion.env.get("DATABASE_NAME") || "tasks";
04
05export interface Task {
06 id: number;
07 title: string;
08 completed: boolean;
09}
10
11export const getTasks = async (): Promise<Task[]> => {
12 const { data, error } = await useQuery(DATABASE_NAME, [
13 "SELECT * FROM tasks",
14 ]);
15 if (error) {
16 throw error;
17 }
18 return (
19 data?.results?.[0]?.rows?.map(
20 (row) =>
21 ({
22 id: row[0],
23 title: row[1],
24 completed: row[2] === 1,
25 } as Task)
26 ) || []
27 );
28};
29
30export const getTask = async (id: number): Promise<Task | null> => {
31 const { data, error } = await useQuery(DATABASE_NAME, [
32 `SELECT * FROM tasks WHERE id = ${id}`,
33 ]);
34 if (error) {
35 throw error;
36 }
37 return (
38 (data?.results?.[0]?.rows?.[0] &&
39 ({
40 id: data?.results?.[0]?.rows?.[0][0],
41 title: data?.results?.[0]?.rows?.[0][1],
42 completed: data?.results?.[0]?.rows?.[0][2] === 1,
43 } as Task)) ||
44 null
45 );
46};
47
48export const createTask = async (task: Omit<Task, "id">): Promise<Task> => {
49 const { data, error } = await useExecute(DATABASE_NAME, [
50 `INSERT INTO tasks (title, completed) VALUES ('${task.title}', ${task.completed}) RETURNING id`,
51 ]);
52 if (error) {
53 throw error;
54 }
55 const newId = data?.results?.[0]?.rows?.[0]?.[0] || "";
56 return { ...task, id: Number(newId) };
57};
58
59export const updateTask = async (
60 id: number,
61 task: Partial<Omit<Task, "id">>
62): Promise<Task | null> => {
63 const existingTask = await getTask(id);
64 if (!existingTask) {
65 return null;
66 }
67
68 const updatedTitle =
69 task.title !== undefined ? task.title : existingTask.title;
70 const updatedCompleted =
71 task.completed !== undefined ? task.completed : existingTask.completed;
72
73 const { data, error } = await useExecute(DATABASE_NAME, [
74 `UPDATE tasks SET title = '${updatedTitle}', completed = ${updatedCompleted} WHERE id = ${id}`,
75 ]);
76 if (error) {
77 throw error;
78 }
79 return { ...existingTask, title: updatedTitle, completed: updatedCompleted };
80};
81
82export const deleteTask = async (id: number): Promise<boolean> => {
83 const { data, error } = await useExecute(DATABASE_NAME, [
84 `DELETE FROM tasks WHERE id = ${id}`,
85 ]);
86
87 if (error) {
88 throw error;
89 }
90
91 return data?.state === "executed" || data?.state === "pending";
92};

The file exports one function per operation: getTasks, getTask, createTask, updateTask, and deleteTask. DATABASE_NAME falls back to tasks. To read a database under another name, set DATABASE_NAME in the .env file of the project.

5. Write the API routes

Hono binds each HTTP method and path to a handler. Every handler wraps its data call in a try block and returns 500 with a JSON error when the call throws.

Replace the contents of src/app.ts:

TypeScript
01import { Hono } from 'hono';
02import {
03 createTask,
04 deleteTask,
05 getTask,
06 getTasks,
07 updateTask,
08} from './db';
09
10const app = new Hono();
11
12app.get('/tasks', async (c) => {
13 try {
14 const tasks = await getTasks();
15 return c.json(tasks);
16 } catch (error) {
17 return c.json({ error: 'Failed to fetch tasks' }, 500);
18 }
19});
20
21app.get('/tasks/:id', async (c) => {
22 try {
23 const { id } = c.req.param();
24 const task = await getTask(Number(id));
25 if (!task) {
26 return c.json({ error: 'Task not found' }, 404);
27 }
28 return c.json(task);
29 } catch (error) {
30 return c.json({ error: 'Failed to fetch task' }, 500);
31 }
32});
33
34app.post('/tasks', async (c) => {
35 try {
36 const { title, completed } = await c.req.json();
37 const newTask = await createTask({ title, completed: completed || false });
38 return c.json(newTask, 201);
39 } catch (error) {
40 console.log(error);
41 return c.json({ error: 'Failed to create task' }, 500);
42 }
43});
44
45app.put('/tasks/:id', async (c) => {
46 try {
47 const { id } = c.req.param();
48 const { title, completed } = await c.req.json();
49 const updatedTask = await updateTask(Number(id), { title, completed });
50 if (!updatedTask) {
51 return c.json({ error: 'Task not found' }, 404);
52 }
53 return c.json(updatedTask);
54 } catch (error) {
55 return c.json({ error: 'Failed to update task' }, 500);
56 }
57});
58
59app.delete('/tasks/:id', async (c) => {
60 try {
61 const { id } = c.req.param();
62 const success = await deleteTask(Number(id));
63 if (!success) {
64 return c.json({ error: 'Task not found' }, 404);
65 }
66 return c.json({ message: 'Task deleted' });
67 } catch (error) {
68 return c.json({ error: 'Failed to delete task' }, 500);
69 }
70});
71
72export default app;

The file covers five routes:

Method and pathResult
GET /tasksReturns every task as a JSON array.
GET /tasks/:idReturns one task, or 404 with { "error": "Task not found" }.
POST /tasksCreates a task from title and completed, and returns it with 201.
PUT /tasks/:idUpdates title and completed, and returns the stored task.
DELETE /tasks/:idRemoves the task and returns { "message": "Task deleted" }.

6. Export the function handler

Azion runs the ES Modules handler: an object with a fetch method, exported as the default of the entry file. Hono supplies that method on the app instance. For the pattern and the legacy alternative it replaces, refer to Migrate handler patterns in Functions.

Replace the contents of src/index.ts:

TypeScript
01import app from './app';
02
03export default app;

The entry file exports the app, so every request reaches the Hono router, which matches it against the five routes.

7. Deploy the project

Run the deploy command from the project directory:

azion deploy

Azion opens Azion Console in the browser, where the deployment logs run until the build finishes. If the browser does not open, follow the link the CLI prints.

Azion builds the project and deploys it to the Azion Web Platform. The deployment returns a workload domain in the format https://xxxxxxx.map.azionedge.net. Propagation takes a few minutes, so wait before you send the first request.

8. Verify the endpoints

Replace <your-azion-domain> with the workload domain of the deployment.

Request the task list
curl https://<your-azion-domain>/tasks

The response carries the three rows inserted into the table:

JSON
01[
02 { "id": 1, "title": "Complete API documentation", "completed": false },
03 { "id": 2, "title": "Test function deployment", "completed": false },
04 { "id": 3, "title": "Review security configurations", "completed": true }
05]
Request one task
curl https://<your-azion-domain>/tasks/1

The response carries a single object:

{ "id": 1, "title": "Complete API documentation", "completed": false }
Create a task
Shell
01curl -X POST https://<your-azion-domain>/tasks \
02 -H "Content-Type: application/json" \
03 -d '{"title": "Write the deployment checklist", "completed": false}'

The response returns 201 with the stored task and the identifier the database assigned:

{ "title": "Write the deployment checklist", "completed": false, "id": 4 }
Update the task
Shell
01curl -X PUT https://<your-azion-domain>/tasks/4 \
02 -H "Content-Type: application/json" \
03 -d '{"title": "Write the deployment checklist", "completed": true}'

The response carries the task with the new values:

{ "id": 4, "title": "Write the deployment checklist", "completed": true }
Delete the task
curl -X DELETE https://<your-azion-domain>/tasks/4

The response confirms the removal:

{ "message": "Task deleted" }

The API creates, reads, updates, and deletes tasks from the deployed function, and every write reaches the database.

Next steps

SQL DatabaseThe ACID-compliant database behind the API, its architecture, and its limits.FunctionsWhat a function is, how it is invoked, and what it can reach during execution.Migrate handler patterns in FunctionsThe ES Modules handler, the Service Worker handler, and the patterns Azion rejects.Azion SQL libraryEvery method of azion/sql, with its parameters and return type.Local developmentRun the project on your own machine with the azion dev command.Troubleshoot function execution and logsFind the cause when the function never runs, stops early, or writes no logs.